Finding Unique Values among several rows

B

brohymn62

New Member
Joined
Aug 15, 2013
Messages
21
I have several columns of data as seen below:


[TABLE="width: 200"]
<tbody>[TR]
[TD]ROW 1[/TD]
[TD]VALUE [/TD]
[TD] ROW 2[/TD]
[TD]VALUE[/TD]
[/TR]
[TR]
[TD]700[/TD]
[TD]4[/TD]
[TD]710[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]705[/TD]
[TD]4[/TD]
[TD]750[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]710[/TD]
[TD]4[/TD]
[TD]756[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]750[/TD]
[TD]4[/TD]
[TD]710[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]750[/TD]
[TD]2[/TD]
[TD]711[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]750[/TD]
[TD]3[/TD]
[TD]702[/TD]
[TD]2[/TD]
[/TR]
</tbody>[/TABLE]

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!
 
Sort by date Sort by votes
brohymn62 said:
I have several columns of data as seen below:


[TABLE="width: 200"]
<tbody>[TR]
[TD]ROW 1
[/TD]
[TD]VALUE
[/TD]
[TD] ROW 2
[/TD]
[TD]VALUE
[/TD]
[/TR]
[TR]
[TD]700
[/TD]
[TD]4
[/TD]
[TD]710
[/TD]
[TD]4
[/TD]
[/TR]
[TR]
[TD]705
[/TD]
[TD]4
[/TD]
[TD]750
[/TD]
[TD]2
[/TD]
[/TR]
[TR]
[TD]710
[/TD]
[TD]4
[/TD]
[TD]756
[/TD]
[TD]3
[/TD]
[/TR]
[TR]
[TD]750
[/TD]
[TD]4
[/TD]
[TD]710
[/TD]
[TD]5
[/TD]
[/TR]
[TR]
[TD]750
[/TD]
[TD]2
[/TD]
[TD]711
[/TD]
[TD]4
[/TD]
[/TR]
[TR]
[TD]750
[/TD]
[TD]3
[/TD]
[TD]702
[/TD]
[TD]2
[/TD]
[/TR]
</tbody>[/TABLE]

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!
Click to expand...

What is your version of Excel?
 
Upvote 0
brohymn62 said:
I use 2010 but I am creating this for compatibility with 2003.
Click to expand...

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)...

