Thread: Excel: Multiple worksheets - comparing text values

Hello all,
I am attempting to read a partial column (A20: Aundetermined) and then compare that with partial columns in multiple worksheets in the same Excel file. I am not a heavy user of Excel but I did stumble across this formula (=IF(ISNA(MATCH ...). If I copy and paste all the appropriate columns from my multiple worksheets onto a new sheet I can then run this formula to compare the text strings from column to column. The formula will produce a 1 (if a match is found) or a 0 in a new column. At the end I then sum these new columns to give me the number of times a value occured in these columns.The issue though, is I want to be able to let other users do this painlessly. In other words perhaps write a macro that would automatically compare this list of column values to other lists (same cell locations) on multiple sheets. One other thing, the worksheets are named by date (ie. 7/2/08, 7-1-08 etc..). I hope this is understandable to most people...if not please email and I will attempt to clarify.

Thank you,
Lonnie Meinke

Hi
Paste the following codes in to the macro window( Alt F11 Insert > module)

Sub lmeinke()
x = Worksheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Row
For b = 20 To x
For a = 2 To Sheets.Count
Cells(2, a) = Worksheets(a).Name
y = Worksheets(a).Cells(Rows.Count, 1).End(xlUp).Row
Cells(1, 1) = "=match(A" & b & "," & Cells(2, a) & "!A20:A" & y & ",0)"
Cells(b, a) = Cells(1, 1)+19
Next a
Next b
End Sub

Run the macro. It will match text of col A from first sheet (A20 to Last row) and lists the row no where it matches. Try it and I will modify it based on your feed back
Ravi

Thank you for the suggestion Ravi,

I end up with a type mismatch error (13 I believe).

Just for clarification I am attempting to compare A20 through perhaps A40 (the number of records can vary by day) to the same columns and rows on previous worksheets in the same workbook.

I can't exactly follow your code...is this what you are attempting?

Thank you again for the help.

Lonnie