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