Excel Wizards - Hear my plea! [please close]

Enc

I have a collumn of cells with strings of numbers, nearly 50k total cells so far and still growing. I know there are probably somewhere between 10 and 30k unique records that have repetition 1-10 times, possibly more, depending on the record.

I need a way to extremely quickly identify and count unique record touches only. Not extremely quickly as in today, I'm still adding to the list and will be until probably after midnight tonight. But tomorrow morning I will need to then validate this list and get it sorted down to just the unique records by noon or so.

Of course I could do that manually by sorting by number and visual checking and deleting lines, but frankly with the volume that will take way too long. I need excel wizardry here.

Halp.

Simpsonia

There's a remove duplicates button. Just clone your data, remove dupes, look at last populated row for count. https://support.office.com/en-us/article/find-and-remove-duplicates-00e35bea-b46a-4d5d-b28e-66a552dc138d

finnith

If you have the most recent release of excel you can also try using the UNIQUE() function.

bwanie

another way is (if unique function doesn't get you what you want):

sort the complete list by the value you want to look for, then add a function that compares your current to previous cells and fill in a "1" where they do not match and a "0" where they do. Copy as value and then filter the "1"s

mts

alternatively, if they are distinct known values you could set up an countif match function

Enc

Simpsonia wrote: »

There's a remove duplicates button. Just clone your data, remove dupes, look at last populated row for count. https://support.office.com/en-us/article/find-and-remove-duplicates-00e35bea-b46a-4d5d-b28e-66a552dc138d

This is exactly what I'll need, thanks for the heads up! Never knew that was there and, frankly, never needed to before today.

Thundyrkatz

Could you use a pivot table?

Corvus

Enc wrote: »

I have a collumn of cells with strings of numbers, nearly 50k total cells so far and still growing. I know there are probably somewhere between 10 and 30k unique records that have repetition 1-10 times, possibly more, depending on the record.

I need a way to extremely quickly identify and count unique record touches only. Not extremely quickly as in today, I'm still adding to the list and will be until probably after midnight tonight. But tomorrow morning I will need to then validate this list and get it sorted down to just the unique records by noon or so.

Of course I could do that manually by sorting by number and visual checking and deleting lines, but frankly with the volume that will take way too long. I need excel wizardry here.

Halp.

Insert Pivot Table, Add to Data Model, Unique Count might work.

Inquisitor77

Pivot table would've been my first approach, too, just because it's so handy that it's basically my default. It has the added benefit of being able to quickly give you an actual count of how many times a unique record shows up, among other things.

Enc

Thanks, all! I got what I needed here.

This can be closed.