+ Reply to Thread
Results 1 to 27 of 27

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

  1. #1
    Registered User
    Join Date
    12-23-2015
    Location
    Malaysia
    MS-Off Ver
    2007
    Posts
    16

    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
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    08-16-2015
    Location
    Antwerpen, Belgium
    MS-Off Ver
    2007-2016
    Posts
    2,372

    Re: Macro to read excel files and sort data according to groups and sub groups in one work

    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

  3. #3
    Registered User
    Join Date
    12-23-2015
    Location
    Malaysia
    MS-Off Ver
    2007
    Posts
    16

    Re: Macro to read excel files and sort data according to groups and sub groups in one work

    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
    Attached Files Attached Files

  4. #4
    Forum Expert
    Join Date
    08-16-2015
    Location
    Antwerpen, Belgium
    MS-Off Ver
    2007-2016
    Posts
    2,372

    Re: Macro to read excel files and sort data according to groups and sub groups in one work

    small modification

    Please Login or Register  to view this content.
    Kind regards
    Leo

  5. #5
    Registered User
    Join Date
    12-23-2015
    Location
    Malaysia
    MS-Off Ver
    2007
    Posts
    16

    Re: Macro to read excel files and sort data according to groups and sub groups in one work

    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
    Attached Files Attached Files

  6. #6
    Forum Expert
    Join Date
    08-16-2015
    Location
    Antwerpen, Belgium
    MS-Off Ver
    2007-2016
    Posts
    2,372

    Re: Macro to read excel files and sort data according to groups and sub groups in one work

    change the if statement to

    Please Login or Register  to view this content.
    Kind regards
    Leo

  7. #7
    Registered User
    Join Date
    12-23-2015
    Location
    Malaysia
    MS-Off Ver
    2007
    Posts
    16

    Re: Macro to read excel files and sort data according to groups and sub groups in one work

    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
    Attached Files Attached Files

  8. #8
    Forum Expert
    Join Date
    08-16-2015
    Location
    Antwerpen, Belgium
    MS-Off Ver
    2007-2016
    Posts
    2,372

    Re: Macro to read excel files and sort data according to groups and sub groups in one work

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


    Kind regards
    Leo

  9. #9
    Registered User
    Join Date
    12-23-2015
    Location
    Malaysia
    MS-Off Ver
    2007
    Posts
    16

    Re: Macro to read excel files and sort data according to groups and sub groups in one work

    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

  10. #10
    Forum Expert
    Join Date
    08-16-2015
    Location
    Antwerpen, Belgium
    MS-Off Ver
    2007-2016
    Posts
    2,372

    Re: Macro to read excel files and sort data according to groups and sub groups in one work

    and those 3 are always 111, 222 , 555 ?

    Kind regards
    Leo

  11. #11
    Registered User
    Join Date
    12-23-2015
    Location
    Malaysia
    MS-Off Ver
    2007
    Posts
    16

    Re: Macro to read excel files and sort data according to groups and sub groups in one work

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

    Regards,
    Afandi

  12. #12
    Registered User
    Join Date
    12-23-2015
    Location
    Malaysia
    MS-Off Ver
    2007
    Posts
    16

    Re: Macro to read excel files and sort data according to groups and sub groups in one work

    Hye Leo,

    Any solution yet that you can help me? Thanks.

    Regards,
    Afandi

  13. #13
    Forum Expert
    Join Date
    08-16-2015
    Location
    Antwerpen, Belgium
    MS-Off Ver
    2007-2016
    Posts
    2,372

    Re: Macro to read excel files and sort data according to groups and sub groups in one work

    no i ame lost, dont know where to sort on

    Kind regards
    Leo

  14. #14
    Registered User
    Join Date
    12-23-2015
    Location
    Malaysia
    MS-Off Ver
    2007
    Posts
    16

    Re: Macro to read excel files and sort data according to groups and sub groups in one work

    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

  15. #15
    Forum Expert
    Join Date
    08-16-2015
    Location
    Antwerpen, Belgium
    MS-Off Ver
    2007-2016
    Posts
    2,372

    Re: Macro to read excel files and sort data according to groups and sub groups in one work

    Maybe like this then ?

    just change the name of the path



    Kind regards
    Leo
    Attached Files Attached Files

  16. #16
    Registered User
    Join Date
    12-23-2015
    Location
    Malaysia
    MS-Off Ver
    2007
    Posts
    16

    Re: Macro to read excel files and sort data according to groups and sub groups in one work

    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
    Attached Files Attached Files

  17. #17
    Forum Expert
    Join Date
    08-16-2015
    Location
    Antwerpen, Belgium
    MS-Off Ver
    2007-2016
    Posts
    2,372

    Re: Macro to read excel files and sort data according to groups and sub groups in one work

    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

  18. #18
    Registered User
    Join Date
    12-23-2015
    Location
    Malaysia
    MS-Off Ver
    2007
    Posts
    16

    Re: Macro to read excel files and sort data according to groups and sub groups in one work

    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

  19. #19
    Forum Expert
    Join Date
    08-16-2015
    Location
    Antwerpen, Belgium
    MS-Off Ver
    2007-2016
    Posts
    2,372

    Re: Macro to read excel files and sort data according to groups and sub groups in one work

    and witch 3 subgroups are regular ?

  20. #20
    Registered User
    Join Date
    12-23-2015
    Location
    Malaysia
    MS-Off Ver
    2007
    Posts
    16

    Re: Macro to read excel files and sort data according to groups and sub groups in one work

    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"?

  21. #21
    Forum Expert
    Join Date
    08-16-2015
    Location
    Antwerpen, Belgium
    MS-Off Ver
    2007-2016
    Posts
    2,372

    Re: Macro to read excel files and sort data according to groups and sub groups in one work

    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.

  22. #22
    Forum Expert
    Join Date
    08-16-2015
    Location
    Antwerpen, Belgium
    MS-Off Ver
    2007-2016
    Posts
    2,372

    Re: Macro to read excel files and sort data according to groups and sub groups in one work

    so 2 things to change

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


    Kind regards
    Leo
    Attached Files Attached Files

  23. #23
    Registered User
    Join Date
    12-23-2015
    Location
    Malaysia
    MS-Off Ver
    2007
    Posts
    16

    Re: Macro to read excel files and sort data according to groups and sub groups in one work

    Ignore this post. See #24

    Thanks Leo.
    Attached Files Attached Files
    Last edited by Afandi1992; 01-13-2016 at 06:00 AM. Reason: Mistaken for

  24. #24
    Registered User
    Join Date
    12-23-2015
    Location
    Malaysia
    MS-Off Ver
    2007
    Posts
    16

    Re: Macro to read excel files and sort data according to groups and sub groups in one work

    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.

  25. #25
    Forum Expert
    Join Date
    08-16-2015
    Location
    Antwerpen, Belgium
    MS-Off Ver
    2007-2016
    Posts
    2,372

    Re: Macro to read excel files and sort data according to groups and sub groups in one work

    yes 111 and 222 and 555 related subgroups before

    so those 3 has to change to the real related subgroups



    Kind regards
    Leo

  26. #26
    Registered User
    Join Date
    12-23-2015
    Location
    Malaysia
    MS-Off Ver
    2007
    Posts
    16

    Re: Macro to read excel files and sort data according to groups and sub groups in one work

    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

  27. #27
    Forum Expert
    Join Date
    08-16-2015
    Location
    Antwerpen, Belgium
    MS-Off Ver
    2007-2016
    Posts
    2,372

    Re: Macro to read excel files and sort data according to groups and sub groups in one work

    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

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Sort Data into Groups
    By Agro12 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 01-15-2015, 06:58 AM
  2. [SOLVED] Macro that can sort and copy groups of data into separate spreadsheets
    By glide2131 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 12-12-2014, 03:12 PM
  3. Replies: 1
    Last Post: 10-10-2012, 11:35 AM
  4. Can't figure out how to sort groups of data
    By Sathed in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 05-01-2012, 11:51 AM
  5. Sort two different groups of data
    By darkjedi in forum Excel General
    Replies: 2
    Last Post: 02-09-2012, 05:12 PM
  6. Need to sort groups of data by name or date
    By mockturtle29 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 11-08-2011, 07:29 PM
  7. sort by groups in excel
    By staceyc in forum Excel General
    Replies: 3
    Last Post: 03-08-2006, 06:10 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1