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...
mahib4u
Oct 20, 2021Copper Contributor
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
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
HansVogelaar
Oct 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.