Compare two columns for a specific project

[prasadsury]

Hello All,

Can one of you please help me with a solution for the following problem in excel:

I am comparing an Intercompany report which has the following columns:
1) Company Code
2) GL Account
3) IC Partner
4) Project Number
5) Closing balance

What I will need is in Col H, will need a formula to check if the Company code & IC partner is used accurately for a specific project.

I am working on a data set that is 3000 lines so any quick help will be greatly appreciated.

Thanks,
Prasad

 

[ETAF]

check if the Company code & IC partner is used accurately for a specific project.

what are you checking against ?
itself

Company code & IC partner is used accurately for a specific project.

can you provide examples of correct and incorrect entries

 

[prasadsury]

I am checking it for a project number

For project aa11 Company code in row 2 is used as an ICP in row17 and ICP in row 2 is used as company code in row 17 (referring to the excel file)

 

[ETAF]

so should the project number have the same codes
a countifs() should work
=IF(COUNTIFS($D$2:$D$17,D2,$A$2:$A$17,"<>"&A2,$C$2:$C$17,"<>"&D2),"check","")

but they are all wrong - from what i can see

the Company Code , ICP and Project - should always be the same

is that what you meant

I changed a couple of the rows to be the same codes and highlighted orange

 

[prasadsury]

Hey ETAF,

Thanks a lot for your swift response.

I actually meant that for aa11 project my row2 had company code as 120 and ICP as 270 but in row 17 whatever that was ICP in Row 2 (which is 270) is my company code and vice versa, so I need this check in column H. So in the sample that I shared all my combinations are would be correct (going by my above logic).

Please do let me know if this clarifies your query and appreciate if you can please help.

Thanks,
Prasad

 

[ETAF]

how about
=IF(OR(COUNTIFS($D$2:$D$17,D2,$A$2:$A$17,A2,$C$2:$C$17,C2)=0,COUNTIFS($D$2:$D$17,D2,$A$2:$A$17,C2,$C$2:$C$17,A2)=0),"check","")

i changed a row -so it does not match

may need some more examples - of wrong - so can test as 3000 lines may not work

 

[prasadsury]

This worked perfectly, thanks a lot... Lots of gratitude!!!!

 

[ETAF]

you are welcome