Extract unique values in a row excel

I

Ingolf

Banned

Joined

Mar 20, 2011

Messages

809

• Oct 29, 2014

• #2

Try this formula in R2 and drag to right and down

=IFERROR(INDEX($B2:$P2,MATCH(1,INDEX((COUNTIF($Q$2:Q2,$B2:$P2)=0)*($B2:$P2<>""),0),0)),"")

Enter this formula with Control+Shift+Enter

 

Upvote 0

B

BobUmlas

Well-known Member

Joined

Mar 14, 2002

Messages

1,181

• Oct 29, 2014

• #3

You need a UDF (User-Defined Function ) -- extraction unique values has always been a vertical process. If you can't copy/paste transpose these values, you can use this function:
Function Unique(rg As Range, which)
Dim cl As New Collection
On Error Resume Next
For Each x In rg
If Len(x) > 0 Then cl.Add x, CStr(x)
Next
Unique = cl(which)
If Unique = 0 Then Unique = ""
End Function

In the worksheet, for row2 you'd enter this somewhere to the right of your data =Unique($B2:$P2,COLUMN(A1)) and fill right as far as you think you need, and fill down.

 

Upvote 0

B

BobUmlas

Well-known Member

Joined

Mar 14, 2002

Messages

1,181

• Oct 29, 2014

• #4

But if the user has data which extends to Z2, your formula doesn't work in AB2 - the user would have to understand how it works & make corrections.

 

Upvote 0

E

Excel98402

New Member

Joined

Oct 29, 2014

Messages

5

• Oct 29, 2014

• #5

Ingolf:

Thank you so very much. This worked. Much appreciated.

I wonder why isn't there a way in Excel to do this easily like it is to find unique values in a column.

 

Upvote 0

E

Excel98402

New Member

Joined

Oct 29, 2014

Messages

5

• Oct 29, 2014

• #6

Hi Ingolf:

I noticed that the values did not come out correctly for several rows. In several instances one or more values were missed.

This is the formula I used:

=IFERROR(INDEX($D2:$AJ2,MATCH(1,INDEX((COUNTIF($AK$2:AK2,$D2:$AJ2)=0)*($D2:$AJ2<>""),0),0)),"")

I have data from D2 to AJ2 and I have applied the formula on AL to AP (because I saw that the maximum of different number unique of values from D2 to AJ2 was 5)

I cannot figure out why would this happen. Any ideas ?

 

Upvote 0

Weazel

Well-known Member

Joined

Dec 24, 2011

Messages

3,155

• Oct 29, 2014

• #7

I think the UDF Bob posted is probably alot more efficient but maybe something like...

Q2=IFERROR(INDEX($B2:$P2,SMALL(IF(FREQUENCY(IF($B2:$P2<>"",MATCH($B2:$P2,$B2:$P2,0)),COLUMN($B2:$P2)-COLUMN($B2)+1),TRANSPOSE(COLUMN($B2:$P2)-COLUMN($B2)+1)),COLUMNS($Q2:Q2))),"") Control Shift Enter

	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S
1	Row Label	10	12	14	15	16	17	18	19	21	24	25	30	31	32	35
2	363820	8.99		19	19	9.99	9.99	9.99	9.99			8.99	8.99	8.99	8.99	8.99	8.99	19	9.99
3	363846	8.99	8.99	19	19	9.99	9.99	9.99	9.99	8.99	8.99	8.99	8.99	8.99	8.99	8.99	8.99	19	9.99
4	369918			39	39	19.99	19.99	19.99	19.99			19.99	19.99	19.99			39	19.99
5	370049			36.99	36.99	24.99	36.99	49	36.99			36.99	36.99	36.99			36.99	24.99	49
6	370148	59	59	55	59	59	59	59	59	59	59	59	59	59	59	59	59	55

<TBODY>
</TBODY>

 

Upvote 0

I

Ingolf

Banned

Joined

Mar 20, 2011

Messages

809

• Oct 29, 2014

• #8

Excel98402 said:

Hi Ingolf:

I noticed that the values did not come out correctly for several rows. In several instances one or more values were missed.

This is the formula I used:

=IFERROR(INDEX($D2:$AJ2,MATCH(1,INDEX((COUNTIF($AK$2:AK2,$D2:$AJ2)=0)*($D2:$AJ2<>""),0),0)),"")

I have data from D2 to AJ2 and I have applied the formula on AL to AP (because I saw that the maximum of different number unique of values from D2 to AJ2 was 5)

I cannot figure out why would this happen. Any ideas ?

Click to expand...

Try this:

=IFERROR(INDEX($B2:$AK2,MATCH(1,INDEX((COUNTIF($AL2:AL2,$B2:$AK2)=0)*($B2:$AK2<>""),0),0)),"")

 

Upvote 0

E

Excel98402

New Member

Joined

Oct 29, 2014

Messages

5

• Oct 29, 2014

• #9

Are you suggesting that to make the change because you think that I am starting from D instead of B as in my example ?

If yes, that is not the case because when I pasted the data, I removed the data in the columns B and C to avoid confusion.

Would it help if I sent the raw data over ?

 

Upvote 0

B

billszysz

Active Member

Joined

Feb 26, 2014

Messages

384

Office Version

1. 365

• Oct 29, 2014

• #10

Hi
If you have positive numbers only, we can use a little trick
Your data from A to P column - Q column has to be empty - result in R

=LARGE((FREQUENCY(IF($B2:$P2="",0,$B2:$P2),IF($B2:$P2="",0,$B2:$P2))>0)*TRANSPOSE(IF($B2:$Q2="",0,$B2:$Q2)),COLUMNS($R2:R2))

and CTRL+SHIFT+ENTER
You can copy this cell right and down
Cell format "0.00,," (just in example)

In my opinion VBA is better in this case.

 

Upvote 0