Forum Discussion

mahib4u's avatar
mahib4u
Copper Contributor
Jan 03, 2022

Find value in same position with specific word in next cell.

 

Hi everyone,

I've two cells(A and B) with values separated by pipes. For a specific word in column B I'd like to fetch the value from column A which is in the same position as the specific word in column A.

Example:

Column A: 20160101|20151110|20150812|20150513|20150212|20141112|20140814|20140514|20140219

Column B: Cancelled|Active|Active|Active|Active|Active|Active|Active|Active

I need the value in column A that matches with the position where 'Cancelled' is present in column B. Both column with have equal values while column A can also have blanks.

IN this example column B has the word 'Cancelled' in first position so I'd like to fetch the value in first position from column A i.e. 20160101

 

Example 2:

Column A: 20160101|20151110|20150812

Column B: Active|Cancelled|Active

Cancelled in 2nd position in column B, I'd like to fetch value in 2nd position from column A i.e. 20151110

Example 3:

Column A: |20151110|20150812

Column B: Cancelled|Active|Active

Cancelled in 1st position in column B, I'd like to fetch value in 1st position from column A i.e. Blank

Example 4:

Column A: 20160101|20151110|20150812

Column B: Active|Cancelled|Cancelled

Cancelled in 2nd abd 3rd positions in column B, I'd like to fetch values in 2nd and 3rd position from column A i.e. 20151110 and 20150812

Can someone please help me out with this.

 

Regards,

Mahender

excel

2 Replies

  • SergeiBaklan's avatar
    SergeiBaklan
    Diamond Contributor
    Jan 04, 2022

    mahib4u 

    As variant

    =LET(
   splitA, FILTERXML(  "<t><s>"&SUBSTITUTE(A2,"|","</s><s>")&"</s></t>", "//s" ),
   splitB, FILTERXML(  "<t><s>"&SUBSTITUTE(B2,"|","</s><s>")&"</s></t>", "//s" ),
   pos, SEQUENCE(ROWS(splitB))*(splitB="Cancelled"),
   f, FILTER(pos, pos),
   IFERROR( TEXTJOIN( ", ", 1, INDEX( splitA, f ) ), "" ) )
  • JKPieterse's avatar
    JKPieterse
    Silver Contributor
    Jan 04, 2022

    mahib4u Interesting challenge! I used Data, From Table to solve this puzzle, see attached. Note that it seems to fail if any of the codes are non-numeric (or sort after the word "Cancel")

Resources