Forum Discussion
comparing pipe-delimited values in excel
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
4 Replies
The attached workbook (now a .xlsm) uses a custom function to return the mismatches.
You'll have to allow macros when opening the workbook.
- 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,
MahenderThe 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.
HansVogelaar Thanks a lot for your help, its working. Have a good day.
