comparing pipe-delimited values in excel

[mahib4u]

Hello everyone,

Whats the best way to compare two cells with pipe-delimited values in excel, here is an example:

Col A: 20210923|20210603|20201120|20191003

Col B: 20210923|20210603

Expected result(values that are not matching between columns A and B): 20201120|20191003

Thank you,

Mahender

[#2]

@mahib4u 

The attached workbook (now a .xlsm) uses a custom function to return the mismatches.

You'll have to allow macros when opening the workbook.

[#3]

@Hans Vogelaar Thanks a lot for your help, its working. Have a good day.

[#4]

Hi Hans,
The Macro is giving me the values in A but not present in B but not the ones that are in B but not in A. I'm sorry for not explaining it clearly. Here is what I'm looking for:
- The values in cells A and B are dates in YYYYMMDD format separated by pipes.
- I need the dates available in A but not in B as well as dates present in B but not in A
Example:
Col A: 20210923|20210603|20201120|20191003
Col B: 20210923|20210603|20201020
Expected result(values that are not matching between columns A and B): 20201120, 20191003 and 20201020(in B but not in A)

- Also, if the dates are +/- 5 days apart from each other would it be possible get both the dates

Example:
Col A: 20210923|20210603|20201120|20191003
Col B: 20210923|20210605|20201020
Expected result: 20210603, 20210605(these two are date mismatches which are +/- 5 days apart but not a complete miss) , 20201120, 20191003(present in A not in B) and 20201020(in B but not in A)

- Also ignore extra pipes in comparison if they are present in either of the columns

Col A: 20210923|20210603|20201120|20191003

Col B: 20210923|||20210603

Expected result: 20201120|20191003

I've edited my original request and updated a new sheet with more examples. Can you please help me out in getting the required data.

Regards,
Mahender

[#5]

@mahib4u 

The attached version returns missing dates from both strings. It doesn't find dates that are within 5 days of each other; that is a step too far.