Extracting unique values across Columns & Rows

[Ramanan Arumugam]

Hi,

I have tried to extract the unique values across columns & rows without additional helper column & not able to avoid helper columns.

Please help me out to extract unique values without helper columns (Using worksheet functions only)

I have attached the sample file.

Regards,
Ramanan

 

[Sajan]

Hello Ramanan,
Following is one approach...

Assuming the Named Reference "data" refers to B2:F9,
then put the following array formula in cell J2 and copy down:
=INDEX(data,MIN(IF(COUNTIF($J$1:$J1,data),FALSE,ROW(data)-MIN(ROW(data))+1)),MATCH(TRUE,ISNA(MATCH(INDEX(data,MIN(IF(COUNTIF($J$1:$J1,data),FALSE,ROW(data)-MIN(ROW(data))+1)),0),$J$1:$J1,0)),0))

enter with Ctrl + Shift + Enter

Cheers,
Sajan.

 

[Lori]

If you wanted to return the results as a single array, you could try this formula I posted recently from formula challenge 11.

First define "m" to refer to the range B2:F9:

Then select the range J2:J11 and enter the following formula using CTRL+SHIFT+ENTER:

=DGET(OFFSET(INDIRECT(TEXT(MODE.MULT(IF(FREQUENCY(COUNTIF(m,"<="&m)+ISTEXT(m)*COUNT(m),COUNTIF(m,"<="&m)+ISTEXT(m)*COUNT(m))>={1,1},MODE.MULT(100000*ROW(m)+COLUMN(m),100000*ROW(m)+COLUMN(m)))),"r0c00000"),0),,,-2),1,OFFSET(m,ROWS(m),,2,1))

An alternative way to do this is to use a multiple consolidation pivot table (via Alt+D+P) with the range A1:F9 as input range then drag Value to the column position. This method will also automatically resize when you refresh the data.

BTW I also recommend Sajan's approach which is shorter than this one.