• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 420
  • Last Modified:

Excel Spreadsheet Sheet Comparison

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.

0
rdracer58
Asked:
rdracer58
2 Solutions
 
TonyWongCommented:
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 :)

Cheers,
Tony
0
 
Saqib Husain, SyedEngineerCommented:
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

0
 
rlarianCommented:
try this
Book1.xlsm
0
 
TracyVBA DeveloperCommented:
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now