Swap values in the rows and column based on given value that are extracted from a parent child data sheet

Status
Not open for further replies.

ukelele_kumar

New Member
Joined
May 17, 2021
Messages
2
Office Version
  1. 365
  2. 2019
  3. 2016
Platform
  1. Windows
  2. MacOS
  3. Web
I have a Excel table in the form below that was extracted from a separate sheet:

Level1Level1-Level2Level2Level2-Level3Level3Level3-Level4Level4
abcdfij
badeggk
bech
di
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:

ChildParent
bc
ad
be
df
eg
ch
di
ij
gk
I want the output in the 'repeated' form and the correspondence maintained as below:

Level1Level2Level3Level4
adij
begk
bch
adf
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.
 

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college
Status
Not open for further replies.

Forum statistics

Threads
1,141,818
Messages
5,708,768
Members
421,588
Latest member
Wawie

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top