Finding Unique Values among several rows

[brohymn62]

I have several columns of data as seen below:

ROW 1	VALUE	ROW 2	VALUE
700	4	710	4
705	4	750	2
710	4	756	3
750	4	710	5
750	2	711	4
750	3	702	2

<tbody>
</tbody>

I am trying to find a way to search through this table... find the unique values in row 1 and 2, then create a dynamic list based on which how many unique values are found. I have only been able to do this on one column... help is much appreciated!

 

[Aladin Akyurek]

I have several columns of data as seen below:

ROW 1	VALUE	ROW 2	VALUE
700	4	710	4
705	4	750	2
710	4	756	3
750	4	710	5
750	2	711	4
750	3	702	2

<tbody>
</tbody>

I am trying to find a way to search through this table... find the unique values in row 1 and 2, then create a dynamic list based on which how many unique values are found. I have only been able to do this on one column... help is much appreciated!

What is your version of Excel?

 

[brohymn62]

I use 2010 but I am creating this for compatibility with 2003.

 

[Aladin Akyurek]

I use 2010 but I am creating this for compatibility with 2003.

Add the following code to your work, using Alt + F11:

Function ArrayUnion(ParamArray Arg() As Variant) As Variant
' Code: Juan Pablo González
' Spec: Aladin Akyurek
' May 4, 2003
' Ref: TinyURL.com - shorten that long URL into a tiny URL
Dim TempUnion() As Variant
Dim i As Long, Itm As Variant, Ctr As Long
For i = LBound(Arg) To UBound(Arg)
Arg(i) = Arg(i)
If IsArray(Arg(i)) Then
For Each Itm In Arg(i)
Ctr = Ctr + 1
ReDim Preserve TempUnion(1 To Ctr) As Variant
TempUnion(Ctr) = Itm
Next Itm
Else
Ctr = Ctr + 1
ReDim Preserve TempUnion(1 To Ctr) As Variant
TempUnion(Ctr) = Arg(i)
End If
Next i
ArrayUnion = TempUnion
End Function

Given the following data on Sheet1 (otherwise, adjust to suit the sheet name)...

X	Value	Y	Value		Distinct Count
700	4	710	4		7
705	4	750	2		Distinct List
710	4	756	3		700
750	4	710	5		705
750	2	711	4		710
750	3	702	2		750
					756
					711
					702

<colgroup><col style="width: 48pt;" span="5" width="64"> <col style="width: 170pt; mso-width-source: userset; mso-width-alt: 8049;" width="226"> <tbody>
</tbody>

Define List using Insert | Name | Define (or Formulas | Name Manager) as referring to:

=arrayunion(Sheet1!$A$2:$A$7,Sheet1!$C$2:$C$7)

Define Ivec as referring to:

=ROW(INDIRECT("1:"&COLUMNS(List)))

F2, control+shift+enter, not just enter:

=SUM(IF(FREQUENCY(IF(List<>"",MATCH("~"&List,List&"",0)),Ivec),1))

F4, control+shift+enter and copy down:

=IF(ROWS($F$4:F4)<=$F$2,
  INDEX(List,SMALL(IF(FREQUENCY(IF(List<>"",
   MATCH("~"&List,List&"",0)),Ivec),Ivec),ROWS($F$4:F4))),"")

 

[brohymn62]

That is awesome!!

 

[Aladin Akyurek]

That is awesome!!

Happy to hear that. Thanks for providing feedback.

 

[brohymn62]

Can I sort this list?

 

[Aladin Akyurek]

Can I sort this list?

F4, control+shift+enter, not just enter, and copy down:

=IF(ROWS($F$4:F4)<=$F$2,MIN(IF(1-ISNUMBER(MATCH(List,$F$3:F3,0)),
  IF(1-(List=$F3),List))),"")