Listing all unique text combinations from three columns in a table

[shabalgoel]

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

[NBVC]

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.

[shabalgoel]

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?

[NBVC]

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

[shabalgoel]

That works! Thanks a lot for your help.