Comparing data in two columns

Question

Hi everyone,

I want to compare one column to an adjacent column. Problem is, they aren't in identical formats. I want to just compare the data and ignore things like dashes and whether they are text or some other format. For example, I want to compare:

• 07 Apr 1969 to 7-Apr-1969
• Mar 1939 to Mar-1939
• 1949 (formatted as text) to 1949 (formatted as an integer or date)

In these three examples, they should be returned as "Match" because in the real world, they are.

Thanks!

Answer 1

Bernie, if I may ask one more thing.

After filtering out all the dates greater than January 1, 1900, I still need to compare the data.

For example, I need to make sure that a TRUE return is achieved when two dates are displayed as 09 Aug 1945 and 9-Aug-1945. The first date is the original and the second sate is the processed version. I need to make the comparison from strictly a content point of view and ignore the missing dashes. Any ideas? Thanks.

Answer 2

Of course! I had to convert the dates from the 1800s into the Text format. Any comparison between Text and Date in one column is like mixing oil and water :). Thanks, Bernie!

Answer 3

Excel does not handle dates before 1/1/1900 - you would need to split those dates into separate columns and compare them by parts.

Answer 4

Hi Bernie,

Thanks for chiming in. I tried your idea but I still get a FALSE value when two dates are formatted differently but contain the same data. For example,

these two cells are seen as not being the same but they are in the real world. The formatting has caused the FALSE return. Thoughts?

04-Mar-1880	04 Mar 1880

Answer 5

Choose each of the columns in turn and use Data / Text to Columns, and in the third dialog choose General as the format.