How to verify if all values in specific cells match with other cells using a formula?

Hi all,

Hope all are doing good and ready for the Christmas

Just some help in attached sheet.

1. In sheet Confirmation Form, cell E17 shows count of values entered from cell E19. However, since E19 onwards there's a formula entered and E17 also counts it where as it shouldn't. So cell E17 should show count as 14 instead of 37.


2. Cell E15 should contain a formula such that if all "present" values in column E starting from cell E19 matches exactly with that of values either in column B or column E of sheet " Decommissioning Request Form" starting from cell B59 or E59, then this cell should show "SERIAL NUMBERS MATCH", else "SERIAL NUMBERS MIS-MATCH".

The reason for either column B or E is that if values in column B are in below form

01003669937919751725043010CJ9U2119753447591903

then formula =IF(B59="","",RIGHT(B59,LEN(B59)-22)) will be applied in cell E59 onwards.

If values in column B are in below form

19753447591903

then there is no requirement of formula in column E.

Hence comparison with either B or E.


3. Cell C13 in sheet "Confirmation Form" contains a formula which should shows as FORM COMPLETE if below conditions are fulfilled.

a> If cells C5, C6, C8, C9. C11, C12 are not blank.

b> If cell C14 in sheet " Decommissioning Request Form" is "Partial Batch Decommissioning", then ,cell C15 in sheet "Confirmation Form" should be "QUANTITY MATCH"

c> If cell C14 in sheet " Decommissioning Request Form" is "Full Batch Decommissioning with Rework", then cell D17 in sheet "Confirmation Form" should not be 0.

I have entered the conditions from cell G4 to G7 but it somehow doesn't work.


Can someone please help?

Q1: use

Formula:

Please Login or Register  to view this content.

I don't understand the requirement for 2 and 3. Is Q3 three separate requirements? C13, C15 and D17?

Q2 is similar to conditional formatting of duplicate values. I want to authenticate that all the values in one column matches exactly with the values in other column using a formula. Just an additional step is that i need an output for this in the form of "SERIAL NUMBERS MATCH" or "SERIAL NUMBERS MIS-MATCH".

The columns to be compared are column E in sheet Confirmation Form starting from cell E19 with that of values either in column B or column E of sheet "Decommissioning Request Form" starting from cell B59 or E59. Reason for comparison with 2 columns i.e. B and E in sheet "Decommissioning Request Form" is stated in post # 1.


Q3 has 3 different requirements but i have already readied them in cells G4 to G6. The problem seems to be with cell G7. I am unsure when to use PRODUCT and when to use IFSUM

This thread appears to be similar to https://www.excelforum.com/excel-for...a-formula.html, but I'm not sure if it's a duplicate. Members should check the other thread before taking the time to respond to this one.

Q3: seems like the formulae in column G are a little convoluted. Try:

Formula:

Please Login or Register  to view this content.

Formula:

Please Login or Register  to view this content.

Formula:

Please Login or Register  to view this content.

Formula:

Please Login or Register  to view this content.

Formula:

Please Login or Register  to view this content.

The first four return TRUE/FALSE. The last one uses the fourth one to display the message.

I don't think =AND(' Decommissioning Request Form'!$C$14="Full Batch with Rework",D17<>0) can ever be TRUE because Decommissioning Request Form'!$C$14 doesn’t have an option to be "Full Batch with Rework"

So:

Formula:

Please Login or Register  to view this content.

Seems to work for me.

I have to say I am getting lost in your logic.

Q3: Try ...

PHP Code: 

="FORM "&
IF(AND(
       LET(c,INDEX(C:C,{5,6,8,9,11,12}),COUNTA(FILTER(c,c<>""))=6),
       OR(
          AND(' Decommissioning Request Form'!$C$14="Partial Batch Decommissioning",$C$15="QUANTITY MATCH"),
          AND(' Decommissioning Request Form'!$C$14="Full Batch Decommissioning with Rework",D17<>0))),
   "COMPLETE","INCOMPLETE") 


