want to add additional condition to If and countifs function

[RAJESH SHAH]

Hello experts,
I am trying to match 2 rows with the help of a formula. The problem is if the difference in amount is more or less than 1/- it shows a mismatch. By editing the formula, I want it to show Matched. Also, I am trying to add one more if function within the formula to get 3 results - "Matched", "Books Not Entered" and "Not in Portal" as shown in sheet Matched Manually. Need your expertise to solve this.
AS there are more than 6000 rows to match at a time, it takes a lot of time to match it manually. Hope to get it done in less than a minute with the help of a perfect formula.

Query IF COUNTIFS.xlsx

How do I change from VBA to Formulas...?

[AliGW]

Administrative Note:

We would very much like to help you with your query, however the thread title does not really convey what your request is about. Tell us what you are trying to do, not how you think it should be done.

Please take a moment to amend your thread title. Make sure that the title properly explains your request. Your title should be explicit and not be generic (this includes function names used without an indication of what you are trying to achieve).

Please see Forum Rule #1 about proper thread titles and adjust accordingly. To edit the thread title, open the original post to edit and then click on Go Advanced (bottom right) to access the area where you can edit your title.

(Note: this change is not optional. No help to be offered until this moderation request has been fulfilled.)

Why have you posted in the VBA section?

[AliGW]

Thanks for the title change.

Why did you post in the VBA section? Do you want a formula? If so, I will move your thread.

[RAJESH SHAH]

Please tell me how to change that, so that in future I can do it if I have posted it by mistake to some other section.

[AliGW]

I have moved it - it is now in the correct section.

[RAJESH SHAH]

Sorry for the blunder and Thanks.

[davsth]

It would be helpful to give what you expect as your answers in the example

Your current formula only says matched for the first unique combination of C and F. I am not seeing how it matches rows

I think a little more explanation is needed, but I do think a formula will do the job, when we know what you want

[RAJESH SHAH]

I have entered the formula in the With Formula sheet in the Remarks column =IF(COUNTIFS(C$2:C2,C2,F$2:F2,F2)=1,"Matched","Mismatch"). There are multiple columns in the original data base. But the only values that match between the Portal and Books is the Tin Column. So I have shared only those columns. I know the formula is wrong that is why I need to correct it.

[JohnTopley]

My understanding:

For a given "TIN" [column C], we need match "PORTAL" GST value with equivalent "BOOKS" GST values and find a match with absolute difference <= 1: this is Matched status.

For non-match, determine whether missing item is from "Portal" or "Books".

Too tricky for my brain !!

[RAJESH SHAH]

Yes. Quite a mind teaser. But anything is possible in Excel.

[RAJESH SHAH]

If the Tin number and Amounts are same as per Books and as per Portal then it is a match. The formula should accept the difference in amount by a margin of +1/- or -1/. If the above condition is not met then it is a mismatch. The additional IF formula will find whether it is Not in Portal or Books not entered which I want it add to the same formula.

[RAJESH SHAH]

For your reference, I am again uploading one workbook to show how I have matched manually the Portal with Books.
Attachment 757599

[Glenn Kennedy]

I have not looked at your attachment at Post 12. Does this work??

=IF(COUNTIFS($C$2:$C$19,C2,$F$2:$F$19,">="&F2-1,$F$2:$F$19,"<="&F2+1)>=2,"Matched",IF(B2="Books","Not in Portal","Books not Entered"))

See file.

[RAJESH SHAH]

Glenn Kennedy. It is working correctly in the sample data. I have shared another worksheet where the rows are a match and your formula is showing mismatch. Please check this...Attachment 757604

[RAJESH SHAH]

Glenn Kennedy. This is to inform you that today the above query finally has been solved.
solution:

=  IF(SUM( (ABS(G2-$G$2:$G$23200)<=1) * (C2=$C$2:$C$23200) * ("Portal"=$B$2:$B$23200))  =  SUM( (ABS(G2-$G$2:$G$23200)<=1) * (C2=$C$2:$C$23200) * ("Tally"=$B$2:$B$23200)  ), "Matched",
    IF(SUM( (ABS(G2-$G$2:$G$23200)<=1) * (C2=$C$2:$C$23200) * ("Portal"=$B$2:$B$23200))  >  SUM( (ABS(G2-$G$2:$G$23200)<=1) * (C2=$C$2:$C$23200) * ("Tally"=$B$2:$B$23200)  ),
    IF(SUM( (ABS(G2-$G$2:$G2)<=1)   * (C2=$C$2:$C2)   * (B2=$B$2:$B2)        )  <= SUM( (ABS(G2-$G$2:$G$23200)<=1) * (C2=$C$2:$C$23200) * ("Tally"=$B$2:$B$23200)  ), "Matched", "Not Found"),
    IF(SUM( (ABS(G2-$G$2:$G$23200)<=1) * (C2=$C$2:$C$23200) * ("Tally"=$B$2:$B$23200) )   >  SUM( (ABS(G2-$G$2:$G$23200)<=1) * (C2=$C$2:$C$23200) * ("Portal"=$B$2:$B$23200) ),
    IF(SUM( (ABS(G2-$G$2:$G2)<=1)   * (C2=$C$2:$C2)   * (B2=$B$2:$B2)        )  <= SUM( (ABS(G2-$G$2:$G$23200)<=1) * (C2=$C$2:$C$23200) * ("Portal"=$B$2:$B$23200) ),"Matched", "Not Found"))))

[davsth]

The attachment in post 12 is invalid

[RAJESH SHAH]

Invalid ...??

[Glenn Kennedy]

I'm not a mind reader.... where is it incorrect??

[RAJESH SHAH]

I have shared it in the attachment

[Glenn Kennedy]

Ignore my last post!!

[Glenn Kennedy]

I'm no longer sure that I understand your requirement!!

[RAJESH SHAH]

I'm no longer sure that I understand your requirement!!

It is okay. I will do it manually till I find a solution. Anyways you gave the correct formula for the original post. Thanks.

[Glenn Kennedy]

I am making some progress, so keep an eye on your thread. I have to leave shortly for a couple of hours.

[RAJESH SHAH]

I am making some progress, so keep an eye on your thread. I have to leave shortly for a couple of hours.

Okies .I will

[JeteMc]

Selecting Attachment 757604 in post #15 results in the following: "Invalid Attachment specified. If you followed a valid link, please notify the administrator".

[AliGW]

Please share the solution here and then mark the thread as solved. Thanks.

EDIT: Thanks for sharing the solution.