Extract unique values from one column using VBA

[boldcode]

Hi,

I want to extract all the unique values from column A starting with A2 to the last cell of column A that has a value and copy those values into cell B2 all the way down to whatever the last cell of column B is.

I have row titles in cells A1 and B1.

Example:

Data Before Macro:

	Column A	Column B
Row 1	All Codes	Distinct Codes
Row 2	456
Row 3	456
Row 4	678
Row 5	678
Row 6	890
Row 7	543
Row 8	543
Row 9	234
Row 10	213
Row 11	905
Row 12	905

<colgroup><col style="mso-width-source:userset;mso-width-alt:4096;width:84pt" width="112"> <col style="mso-width-source:userset;mso-width-alt:5997;width:123pt" width="164"> <col style="mso-width-source:userset;mso-width-alt:4973;width:102pt" width="136"> </colgroup><tbody>
</tbody>

Data After Macro:

	Column A	Column B
Row 1	All Codes	Distinct Codes
Row 2	456	456
Row 3	456	678
Row 4	678	890
Row 5	678	543
Row 6	890	234
Row 7	543	213
Row 8	543	905
Row 9	234
Row 10	213
Row 11	905
Row 12	905

<colgroup><col style="mso-width-source:userset;mso-width-alt:4864;width:100pt" width="133"> <col style="mso-width-source:userset;mso-width-alt:4571;width:94pt" width="125"> <col style="mso-width-source:userset;mso-width-alt:5120;width:105pt" width="140"> </colgroup><tbody>
</tbody>

Thanks,

BC

 

[hiker95]

Englishboxer,

Welcome to the MrExcel forum.


We would like more information. Please see the Forum Use Guidelines in the following link:

http://www.mrexcel.com/forum/board-announcements/127080-guidelines-forum-use.html


See reply #2 at the next link, if you want to show small screenshots, of the raw data, and, what the results should look like.

http://www.mrexcel.com/forum/about-board/508133-attachments.html#post2507729

 

[Englishboxer]

Hi,

Apologies, here is a better explanation.

I am using the Sub GetUniques() that you created to go through a table (example below) and get all the unique names.

Excel 2010

	O	P
1	Agency Candidate	Month
35	Name1	December
36	Name1	January
37	Name1	February
38	Name1	March
39	Name2	January
40	Name2	February
41	Name3	March
42	Name3	January
43	Name3	February

<colgroup><col style="width: 25pxpx"><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet2​

Your GetUniques() Sub is doing exactly what it should and providing me with a list:
Name1
Name2
Name3

I was hoping to add to your code (if i was able) to produce something like:
Excel 2010

	F	G
17	Agency Candidate	Month
18	Name1	February
19	Name1	March
20	Name2	December
21	Name3	December
22	Name3	January

<colgroup><col style="width: 25pxpx"><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet3​

So I would like the code to look up unique values based on 2 columns not 1.

Many thanks for your help.

 

[hiker95]

So I would like the code to look up unique values based on 2 columns not 1.

Englishboxer,

Here is a macro solution for you to consider that is based on the above quote.

My results do not match yours.

Could you explain in detail how you came up with your results?


Sample worksheets:

Excel 2007
	B	C
2	Agency Candidate	Month
3	Name1	December
4	Name1	January
5	Name1	February
6	Name1	March
7	Name2	January
8	Name2	February
9	Name3	March
10	Name3	January
11	Name3	February
12
Instructions

Excel 2007
	F	G
17	Agency Candidate	Month
18
19
20
21
22
23
24
25
26
27
Sheet3

And, after the macro:

Excel 2007
	F	G
17	Agency Candidate	Month
18	Name1	December
19	Name1	January
20	Name1	February
21	Name1	March
22	Name2	January
23	Name2	February
24	Name3	March
25	Name3	January
26	Name3	February
27
Sheet3

Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

Sub ExtractUniqueCombinations()
' hiker95, 04/17/2017, ME649576
Dim w2 As Worksheet, w3 As Worksheet
Dim r  As Long, t As String, lr As Long
Dim d As Object, a As Variant, o As Variant, c As Long, n As Long
Set w2 = Sheets("Sheet2")
Set w3 = Sheets("Sheet3")
With w2
  lr = .Cells(Rows.Count, "O").End(xlUp).Row
  a = .Range("O35:P" & lr)
  ReDim o(1 To UBound(a, 1) + 1, 1 To UBound(a, 2))
  o(1, 1) = "Agency Candidate": o(1, 2) = "Month"
End With
Set d = CreateObject("Scripting.Dictionary")
d.CompareMode = vbTextCompare
n = 1
For r = 1 To UBound(a, 1)
  t = a(r, 1) & "," & a(r, 2)     ' & "," & a(r, 3)
  If Not d.Exists(t) Then
    n = n + 1
    For c = 1 To 2
      o(n, c) = a(r, c)
    Next c
    d.Add t, n
  End If
Next r
With w3
  .Range("F17").Resize(d.Count + 1, 2) = o
  .Columns("F:G").AutoFit
  .Activate
End With
End Sub

Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm, and, answer the "do you want to enable macros" question as "yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.

Then run the ExtractUniqueCombinations macro.

 

[Englishboxer]

Thanks so much for this hiker95,

I wouldn't expect your results to match as I noticed the following day that I highlighted the wrong section when uploading the examples.

I'm sure this will be better than the workaround (c&p to another sheet and dedupe then c&p back in to the table) that I came up with.

 

[hiker95]

