Multi-Sheet Combine and Compare Data

[BDHamp]

I can generally figure out simple scripts or adapting existing scripts to suit my needs, and I have looked through various searches here and attempted to adapt scripts that do something similar to what I'm trying to do. But I'm pulling what's left of my hair out. And, I am having trouble distilling this down to its essential elements, so please pardon the length.

What I have is this:

- Two sheets of data representing clients common to two different groups.
- These sheets are generated by each group's own accounting process.
- Each sheet has common or similar fields.
- Each client has a unique ID common to both sheets (ExternalID).
- Each sheet also has its own unique ID (InternalID1 on Sheet1 and InternalID2 on Sheet2)
- Each client can have multiple entries on each sheet.
- Some clients are reported on Sheet2 but not on Sheet1 and vice versa.
- Most of the non-reported clients are caused by a discrepancy with ExternalID. In these cases, The left 9 characters of InternalID1 usually has the correct number.

What I need to do is this:

- Find all unique ExternalID numbers from Sheet1 and Sheet2, sum the totals for ExternalTotal and InternalTotal and place all relevant info in a single row on Sheet3 with these totals side-by-side.

The ultimate goal is comparison of ExternalTotal and InternalTotal to make sure it is the same and to find the discrepancies in ID# that make it seem to one group or the other than this is a separate client.

In the past this has been eye-balled after printing out each sheet. There can be upwards of 5,000 unique IDs, so as you can imagine, that can consume a person's entire week. I only really need help with combining all the data for each unique ID# and putting it onto Sheet3. If I can get it all there, the rest I know how to do.

I have attached a spreadsheet with sample data that shows exactly how the reports appear in Sheets 1 and 2 in addition to what I want in Sheet3.

Sorry for the wall of text. Any guidance at all will be appreciated.

[jindon]

Try the attached

[BDHamp]

Merciful heavens, you went above and beyond.

This will require tweaking, but you got me past the major hurdle I was having. With the actual data (5388 records), it essentially generates two lists, one based on the Sheet1 data, the other on Sheet2 and places them one on top of the other. I think I can manipulate this to get what I need and will report back once I have had time to play with this today. It works perfectly with the test data of course. I haven't had time to scrutinize the code, but after a brief glance I suspect the problem may be with column labels, which are different (entirely non-descriptive jargon) on the actual sheets the accounting processes spit out. I used descriptive labels in the examples to lessen confusion.

Again, thank you. I can't imagine how to repay you.

[BDHamp]

Well, I was completely wrong about the problem, which was obvious once I actually looked at the code.

It was a data type problem. One of the reports spits out the ID number that is serving as the key as text whereas the other does it as a number. So, the script was seeing it as two separate things.

I'll add a little script that changes that to a number. For testing purposes I did it "manually" on a separate column with =VALUE(text), then changed where the script was looking, and it worked absolutely perfectly.

Thank you again.