multiple records

[ahhhmed]

Hi all,


In column A there is a list of items: numbers or names, but some are duplicated; eg


a

a

a

b

d

c

c

a

d

d

I want to make a new list in column B with no duplication

a

b

c

d

What formula do I use in B?

Thanx in advance

 

[Hui]

Use an Advanced Filter

Copy to another location

Criteria Range - leave blank

Copy Unique values only

 

[ahhhmed]

Thanks Hui,

I don't want to use filters. I am looking for a formula that does this automatically.

 

[asa]

Hi ahhhmed, good day!


Here's a formula you can use to extract unique values. It does not sort them, and it does not handle blanks in the list elegantly:

[pre]

=INDEX($A$2:$A$11,MATCH(0,COUNTIF(B$2:B2,$A$2:$A$11),0))
It assumes your list is in Column A, ranging from A2:A11, and your unique values list is in column B.  Put the above formula in cell [b]B3.


In cell B2[/b], put:

[pre][code]=$A$2

[/pre]
You will see #NA! errors after all the unique values in the list. To eliminate them and leave those cells blank, you can use IFERROR (Excel 2007+) or ISERROR. Here's the mod for Excel 2007+:

=IFERROR(INDEX($A$2:$A$11,MATCH(0,COUNTIF(B$2:B2,$A$2:$A$11),0)),"&#34[/code][/pre]
For a more sophisticated formula that will sort your unique values alphabetically:

Create a unique distinct alphabetically sorted list, extracted from a column in excel


Similar, but removing blanks and sorting numbers numerically and text alphabetically:

Create a unique distinct sorted list containing both numbers text removing blanks in excel


Similar, but working on a two-dimensional (multi-row, multi-column) range:

Excel 2007/2010 array formula: Filter unique distinct values, sorted and blanks removed


Asa

 

[ahhhmed]

I have my original list in B19:B45

I have my shortened list in C80:C85


I use this array in C80


=INDEX(List6; MATCH(MIN(IF(ISBLANK(List6)+COUNTIF(B1:$B$1; List6); ""; IF(ISNUMBER(List6); COUNTIF(List6; "<"&List6); COUNTIF(List6; "<"&List6)+SUM(IF(ISNUMBER(List6); 1; 0))+1))); IF(ISBLANK(List6)+COUNTIF(B1:$B$1; List6); ""; IF(ISNUMBER(List6); COUNTIF(List6; "<"&List6); COUNTIF(List6; "<"&List6)+SUM(IF(ISNUMBER(List6); 1; 0))+1)); 0))


But it gives me only one value and repeats it down the list


Can you amend the array for me, please?

 

[fred]

how about simply use a pivot table on column A. then on the pivot table copy from column A the summary of product and paste as value back on column B on the original worksheet? Wouldn't that be simple enough?

 

[ahhhmed]

I need the calculation to be automatic because the shortened list is a base for other lists.

 

[asa]

Hi ahhhmed,

Here you go.


Just make sure cell C79 is blank or at least does not contain any value that could be in your List6 range.


Enter the formula like this in cell C80.

=INDEX(List6, MATCH(MIN(IF(ISBLANK(List6)+COUNTIF(C$79:$C79, List6), "", IF(ISNUMBER(List6), COUNTIF(List6, "<"&List6), COUNTIF(List6, "<"&List6)+SUM(IF(ISNUMBER(List6), 1, 0))+1))), IF(ISBLANK(List6)+COUNTIF(C$79:$C79, List6), "", IF(ISNUMBER(List6), COUNTIF(List6, "<"&List6), COUNTIF(List6, "<"&List6)+SUM(IF(ISNUMBER(List6), 1, 0))+1)), 0))

Ctrl-Shift-Enter


Then copy the formula down as far as you like.


Asa

 

[Luke M]

If there's no blanks in the list, you can shorten the formula to this.

Putting formula into B2:

=IF(AND(ISNUMBER(MATCH(List6,B$1:B1,0))),"",INDEX(A:A,MIN(IF(ISNA(MATCH(List6,B$1:B1,0)),ROW(List6)))))


Note that this is an array formula.

 

[ahhhmed]

The formula works very well luke, but the longer one has many advantages:

it ignores blanks, it puts items in alphabetical order, and it does not show errors