Formulate a list

[money n da sank]

I have a very large data base and need to condense it to a smaller format.

Currently, there is a list of people, and each person has a corresponding number (not shown in sample). Some names are listed more than once. I need to get to where I can sort the large data by either the names or numbers of the people, and then create the smaller sheet where each name is only listed once.

I know this is possible because I have done this before. I just wasn't able to save the file.

I have attached a simple sample spreadsheet, so if anyone who has an idea of how to do this wouldn't mind looking at it a bit, it would be greatly appreciated.

Thank you,

Jay Coulter

[JBeaucaire]

1) Put a "title" in B1 above your list of names
2) Highlight the data including the new "title"
3) Select Data > Filter > Advanced Filter
4) Use the settings:
[x] Copy to another location
Copy to: $I$5
[x] Unique values only

[money n da sank]

That works, but I'd like to be able to get the list automated with a formula, so that if the data in the feed changes then the list will automatically update itself.

I know this is possible it just may require a thick combination of functions to do so.

[JBeaucaire]

1) Put the list in column A with no blanks above or inside the list, start at A1

2) Put this array formula in H1:

=INDEX(A$1:A$100, MATCH(0,COUNTIF(A$1:A$100,"<"&A$1:A$100), 0))

...confirmed by pressing CTRL-SHIFT-ENTER. The word "Allen" should appear if you enter that correctly.

3) In H2 enter this array formula:

=IF(COUNTIF(A$1:A$100,">"&H1), INDEX(A$1:A$100, MATCH(COUNTIF(A$1:A$100,"<="&H1), COUNTIF(A$1:A$100,"<"&A$1:A$100),0)),"")

...confirmed by pressing CTRL-SHIFT-ENTER. The word "Anthony" should appear if you enter that correctly.

4) Copy cell H2 and paste in H3:H50...or as far down as you wish to go.

You will receive an alphabetized listing of the unique values in column A.

[JBeaucaire]

If that takes care of your need, please click EDIT in your original post, click GO ADVANCED and set the PREFIX box to SOLVED.

[money n da sank]

Very helpful. I'm about to start a new thread and I am taking it to the next step. Thank you for your help.