Jan 03 2022 09:26 PM
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
Jan 04 2022 02:46 AM
@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")
Jan 04 2022 03:45 AM
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 ) ), "" ) )