I have a Excel table in the form below that was extracted from a separate sheet:
| Level1 | Level1-Level2 | Level2 | Level2-Level3 | Level3 | Level3-Level4 | Level4 |
|---|
| a | b | c | d | f | i | j |
| b | a | d | e | g | g | k |
| b | e | c | h | | |
| | | d | i | | |
The Level1 is parent records, Level2 is child records, Level3 is grandchild records and so on extracted from a separate sheet... Level1-Level2 is the value of the Level1 that corresponds to Level2 present in the separate sheet, Level2-Level3 is the value of the Level2 that corresponds to Level3 present in the separate sheet, and so on... We have the Level1-Level2 adjacent to Level2, Level2-Level3 adjacent to Level3,... at different position, present in that separate sheet. Values in Level1, Level2,.. are unique. while values in Level1-Level2, Level2-Level3 can be duplicate as in that separate sheet the parent has multiple child, i.e., Level1 has multiple Level2 placed at different rows in that separate sheet
the separate sheet was this:
| Child | Parent |
|---|
| b | c |
| a | d |
| b | e |
| d | f |
| e | g |
| c | h |
| d | i |
| i | j |
| g | k |
I want the output in the 'repeated' form and the correspondence maintained as below:
| Level1 | Level2 | Level3 | Level4 |
|---|
| a | d | i | j |
| b | e | g | k |
| b | c | h | |
| a | d | f | |
I was thinking of starting from Level4 in upper table, take each value from Level4 i.e., start with j and its corresponding value is i in the first table, so that to be replaced in the second table Level3 column, i to be replaced by f and then where i was present(E5) in the column Level3 in first table only, it is replaced by f in the same column. Swap like this for all columns. For that I was thinking if the correspondence{e.g., in Level3-Level4, the first value should be i, but since we have f in Level3; so the correspondence is not matched} is not matched, the relative address is placed at Level3 in first table at f and value is get to there using INDIRECT and ADDRESS function and it is replaced by i. Then, there are two i in Level3, we want to search the second i and replace that with f
And in the end if there is missing values, then input the VLOOKUP value in the previous column. for eg., in altered Level3 column, last value f has no value in Level2 in the first table, we input the VLOOKUP value in Level2 corresponding to f that is d
I am looking into this for few days now. Can this be solved using a macro. Any leads would be highly grateful.
In case of any clearance in the above question, please feel free to tell.
