Comparing Two Excel Sheets with IDs, One Sheet is larger than the other

Help Activities
, , ,
1

Test.zip (11.8 KB)

Good afternoon,

I’ve provided the two excel files (foo data) to help explain my current question.

Scenario:

I have two different worksheets that share a common denominator column called the “PHY ID”

I am using this column to compare and find like values from both worksheets and take the matching rows and push them out to an additional sheet of ‘results’.

Excel files:
Compliance Provider Roster - Smaller data (has less rows)
Annual Training Report - Larger data (has more rows)

I have approached the solution as follows:

  1. Read in both sheets, output each to a corresponding data table, sort data tables on column “PHYID” on both excel files by ascending
  2. Create a Results data table and clear this data table.
  3. To Compare: Create a For Each Row with the outer - Annual Training Report data table being the larger of the two and wrapping another for each row inside of that: inner - Compliance Provider Roster
  4. Compare the PHY ID’s as shown in the screen show.

Question:
Because there are not equal amount of rows on the reports, is there a way I could write the else statement if it does find a match (nested if) to not re-check values in that column again because further comparisons aren’t going to yield anything if the number (PHY ID) is bigger than the number I am searching for. I’m not sure how to write this.

Thanks

image
image959×811 66.6 KB

1 Like
2

you can use joins for this scenario

3

could you elaborate?

4

the example files I provided aren’t good examples because one of the sheets (the actual protected data, i can’t post here) has several more columns

5

please follow this video

1 Like
6

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.