Aligning duplicates on the same rows in Excel.

One of the projects I’ve worked on involved thousands of agreement numbers. Each agreement was assigned a status depending on what stage it’s in. I needed to find a way to track agreements that had a status change based on comparing previous and current day feeds. This wouldn’t be such a big deal if we’re dealing with a small number of agreements. However, since I was dealing with close to 4,000 agreements, I needed to find a better and faster way to do it.

This was my solution:

=IF(ISNA(MATCH(A1,C:C,0)),"",INDEX(C:C,MATCH(A1,C:C,0)))

This formula completely automated the task of comparing thousands of agreements. Here is how it works:

Let’s say you have two columns in Excel like the one below. Column 1 and column 2 have some identical values, but they are not aligned on the same rows. Here is what you need to do to…