Many times you are in a situation that you have worked on a excel did changed couple of cells, sorted the data based on the some or the other column. You have the original copy of the file. Now, you want to figure out what has changed, which row moved where.You are not alone in this situation. Let us explore how to address that.
If you are looking for simply finding if the two sheets are similar or has there been any changes then you you might refer to another article Find duplicates in excel
How to find a duplicate row in another sheet?
The basic idea here is to search each row of one excel sheet to all the rows of the other sheet and and mark the first match. Tedious way is to search cell by cell. But the trick that we are going use is to combine all the cells of a row to form the content to be searched. We will then create a helper sheet where each cells of each row of the second sheet is combined and kept in each line. Then find the row data within this new helper sheet. This would be more clear in the following use case and example.
Let's take two excel sheets as follows. Two sheets have same data except two cells that is marked in yellow. One row in each sheet has been moved to other location without changing the row content
We combine the cells of the row by excel formula CONCAT . Create a new sheet (sheet3 in the following example) and typein =CONCAT(Sheet2!A2:D2) in C2. This will combine or concatenate the contents of the row to cell C2 of sheet3. Similarly copy the content till C8. Now C2 through C8 have the row values of Sheet 2 row 2 till row 8
Next, put =IFNA(MATCH(CONCAT(Sheet1!A2:D2),C$2:C$9,0),FALSE) in D2 of sheet3. and then copy and paste till D8. Let me explain the formula in two parts. the core part is MATCH(CONCAT(Sheet1!A2:D2),C$2:C$9,0) . This part tries to search the sheet1 Row 2 contents in the the pre-populated concatenated contents in from C2 till C9. If it find a match, it shows the row number where it could find the content, that is being searched. Wherever, it finds a match, it gives the row number relative to the first rows and if did not find a match , then it creates a value #N/A. This is where the second part comes handy. If the output of the match formula creates #NA, then that is replaced by the word "False". False is generated for the rows that do not match at all. in the example two cells were marked in yellow and had different value. Now, depending on the use cases, you might be interested in finding how many duplicates are there by simply filtering. on D2 column. And, for each rows of the sheet1, it shows where is the duplicate line
Hope that it was useful for you.Will try share more contents for excel in near future