Excel 2007 : How to create a list that returns unique values

[Darlingdamsel]

Hi

I have seen a few posts like my problem, but none of the answers seem an exact fit – so here it goes.

I need to collate the results from several sheets, but in a nutshell – I want to generate a list but I only want items to be listed once.

Below is an example from 2 sheets.

Michigan Illinois
1 0 0
2 0 0
3 0 0
4 University of Michigan, Ann Arbor Illinois State University
5 0 University of Illinois at Urbana
6 0 0
7 0 0
8 0 0
9 0 0
10 0 0
11 0 0



What I want to generate a list that says

University of Michigan, Ann Arbor
Illinois State University
University of Illinois at Urbana

I thought about creating a centralised list with all the sheets like a above as a starting point then having a ‘#’ column and some how adding things together so the forumula doesn’t generate a value twice – but I just could not figure out the right way to programme it in.

Any and all ideas welcome!

[dip11]

I don't quite understand what you mean - an example workbook would help.

In terms of the general problem though, does the following work?
Copy all the results into 1 sheet (ideally 1 column if there are duplicates)

Click on Data - Remove Duplicates and select the column. Should now only have unique values.

[Darlingdamsel]

Hi Dip

The table in my post is what the data looks like ...

Column 1 has the universities numbered
Column 2 has state 1 if they are 'Hot' leads
Column 3 has state 2 if they are 'Hot' leads

# Michigan Illinois
1 0 0
2 0 0
3 0 0
4 University of Michigan, Ann Arbor, MI Illinois State University
5 0 University of Illinois at Urbana–Champaign
6 0 0
7 0 0
8 0 0
9 0 0
10 0 0
11 0 0
12 0 0


And the purpose of returning unique values automatically in a clean sheet is that I want to tag a Vlookup to the sheet and add some other numbers (such as enrollment), if the university shows up in the list. So it can't be manual process.

[dip11]

Hm, not an expert on this, but HaseebA replied this to a thread I created a long time ago:
http://www.excelforum.com/showthread...=1#post2662743

It didn't quite do what I wanted in that it skipped duplicate values, but that's exactly what you need. You would have to adjust the references in the formula accordingly of course. You only need the B24 formula.

[Darlingdamsel]

Hi Dip

Thanks for the reply. I went through the tread and did not find a solution as all the formulas were number based as far as I could tell? Plus, unfortunately none of the spreadsheets were live so I could not compare data sets.

I did not realise that I could attach a file and so attach is a cut out of mine.

Hopefully it makes sense?

[Darlingdamsel]

Just bumping ... I could really use a brilliant/clever solution that is soo beyond me. Smile.

Anyone?

Thanks!

[Darlingdamsel]

Hi All

I am still working on this. What I have realised is that it really comes down to whether a value listed on the hotlist can only be listed once. I think if I could describe it then the formula would be

"if the value is already listed on the hotlist, the the formula should look for the next value on the list that is not zero and list it."

Any ideas? If(A8>0,0,0) the latter part is straighforward. Its the initial part I struggle with.

Thanks!

[Haseeb Avarakkan]

Hi,

See the attached. Assume heading always will be in row 3 in each sheet. Currently formula only looking in column B:M on each sheet.

Hope this helps you.

[Darlingdamsel]

Hi Haseeb

Looks like my thank you disappeared? How odd! I must have done something wrong.

Thank you! Smile.

I missed the response and so am now only getting back. Can I ask you a couple of questions of how the formula works? Where is it looking up the 'sent' column? I don't see the reference? And can you put into words what it is doing? Its kind of beyond my excel skills ...

Thanks!