[TABLE="width: 410"]
<colgroup><col style="width: 48pt;" span="5" width="64"> <col style="width: 170pt; mso-width-source: userset; mso-width-alt: 8049;" width="226"> <tbody>[TR]
[TD="class: xl66, width: 64, bgcolor: white"]X[/TD]
[TD="class: xl66, width: 64, bgcolor: white"]Value[/TD]
[TD="class: xl66, width: 64, bgcolor: white"]Y[/TD]
[TD="class: xl66, width: 64, bgcolor: white"]Value[/TD]
[TD="class: xl65, width: 64, bgcolor: white"] [/TD]
[TD="class: xl66, width: 226, bgcolor: white"]Distinct Count[/TD]
[/TR]
[TR]
[TD="class: xl65, width: 64, bgcolor: white, align: right"]700[/TD]
[TD="class: xl65, width: 64, bgcolor: white, align: right"]4[/TD]
[TD="class: xl65, width: 64, bgcolor: white, align: right"]710[/TD]
[TD="class: xl65, width: 64, bgcolor: white, align: right"]4[/TD]
[TD="class: xl65, width: 64, bgcolor: white"] [/TD]
[TD="class: xl65, width: 226, bgcolor: white, align: right"]7[/TD]
[/TR]
[TR]
[TD="class: xl65, width: 64, bgcolor: white, align: right"]705[/TD]
[TD="class: xl65, width: 64, bgcolor: white, align: right"]4[/TD]
[TD="class: xl65, width: 64, bgcolor: white, align: right"]750[/TD]
[TD="class: xl65, width: 64, bgcolor: white, align: right"]2[/TD]
[TD="class: xl65, width: 64, bgcolor: white"] [/TD]
[TD="class: xl66, width: 226, bgcolor: white"]Distinct List[/TD]
[/TR]
[TR]
[TD="class: xl65, width: 64, bgcolor: white, align: right"]710[/TD]
[TD="class: xl65, width: 64, bgcolor: white, align: right"]4[/TD]
[TD="class: xl65, width: 64, bgcolor: white, align: right"]756[/TD]
[TD="class: xl65, width: 64, bgcolor: white, align: right"]3[/TD]
[TD="class: xl65, width: 64, bgcolor: white"] [/TD]
[TD="class: xl65, width: 226, bgcolor: white, align: right"]700[/TD]
[/TR]
[TR]
[TD="class: xl65, width: 64, bgcolor: white, align: right"]750[/TD]
[TD="class: xl65, width: 64, bgcolor: white, align: right"]4[/TD]
[TD="class: xl65, width: 64, bgcolor: white, align: right"]710[/TD]
[TD="class: xl65, width: 64, bgcolor: white, align: right"]5[/TD]
[TD="class: xl65, width: 64, bgcolor: white"] [/TD]
[TD="class: xl65, width: 226, bgcolor: white, align: right"]705[/TD]
[/TR]
[TR]
[TD="class: xl65, width: 64, bgcolor: white, align: right"]750[/TD]
[TD="class: xl65, width: 64, bgcolor: white, align: right"]2[/TD]
[TD="class: xl65, width: 64, bgcolor: white, align: right"]711[/TD]
[TD="class: xl65, width: 64, bgcolor: white, align: right"]4[/TD]
[TD="class: xl65, width: 64, bgcolor: white"] [/TD]
[TD="class: xl65, width: 226, bgcolor: white, align: right"]710[/TD]
[/TR]
[TR]
[TD="class: xl65, width: 64, bgcolor: white, align: right"]750[/TD]
[TD="class: xl65, width: 64, bgcolor: white, align: right"]3[/TD]
[TD="class: xl65, width: 64, bgcolor: white, align: right"]702[/TD]
[TD="class: xl65, width: 64, bgcolor: white, align: right"]2[/TD]
[TD="class: xl65, width: 64, bgcolor: white"] [/TD]
[TD="class: xl65, width: 226, bgcolor: white, align: right"]750[/TD]
[/TR]
[TR]
[TD="class: xl65, width: 64, bgcolor: white"] [/TD]
[TD="class: xl65, width: 64, bgcolor: white"] [/TD]
[TD="class: xl65, width: 64, bgcolor: white"] [/TD]
[TD="class: xl65, width: 64, bgcolor: white"] [/TD]
[TD="class: xl65, width: 64, bgcolor: white"] [/TD]
[TD="class: xl65, width: 226, bgcolor: white, align: right"]756[/TD]
[/TR]
[TR]
[TD="class: xl65, width: 64, bgcolor: white"] [/TD]
[TD="class: xl65, width: 64, bgcolor: white"] [/TD]
[TD="class: xl65, width: 64, bgcolor: white"] [/TD]
[TD="class: xl65, width: 64, bgcolor: white"] [/TD]
[TD="class: xl65, width: 64, bgcolor: white"] [/TD]
[TD="class: xl65, width: 226, bgcolor: white, align: right"]711[/TD]
[/TR]
[TR]
[TD="class: xl65, width: 64, bgcolor: white"] [/TD]
[TD="class: xl65, width: 64, bgcolor: white"] [/TD]
[TD="class: xl65, width: 64, bgcolor: white"] [/TD]
[TD="class: xl65, width: 64, bgcolor: white"] [/TD]
[TD="class: xl65, width: 64, bgcolor: white"] [/TD]
[TD="class: xl65, width: 226, bgcolor: white, align: right"]702[/TD]
[/TR]
</tbody>[/TABLE]

Define List using Insert | Name | Define (or Formulas | Name Manager) as referring to:
Rich (BB code): 
=arrayunion(Sheet1!$A$2:$A$7,Sheet1!$C$2:$C$7)

Define Ivec as referring to:
Rich (BB code): 
=ROW(INDIRECT("1:"&COLUMNS(List)))

F2, control+shift+enter, not just enter:
Rich (BB code): 
=SUM(IF(FREQUENCY(IF(List<>"",MATCH("~"&List,List&"",0)),Ivec),1))

F4, control+shift+enter and copy down:
Rich (BB code): 
=IF(ROWS($F$4:F4)<=$F$2,
  INDEX(List,SMALL(IF(FREQUENCY(IF(List<>"",
   MATCH("~"&List,List&"",0)),Ivec),Ivec),ROWS($F$4:F4))),"")
 
Upvote 0
You must log in or register to reply here.

Similar threads

C
  • Solved
VBA, Substitute, Find and Replace, Trim Not working to remove space
Replies
4
Views
233
Alex Blakenburg
A
T
  • Solved
Find Values of Cells and Replace values of Named Cells between Sheets
2 3
Replies
22
Views
648
Peter_SSs
Peter_SSs
Burrgogi
  • Question Question
Need some help with VBA - evaluating a string of numbers and from VLOOKUP routine.
Replies
2
Views
164
StephenCrump
S
M
  • Solved
Indirect reference of 2 cells in "sheet1" to look up value in table "table1"
Replies
8
Views
188
Kaper
Kaper
L
  • Question Question
VB Error 91
Replies
2
Views
153
Peter_SSs
Peter_SSs

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top