It doesn't come within the constraints of:

Out of the above 3 requirements, 1st one is mandatory while rest 2 depends on the dropdown chosen in cell C14 in sheet " Decommissioning Request Form"

It doesn't matter how simple or complex the criteria for an IF statement are, the outcome is TRUE or FALSE. If the outcome is not TRUE based on the criteria tested, it will return FALSE. If you don't specifically test for a condition, then it may or may not affect the outcome. In this case, the dependency is on cell C14 where you only test two of the possible conditions.

Try this:

PHP Code: 

="FORM "&
IF(AND(
       LET(c,INDEX(C:C,{5,6,8,9,11,12}),COUNTA(FILTER(c,c<>""))=6),
       OR(
          AND(' Decommissioning Request Form'!$C$14="Partial Batch Decommissioning",$C$15="QUANTITY MATCH"),
          AND(' Decommissioning Request Form'!$C$14="Full Batch Decommissioning with Rework",D17<>0),
              ' Decommissioning Request Form'!$C$14="Full Batch Decommissioning without Rework")),
   "COMPLETE","INCOMPLETE") 


Perhaps the following:
1. Change the formula in cells E59 and down on the Decommissioning Request Form sheet to read: =IF(B59="","",IF(LEN(B59)>14,RIGHT(B59,LEN(B59)-22),B59))
2. Populate cells F19 and down on the Confirmation Form sheet using: =AND(E19<>"",MATCH(E19,' Decommissioning Request Form'!E$59:E$95,0))
3. Populate cell E15 on the Confirmation Form sheet using: =IF(COUNTIFS(F19:F55,TRUE)=E17,"SERIAL NUMBERS MATCH","SERIAL NUMBERS MIS-MATCH")
Let us know if you have any questions.

Hi JeteMc.

Thanks for your response. Its working perfectly

Few queries.

1. Cell E57 in sheet Decommissioning Request Form contains formula

Formula:

Please Login or Register  to view this content.

which is also counting the cells with no values but containing just the formula. Can formula be modified such that only cells with values be counted?

The reason for formula in blank cells is that there is no definite count of values that will be entered in column B. It can also range upto 25000. So the below formula will be present atleast upto E25000.

Formula:

Please Login or Register  to view this content.

Can you suggest?


2. Cell E19 in sheet Confirmation Form contains formula

Formula:

Please Login or Register  to view this content.

. Can the formula in F19 be made direct such that column E is not required and it directly pulls data from column B?


3. 1 more suggestion please

Filling the formula upto 25000 rows is increasing excel binary file size considerably. Any alternative to this?

1. For cell E57 use: =SUMPRODUCT(--(E59:E1048576<>""))
2. For cells F19 and down use: =ISNUMBER(MATCH(RIGHT(B19,LEN(B19)-22),' Decommissioning Request Form'!E$59:E$50000,0))
3. Removing the formula from E19 and down on the Confirmation Form sheet may have helped.

Thanks JeteMc.

Point 3:

I still need to use formula in column F which anyhow increases the file size. 'll see what i can do about it.


Point 2:

I am now using below formula in column F

Formula:

Please Login or Register  to view this content.

Since above formula is direct and helper column E is no longer required, i changed reference cell E17 to B17 as per your earlier below formula

Formula:

Please Login or Register  to view this content.

The issue is that when there is no data present in column B starting from cell B19 and no data in column E from cell E59 of sheet Decommissioning Request Form, cell E15 shows "SERIAL NUMBERS MATCH"

Can the formula be modified such that if all cells from B19 up to B50000 in sheet Confirmation Formare blank, then cell should show as "ENTER SERIAL NUMBERS". If they are filled, then the formula will do its regular job i.e. testing for SERIAL NUMBERS MATCH / MIS-MATCH.

Please try the following in cell E15:

Formula:

Please Login or Register  to view this content.

Let us know if you have any questions.

The formula could be written:

