+ Reply to Thread
Results 1 to 7 of 7

VBA Creating a list of unique values from one column based on criteria from another column

Hybrid View

bilbo85 VBA Creating a list of unique... 02-09-2016, 12:30 PM
JOHN H. DAVIS Re: VBA Creating a list of... 02-09-2016, 12:41 PM
bilbo85 Re: VBA Creating a list of... 02-09-2016, 01:29 PM
Vraag en antwoord Re: VBA Creating a list of... 02-09-2016, 12:44 PM
JOHN H. DAVIS Re: VBA Creating a list of... 02-09-2016, 02:20 PM
bilbo85 Re: VBA Creating a list of... 02-09-2016, 02:35 PM
JOHN H. DAVIS Re: VBA Creating a list of... 02-09-2016, 02:38 PM
  1. #1
    Registered User
    Join Date
    12-08-2015
    Location
    Birmingham, England
    MS-Off Ver
    2007
    Posts
    44

    VBA Creating a list of unique values from one column based on criteria from another column

    Hi,

    Using VBA, is it possible to create a unique list of values from column A if column B meets a certain criteria?

    For example,

    COL A COL B
    Dave Overdue
    Dave Not Overdue
    Glen Overdue
    Glen Overdue
    Ben Not Overdue
    Ben Not Overdue
    Adam Not Overdue
    Adam Overdue

    If the criteria is column B containing "Overdue", the returned list should be:

    Dave
    Glen
    Adam

    This removes "Ben" as neither row contains "Overdue" and then only returns unique values.

    This unique list should be returned in a new sheet if possible.

    Thanks
    Last edited by bilbo85; 02-09-2016 at 02:45 PM.

  2. #2
    Forum Expert
    Join Date
    06-12-2012
    Location
    Ridgefield Park, New Jersey
    MS-Off Ver
    Excel 2003,2007,2010
    Posts
    10,241

    Re: VBA Creating a list of unique values from one column based on criteria from another co

    Maybe:

    Sub bilbo85()
    Dim ws As Worksheet, x As Range, i As Long
    Set ws = ActiveSheet
    Sheets.Add.Name = "New Sheet"
    With ws
        For i = 2 To .Range("B" & Rows.Count).End(3).row
            If .Cells(i, "B") = "Overdue" Then
                Set x = Sheets("New Sheet").Columns(1).Find(.Cells(i, "A"), LookIn:=xlValues, lookat:=xlWhole)
                    If Not x Is Nothing Then
                        GoTo zz
                    Else
                        Sheets("New Sheet").Range("A" & Rows.Count).End(3)(2) = .Cells(i, "A")
                    End If
                Set x = Nothing
            End If
    zz:
        Next i
    End With
    
    End Sub

  3. #3
    Registered User
    Join Date
    12-08-2015
    Location
    Birmingham, England
    MS-Off Ver
    2007
    Posts
    44

    Re: VBA Creating a list of unique values from one column based on criteria from another co

    Thanks John, this is working nicely.

    If the data starts in row 2, is it possible to pull through the header as well into the new sheet?

  4. #4
    Valued Forum Contributor
    Join Date
    04-01-2015
    Location
    The Netherlands
    MS-Off Ver
    2003/2007/2010/2016/office 365
    Posts
    880

    Re: VBA Creating a list of unique values from one column based on criteria from another co

    When your data start in A1

    Sub VenA()
    ar = Sheets(1).Cells(1).CurrentRegion
    For j = 2 To UBound(ar)
        If ar(j, 2) = "Overdue" And InStr(1, c00, ar(j, 1)) = 0 Then c00 = c00 & "|" & ar(j, 1)
    Next j
    Sheets(2).Cells(1).Resize(UBound(Split(c00, "|"))) = Application.Transpose(Split(Mid(c00, 2), "|"))
    End Sub

  5. #5
    Forum Expert
    Join Date
    06-12-2012
    Location
    Ridgefield Park, New Jersey
    MS-Off Ver
    Excel 2003,2007,2010
    Posts
    10,241

    Re: VBA Creating a list of unique values from one column based on criteria from another co

    Try:

    Sub bilbo85()
    Dim ws As Worksheet, x As Range, i As Long
    Set ws = ActiveSheet
    Sheets.Add.Name = "New Sheet"
    Rows(1).Value = ws.Rows(1).Value
    With ws
        For i = 2 To .Range("B" & Rows.Count).End(3).row
            If .Cells(i, "B") = "Overdue" Then
                Set x = Sheets("New Sheet").Columns(1).Find(.Cells(i, "A"), LookIn:=xlValues, lookat:=xlWhole)
                    If Not x Is Nothing Then
                        GoTo zz
                    Else
                        Sheets("New Sheet").Range("A" & Rows.Count).End(3)(2) = .Cells(i, "A")
                    End If
                Set x = Nothing
            End If
    zz:
        Next i
    End With
    
    End Sub

  6. #6
    Registered User
    Join Date
    12-08-2015
    Location
    Birmingham, England
    MS-Off Ver
    2007
    Posts
    44

    Re: VBA Creating a list of unique values from one column based on criteria from another co

    Great, thanks.

  7. #7
    Forum Expert
    Join Date
    06-12-2012
    Location
    Ridgefield Park, New Jersey
    MS-Off Ver
    Excel 2003,2007,2010
    Posts
    10,241

    Re: VBA Creating a list of unique values from one column based on criteria from another co

    You're welcome. Glad to help out and thanks for the feedback. Please mark this thread as SOLVED.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Creating a Filter loop based on unique values in a column
    By Ragnarok_421 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 08-23-2014, 05:34 AM
  2. Replies: 3
    Last Post: 07-08-2014, 03:10 PM
  3. Creating a comma seperated list based on a search criteria from a column
    By HUGH JORGAN in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 10-30-2013, 05:20 PM
  4. [SOLVED] Sum Unique Values Based on Other Column Criteria
    By jfist85 in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 05-06-2013, 03:21 PM
  5. [SOLVED] Summary statistics based on criteria for unique values in column
    By nearlylost in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 09-23-2012, 04:10 AM
  6. Creating a list in one column based on criteria in two other columns!?
    By chelseasikoebs in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 05-02-2009, 11:00 PM
  7. how to loop Criteria based on unique values in a column
    By excelkeechak in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 04-03-2009, 11:01 AM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1