• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

COUNT - IF combined values of two columns MATCH

S

Sreehari

Member
Dear XL Ninjas ,

i need to take the COUNT,IF the combined value in fields A&B of sheet1 does not match with combined value of fields a & b of sheet2.

A sample file attached with sample data and expected output
 
Hi,
The attachment got lost in the post but would something like this help?
=IF(A1+B1<>Sheet2!A1+Sheet2!B1, COUNT(A1,B1), 0)
 
@Sreehari

Not very much sure by your description of output, but see the attached file in output sheet, is the result right check them.

Regards,
 

Attachments

  • cc (1).xlsx
    18.9 KB · Views: 3
Guess this formula will work
=IF(ISNUMBER(MATCH(data!B2,Lookup!$A$2:$A$9,0)+MATCH(data!C2,Lookup!$B$2:$B$9,0)),0,1)
 
@ somendra Ninja ,

You are wonderful, it is working..

can we alos include below extra condition "in bold" in the formula.

COUNT ,IF the file name contains file type KER01,KER02... concatenate value in column A&B of data! does not match with combined value of fields a & b of Lookup sheet --> less count of NF in column C ( header B) of data!

@nebu,

Thanks for your effort also..seems some issue with the formula it always gives the value as 1.
 
Well the idea was to just flag it with 1s and 0s if u can pull a pivot out of it you will get the desired results, but Somendra has done it with formula alone and has Somendra had mentioned your explanation were bit vague.....
 
Thanks a lot its perfect..

being array function its taking some time for getting output also my working base is greater than 100000 rows..but still it is great.

can you also please help me with correcting the formula in my attached file.
 

Attachments

  • BB.xlsx
    19.2 KB · Views: 2
I hope this is the file where you are checking IMSI? If so than the description in your file is not clear. Can you explain why you got the expected output?

Regards,
 
yes , this file checking is for IMSI

for IMSI chk1


TOTAL COUNT of Matching IMSI ,IF the file name of DATA! contains file type KER01 and Left of column B,5 of DATA! MATCH with column A value of lookup!
for example of how I got the expected output as 3 for 1st case :Left of column B,5 of DATA! Which are in cells B9,B11,B12 matches with values in cells column A2 & A2 values of lookup!

for IMSI chk2

TOTAL COUNT of NONMatching IMSI ,IF the file name of DATA! contains file type KER01 and Left of column B,5 of DATA! MATCH with column A value of lookup! Except count of 40419.

example of how I get the expected output as 3 for 1st case : NON of the values of Left of column B,5 of DATA! Not matching with any of the values in column A values of lookup! and Left of column B,5 <>"40419" of DATA!

file not getting prpperly uploded.
 
Are you going to mention IMSI Check 1 and IMSI Check 2 in a column in Data sheet as you did in your sample file? If not than what is the identification of these, that which numbers are IMSI #1 or #2?

Regards,
 
the requirement is , 1st 5 digits of column B of DATA! aganist each file type(KER01/2..) is matching with values in column A of lookup! the total matching count needs to be updted as IMSI check 1

similarly the count of non matching values except the 1st 5 digits of column B of sheet DATA comming as 40419 the total matching count needs to be updted as IMSI check 1

in both the cases the identifier is 1st 5 digits of column B of DATA!.
 
You must log in or register to reply here.
Back
Top