1

Ive got column A in spreadsheet with lots of repeat data, i want to write a formula to generate a report of each unique piece of data.

Eg. in column A :

  • sam
  • sam
  • sam
  • tom
  • mike
  • tom
  • mike

The report would output :

  • sam
  • tom
  • mike

Previously i was in column B writing a2=a1 then filtering column B on "FALSE" i could then highlight the remaining values in column A to the unique values, as it would only show "FALSE" where the data was different to the cell above, but this only works when all the data is sorted so it all sites together.

sam
  • 9,486
  • 36
  • 109
  • 160
  • Possible duplicate of [Getting unique values in Excel by using formulas only](https://stackoverflow.com/questions/1429899/getting-unique-values-in-excel-by-using-formulas-only) – iDevlop Sep 20 '17 at 12:44

3 Answers3

1

You should use this array formula =IFERROR(LOOKUP(2,1/(COUNTIF($B$1:B1,$A$2:$A$8)=0),$A$2:$A$8),"") in cell B2, in case your values are in column A. Please enter this formula with a combination of CTRL+SHIFT+ENTER. Then you can simply drag your formula down in order to get all distinct values.

enter image description here

Kresimir L.
  • 2,301
  • 2
  • 10
  • 22
1

You can also try without Lookup function =IFERROR(INDEX($A$2:$A$8, MATCH(0,COUNTIF($C$1:C1, $A$2:$A$8), 0)),"")

enter image description here

akashmendapara
  • 21
  • 5
0

Another option, using non-array formula

=IFERROR(INDEX($A$2:$A$8,MATCH(0,INDEX(COUNTIF($B$1:B1,$A$2:$A$8),0,0),0)),"")

Drag/Copy down formula as required.

enter image description here

Mrig
  • 11,612
  • 2
  • 13
  • 27