Identify the Sheet Name Where an Excel Cell Value is Present Across Multiple Sheets and Display it in a Separate Column

The column where the output should be placed on the destination sheet is called “OutputCol”. The loop is designed to cover Rows 1 to 10 on each sheet, but can be adjusted as needed. Another approach is to assume that the lookup values are stored in sheet1 column A, while the lookup column is sheet2 column A. There are 42 other sheets containing 20 to 1500 values each, all in a single column A. The goal is to check whether any of the approximately 2000 values in column A of sheets 1 and 2 exist in any of the other 42 sheets.

Question:

My workbook consists of two sheets, with a single column A having around 2000 values each. Apart from these, there are 42 other sheets that have a column A each, with varying values ranging from 20 to 1500.

I am attempting to verify whether the approximately 2000 entries in column A from sheets 1 and 2 correspond to any of the
values exist
in the remaining 42 sheets. If they do, then I want the corresponding sheet name to be displayed in column B.

So:

IF sheet1.A1.value EXISTS IN sheet3.A:A
RETURN sheet3.name
ELSE IF sheet1.A1.value EXISTS IN sheet4.A:A
RETURN sheet4.name

etc…


Solution 1:

Sub test()
Dim ws As Worksheet
Dim i As Integer
Dim fRange As Range
'Perform search for Sheet1
Set ws = Sheets("Sheet1")
i = 3
While i <= ActiveWorkbook.Sheets.Count
ws.Select
Set fRange = Range("A1")
fRange.Select
While fRange.Value <> ""
Sheets(i).Select
Range("A1").Select
While ActiveCell.Value <> ""
If ActiveCell.Value = fRange.Value Then
fRange.Offset(0, 1).Value = Sheets(i).Name
ActiveCell.Offset(1, 0).Select
Else
ActiveCell.Offset(1, 0).Select
End If
Wend
Set fRange = fRange.Offset(1, 0)
Wend
i = i + 1
Wend
'Perform search for Sheet2
Set ws = Sheets("Sheet2")
i = 3
While i <= ActiveWorkbook.Sheets.Count
ws.Select
Set fRange = Range("A1")
fRange.Select
While fRange.Value <> ""
Sheets(i).Select
Range("A1").Select
While ActiveCell.Value <> ""
If ActiveCell.Value = fRange.Value Then
fRange.Offset(0, 1).Value = Sheets(i).Name
ActiveCell.Offset(1, 0).Select
Else
ActiveCell.Offset(1, 0).Select
End If
Wend
Set fRange = fRange.Offset(1, 0)
Wend
i = i + 1
Wend
End Sub


Solution 2:


Iterate through the worksheets using a For Each loop. Employ the Range.Find method to verify the presence of the cell in the worksheet. In case of existence, write down the name of the sheet on the cover sheet. You can either merge to a single cell or use a counter to switch to the next unoccupied column.

Frequently Asked Questions