Formula:

Please Login or Register  to view this content.

That said, my thinking is that since there is a formula in column E of the Decommissioning Request Form sheet anyway then why not just let the formula put all numbers in column E.
It should not take any more computing power to add to the formula for column E and probably will be more economical than calculating a complex IFERROR type formula in column F of the Confirmation Form sheet.

Try the following:

Formula:

Please Login or Register  to view this content.

Let us know if you have any questions.

Many thanks JeteMc. Its working now.

Just 1 query and 1 issue.

1. How can below formula be modified to count only unique values

Formula:

Please Login or Register  to view this content.

2. An issue detected. If there's any duplicate value in column B in sheet "Serialisation Confirmation Form", then that also is considered as TRUE in column E of the same sheet. However, there shouldn't be any duplication. To resolve this issue for column B, C and D, i added a condition to the existing formula in cell C15 such that if there's any duplicate serial number, cell should show as "DUPLICATE SERIAL NUMBERS DETECTED". I tried below formula

Formula:

Please Login or Register  to view this content.

Although there are no duplicate serial numbers in column B, C or D, yet cell shows as "DUPLICATE SERIAL NUMBERS DETECTED"

Is there any mistake in formula?

Originally Posted by rizwanulhasan

Many thanks JeteMc. Its working now.
Just 1 query and 1 issue.

1. How can below formula be modified to count only unique values

Formula:

Please Login or Register  to view this content.

For this you can use:

Formula:

Please Login or Register  to view this content.

2.

Formula:

Please Login or Register  to view this content.

You can't confront those values because COUNTA will bring 0 as a result and ROWS 1 for columns C and D, you tried to compensate this with -3, but the first column(B) has no zero to add for ROWS so it will return the same result as COUNTA.

My solution for this would be:

Formula:

Please Login or Register  to view this content.

Thanks for the rep.

Apologies but 1 issue.

Column E in sheet Serialisation Confirmation Form gives the result as TRUE if the values it contains matches with those in either column B or E in sheet Decommissioning Request Form.

However, when values are pasted in either column B or E in sheet Decommissioning Request Form from a notepad etc, the result in column E in sheet Serialisation Confirmation Form is FALSE despite the values are same.

The match formula works if i paste values from excel but not from notepad etc.

Any solution how to overcome this issue please

I suspect the answer is that NOTEPAD adds extra character(s) at front and/or back of the text string. Try using TRIM (text.......).

Originally Posted by TMS

It doesn't matter how simple or complex the criteria for an IF statement are, the outcome is TRUE or FALSE. If the outcome is not TRUE based on the criteria tested, it will return FALSE. If you don't specifically test for a condition, then it may or may not affect the outcome. In this case, the dependency is on cell C14 where you only test two of the possible conditions.

Try this:

PHP Code: 

="FORM "&
IF(AND(
       LET(c,INDEX(C:C,{5,6,8,9,11,12}),COUNTA(FILTER(c,c<>""))=6),
       OR(
          AND(' Decommissioning Request Form'!$C$14="Partial Batch Decommissioning",$C$15="QUANTITY MATCH"),
          AND(' Decommissioning Request Form'!$C$14="Full Batch Decommissioning with Rework",D17<>0),
              ' Decommissioning Request Form'!$C$14="Full Batch Decommissioning without Rework")),
   "COMPLETE","INCOMPLETE") 


Hi again,

Just a help please.

In addition to the existing formula, a condition needs to be added such that cell C13 in sheet "Serialisation Confirmation Form" should show FORM INCOMPLETE if cell C14 in sheet "Decommissioning Request Form" is Partial Batch Decommissioning and if cells in sheet "Serialisation Confirmation Form" B18 contains SERIAL NUMBERS MIS-MATCH or C18 contains CASE NUMBERS MIS-MATCH or D18 contains PALLET NUMBERS MIS-MATCH. In other words, if any of the cells B18, C18 or D18 contains the word MIS-MATCH

Can someone please help