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

The Level1 is parent records, Level2 is child records, Level3 is grandchild records and so on...
Level1-Level2 is the value of the Level1 that corresponds to Level2, Level2-Level3 is the value of the Level2 that corresponds to Level3, and so on...

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" that to be replaced in the Level3 column, "i" to be replaced by "f" and then where "i" was present(E5) in the column Level3, it is replaced by "f". Swap like this for all columns..For that I was thinking if the correspondence is not matched, the relative address is placed and value is get from there using INDIRECT and ADDRESS function..

and in the end if there is missing values, then input the vlokoup'd value in the previous column. eg., in altered Level3 column, last value "f" has no value in Level2, we input the vlookup'd value in Level2 corresponding to "f".

I am looking into this for few days now. Can this be solved using a macro. Any leads would be highly grateful. Thanks in advance. In case of any clearance in the above question, please feel free to tell.
 

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.

Forum statistics

Threads
1,144,572
Messages
5,725,071
Members
422,590
Latest member
Mikeyyy

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