Excel Spreadsheet Sheet Comparison

Posted on 2011-05-04
Last Modified: 2012-08-14
We have an Excel spreadsheet with multiple sheets/tabs. On the first tab we have a full list of clients. Each of the following tabs represent individual months in which migrations will take place, and contain subsets of the clients contained on the first tab.

On the first tab containing the complete list of clients, we want to know which month, and consequently which secondary tab, they are on. On all tabs each client is identified by a unique ID number.

Is there a way to match the IDs on the main list and notate which other sheet(s) they appear on? We would obviously not want to manually go through all the data, so if there's an at least semi-more automated way to complete this, it would be appreciated.

Question by:rdracer58
    LVL 1

    Expert Comment

    Hi there,

    First thing that comes to my mind is VLOOKUP or  INDEX function depending on the criteria you are wanting to incorporate and layout of referenced sheets.
    Uploading a test workbook with dummy data will help us immensely to get to a solution for you RdRacer :)

    LVL 43

    Accepted Solution

    Try this macro. I did not test it as I had no data in hand.
    Sub listmonths()
    Set fws = ActiveWorkbook.Sheets("sheet1")  '<--------Name of first worksheet
    For Each cel In fws.Range("A:A").Cells '<--------column which contains the IDs on first sheet
        For Each ws In ActiveWorkbook.Sheets
            If fws.Name <> ws.Name Then
                If Not IsEmpty(cel.Value) Then
                    If Not (ws.Range("A:A").Find(cel.Value)) Is Nothing Then '<--------column which contains the IDs on secondary sheets
                        cel.Offset(0, 2) = ws.Name '<-----results to be inserted two columns from the ID
                    End If
                End If
            End If
        Next ws
    Next cel
    End Sub

    Open in new window

    LVL 4

    Assisted Solution

    try this
    LVL 24

    Expert Comment

    This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    What Security Threats Are You Missing?

    Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

    In case Office 2010 has not been deployed in your environment, this article may be quite useful. In our office, we wanted a way to deploy Microsoft Office Professional Plus 2010 through an automated batch file via logon script. This article is docum…
    My experience with Windows 10 over a one year period and suggestions for smooth operation
    The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
    This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

    759 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    13 Experts available now in Live!

    Get 1:1 Help Now