• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

please help in vba macro..compare and merge data given in parent child hierarchy

vj523

New Member
Hello Everyone, i have 2 data table in parent child hierarchy as given below:

Now i need to create 1 table by merging both table. it should check the every child corresponding to the parent, if there are 2 child in first table and 2 child in other table for same parent then under the parent 4 child should be visible. please refer below table.
I am looking for a solution in vba macro.
In case of any question, please let me know.
Your expert advise would be very helpful for me.
Thanks in Advance.
 

Attachments

vletm

Excel Ninja
vj523
a) Could You write clear rules - how this should work?
b) Upload a sample Excel file ( if You would like to have 'vba macro' ) which has situation before and after.
 

vj523

New Member
Hi,
Thanks for your reply.
Please find attached excel sheet which contains input and expected output table.
For Example: There are 3 scenarios given in input data hierarchy 1 and hierarchy 2 table
1. We will start the data checking from hierarchy table 1. First it will pick first root i.e "AA", will search this root in table 2 if found then will check the child object of this.In this case child objects are different in both table so hierarchy is AA->AB->AC and AA->BB->AC. now we will merge all the child under the AA root as given below and in excel sheet also in merged data table

AA
AB
AC
BB
AC


2. now it will search 2nd root i.e. "AD" in table 2 and found child hierarchy is same in both table so we have written it 1 time in merged data table.

3. If any root from 1st table could not find in 2nd table or vice versa then will write the same hierarchy in same format in merged data.

All example given in attached excel sheet with highlighted color.

please let me know, in case of any question.

I have attached the excel file through "Attach Files" option but i am not sure it is attached or not because i can not see any attachment here. please let me know, if you are also not able to see the attached excel.
Thanks.
 
Last edited:

vj523

New Member
Hi,
I have done changes in values in given excel as compared to PNG just because to clear the requirement.
please find attached excel file that will be helpful to understand the requirement.
 

Attachments

vletm

Excel Ninja
vj523
with #3 reply
You're using terms like root and child object.
Should some only guess what would those mean?
Please reread #2 reply's a)...
 

vj523

New Member
Hi,
Thanks for your prompt response.
Data is given in parent child relationship in the attached excel. So here parent is called as root and child is called as child object.
For example from attached sheet: data is given as
AA
AB
AC
AD
AE
AF
AG
AH
AI

"AA" is parent of "AB" & "AC" so we called "AA" is root and "AB" & "AC" are child object of "AA".
"AD" is parent of "AE" & "AF" so we called "AD" is root and "AE" & "AF" are child object of "AD".
"AG","AH","AI" are child object of "AD".
please let me know for any further query.

Thanks.
 

vletm

Excel Ninja
vj523
There seems to be also some grandchild ... hmm?
Layout should be something like this..
There are marked needed columns.
Press [ Do It ] to get something ( it's animated because You tried to explain some steps ).
 

Attachments

vj523

New Member
Dear,
Thank you so much for your help and really it seems very helpful.

I clicked on "Do it" and under the "column needs for the output" two tables are coming and 2nd table is having some data as per expectations. But we need to consider some more cases in output:
1. If parent is same but child hierarchy is different so should be merged as given below:

Example: in the given excel, data is like AA ->AB ->AC (in 1st table)and AA ->BB ->AC (in 2nd table) then output should be like below. here hierarchy is not same under same parent (AA) so we need to merge it as given below:

AA
AB
AC
BB
AC

2. If parent is same and hierarchy is also same then should display in output table one time only as given below:
Example: in both the table having data like AD ->AE ->AF and this is common in both table because hierarchy is exactly same so in the output table will display it one time only like below
AD
AE
AF

3. input data row and column size is not constant so there can be unknown number of row or column.

Please let me know, incase of any query.

Thanks.
 

vletm

Excel Ninja
vj523
Do it same result as You've given with Your original sample? (snapshot below)
Screenshot 2020-01-17 at 20.09.38.png
You added two snapshots
... my eyes find those from above snapshots.
What would be Your message?

Your inputs have fixed amount of columns and those should be in fixed positions in the sheet.
No matter how many rows there would be - that's not a limit!

If even minor changes later from Your original 'hope',
then that would mean that everything should do from zero!
The zero means ... maybe You could image that ... who knows?
= You should focus before You've asked something

You asked: Please let me know, incase of any query.

... hmm?
... should I have?
... for me, it seems to work just as You wrote.
... ... or I could take those colors away ... hmm?
 

vj523

New Member
Hi,
Thanks for your kind support.
I am extremely Sorry for trouble, if rework would be required to make the solution generic in terms of unknown number of columns. I have given you as a example in excel sheet but data can be more bulky thats why i was trying to do it through vba macro to reduce the manual effort.

we can do one more thing, we can fix the number of columns also If it would be easy to implement.I mean to say, i m sure number of column would not be more then 10 in each input table so we can fix it.

when i am clicking on "Do It" it gives me 2 table and i am assuming 2nd table is the final output table but it contains some duplicate entry which we need to delete based on certain condition as above i have given example.
please find below snapshot for duplicate entry (red color highlighted).
65051

Color coding can we do in this way:
--If hierachry is common in both sheet then we can leave it without color
--if Hierarchy is present in table 1 but not exists in table 2 then we can give any other color to that entry.
--if hierarchy is present in table 2 but not exists in table 1 then we can given any other color.
65050

Thanks.
 

vletm

Excel Ninja
vj523
Have You totally forgot ...?

You added two snapshots
... my eyes find those from above snapshots.
What would be Your message?

Did You changed something or is everything same?
How Your #1 & #2 could be suddenly okay? ... hmm?

You're writing about 'we' ... hmm?
.. if more columns then all those 'table's will be in own sheet.

After [ Do it ] as I've written - ( it's animated because You tried to explain some steps ).
... and in the end of that 'show' ... there should be one 'table'.
hmm? if those two 'hierachies' should compare and merge ... that 'job' should do someway!
... if 'animated' then You could see some steps of that 'job'.

Colors
... now, You dream to add something which was ... or should I find that feature in the original 'hope'?
ps1 the 'AA-AB-AC' seems to be in top of both 'hierachy' ... and for Your eyes tells something else? ...hmm?
ps2 'BB-AC' ... in table2 but not exist in table2 ... yes?
>>> which file do You use?
 
Top