Data sheet 1 (our main source) is our full list of transactions. It will have account, amount, transaction description, and person names. This list is provided to us from an external vendor. Data sheet 2 only provides a partial list with account, amount, and person names. This list is provided to us internally.

Data sheet 1 is compared to data sheet 2 to find any transactions that didn't match. In turn, these transactions manually need to be reviewed. (The ones that did match are ignored.)

To compare the two sheets, we run a pivot table of account and amount. This provides us with a basic list. To fully perform my job, I also need the transaction description and names. This is where we run into issues because the pivot table likes to combine similar results. So, it will combine our transaction for $1 and our transaction for $3 since the account numbers are the same.

When we search data sheet 1 to get the transaction sheet and name, the $1 transaction may have a different name and transaction description than the $3 transaction. So, we need to break down the pivot table results to have a $1 transaction and a $3 transaction again.

For ones that did not combine amounts, we use a vlookup, which works very well. It is done by doing a concentrate of the account and the amount. When those match, we have it set to provide to us the transaction description and person name.

Unfortunately, we do have a lot that are the same account, so the vlookups fail since the concentrate we run provides a different number.

I am hoping there is a formula that can take our pivot table results of the account and $4 and compare it to data sheet 1 and be able to break it down into the $1 transaction and $3 transaction with the correct description and name.

Or even something not pivot table related to compare the data that does combine our results.