Extract unique values from one column using VBA

boldcode

Active Member
Joined
Mar 12, 2010
Messages
347
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 AColumn B
Row 1All CodesDistinct Codes
Row 2456
Row 3456
Row 4678
Row 5678
Row 6890
Row 7543
Row 8543
Row 9234
Row 10213
Row 11905
Row 12905

<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 AColumn B
Row 1All CodesDistinct Codes
Row 2456456
Row 3456678
Row 4678890
Row 5678543
Row 6890234
Row 7543213
Row 8543905
Row 9234
Row 10213
Row 11905
Row 12905

<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
 
when i used the below code to paste the unique values from column a to b, first value in column a is always getting repeated at the end of column b.


Sheet1.Range("A1:A10").AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Sheet1.Range("B1:B10"), Unique:=True


column A: test1,test2,test3,test1,test2,test3

column B: test1,test2,test3,test1


As shown above test1 is getting repeated. I tried changing the values, alter sequence in column a but still 1st value is getting repeated.
 
Upvote 0

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.
I believe the filter mechanism always considers the first row as headers. It is probably including the second "test1" as a filtered element and preserving the first "test1" as the header. Try changing "A1:A10" to "A2:A10"

Hope that helps.

Gary
 
Upvote 0
This code works just fine but it omits the 0s. Any idea how that can be changed?

boldcode,


Sample raw data:


Sheet1

*AB
1All CodesDistinct Codes
2456*
3456*
4678*
5678*
6890*
7543*
8543*
9234*
10213*
11905*
12905*
13**

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:84px;"><col style="width:114px;"></colgroup><tbody>
</tbody>


Excel tables to the web >> Excel Jeanie HTML 4




After the macro:


Sheet1

*AB
1All CodesDistinct Codes
2456456
3456678
4678890
5678543
6890234
7543213
8543905
9234*
10213*
11905*
12905*
13**

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:84px;"><col style="width:114px;"></colgroup><tbody>
</tbody>


Excel tables to the web >> Excel Jeanie HTML 4




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

1. Copy the below code, by highlighting the code and pressing the keys CTRL + C
2. Open your workbook
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. Press the keys ALT + I to activate the Insert menu
5. Press M to insert a Standard Module
6. Where the cursor is flashing, paste the code by pressing the keys CTRL + V
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel, open the workbook, and press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.


Code:
Option Explicit
Sub GetUniques()
' hiker95, 07/26/2012
' http://www.mrexcel.com/forum/showthread.php?649576-Extract-unique-values-from-one-column-using-VBA
Dim d As Object, c As Variant, i As Long, lr As Long
Set d = CreateObject("Scripting.Dictionary")
lr = Cells(Rows.Count, 1).End(xlUp).Row
c = Range("A2:A" & lr)
For i = 1 To UBound(c, 1)
  d(c(i, 1)) = 1
Next i
Range("B2").Resize(d.Count) = Application.Transpose(d.keys)
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


Then run the GetUniques macro.
 
Upvote 0
This code works just fine but it omits the 0s. Any idea how that can be changed?

mearjun,

Without NOT seeing your actual raw data, it is difficult to say.

Please see my instructions in my reply #9, so that we can see your actual raw data workbook/worksheet(s).
 
Upvote 0
Thanks for the quick reply. My data is in a column similar to the original question. But it has around 20000 values so I am not sure how I can send you a screenshot of it. As I said earlier the code works fine but somehow omits the 0s. I can email it to you if that is possible. And once again thanks for the help. Much appreciated.

mearjun,

Without NOT seeing your actual raw data, it is difficult to say.

Please see my instructions in my reply #9, so that we can see your actual raw data workbook/worksheet(s).
 
Upvote 0
My data is in a column similar to the original question. But it has around 20000 values so I am not sure how I can send you a screenshot of it.

mearjun,

1. What version of Excel, and, Windows are you using?

2. Are you using a PC or a Mac?

In order to continue, and, so that I can get it right on the first try, I would like to see your actual raw data workbook/worksheet.

The following is a free site:

You can upload your workbook to (the BLUE link-->) Box Net ,
sensitive data changed
mark the workbook for sharing
and provide us with a link to your workbook.


If you are not able to provide the above, then:

Click on the Reply to Thread button, and just put the word BUMP in the thread. Then, click on the Post Quick Reply button, and someone else will assist you.
 
Upvote 0
What's supposed to happen with the "zeros"? Zero is a unique value just like everything else... do you want to show that there is a blank entry? I think that within the for loop you could probably test for a zero/vbnullstring value and then do whatever it is that you're interested in. HTH. Dave
 
Upvote 0
Hello and thank you for your post, it helped me quite a lot. However I have a point to mention which I am not quite sure why it is occurring. While the script extracts the unique codes in the defined cell and sheet, for some reason the first value of the to-be-extracted range (table in my case) is duplicated twice in the new cell that I want the values to be copied to. Do you have any idea why is this occurring? Please find my script:

Sub test()

Sheets("Working").Range("Employees[EmployeeName]").AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Sheets("INVOICE").Range("C12"), Unique:=True

End Sub


Therefore, my new range is:

ANDREW
ANDREW
THOMAS
BOB
NICK

while ANDREW should be copied once. Furthermore I have figured out that the first cell (first ANDREW) is automatically named "Extract" in Excel. There must be something to do with that but I cannot figure out now. Thank you in advance for your help.
 
Last edited:
Upvote 0
vbatryhard,

Welcome to the MrExcel forum.

1. What version of Excel, and, Windows are you using?

2. Are you using a PC or a Mac?


When you respond to your helper(s), please use their site ID/username/handle.

This will keep thread clutter to a minimum, and, make the discussion easier to follow.


To start off, and, so that we can get it right on the first try:

Can you post a screenshot of the actual raw data worksheet?

And, can you post a screenshot of the worksheet results (manually formatted by you) that you are looking for?

To post a small screen shot (NOT a graphic, or, picture, or, PNG/IMG file, or, flat text) try one of the following:

There are several methods. Here are 3 for you to investigate.

Excel Jeanie
Download

Borders-Copy-Paste
http://www.mrexcel.com/forum/about-board/444901-how-create-table-like-aladin.html#post2198045

MrExcel HTML Maker
If you do not know how to install and how to use HTML Mr.Excel Maker
https://www.youtube.com/watch?v=JycvgGppxt0&feature=youtu.be


To test the above:
Test Here


Or, you can post your workbook/worksheets to the following free site, and provide us with a link to your workbook:

https://dropbox.com
 
Upvote 0

Forum statistics

Threads
1,213,561
Messages
6,114,316
Members
448,564
Latest member
ED38

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