compare rows to columns using excel formula

[divmorales]

Hi,

I have two reports I need to compare, the 1st one is presented vertically and the 2nd one is horizontally. I need to compare that both reports got the same amount however I don't know the formula.

Example data:


1ST Report

ID Company Amount1 Amount2

1 A 100 50

1 B 200 100

1 C 300 150

2 A 150 75

2 B 150 75

2 C 200 100


2ND REPORT

ID Co.A-Amount1 Co.B-Amount1 Co.C-Amount1 Co.A-Amount2 Co.B-Amount2 Co.C-Amount3

1 100 200 300 50 100 150

2 150 150 200 75 75 100


Thanks!

 

[Faseeh]

Hi divmorales,


Welcome to the forum,


Assuming that your first report is in Sheet1 in A1:D7 and the second report in sheet2 between A1:G3, use this formula and drag to right and down:

=IF(SUMPRODUCT((Sheet2!$A$2:$A$3=$A2)*(Sheet2!$B$1:$G$1=("Co."&$B2&"-"&C$1))*(Sheet2!$B$2:$G$3))=C2,"Match","-")

Let me know if it does't help i will upload the sample file.


Regards,

Faseeh