+ Reply to Thread
Results 1 to 9 of 9

VBA for automatically grouping rows based on text

  1. #1
    Registered User
    Join Date
    03-11-2009
    Location
    Seattle, WA
    MS-Off Ver
    Office 2007
    Posts
    58

    VBA for automatically grouping rows based on text

    Hello,

    I have a pivot table (vendor spend report) which has over 7000 rows of data. There are 38 vendors (some vendors appear multiple times i.e. Accenture Itay, Accenture Germany, etc.). Vendors names are in column B.

    I need to have the VBA script search for each of the vendors (and related names) and group them together. Also, last one is for all the MISC. vendors which need to be group together (one's that dont belong to the 38 managed vendors).

    Also, I need to rename the Group for each one; rather than Group1, I need to name it "Accenture All" etc...

    Any help would be appreciated.

    Thank you!!
    Last edited by Dannypak; 07-13-2009 at 10:54 AM.

  2. #2
    Forum Guru
    Join Date
    08-26-2007
    Location
    London
    Posts
    4,606

    Re: VBA for automatically grouping rows based on text

    I suggest you attach a sample workbook of your data and indication of how the names should be grouped. Could you not use a formula to include the group name and incorporate that into your pivot table?

  3. #3
    Registered User
    Join Date
    03-11-2009
    Location
    Seattle, WA
    MS-Off Ver
    Office 2007
    Posts
    58

    Re: VBA for automatically grouping rows based on text

    Hi,
    I've attached the Grouping Sample Worksheet...
    As you can see, the "Main Data" worksheet is just a alpha llisting..
    the "Grouped" worksheet is what I would like to have a macro(VBA script) automatically search for vendor name(s) and group similar names together: such as; all names starting with Accent*, BainP, etc...

    I also, manually add a space infront of the group name so that I can sort by alpha order to show grouped names first.

    Looking forward to your help :-)
    btw: actual pivot table too large to upload (copy/pasted values only)

    I'm looking to adjust the following code to select Range by searching for all rows containing certain text and using that as a range to select and rename. Also, can you use * wildcard to search for names containing characters that start with xxx*?

    Please Login or Register  to view this content.
    Attached Files Attached Files
    Last edited by Dannypak; 07-08-2009 at 11:56 AM.

  4. #4
    Forum Guru
    Join Date
    08-26-2007
    Location
    London
    Posts
    4,606

    Re: VBA for automatically grouping rows based on text

    So you need the VBA to generate the list of group names? Or do you have a list which the code could check through (did you generate the attached pivot table manually)? Does the list vary each time?

    If the former, it might be quite tricky as I think you would need some sort of rule for VBA to check, e.g. does it look at each cell and compare the first word in each.

    That would appear to work for e.g. Accenture, Accordo but would it work in all cases? That said, could be messy as it's not always "Accenture " but "Accenture(..." or "Accenture,...".

  5. #5
    Registered User
    Join Date
    03-11-2009
    Location
    Seattle, WA
    MS-Off Ver
    Office 2007
    Posts
    58

    Re: VBA for automatically grouping rows based on text

    Hi StephenR,

    Thanks for taking a look at this problem.

    Yes, the list is always same (although one may get deleted every now and then), there are total of 38 vendors. The problem is that the vendor name and account # are in a single cell (this is the way it needs to be kept). Therefore, those need to be grouped (sample wksht attached).

    This would only work if a wild card (*) can be used to do the search (i.e. Accent*). The code I wrote is handwritten and as you can see it just groups certain rows which doesn't do much for me since the row numbers always change.

    One last issue; some vendors are owned by another vendor which also need to be grouped under main vendor name. The main data needs to keep them separate but the folks who look at these reports like to see them grouped.

    I'm wanting to create a code that's similar to the one I handwrote, but one that picks rows based on TEXT* rather than cell reference.

    I hope this clears it up a bit more....

    Thank you!!!
    dan

  6. #6
    Forum Guru
    Join Date
    08-26-2007
    Location
    London
    Posts
    4,606

    Re: VBA for automatically grouping rows based on text

    OK, to tackle this I think one would need the list of 38 group names, and details of who owns who. Btw, I would not be inclined to use a pivot table as I have limited experience of automating them.

  7. #7
    Registered User
    Join Date
    03-11-2009
    Location
    Seattle, WA
    MS-Off Ver
    Office 2007
    Posts
    58

    Re: VBA for automatically grouping rows based on text

    Actually,
    I can copy the data from Piviot table into a new worksheet too. It really won't matter since all they want is to see them grouped.

    Here's the list of vendor names: each vendor has anywhere from 10 to 50+ rows which represent different account numbers that exist. so the important thing here is that I need to be able to search and select all rows that match characters+* (wild card) to capture all that belong to each group name.

    Accenture
    ADITI
    ALLYIS
    Archstone
    ARNOLD GROUP
    ATK: AT Kearney
    BAIN
    Bearing Point
    Booz
    BOSTON
    Ciber
    COGNIZANT
    Compucom
    CONGRUENT
    D & T
    DUFF & PHELPS
    E & Y
    EDS
    Fujitsu
    HCL
    Hitachi
    HP
    INFOSYS
    KATZENBACH
    KEYSTONE
    KPMG
    LG CONSULTING
    Market Bridge
    MCKINSEY
    Mercer HR
    MP TECH
    MURPHY & ASSOCIATES
    Oliver Wyman
    PEOPLEFIRM
    Point B
    PRTM
    PWc
    RESOURCES GLOBAL PROFESSIONALS
    REVEL CONSULTING
    SIEMENS
    Sogeti
    SOLUTIONSIQ
    TATA TCS
    TWO DEGREES
    UNISYS
    Vitalent
    VMC
    Watson Wyatt
    Wimmer
    Wipro
    Last edited by Dannypak; 07-08-2009 at 06:44 PM.

  8. #8
    Forum Guru
    Join Date
    08-26-2007
    Location
    London
    Posts
    4,606

    Re: VBA for automatically grouping rows based on text

    This is not the most efficient code, but I think it does what you want.
    Please Login or Register  to view this content.

  9. #9
    Registered User
    Join Date
    04-30-2010
    Location
    delhi
    MS-Off Ver
    Excel 2003
    Posts
    2

    Re: VBA for automatically grouping rows based on text

    Quote Originally Posted by StephenR View Post
    This is not the most efficient code, but I think it does what you want.
    Please Login or Register  to view this content.


    Hi,

    Further to this I have somewhat a smiliar problem. I have a list that is changed everytime. How can I modify the code such that it searches for similar words in the new list and groups them according to the situation quoted above

    Thanks
    Vishal

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Tags for this Thread

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