Find Common Values in All Columns

April 2, 2009 at 09:18:16
Specs: Windows XP
I have a spreadsheet with 42 (A thru AP) columns of text values. Some values appears in all 42 columns, while most appear in some, but not all. Is there a macro that will scan the columns for values that appear in all 42 columns & place it in the 43rd (AQ)column?

I found a few threads that dealth with filtering, but I don't think that will work.

See More: Find Common Values in All Columns

Report •

April 2, 2009 at 13:57:22
re: Is there a macro that will....

There is now!

Sub FindCommonValues()
'Loop through 42 columns
 For col = 1 To 42
'Find last cell with data in each column
  lastRow = Cells(Rows.Count, col).End(xlUp).Row
'Loop through each value in column
   For Each myVal In Range(Cells(1, col), Cells(lastRow, col))
'Reset Found counter
    foundCount = 0
'Search all columns for value
     For srchCol = 1 To 42
      With Columns(srchCol)
       Set c = .Find(myVal, lookat:=xlWhole)
'Increment counter each time value is found
        If Not c Is Nothing Then foundCount = foundCount + 1
       End With
'If 42 values are found, value is in every column
   If foundCount = 42 Then
'Make sure value hasn't already been listed
      lastAQrow = Cells(Rows.Count, "AQ").End(xlUp).Row
       With Range("AQ1:AQ" & lastAQrow)
        Set c = .Find(myVal)
         If c Is Nothing Then
'If it's not already there, put value in next cell
          nxtVal = nxtVal + 1
          Range("AQ" & nxtVal) = myVal
         End If
        End With
     End If
'Loop for next value
'Loop for next column
End Sub

Report •
Related Solutions

Ask Question