+ Reply to Thread
Results 1 to 5 of 5

Listing all unique text combinations from three columns in a table

Hybrid View

  1. 07-17-2012, 06:24 AM #1
    shabalgoel
    shabalgoel is offline
    Registered User
    Join Date
    07-17-2012
    Location
    New Delhi, India
    MS-Off Ver
    Excel 2007
    Posts
    3

    Listing all unique text combinations from three columns in a table

    Hi all,

    I'm really sorry but in my angst I submitted this question in the Excel 2007 board a little earlier. My apologies, the mistake was unintentional and I thought I would repost it here, in the proper forum, to get more exposure.

    I'm pretty much an amateur at excel and have really been struggling at this. The problem is:

    1. On one sheet, I have a table, 'Client_Directory', that is manually input by the user.
    2. On another sheet, I would like excel to automatically create/populate a table, 'Segment_Directory', that would reference Client_Directory.
    3. Segment_Directory would have 5 columns:
    i. 'Segment ID' - this field should start with '1' and automatically increment everytime a new row is added
    ii. 'Industry'
    iii. 'Importance of Brand Security'
    iv. 'Volume'
    v. 'Number of clients'
    4. Columns ii, iii and iv should be populated with each unique combination of values that exists from the corresponding three columns in Client_Directory. E.g. if Client_Directory contains:

    Beverage Low Medium
    Beverage Low Medium
    Automobile Low Medium

    then columns ii, iii and iv in Segment_Directory should contain:

    Beverage Low Medium
    Automobile Low Medium

    5. Column v, 'Number of clients', should contain the number of instances of duplicates that exist in Client_Directory. E.g. in the example above, this would be '2' for the row 'Beverage Low Medium'.

    I am attaching the excel file I'm working on here, along with how the Segment_Directory should ideally look.

    Is there any excel function / VBA script that would help in this case? Thanks in advance for your help. If I didn't explain my question effectively, please do let me know.

    Cheers,
    Shabal
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  2. 07-17-2012, 09:07 AM #2
    NBVC
    NBVC is offline
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: Listing all unique text combinations from three columns in a table

    Try these formulas:

    In C14:

    =IF(D14="","",COUNTA($C$13:C13))
    in D14:

    =INDEX(Client_Directory[Industry],MATCH(1,IF(Client_Directory[Industry]&Client_Directory[Importance of Brand Security]&Client_Directory[Sales Volume]<>"",IF(ISNA(MATCH(Client_Directory[Industry]&Client_Directory[Importance of Brand Security]&Client_Directory[Sales Volume],$D$12:$D13&$E$12:$E13&$F$12:$F13,0)),1)),0))

    confirmed with CTRL+SHIFT+ENTER not just ENTER

    in E14:

    =INDEX(Client_Directory[Importance of Brand Security],MATCH(1,IF(Client_Directory[Industry]&Client_Directory[Importance of Brand Security]&Client_Directory[Sales Volume]<>"",IF(ISNA(MATCH(Client_Directory[Industry]&Client_Directory[Importance of Brand Security]&Client_Directory[Sales Volume],$D$12:$D13&$E$12:$E13&$F$12:$F13,0)),1)),0))

    confirmed with CTRL+SHIFT+ENTER not just ENTER

    in F14:

    =INDEX(Client_Directory[Sales Volume],MATCH(1,IF(Client_Directory[Industry]&Client_Directory[Importance of Brand Security]&Client_Directory[Sales Volume]<>"",IF(ISNA(MATCH(Client_Directory[Industry]&Client_Directory[Importance of Brand Security]&Client_Directory[Sales Volume],$D$12:$D13&$E$12:$E13&$F$12:$F13,0)),1)),0))

    confirmed with CTRL+SHIFT+ENTER not just ENTER

    in G14:

    =COUNTIFS(Client_Directory[Industry],D14,Client_Directory[Importance of Brand Security],E14,Client_Directory[Sales Volume],F14)
    confirmed with just ENTER.

    copy these formulas down the table.
    Where there is a will there are many ways.

    If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below left corner

    Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
    Reply With QuoteRegister To Reply
  3. 07-17-2012, 10:04 AM #3
    shabalgoel
    shabalgoel is offline
    Registered User
    Join Date
    07-17-2012
    Location
    New Delhi, India
    MS-Off Ver
    Excel 2007
    Posts
    3

    Re: Listing all unique text combinations from three columns in a table

    Thanks NBVC, that was really helpful. Your code does pretty much all I wanted. But it would be nice if the Segment Directory table could be automatically appended once a new unique combination is entered in the Client Directory, instead of having to drag down the table manually to populate the new row(s). Is there a way this can be done?
    Reply With QuoteRegister To Reply
  4. 07-17-2012, 10:11 AM #4
    NBVC
    NBVC is offline
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: Listing all unique text combinations from three columns in a table

    You would need VBA to do that... but not sure if it is necessary and it's not my realm anyway

    You can add error traps to the formulas, and copy down as far as you want... then the rows will be blank until data matches are found....

    So in D14:

    =IFERROR(INDEX(Client_Directory[Industry],MATCH(1,IF(Client_Directory[Industry]&Client_Directory[Importance of Brand Security]&Client_Directory[Sales Volume]<>"",IF(ISNA(MATCH(Client_Directory[Industry]&Client_Directory[Importance of Brand Security]&Client_Directory[Sales Volume],$D$12:$D13&$E$12:$E13&$F$12:$F13,0)),1)),0)),"")
    in E14:

    =IFERROR(INDEX(Client_Directory[Importance of Brand Security],MATCH(1,IF(Client_Directory[Industry]&Client_Directory[Importance of Brand Security]&Client_Directory[Sales Volume]<>"",IF(ISNA(MATCH(Client_Directory[Industry]&Client_Directory[Importance of Brand Security]&Client_Directory[Sales Volume],$D$12:$D13&$E$12:$E13&$F$12:$F13,0)),1)),0)),"")
    in F14:

    =IFERROR(INDEX(Client_Directory[Sales Volume],MATCH(1,IF(Client_Directory[Industry]&Client_Directory[Importance of Brand Security]&Client_Directory[Sales Volume]<>"",IF(ISNA(MATCH(Client_Directory[Industry]&Client_Directory[Importance of Brand Security]&Client_Directory[Sales Volume],$D$12:$D13&$E$12:$E13&$F$12:$F13,0)),1)),0)),"")
    each reconfirmed with CSE keys and copied down.

    G14:

    =IF(C14="","",COUNTIFS(Client_Directory[Industry],D14,Client_Directory[Importance of Brand Security],E14,Client_Directory[Sales Volume],F14))
    copied down
    Reply With QuoteRegister To Reply
  5. 07-17-2012, 10:33 AM #5
    shabalgoel
    shabalgoel is offline
    Registered User
    Join Date
    07-17-2012
    Location
    New Delhi, India
    MS-Off Ver
    Excel 2007
    Posts
    3

    Re: Listing all unique text combinations from three columns in a table

    That works! Thanks a lot for your help.
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1