Forum Discussion
mahib4u
Oct 19, 2021Copper Contributor
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 resul...
HansVogelaar
Oct 19, 2021MVP
The attached workbook (now a .xlsm) uses a custom function to return the mismatches.
You'll have to allow macros when opening the workbook.
- mahib4uOct 20, 2021Copper ContributorHi 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- HansVogelaarOct 20, 2021MVP
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.
- mahib4uOct 19, 2021Copper Contributor
HansVogelaar Thanks a lot for your help, its working. Have a good day.