Juxtaposing successive levels from parent-child relational data

[Marbleking]

Hi! I am trying to reconstruct hierarchical relationships from exported data from a database which contains lots of rows. Some rows do not contain enough of the keys necessary to establish a parent-child relationship, but there are always at least one row and often several which do contain the necessary keys to establish a link between two levels in the hierarchy. There hierarchy consists of three levels, but all three levels may not yet have branched out completely at any given time.

The example in the attached workbook should make the task clearer. I have tried building up the formulas by using a lot of different array functions from all over the Interwebs, but haven't been able to arrive at something that works all the way and in a neat way.

I'd appreciate if anyone is able to crack the code for this.

Best regards,
Marbleking

[menem]

If you don't mind to use MANY of helper columns. >_<"
Please try this file (I can not explain about formula and flow, sorry)

Note : may not work with number store as text.

Regards.

[Marbleking]

Hi Menem! Thank you very much for this. This is a useful solution and one that doesn't grind Excel to a halt with big calculations.

I inserted an apostrophe before some of the numbers and your formulas still seemed to work. I would prefer the sorting order to be column 1, then column 2 and then column 3, though. It seems to be somewhat the other way around in your suggestion, with the blanks always at the bottom of each column. Column 1 should keep the values in order, and then the column 2 values that correspond to each of the column 1 values should be in order within themselves, meaning there might be gaps along the way in column 2. And column 3 should likewise be in order respective of the order in column 2.

If you are able to adjust for the sorting, it would be perfect.

Also, if anyone is able to come up with a solution that doesn't require helper columns, that would be exciting! Perhaps with the use of the new dynamic array functions, like UNIQUE, SORT, FILTER etc.

Thank you very much.

Regards,
Marbleking

[menem]

Please try this file.

Regards.

[AliGW]

Please try this file (I can not explain about formula and flow, sorry)

@ Menem

Sorry for off-topic interjection:

Although there is no official rule regarding this behaviour, we request that wherever possible both the question AND the answer be provided in substantive detail here within the thread. An attached workbook is an excellent aid for posing a question and offering a solution, but solely doing that with no in thread explanation makes it difficult for researchers to understand or consider the Q & A of this thread without downloading what may be a pointless doc to them, if they can do that at all. Doing that also hides the content from search engines so others may never benefit from this.

I'm sure you understand, and we look forward to seeing you post your formulas/macros in your posts for the searching benefit of all.

[Bo_Ry]

Please try at
F3

Please Login or Register  to view this content.

G3
=IFNA(INDEX($A$3:$A$24,MATCH(TRUE,COUNTIF(G$2:G2,$A$3:$A$24)<COUNTIFS($B$3:$B$24,$A$3:$A$24)*($B$3:$B$24=F3),)),INDEX($A$3:$A$24,MATCH(F3,$B$3:$B$24/ISNA(MATCH($A$3:$A$24,$G$2:G2,)),)))

H3
=IFNA(INDEX($A$3:$A$24,MATCH(G3,$B$3:$B$24/ISNA(MATCH($A$3:$A$24,H$2:H2,)),)),"")

all formula require Ctrl+Shift+Enter

[menem]

I will try my best to explain.

1. Create each 'Level' for each row.

'Level1'
G3
=IF(LEFT(C3,6)="Level1",A3,IF(LEFT(D3,6)="Level2",B3,IFERROR(VLOOKUP(B3,A:B,2,FALSE),"")))

'Level2'
H3
=IF(LEFT(D3,6)="Level2",A3,IF(LEFT(D3,6)="Level3",B3,""))

'Level3'
I3
=IF(LEFT(D3,6)="Level3",A3,"")

2. Check for how many data are list in table.

K3 (Have only L1)
=IF(LEFT(C3,6)="Level1",IF(COUNTIFS(D:D,"Level2*",B:B,A3)=0,1,0),0)

L3 (Have L1&L2)
=IF(LEFT(D3,6)="Level2",IF(COUNTIFS(D:D,"Level3*",B:B,A3)=0,1,0),0)

M3 (Have L1,L2,L3)
=IF(AND(G3<>"",H3<>"",I3<>""),1,0)

3. Generate rank order (sorted)

O3
=IF(SUM(K3:M3)>0,VALUE(TEXT(IFERROR(RANK(G3,G:G,1),0),"00000")&TEXT(IFERROR(RANK(H3,H:H,1),0),"00000")&TEXT(IFERROR(RANK(I3,I:I,1),0),"00000")),0)

