Excel 2010 - finding SSN matches in two columns and then compare amounts

[blueboyz]

I need to find the matching SSN/TINS (that appear on two different worksheets) and then compare the 1099 amounts for that SSN/TIN. If the amounts match, do nothing. If the amounts are different, highlight the amounts or put the SSN/TIN and the two different amounts in new columns.

Is this possible to do in Excel?

Thank you in advance.

 

[Deniall]

In English ?

 

[SkipVought]

Hi,

What happens in vagueness, stays in vagueness!

I'd use the SUMPRODUCT() function in an adjacent column.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[kray4660]

Sounds like a job for VBA if the SSN's are not ordered the same or there are missing SSN's in one of the columsn.

 

[SkipVought]

No VBA required.


Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[zelgar]

Skip, can you describe how SUMPRODUCT function would work in this situation. The function might be able to put the different amount in new columns (highlighting might also be possible with conditional formatting), but how would you get the data using the SUMPRODUCT function? For simplicity sake, let's say the spreadsheets are called ORIG and REV, the data for both spreadsheets are in Sheet1 and the SSN/TINS are in Col A & the 1099 values are in Col B. What would the SUMPRODUCT function need to look like?

 

[SkipVought]

1) Naturally, the 1099 values are numbers.
2) I have set up the two tables on two sheets as Structured Tables with the names tORIG and tREV.

[pre]
tORIG in sheet ORIG

SSN/TIN 1099 REV
123 55 66 =SUMPRODUCT((tREV[SSN/TIN]=[@[SSN/TIN]])*(tREV[1099]))
234 66 11
-------------------
tREV in sheet rev

SSN/TIN 1099
345 44
123 66
234 11
[/pre]]


Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[Deniall]

The method Skip is suggesting is well described at URL

http://www.xldynamic.com/source/xld.SUMPRODUCT.html

 

[SkipVought]

I am indebted to Microsoft MVP Ken Wright for explaining this unique use of the SUMPRODUCT() function here many years ago!

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]