Thanks so much for this hiker95,

I wouldn't expect your results to match as I noticed the following day that I highlighted the wrong section when uploading the examples.

I'm sure this will be better than the workaround (c&p to another sheet and dedupe then c&p back in to the table) that I came up with.

Englishboxer,

So that we can get it right on the next try, can we have the correct raw data worksheet screenshots, and, the resulting screenshot (manually formatted by you) for the results that you are looking for?

 

[reggieneo]

Englishboxer,

Here is a macro solution for you to consider that is based on the above quote.

My results do not match yours.

Could you explain in detail how you came up with your results?


Sample worksheets:

Excel 2007

	B	C
2	Agency Candidate	Month
3	Name1	December
4	Name1	January
5	Name1	February
6	Name1	March
7	Name2	January
8	Name2	February
9	Name3	March
10	Name3	January
11	Name3	February
12

<colgroup><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Instructions​

Excel 2007

	F	G
17	Agency Candidate	Month
18
19
20
21
22
23
24
25
26
27

<colgroup><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet3​

And, after the macro:

Excel 2007

	F	G
17	Agency Candidate	Month
18	Name1	December
19	Name1	January
20	Name1	February
21	Name1	March
22	Name2	January
23	Name2	February
24	Name3	March
25	Name3	January
26	Name3	February
27

<colgroup><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet3​

Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

Sub ExtractUniqueCombinations()
' hiker95, 04/17/2017, ME649576
Dim w2 As Worksheet, w3 As Worksheet
Dim r  As Long, t As String, lr As Long
Dim d As Object, a As Variant, o As Variant, c As Long, n As Long
Set w2 = Sheets("Sheet2")
Set w3 = Sheets("Sheet3")
With w2
  lr = .Cells(Rows.Count, "O").End(xlUp).Row
  a = .Range("O35:P" & lr)
  ReDim o(1 To UBound(a, 1) + 1, 1 To UBound(a, 2))
  o(1, 1) = "Agency Candidate": o(1, 2) = "Month"
End With
Set d = CreateObject("Scripting.Dictionary")
d.CompareMode = vbTextCompare
n = 1
For r = 1 To UBound(a, 1)
  t = a(r, 1) & "," & a(r, 2)     ' & "," & a(r, 3)
  If Not d.Exists(t) Then
    n = n + 1
    For c = 1 To 2
      o(n, c) = a(r, c)
    Next c
    d.Add t, n
  End If
Next r
With w3
  .Range("F17").Resize(d.Count + 1, 2) = o
  .Columns("F:G").AutoFit
  .Activate
End With
End Sub

Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm, and, answer the "do you want to enable macros" question as "yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.

Then run the ExtractUniqueCombinations macro.

Dear Hiker95, I have been doing my project with a lot of index match when I finally saw this wonderful short code of yours that does a lot. I have tested and used it in some of my queries ; it's just awesome.
Can I asked your help to use this with dynamic variable instead of hard coded "Agency Candidate" and "Month" as an example above: I would like to loop through my cells and have these variable to change as the codes array runs through all the rows of multiple columns and get only the unique values. very similar with what you have done above except that I need the criteria to be changing. Criteria to match F : is tea name and G is the Hours they have. thank you. reggieneo

	F	G
17	Team Name	Hours
18	Name1	4
19	Name1	6
20	Name1	5
21	Name1	4
22	Name2	7
23	Name2	8
24	Name3	9
25	Name3	4
26	Name3	6
27

<colgroup><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

 

[Peter_SSs]

	F	G
17	Team Name	Hours
18	Name1	4
19	Name1	6
20	Name1	5
21	Name1	4
22	Name2	7
23	Name2	8
24	Name3	9
25	Name3	4
26	Name3	6
27

<colgroup><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Can you also post the expected results from that sample data?

BTW, best not to fully quote long posts as it makes the thread harder to read/navigate. If you want to quote, quote small, relevant parts only.

 

[reggieneo]

Hi Peter, thanks for reply. After checking each row of " F "Column, I expect to have a result in "I" Column the unique values from "G" if rows in "F " Column matches "H": please see below

	F	G H I
	Team Name	Hours
18	Name1	4 Name1 4
19	Name1	4 Name 2 7
20	Name1	4 Name 3 9
21	Name1	4
22	Name2	7
23	Name2	7
24	Name3	9
25	Name3	9
26	Name3	9

<tbody>
</tbody>

 

[reggieneo]

sorry the table was messed up, here I sorted it again: thanks

	F	G	H	I
	Team Name	Hours
18	Name1	4	Name1	4
19	Name1	4	Name 2	7
20	Name1	4	Name 3	9
21	Name1	4
22	Name2	7
23	Name2	7
24	Name3	9
25	Name3	9
26	Name3	9

<tbody>
</tbody>

 

[Peter_SSs]

Hi Peter, thanks for reply. After checking each row of " F "Column, I expect to have a result in "I" Column the unique values from "G" if rows in "F " Column matches "H": please see below

	F	G H I
	Team Name	Hours
18	Name1	4 Name1 4
19	Name1	4 Name 2 7
20	Name1	4 Name 3 9
21	Name1	4
22	Name2	7
23	Name2	7
24	Name3	9
25	Name3	9
26	Name3	9

<tbody>
</tbody>

I cannot be sure what is individually in columns G, H & I but that seems like different data from your original post, particularly in relation to column G.
Can you post more clearly? (My signature block below has suggestions for that.)

EDIT: OK, I've now seen your correction. I will look at it.