4. Find 1st data rank (Don't copy down)
R3
=SMALL(O:O,COUNTIF(O:O,0)+1)

5. Check rank of 1st data (and others)
Q3
=RANK(R3,O:O,1)

6. Find 2nd and next
R4
=SMALL(O:O,Q3+1)

7. Find row of data (rank value)
S3
=IFERROR(MATCH(R3,O:O,0),0)

8. Display output (copy down and right 2 columns)
T3
=IF($S3>0,INDEX(G:G,$S3),"")

Regards.

[Marbleking]

Thanks a lot menem and Bo_Ry!

Menem: Since your formula in the O column uses the RANK funtion, which can only rank numbers, I had to insert three more helper columns to convert the data in the G, H and I column to numbers by using the NUMBERVALUE function. The data in these columns will be fetched from data exported from a database, where the serial numbers are stored as text.

Bo_Ry: I had to insert double negatives (--) in front of the lookup_value in the first Match function in your column H formula, to have it handle numbers coming from text.

I think both solutions are very impressive, but I tested them with inserting duplicate lines in my raw data table and then none of the solutions returned correct data at the correct place. If you insert a couple of rows somewhere in the table and copy some of the information from the row above down in the new rows, you will see what I mean. Each row in the results table should return a unique relationship between the three levels.

Would you be able to adjust your solutions to accommodate this? It would be fantastic!

Best regards,
Marbleking

[Bo_Ry]

Please try Power Query
Open Power Query/Get and Transform. Click on New Query.
Open blank query in the editor, launch Advanced Editor and paste in the following code.

Please Login or Register  to view this content.

[menem]

Could you please show me the sample of your real data pattern (number store as text) ?
I wish to chekc does they are the same in each row for same data (and find another way to convert to number - if possible)

Regards.

[Marbleking]

Hello!

Menem: I have attached a workbook containing actual data. (The database output may contain several other rows and columns that are not relevant to the problem we are currently discussing, which have been removed for the purpose of clarity).

Bo_Ry: I have tried your Power Query solution and it seems to work perfectly. Would you be able to adapt it to the updated data that I have attached?

Regards,
Marbleking

[Bo_Ry]

Please try

Please Login or Register  to view this content.

[Marbleking]

Hi Bo_Ry,

Thanks for the reply. There are a few issues with this solution. For example, if you look at Batch 15014976 (Cell A1077), you see that the Filter Code for this batch is HALB and the Deleted code is false. This means that this batch belongs to Level3 (because of what is written in the Filter Code and Deleted adjacent to this number). It also means that the batch2 number in column Batch2 (Cell B1077), which is 15009018, belongs to Level2 and is the parent of Batch 15014976. If we search for the value 15009018, we will also find it in the Batch column (Cell A966). The Filter Code and Deleted adjacent to this value is SOLUTION and false, respectively, verifying that this batch belongs to Level2. In Batch2 (cell B966) we find the value 15007796. This value is the parent of 15009018 (cell A966) and belongs in Level1. If we search for 15007796 in the Batch column, we will find it e.g. in cell A952. The qualifiers for Level1 values are not Filter Code and Deleted, but Material and Deleted. Only a select list of values in Material qualifies the value in Batch to be a Level1 value. The relevant Material values are listed in J7:J14. The relevant Deleted value is "false", as for all.

In this example, we had a number for all three levels, but often only Level1, Level1 and Level2, or Level2 exists. All Level1, Level2 and Level3 values will be found in column Batch, and all Level1 and Level2 values will also be found in column Batch2 if and when they become parent to a lower level. Level1 and Level2 may both have 0 or many children.

There may be many duplicate rows, and the Batch values may also be listed adjacent to irrelevant Batch2 values, but the use of correct values in Filter Code and Deleted should make it possible to not include them. The raw data table will include many columns and rows with various additional and related data that may make it difficult to use a simple remove duplicates function.

In your solution I see that you make no use of the qualifying codes. Also, your output table include listings like these:

Level1 - Level2 - Level3
15007796
15007796 - 15009018
15007796 - 15009018 - 15014976
15009018
15009018 - 15014976
15014976

But this would be the desired outcome for these three values:

Level1 - Level2 - Level3
15007796 - 15009018 - 15014976

Regards,
Marbleking

[menem]

Please verify, I think that these rows are L3 , but I can't find for L1 value of them

ie.
36
70
72
108
109
110
115
116
119
120

Regards.

[Marbleking]

Hi Menem,

If you filter the table this way, you'll see the various levels:

Apply filter "HALB" on Filter Code and "false" on Deleted: This gives you the Level3 values in column A (Batch) with the corresponding Level2 values in column B (Batch2).

Apply filter "SOLUTION" on Filter Code and "false" on Deleted: This gives you the Level2 values in column A (Batch) with the corresponding Level1 values in column B (Batch2).

Apply filter according to numbers in range J7:J14 on Material and "false" on Deleted: This gives you the Level1 values in column A (Batch).

And there may be duplicate responses to these filters. But the complete database export contains other columns too, which haven't been included in the example. Therefore, each row as a whole may not be an exact duplicate of any other row.

Best regards,
Marbleking

[AliGW]

Guys - a bit of thought, please!!!

Look at all the real estate taken up by the last two posts - don't do this again. Instead, provide Excel WBs containing your checklists. Thanks.

[Marbleking]

Hi AliGW! Apologies! Have a nice day! Regards, Marbleking

[AliGW]

I'd appreciate your both fixing it, please - you have made the thread really difficult to read. Thanks.

EDIT: Thanks for fixing yours, Marbleking.

[menem]

AliGW, sorry for my mistake. >_<

Marbleking, I'v use this logic for identify L1,L2,L3 or none.

IF Deleted is true or Batch is blank = none. (result as "" )
IF Batch not blank and len of Batch2 is 0 = L1
IF Found Batch2 in Batch = L2
Else = L3

as formula in G2
=IF(OR(E2="true",ISBLANK(A2)),"",IF(AND(NOT(ISBLANK(A2)),LEN(B2)=0),"L1",
IF(COUNTIF(A:A,B2)>0,"L2","L3")
))

Then I try to find each L's of each row.
I2 (L1)
=IF(G2="L1",A2,IF(G2="L2",B2,IF(G2="L3",VLOOKUP(B2,A:B,2,FALSE),"")))

J2 (L2)
=IF(G2="L2",A2,IF(G2="L3",VLOOKUP(A2,A:B,2,FALSE),""))

K2 (L3)
=IF(G2="L3",A2,"")

But found some of L1 are N/A#, so, I've stuck at this point. T_T

Regards.

[Marbleking]

Hello!

I have built the logic that I think gets the results that I want, using four x three helper columns and one x results columns.
However, the formulas required are very memory intensive and will not be practical for real applications.
The external database exports I am planning to use this logic on consists of between 100.000 and 500.000 rows of various data, of which approximately 500 to 2.000 rows contains the relevant data as reflected in the attached workbook.

I would therefore be very grateful if someone would be able to transform my example to something more efficient and robust, presumably using VBA, Power Query and/or improved functions.

Below are the formulas along with some explanation:

Section 1 (splitting two columns with data from three hierarchical levels into three columns):

A2:

Please Login or Register  to view this content.

B2:

Please Login or Register  to view this content.

C2:

Please Login or Register  to view this content.

Section 2 (filling in gaps in parent levels to connect as many of the three levels as possible by going backwards from higher to lower levels):

E2:

Please Login or Register  to view this content.

F2:

Please Login or Register  to view this content.

G2:

Please Login or Register  to view this content.

Section 3 (removing duplicate rows):

I2:

Please Login or Register  to view this content.

J2:

Please Login or Register  to view this content.

K2:

Please Login or Register  to view this content.

Section 4 (removing incomplete rows where more complete rows exist and inserting zeros for blanks):

M2:

Please Login or Register  to view this content.

N2:

Please Login or Register  to view this content.

O2:

Please Login or Register  to view this content.

Section 5 (sorting the resulting rows in ascending order by level 1, then by level 2 and lastly by level 3):

Q2:

Please Login or Register  to view this content.

R2:

Please Login or Register  to view this content.

S2:

Please Login or Register  to view this content.

I am looking forward to your replies. I am sure this solution can be improved significantly.

Best regards,
Marbleking

[Bo_Ry]

Just my guess and my last try

Please Login or Register  to view this content.

[Marbleking]

Thanks for your effort, Bo_Ry

Power Query may hold the solution to this conundrum, I'm sure. I am not too familiar with Power Query, so I am not able to identify how your code could be tweaked to yield the exact match with the formula/function version. But thanks again!

Perhaps someone else is able to work it out? I'd love to see.

Have a nice day,
Marbleking

[AliGW]

Thread closed in favour of one opened in the commercial services section.