Macro to read excel files and sort data according to groups and sub groups in one workbook

Hi. I need help to solve my problem. I want to create a macro VBA that able to read the excel files and sort it in a structured manner in a new workbook. The macro will read the group first then it will read the sub group and stated which one are top level first. Here i provide 3 example files that the macro would read and the output i wanted it to be like the output file i uploaded below. Much appreciated if you can help me. If you not understand you may ask me.

Thank you.

Regards,
Afandi

little start
with first value of sub group in B7

cnange red part to your path

Please Login or Register  to view this content.

Kind regards
Leo

Thanks Leotaxi for helping.

The code really work like i want it to be but now i want to assume that i add another file that has group only without sub group which mean it isempty. The output produce some error. Hope you can help. Here i attach the error i faced when run the folder that contain the group only without the sub group.

Thanks

Regards,
Afandi

small modification

Please Login or Register  to view this content.

Kind regards
Leo

Hye Leotaxi,

Thank you very much for your help! I really appreciate it. Another one situation, can we make the one without the sub group is put under the different row. You may refer to the New Output I attach below. I nearly accomplished it thanks to you.

Thanks a lot.

Regards,
Afandi

change the if statement to

Please Login or Register  to view this content.

Kind regards
Leo

Superb LeoTaxi!

It works like a charm. Another situation I faced is when the group is not related to the sub group, can the group move to the other row just like the output you guide before? For example, the Excel File 1111.xls contain subgroup that is not relatable to the other group in Excel Files.

Thanks.

Regards,
Afandi

and when is a subgroup not related ?
we have to tel that to excel, to make any changes.


Kind regards
Leo

Hi Leo,

What i mean is the group 1111 and the subgroup 1010 does not exist in any groups from those 3. So, i want it to be separated just like group 1111 with an empty sub group just like the code u help me earlier.

Sorry for the inconvenience. Thanks.

Regards,
Afandi

and those 3 are always 111, 222 , 555 ?

Kind regards
Leo

Yes it is Leo, those 3 files will be the same group and sub group without any changes.

Regards,
Afandi

Hye Leo,

Any solution yet that you can help me? Thanks.

Regards,
Afandi

no i ame lost, dont know where to sort on

Kind regards
Leo

The sort is like the same as before but with an addition of a new group that does not contain any of the subgroup from those 3 group before. Before this you aid me to show the group in a new row with an empty sub group right? but this time i want it precisely the output like that when the macro detect the subgroup does not involve in any group or subgroup.

Thanks.

Regards,
Afandi

Maybe like this then ?

just change the name of the path



Kind regards
Leo

Hye Leo,

Great this is exactly the output like i wanted. Thanks a lot Leo. I tried to applied this code to the real environment which means my real data but it doesnt seem to work. Can you check it? Here i give you my archive of the folder that i need to sort on and the required output. I already change my path and the column from ws2.Range("B7") to ws2.Range("C9") but the output seems error.

Thanks

Regards,
Afandi

because there is no data in column C
only column where is data is column A
and i dont see group or subgroup in those, you have to tell me
i have things like

Type,Part,
Assembly Number,R0885-F0017-01,
Description,"ASSY,ATLAS PMC-SEM BRACKET WITH PCBA",
,,
,,
,,
,,
Level,Item,P/N
1,2,R0885-F0009-03
1,3,R0885-G0012-01
1,1,R0885-F0017-01-ME


Kind regards
Leo

Hi Leo,

Okay, the Assembly Number = Group and P/N = Subgroup. The Type,Part,Description,Level and Item you may ignore that column. The Macro just need to see Assembly Number and the P/N in order to produce the sort output.

Thanks.

Regards,
Firdaus

and witch 3 subgroups are regular ?

Sorry Leo, i could not understand when you mention "which 3 subgroups are regular?" . Do you mean subgroups that not related to the other groups in "Assemblies"?

in #10 i ask if the subgroups are always 111,222 or 555
now i see other subgroups
so 1 of the things that has to be done is change this line

Please Login or Register  to view this content.

to the real 3 subgroups.

so 2 things to change

the path and the line of subgroups like i said in previus post


Kind regards
Leo

Please Login or Register  to view this content.

Leo, how do you know that line of code to be put "111", "222" and "555" ? Can you explain to me so that i can research a bit with my data. Thanks.

yes 111 and 222 and 555 related subgroups before

so those 3 has to change to the real related subgroups



Kind regards
Leo

Please Login or Register  to view this content.

Okay but Leo i still could not understand based on my previous data with the 111, 444,888 and 1111 file. How do you know that line of code is not equal to 111, 222 and 555?
Please help me with this last line. Then it is solved.

Thanks.

Regards,
Afandi

Afandi,

see the next lines

Please Login or Register  to view this content.

column A = File name
Column B = Subgroup
Column C = If Related then "A" if not related then "B"

later in code i delete columns "A:C"

Kind regards
Leo