List all Worksheets a Value is listed

sdizzle

New Member
Joined
Nov 23, 2016
Messages
3
Hello and thanks in advance for your help!

I have 97 or so worksheets in a workbook. Each worksheet represents a Data Table and has all of the column names from those data tables listed in Column A. I also have a worksheet, named "Unique Fields" that lists all of the unique column names from each of the 97 worksheets listed in Column A.

What I am hoping for is a Macro/Function that will look up each value in my "Unique Fields" sheet, look through all of the other worksheets and then list all of the sheet names that it finds that value in.

For Example:
In my "Unique Fields" sheet, my unique values start in A2 and go to A500. The value in A2 is "ProductPlanID". That value is found in multiple worksheets, such as "ContractType", "FlexProduct" and "Product Status". On my "Unique Fields" sheet, I would like it to return B2 as "Contract Type", C2 as "FlexProduct", D2 as "Product Status", etc.

Any help would be greatly appreciated!
 

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.
Which row are the column names on in the other sheets?
 
Upvote 0
They could be in any row, but they will all be in column A. So going back to my example, "ProductPlanID" could be in A4 in the sheet "ContractType", A50 in "FlexProduct" and A100 in "ProductStatus".
 
Upvote 0
Code:
Sub F()


    Dim sh As Worksheet, wksUniq As Worksheet
    Dim r As Long, c As Long
    Dim sName$
    Dim cell As Range
    
    Set wksUniq = Sheets("Unique Fields")
    For r = 2 To 500
        c = 1
        sName = wksUniq.Cells(r, 1)
        For Each sh In Sheets
            If Not sh.Name = wksUniq.Name Then
                Set cell = sh.Cells.Find(sName)
                If Not cell Is Nothing Then
                    c = c + 1
                    wksUniq.Cells(r, c) = sh.Name
                End If
            End If
        Next
    Next


End Sub
 
Upvote 0
Code:
Sub UniqueFields()
Dim sh As Worksheet, dSht As Worksheet, R As Range, c As Range, F As Range, V As Variant, ct As Long, S As String
Set dSht = Sheets("Unique Fields")
Set R = dSht.Range("A2:A" & dSht.Cells(Rows.Count, "A").End(xlUp).Row)
For Each c In R
    For Each sh In Worksheets
        If sh.Name <> dSht.Name Then
            Set F = sh.UsedRange.Find(c.Value)
            If Not F Is Nothing Then
                ct = ct + 1
                S = S & "," & sh.Name
            End If
        End If
    Next sh
    If ct > 0 Then
        V = Split(Right(S, Len(S) - 1), ",")
        dSht.Range(c.Offset(0, 1), c.Offset(0, ct)).Value = V
    End If
    ct = 0
    Erase V
Next c
End Sub
 
Last edited:
Upvote 0
Oops, thought you were dealing with headers across columns.:eek:

Try this.
Code:
Dim wsUnique As Worksheet
Dim ws As Worksheet
Dim rngFields As Range
Dim rngFld As Range
Dim arrShts As Variant
Dim Res As Variant
Dim cnt As Long

    Set wsUnique = Sheets("UniqueFields")

    Set rngFields = wsUnique.Range("A2", wsUnique.Range("A" & Rows.Count).End(xlUp))

    For Each rngFld In rngFields
        cnt = 0
        ReDim arrShts(1 To Sheets.Count)
        For Each ws In ActiveWorkbook.Sheets
            If ws.Name <> wsUnique.Name Then
                Res = Application.Match(rngFld.Value, ws.Columns(1), 0)
                If Not IsError(Res) Then
                    cnt = cnt + 1
                    arrShts(cnt) = ws.Name
                End If
            End If
        Next ws
        If cnt > 0 Then
            ReDim Preserve arrShts(1 To cnt)
            rngFld.Offset(, 1).Value = Join(arrShts, ",")
        End If
    Next rngFld
 
Upvote 0

Forum statistics

Threads
1,213,534
Messages
6,114,185
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top