Reducing a list of numbers

  • Thread starter Thread starter Ernie Fenwick
  • Start date Start date
E

Ernie Fenwick

How can I reduce a list of numbers with several
repetitions to a list only showing 1 instance of each
number. ie 3,5,7,3,4,8,4,3,6,5 to 3,4,5,6,7,8

Thanks

Ernie Fenwick
 
You can use a formula in the next column, that will return the value of the
list-item if there is only 1 of that value in that list.

It's called extracting unique entries from a list, and you can find
information here.
http://www.cpearson.com/excel/duplicat.htm#ExtractingUnique

If you wanted to automate it, you could either use this formula in a macro,
then do the following copying/pasting over your current list which contains
duplicates, or do the whole thing in visual basic, but the formula would be
the easiest way to go.
 
If they are in one cell, use data>text to columns and comma as delimiter,
then apply advanced>filter unique records only and copy to another location.
If they are in separate cells repeat the latter part but not the data>text
to columns..
 
You can do that in one fell swoop, assuming the numbers are in
A2:A20, array enter this formula in B2 and copy down until you get
a zero (in case there is a zero in the list then copy down until you get an
error)

=INDEX($A$2:$A$20,MATCH(0,COUNTIF($B$1:B1,$A$2:$A$20),0))

array enter = ctrl + shift & enter

works for text as well

Note that the formula has to be entered in the same column one cell below as
the first countif
reference
 
How can I reduce a list of numbers with several
repetitions to a list only showing 1 instance of each
number. ie 3,5,7,3,4,8,4,3,6,5 to 3,4,5,6,7,8
Click to expand...

With worksheet functionality only, if the whole list has each number in a
separate cell in A1:A10, you could enter the following formulas in B1 and B2.

B1: =A1
B2: =INDEX($A$1:$A$10,MATCH(0,COUNTIF(B$1:B1,$A$1:$A$10),0))

Select B2 and fill it down until formulas evaluate #N/A.
 
Think Harlan missed out <g> mentioning that
his formula in B2 needs to be array-entered

Hold down Ctrl & Shift keys, press Enter
(instead of just pressing Enter)
 
Back
Top