Compare diffrerent colomns based on one colomn

[luckyvasul]

2014-07-30_16-52-23.png

Fist I want to compare ColB=ColH, if TRUE, compare colA=colG, if TRUE, again compare colC=colI, IF all this conditions true, then give (colD-colJ) on colL.

all the unmatched rows in 2 tables to populate with different for each table

Please do help me, very urgent in need?

[Speshul]

Try this, untested formula in column L. Start in row 1 and copy the formula down:

=if(and(b1=h1,a1=g1,c1=i1),d1-j1,"")



Welcome to the forum!

[Vikas_Gautam]

all the unmatched rows in 2 tables to populate with different for each table

what does that mean..?

Don't forget to click *

[Vikas_Gautam]

If you want to find unmatched rows then you can use conditional formatting...

where formula is...

=or($b1<>h1,$a1<>g1,$c1<>i1)

select the range first... before applying that formula..

Don't forget to click *

[luckyvasul]

2014-07-31_8-40-12.png

Thank Gautam , for your reply.

As in the attached picture. the formula stops at row 17, as the B17 != H17 (not to compare the colomns, look for the values in colomns to be matched, and then take the difference)

I need to populate data into new table as,

1. first compare look for ColB=ColH , then Look for colG=ColA, then look for ColI=ColC, if all these all matched, copy the matched data into another coloms (seperate table within the sheet), adding another colomn with difference of colD-colJ
2. If any of them are not matched (or not existed) in either of the tables, the rows of Table1 and Table2, should populate with different colors (for reference)
3. If they ar not matched with Table 2