Page 1 of 2 12 LastLast
Results 1 to 10 of 17

Extract unique values from one column using VBA

This is a discussion on Extract unique values from one column using VBA within the Excel Questions forums, part of the Question Forums category; Hi, I want to extract all the unique values from column A starting with A2 to the last cell of ...

  1. #1
    Board Regular
    Join Date
    Mar 2010
    Posts
    324

    Default Extract unique values from one column using VBA

    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



    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

    Thanks,

    BC

  2. #2
    Board Regular jdsouza's Avatar
    Join Date
    Jul 2012
    Posts
    96

    Default Re: Extract unique values from one column using VBA

    Try this ..

    Code:
    Public Sub ABC()Dim StartRow As Long, EndRow As Long, J As Integer, K As Long, L As Integer
    Dim fGroup As Boolean
    'Assuming your data is in Sheets(1) and 'All Codes' is in column 'C'
    Application.ScreenUpdating = False
    StartRow = 2
    Sheets(1).Select
    Sheets(3).Columns("A:IV").Clear
    Sheets(2).Columns("A:IV").Clear
    Sheets(1).Cells(1, 3).Select
    Sheets(1).Cells(ActiveSheet.Rows.Count, 3).Select
    EndRow = Selection.End(xlUp).Row
    Sheets(1).Cells(1, 3).Select
    Sheets(1).Range(Range(Cells(StartRow, 3), Cells(EndRow, 3)).Address).Copy
    Sheets(1).Cells(1, 4).Select
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False
    Sheets(1).Range(Range(Cells(StartRow, 4), Cells(EndRow, 4)).Address).RemoveDuplicates Columns:=1, Header:=xlYes
    Sheets(1).Cells(1, 4).Select
    Sheets(1).Cells(ActiveSheet.Rows.Count, 4).Select
    EndRow = Selection.End(xlUp).Row
    Sheets(1).Range(Range(Cells(StartRow, 4), Cells(EndRow, 4)).Address).Copy
    
    
    MsgBox "Done." & vbNewLine & "Unique records copied to clipboard"
    
    
    End Sub

  3. #3
    Board Regular
    Join Date
    Feb 2009
    Location
    Massachusetts, USA
    Posts
    1,777

    Default Re: Extract unique values from one column using VBA

    Another option:

    Code:
    Public Sub Test()
    
    ActiveSheet.Range("A2:A65536").AdvancedFilter Action:=xlFilterCopy, CopyToRange:=ActiveSheet.Range("B2"), Unique:=True
    
    End Sub
    Murphy's Fifth Law: If anything absolutely can NOT go wrong, it will anyway.

  4. #4
    Board Regular
    Join Date
    Apr 2009
    Location
    Northeast PA, USA
    Posts
    10,755

    Default Re: Extract unique values from one column using VBA

    boldcode,


    Sample raw data:


    Sheet1

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


    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  


    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.
    Have a great day,
    hiker95

    Windows 8.1, Excel 2007

  5. #5
    New Member
    Join Date
    Jul 2012
    Posts
    21

    Default Re: Extract unique values from one column using VBA

    Code:
    Sub FindDistinctValues()
    Dim LastRowFrom As Long
    Dim LastRowTo As Long
    Dim i As Long, j As Long
    Dim temp As Integer
    Dim found As Boolean
    'determines the last row that contains data in column A
    LastRowFrom = Range("A" & Rows.Count).End(xlUp).Row
    'Loop for each entry in column A
    For i = 2 To LastRowFrom
       'get the next value from column A
       temp = Range("A" & i).Value
       
       'Determine the last row with data in column B
       LastRowTo = Range("B" & Rows.Count).End(xlUp).Row
       
       'initialize j and found
       j = 1
       found = False
       
       'Loop through "To List" until a match is found or the list has been searched
       Do
          'check if the value exists in B column
          If temp = Range("B" & j).Value Then
             found = True
          End If
          'increment j
          j = j + 1
          Loop Until found Or j = LastRowTo + 1
       
       'if the value is not already in column B
       If Not found Then
          Range("B" & j).Value = temp
       End If
    Next i
    End Sub

  6. #6
    Board Regular
    Join Date
    Mar 2010
    Posts
    324

    Default Re: Extract unique values from one column using VBA

    Gary, lostitagain, hiker95, jdsouza,

    I gave all 4 recommendations a try and they all produce the result I was looking for. Thank you all for your responses, I appreciate it.

    BC

  7. #7
    Board Regular
    Join Date
    Apr 2009
    Location
    Northeast PA, USA
    Posts
    10,755

    Default Re: Extract unique values from one column using VBA

    boldcode,

    Thanks for the feedback.

    You are very welcome. Glad we could help.

    Come back anytime.
    Have a great day,
    hiker95

    Windows 8.1, Excel 2007

  8. #8
    Board Regular
    Join Date
    Jan 2013
    Posts
    55

    Default Re: Extract unique values from one column using VBA

    How do I do this to loop through either all or specific sheets in a workbook and not have it overwrite the output on each loop?

    Quote Originally Posted by hiker95 View Post
    boldcode,


    Sample raw data:


    Sheet1

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


    Excel tables to the web >> Excel Jeanie HTML 4




    After the macro:


    Sheet1

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


    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.
    Last edited by blthlt; Feb 3rd, 2014 at 11:31 AM.

  9. #9
    Board Regular
    Join Date
    Apr 2009
    Location
    Northeast PA, USA
    Posts
    10,755

    Default Re: Extract unique values from one column using VBA

    blthlt,

    How do I do this to loop through either all or specific sheets in a workbook and not have it overwrite the output on each loop?
    I am going to guess that your raw data structure is not the same as boldcode's.

    Please do not post your questions in threads started by others - - this is known as thread hijacking.

    Always start a new thread for your questions and, if you think it helps, provide a link to any other thread as a reference.

    Start a new thread for your question and be sure to give it a search friendly title that accurately describes your need.


    In your NEW thread also include:

    What version of Excel and Windows are you using?

    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 your data, you can download and install one of the following two programs:
    Excel Jeanie
    MrExcel HTML Maker

    Or, when using Internet Explorer, just put borders around your data in Excel and copy those cells into your post.
    See reply #2 the BLUE text in the following link:
    how to create table like Aladin


    If you are not able to give us screenshots:
    You can upload your workbook to Box Net,
    sensitive data changed
    mark the workbook for sharing
    and provide us with a link to your workbook.


    Then send me a Private Message, with a link to your NEW thread, and, I will have a look.


    If you are not able to do 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.
    Have a great day,
    hiker95

    Windows 8.1, Excel 2007

  10. #10
    Board Regular
    Join Date
    Jan 2013
    Posts
    55

    Default Re: Extract unique values from one column using VBA

    Quote Originally Posted by hiker95 View Post
    blthlt,



    I am going to guess that your raw data structure is not the same as boldcode's.

    Please do not post your questions in threads started by others - - this is known as thread hijacking.

    Always start a new thread for your questions and, if you think it helps, provide a link to any other thread as a reference.

    Start a new thread for your question and be sure to give it a search friendly title that accurately describes your need.


    In your NEW thread also include:

    What version of Excel and Windows are you using?

    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 your data, you can download and install one of the following two programs:
    Excel Jeanie
    MrExcel HTML Maker

    Or, when using Internet Explorer, just put borders around your data in Excel and copy those cells into your post.
    See reply #2 the BLUE text in the following link:
    how to create table like Aladin


    If you are not able to give us screenshots:
    You can upload your workbook to Box Net,
    sensitive data changed
    mark the workbook for sharing
    and provide us with a link to your workbook.


    Then send me a Private Message, with a link to your NEW thread, and, I will have a look.


    If you are not able to do 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.
    Actually, I thought since the OP's question WAS ANSWERED, me asking my question shouldn't have been a thread jack, rather I'm asking how to do the same VBA code just looped through multiple sheets. I've always been around forums where people would help people instead of "over moderate". I see no harm in ADDING TO the information in this thread seeing how I can make the changes to the existing code work with my raw data. I honestly feel with the time you took to write your "go start your own thread" post you could have answered my question which was, how do I loop THIS CODE through all sheets in a workbook. I don't think I'm asking for too much considering I thought most forums didn't like duplicate post. Ill hold off on doing your recommendation as I would rather wait for someone to just answer my question; how do I loop the code in my first post quote through all pages of a book.

Page 1 of 2 12 LastLast

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com