Relate rows (child/ parent), secure relations for sorting

[Cornelius]

Hi everyone,

i already searched a lot and it seems like there is no build-in
function in order to relate rows in an excel table.
Although, I am quite certain that a lot of users are confronted with
this or a similar kind of problem. So maybe anyone can share some
advice/ best-practice ...

The Scenario:
I have a table with investments and subinvestments. Users need to
modify and add sub-/ investments. They also want to filter, sort and
search for a certain investment (that's why I use tables).
Therefore I need to develop a solution in order to keep related data
together. As an Investment has a unique name, from a data-perspective
the relation can easily be made using the investment-name and the
column for "sub-/ investment" (child, parent). But what needs to be
done in order to keep them related in excel? Can I modify the standard
sorting behaviour in a certain way?

Also, would a different sorting destroy the standard-order of the
rows. I could therefore introduce a new column with an incremental
number (ID). Unforunately when somebody wants to enter a new
subinvestment, I either need to force the user to insert the new data
at the end of the table (then the user needs to filter in order to
view all rows for a investment alltogether) or I need to write a makro
that updates all the IDs when a new row is inserted somewhere.

Please feel free to share any kind of idea that pops into your mind.
Regards,

Cornelius

 

[Roger Govier]

Hi Cornelius
I'm not sure whether I am understanding you problem correctly.

If you have the Name in column A and the first value relating to that name
in column B, and subsequently another value of the Name in column A, but
it's second value in column B, its third in column C and so on, then you
could do the following

Insert a new column at B
In B1 enter
=A1&"_"&MATCH(99^99,C1:Z1)
and copy down accordingly

Now if you do your sort on Column B, all of the data for each Name will be
together

--

Regards
Roger Govier

Hi everyone,

i already searched a lot and it seems like there is no build-in
function in order to relate rows in an excel table.
Although, I am quite certain that a lot of users are confronted with
this or a similar kind of problem. So maybe anyone can share some
advice/ best-practice ...

The Scenario:
I have a table with investments and subinvestments. Users need to
modify and add sub-/ investments. They also want to filter, sort and
search for a certain investment (that's why I use tables).
Therefore I need to develop a solution in order to keep related data
together. As an Investment has a unique name, from a data-perspective
the relation can easily be made using the investment-name and the
column for "sub-/ investment" (child, parent). But what needs to be
done in order to keep them related in excel? Can I modify the standard
sorting behaviour in a certain way?

Also, would a different sorting destroy the standard-order of the
rows. I could therefore introduce a new column with an incremental
number (ID). Unforunately when somebody wants to enter a new
subinvestment, I either need to force the user to insert the new data
at the end of the table (then the user needs to filter in order to
view all rows for a investment alltogether) or I need to write a makro
that updates all the IDs when a new row is inserted somewhere.

Please feel free to share any kind of idea that pops into your mind.
Regards,

Cornelius

__________ Information from ESET Smart Security, version of virus
signature database 5641 (20101123) __________

The message was checked by ESET Smart Security.

http://www.eset.com

__________ Information from ESET Smart Security, version of virus signature database 5641 (20101123) __________

The message was checked by ESET Smart Security.

http://www.eset.com

 

[Cornelius]

Hi Roger,

thanks for your reply. I actually found a way to get rid of the child/
parent-relation, so problem solved in a different way.
I guess that's the best solution as Excel is not to be used for
relational data-modeling.

One question about your function though:
It concatenates the name with the Index of highest number to be found
in a certain row: <name>_<Array-Index>
So the sorting might change from time to time as numbers change and it
could also result in same identifiers for all investments/
subinvestments, if the column having the highest number is the same
for every row. This way just sorting by name would do the same job. Am
I right?

Regards, Cornelius