formula to create list of unique items

Originally Posted by JBeaucaire

Two formulas together will do this, and both are array formulas. Let's assume that list is in column A.

Over in another column, let's use M, enter this formula in M1:

=INDEX($A$1:$A$1000,MATCH(0,COUNTIF($A$1:$A$1000,"<"&$A$1:$A$1000),0))
...and press CTRL-SHIFT-ENTER to activate an array in that cell, ENTER alone will not work.

Then in M2 enter this array formula:

=IF(COUNTIF($A$1:$A$1000,">"&M1),INDEX($A$1:$A$1000,MATCH(COUNTIF($A$1:$A$1000,"<="&M1),COUNTIF($A$1:$A$1000,"<"&$A$1:$A$1000),0)),"")
...and press CTRL-SHIFT-ENTER to activate an array in that cell, ENTER alone will not work.

Now, copy that second formula down as many cells as necessary to get the full list to appear, and maybe some extras for good measure.

NOTE: Side effect will also be the list created will be alphabetized.

Perfect, thank you.

Please edit the title

Sorry about that.

I'm bumping this up not because the previous solution didn't work, but because I'm trying to see if there is any other way of doing this that won't bog down the system as much.

Does anybody know of any other method?

Thanks for your help. I really appreciate it.

I do not think having a script running continuously in the background would be a good idea either, mainly the data source in this particular spreadsheet isn't updated individually.

In my spreadsheet, the user runs a macro to retrieve his/her account data from a website. This may include data on 2 days, it may have data on 30 days. Nothing is manually inputted.

What I'm trying to do with this is to provide some analysis on each individual date on this data... and by grabbing a "unique date list," I can use some other functions to match / analyze.

I understand that the way you mentioned earlier may already be the fastest way; I'm just making sure

Again, thakns for your help.. and I hope I'm clear in my description of the data.

Thanks.

Originally Posted by ChemistB

There are a number of ways that are not so "automatic" that can be done quickly and won't drain your system. Two that come to mind are the Advanced Filter or a dummy column that detects duplicates that you can then sort on.

Hmm, I like the dummy variable idea. I'll try it soon, and I'll post if it works.

An "on-demand" macro to create a list of unique dates from the column in question would not be resource intense. Having it run in the background wouldn't either. It doesn't sound like it's a lot of data (is it?), but this is doable many ways, including the way you've already got. Up to you if this is solved or not.

Its actually quite a lot of data- Most data sets are about 1000 rows, maybe 5-6 columns?

Thank you both again for your help. I'll post back shortly.

Originally Posted by JBeaucaire

The array I suggested above does it, but if you want a non-array solution, attached is an example of how something like that could work.

A helper column indicates the first instance of each unique name and supplies a unique "index number" for each name it flags. Colored in yellow.

The pink cells are the final list. A standard index/match formula finds the name associated with each index number. No array at all.

Whoa, its beautiful

I've just figured the COUNTIF out, and working through the index/match... but it looks like this will run a lot quicker.

I'll post an update once I decipher the formulas and input it in my my xls.

Thank you.

Originally Posted by JBeaucaire

The array I suggested above does it, but if you want a non-array solution, attached is an example of how something like that could work.

A helper column indicates the first instance of each unique name and supplies a unique "index number" for each name it flags. Colored in yellow.

The pink cells are the final list. A standard index/match formula finds the name associated with each index number. No array at all.

I didn't forget about this thread

I got everything to work, and yes, this method is a lot faster than the array method.

JBeaucaire-- you offered 3 solutions for me here. Thank you.

Perfect.... It worked.

Originally Posted by JBeaucaire

Two formulas together will do this, and both are array formulas. Let's assume that list is in column A.

Over in another column, let's use M, enter this formula in M1:

=INDEX($A$1:$A$1000,MATCH(0,COUNTIF($A$1:$A$1000,"<"&$A$1:$A$1000),0))
...and press CTRL-SHIFT-ENTER to activate an array in that cell, ENTER alone will not work.

Then in M2 enter this array formula:

=IF(COUNTIF($A$1:$A$1000,">"&M1),INDEX($A$1:$A$1000,MATCH(COUNTIF($A$1:$A$1000,"<="&M1),COUNTIF($A$1:$A$1000,"<"&$A$1:$A$1000),0)),"")
...and press CTRL-SHIFT-ENTER to activate an array in that cell, ENTER alone will not work.

Now, copy that second formula down as many cells as necessary to get the full list to appear, and maybe some extras for good measure.

NOTE: Side effect will also be the list created will be alphabetized.