SOLVED

Combining 2 Formulas to Check and Return Value

Copper Contributor

Need Help 
We have Columns contains numeric value , I need Excel Formula to copy the same cell if only the Digit Number 9 & 8 from right to left equal (50, 52, 54, 55, 56 or 58), other wise return Null. Below sample of the data and Expected Values

 

The DataExpected Values
777797247379Null
777483910509Null
777984179917Null
777505626795777505626795
777185360938Null
777501884978777501884978
777562393745777562393745
777882465989Null
777223529392Null
777585172285777585172285
7770 4982152Null
777554567499777554567499
777145024522Null
777235624042Null
777663870157Null
777504422934777504422934
777799635466Null
777866791186Null
777585726099777585726099
12 Replies
best response confirmed by mrxware (Copper Contributor)
Solution

@mrxware 

Sample.png

in C2 one way:

=IF(ISNUMBER(MATCH(IFERROR(MID(A2,LEN(A2)-8,2),""),{"50","52","54","55","56","58"},0)),A2,"")
Thanks A lot
You're welcome + Thanks for providing feedback

@mrxware

To dynamically assess any sized data set, try:

 

=LET(DATA,A1:A19,
IF(MMULT(--(--MID(DATA,LEN(DATA)-8,2)={50,52,54,55,56,58}),SEQUENCE(6)),
DATA,"Null"))

 

Replace A1:A19 with your dataset range.

Alternatively with 2021 or 365:

=IF(ISNUMBER(XMATCH(MID(A1:A19,LEN(A1:A19)-8,2),{"50","52","54","55","56","58"})), A1:A19, "null")

 

Hi @MY (^0 isn't required in your nice MMULT option)

@mrxware 

A variant with XOR:

=IF(XOR(LEFT(RIGHT(A2,9),2)*1={50,52,54,55,56,58}),A2,"Null")

 

Haha thanks, I woke up this morning asking myself why I put that there!
Can you please explain this Formula ?
Appreciate Your Help
Thanks, It's look simple. Can You Please explain it briefly

@mrxware

=LET(DATA,A1:A19,
IF(MMULT(--(--MID(DATA,LEN(DATA)-8,2)={50,52,54,55,56,58}),SEQUENCE(6)),
DATA,"Null")) 

 

So if we digest it in parts:

  1. LET(DATA,A1:A19,...) : allows you to define names/variables where in this case DATA = A1:A19. This way you only need to change the range once for DATA and it updates for all instances of DATA in the formula. The formula you want to execute goes into the "..." space.
  2. MID(DATA,LEN(DATA)-8,2) : generates a dynamic array of the 8th and 9th character in the range i.e. a column of 2 digits (8th and 9th character from right)
  3. --MID(DATA,LEN(DATA)-8,2) : normally the use of "--" converts TRUE and FALSE values to 1 and 0 respectively, but in this instance it converts numerical STRING into NUMBER. If your data range is always NUMBER then this can be disregarded.
  4. --MID(DATA,LEN(DATA)-8,2)={50,52,54,55,56,58} : Checks if the double digit is equal to 50, 52 ... 58 for each row of your range. This will return an array of size (n x 6) with either TRUE or FALSE values. Rows with one match will have one TRUE in the respective row.
  5. --(--MID(DATA,LEN(DATA)-8,2)={50,52,54,55,56,58}) : Converts the TRUE or FALSE values to 1 or 0 respectively. Rows with one match will have one "1" in the respective row.
  6. SEQUENCE(6) : Generates an array of size 6 x 1 with integers 1 to 6
  7. MMULT(--(--MID(DATA,LEN(DATA)-8,2)={50,52,54,55,56,58}),SEQUENCE(6)) : Matrix multiplies the array of size (n x 6) made up of 1's and 0's with the array of size (6 x 1). The resultant is an array of size (n x 1) whereby rows with one match will have a positive integer in the respective row whilst no matches results in a 0.
  8. =LET(DATA,A1:A19,
    IF(MMULT(--(--MID(DATA,LEN(DATA)-8,2)={50,52,54,55,56,58}),SEQUENCE(6)),
    DATA,"Null")) : the curious thing about the IF statement is that the first value must be a TRUE or FALSE value OR alternatively, a number not equal to 0 for TRUE and 0 for FALSE. In step 7, we now have an array of size (n x 1) made up for positive integers and 0's that define our TRUE and FALSE for each row. Plug it into the IF statement and we get DATA if TRUE or Null if FALSE.

This is a mathematical outlook on solving this problem so I highly suggest replicating steps 2 to 8 separately (replace DATA with A1:A19 for steps 2 to 7) to understand what's happening at each step.

 

Hopefully this helps.

That is absolutely fantastic and helpful
You give me the fish and helped me how to fishing
Appreciate your effort. Thanks
1 best response

Accepted Solutions
best response confirmed by mrxware (Copper Contributor)
Solution

@mrxware 

Sample.png

in C2 one way:

=IF(ISNUMBER(MATCH(IFERROR(MID(A2,LEN(A2)-8,2),""),{"50","52","54","55","56","58"},0)),A2,"")

View solution in original post