Extract unique values from columns

[proficient]

Hi all,

Need your help, I have two columns, column A has

A B C O E W G H
whereas column B has
A B C E G H J L P

I want Unique alphabats from column B only, means as you can see O and W are also unique in column A but I want to extract J, L and P only.

TIA

 

[proficient]

Re: Want to extract unique values from columns

@Aladin Akyurek

 

[JoeMo]

Re: Want to extract unique values from columns

While you're waiting for Aladin to respond, here's a UDF you can try. See the example below for setting the arguments.

Excel Workbook
	A	B	C	D
1			Unique To Col B	Unique to col A
2	A B C O E W G H	A B C E G H J L P	J L P	O W
Sheet4

Function UniqueToCol(R As Range, Col As Long) As String
Dim V1 As Variant, V2 As Variant, i As Long, j As Long
If Col > R.Columns.Count Or Col > 2 Then
    UniqueToCol = CVErr(xlErrValue)
    Exit Function
End If
V1 = Split(R(1), " ")
V2 = Split(R(2), " ")
Application.Volatile
Select Case Col
    Case 2  'look for elements in the second col of R that are not in the first col of R
        For i = LBound(V2) To UBound(V2)
            For j = LBound(V1) To UBound(V1)
                ct = ct + 1
                If V2(i) = V1(j) Then Exit For
                If ct = UBound(V1) + 1 Then UniqueToCol = UniqueToCol & " " & V2(i)
            Next j
            ct = 0
        Next i
        UniqueToCol = Trim(UniqueToCol)
    Case 1  'look for elements in the first col of R that are not in the second col of R
        For i = LBound(V1) To UBound(V1)
            For j = LBound(V2) To UBound(V2)
                ct = ct + 1
                If V1(i) = V2(j) Then Exit For
                If ct = UBound(V2) + 1 Then UniqueToCol = UniqueToCol & " " & V1(i)
            Next j
            ct = 0
        Next i
        UniqueToCol = Trim(UniqueToCol)
End Select
End Function

 

[Rick Rothstein]

Re: Want to extract unique values from columns

Need your help, I have two columns, column A has

A B C O E W G H
whereas column B has
A B C E G H J L P

Are those letters all in one cell (space delimited as shown) or is each individual letter in its own cell down the column?

 

[proficient]

Re: Want to extract unique values from columns

No, all letters are in seperate cells, assume range from A1 to A10 and B1 to B10

 

[Aladin Akyurek]

Re: Want to extract unique values from columns

Book1
	A	B	C	D
1	A	A		J
2	A	B		L
3	B	C		P
4	C	E
5	O	G
6	E	H
7	W	J
8	G	L
9	H	P
Sheet1

In D1 control+shift+enter, not just enter, and copy down:

=IFERROR(INDEX($B$1:$B$9,SMALL(IF(FREQUENCY(IF(1-($B$1:$B$9=""),IF(ISNA(MATCH($B$1:$B$9,$A$1:$A$9,0)),MATCH($B$1:$B$9,$B$1:$B$9,0))),ROW($B$1:$B$9)-ROW($B$1)+1),ROW($B$1:$B$9)-ROW($B$1)+1),ROWS($D$1:D1))),"")

This creates a unique list of items from column B which do not occur in column A. Hope this is the correct interpretation of what you are asking for.

 

[JoeMo]

Re: Want to extract unique values from columns

No, all letters are in seperate cells, assume range from A1 to A10 and B1 to B10

Not the way I interpreted the initial post, so the UDF in post#3 is not applicable.

 

[Rick Rothstein]

Re: Want to extract unique values from columns

Aladin has given you a (somewhat complex) formula solution. If you are willing to entertain a VB solution, here is a subroutine that you would call from within a macro or event procedure. The subroutine takes three arguments... the first is the column of cells you want to check for unique letters (Column B from your example), the second is the columns of cells you want to compare is against (Column A from your example) and the third is the first cell that the output will start in.

[table="width: 500"]
[tr]
	[td][B][COLOR="#008000"]' This is the subroutine that you call from your macro or event procedure[/COLOR][/B]
Sub UniqueToCol(CheckCol As Range, CompareCol As Range, OutCell As Range)
  Dim V As Variant, UCol As Variant, CCol As Variant
  UCol = Application.Transpose(CheckCol)
  CCol = CompareCol
  For Each V In CCol
    UCol = Filter(UCol, V, False)
  Next
  OutCell.Resize(1 + UBound(UCol)) = Application.Transpose(UCol)
End Sub


[B][COLOR="#008000"]' A test macro to show how to call the above subroutine[/COLOR][/B]
Sub Test()
  UniqueToCol Range("B1:B9"), Range("A1:A8"), Range("C1")
End Sub[/td]
[/tr]
[/table]

 

[proficient]

Re: Want to extract unique values from columns

Yes.

 

[proficient]

Re: Want to extract unique values from columns

	A	B	C	D
A	A	J
A	B	L
B	C	P
C	E
O	G
E	H
W	J
G	L
H	P

<colgroup><col style="width: 25pxpx"><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: right"][/TD]

[TD="align: center"]2[/TD]

[TD="align: right"][/TD]

[TD="align: center"]3[/TD]

[TD="align: right"][/TD]

[TD="align: center"]4[/TD]

[TD="align: right"][/TD]

[TD="align: center"]5[/TD]

[TD="align: right"][/TD]

[TD="align: center"]6[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]9[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Sheet1​

In D1 control+shift+enter, not just enter, and copy down:

=IFERROR(INDEX($B$1:$B$9,SMALL(IF(FREQUENCY(IF(1-($B$1:$B$9=""),IF(ISNA(MATCH($B$1:$B$9,$A$1:$A$9,0)),MATCH($B$1:$B$9,$B$1:$B$9,0))),ROW($B$1:$B$9)-ROW($B$1)+1),ROW($B$1:$B$9)-ROW($B$1)+1),ROWS($D$1:D1))),"")

This creates a unique list of items from column B which do not occur in column A. Hope this is the correct interpretation of what you are asking for.

@Aladin Akyurek, You can't be wrong ever; I believe! Thanks a lot! this is exactly what I wanted.

Could you explain the formula for me?