# 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

#1
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 Next '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 Next 'Loop for next column Next End Sub```

Report •
Related Solutions