+ Reply to Thread
Results 1 to 2 of 2

Need to find matching data in filtered columns

Hybrid View

  1. #1
    Valued Forum Contributor
    Join Date
    01-16-2012
    Location
    England
    MS-Off Ver
    MS 365
    Posts
    1,398

    Need to find matching data in filtered columns

    Probably missing something obvious here.

    Code needs to filter Col C to show only entries with "2" value (which means there are two records with the same combination in Col B).

    What I cannot see is how to then "find" each pair in Col B? Because if I don't know what row number will be the first filtered record, I won't be able to set the "Find" function to look for whatever is in Col B of that row, then cycle to the next visible row.

    Having found the pair, the Code will then compare Col A. If both values match, the records go to Col H - J, and if they are different the records go to Col M, as shown on the attached.

    
    Option Explicit
    
    Dim a As Integer
    Dim rng As Range
    
    
    Sub SETPAIRS()
    
        With ActiveSheet
        a = .Cells(.Rows.Count, "A").End(xlUp).Row
        End With
    
    'Select rows where Column C is 2:
    
        ActiveSheet.Range("A1:C" & a).AutoFilter Field:=3, Criteria1:="2"
    
    'Find "pairs" in Col B: 
    
    'Loop through Column B, looking for matching values
       
        Set rng = Range("B2", Range("B").End(xlDown)).Cells.SpecialCells(xlCellTypeVisible)
        Const WHAT_TO_FIND As String = "??" 'How do I know the first filtered row number, or what it will say in Col B?
    
    'First thoughts on how to then check whether Col A is identical where a pair is found (Code incomplete) 
              Set m = ws.Range("B:B").Find(What:=WHAT_TO_FIND) 'This will be whatever is in Col B of the first filtered row
               If Not m Is Nothing Then
                For Each cell In rng
               Set m = .Find(Range("A" & rw))
               If m Is Nothing Then
              Next
    
        End Sub
    All suggestions, pointers and solutions accepted gratefully, as ever

    Ochimus
    Attached Files Attached Files
    Last edited by Ochimus; 12-09-2017 at 09:20 PM.

  2. #2
    Forum Expert mike7952's Avatar
    Join Date
    12-17-2011
    Location
    Florida
    MS-Off Ver
    Excel 2007, Excel 2016
    Posts
    3,520

    Re: Need to find matching data in filtered columns

    Maybe this will work for you

    Sub FindPairs()
     Dim arr, tmp, i As Long
     Dim key
    
     With ActiveSheet
        arr = .Range("a1").CurrentRegion
     End With
     
     With CreateObject("scripting.dictionary")
        For i = 1 To UBound(arr)
            If arr(i, 3) = 2 Then
                key = arr(i, 1) & arr(i, 2) & arr(i, 3)
                If Not .exists(key) Then
                    tmp = Array(arr(i, 1), arr(i, 2), arr(i, 3), 1)
                Else
                    tmp = .Item(key)
                    tmp(3) = tmp(3) + 1
                End If
                .Item(key) = tmp
            End If
        Next
        For Each key In .keys
            If .Item(key)(3) > 1 Then
                ActiveSheet.Cells(Rows.Count, "H").End(xlUp).Offset(1).Resize(.Item(key)(3), 3) = .Item(key)
            Else
                ActiveSheet.Cells(Rows.Count, "M").End(xlUp).Offset(1).Resize(, 3) = .Item(key)
            End If
        Next
     End With
    End Sub
    Thanks,
    Mike

    If you are satisfied with the solution(s) provided, please mark your thread as Solved.
    Select Thread Tools-> Mark 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. find most frequent entry in columns from a filtered selection
    By wookietv in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 12-08-2015, 05:19 PM
  2. find matching row data in two columns, delete the second row.
    By master-richie in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 01-14-2015, 11:35 AM
  3. [SOLVED] Counting filtered data and matching to specific monthly headers
    By macrorookie in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 04-02-2014, 09:44 PM
  4. Replies: 2
    Last Post: 04-18-2013, 05:56 PM
  5. Replies: 1
    Last Post: 03-30-2012, 02:40 PM
  6. Find matching number in two columns
    By Xalaal2009 in forum Excel Programming / VBA / Macros
    Replies: 11
    Last Post: 09-08-2009, 05:22 PM
  7. need to find matching cells in 2 columns
    By riotstar in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 05-13-2005, 07:33 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