8

I would like to compare two Excel spreadsheets

Both have the same columns, in the same order, but not the same rows.

I have a spreadsheet with 1,000 rows and 50 columns including a unique ID for each row and personal data (address, phone number, etc.).

I want to compare it with the same base extracted one year ago.

My objective is to know all changes (for example, someone who changed his address).

I tried with spreadsheet (as explained here: Compare two excel files) but it doesn't work because in my "new" file I have new rows, and I have some rows in the old file which are not in the new.

Spreadsheet compare with Excel line number. Is it possible to compare with the first column (Unique ID)?

Here is a very simplified example: https://filebin.net/g4w98251y9mfwug6

There are two sheets representing my two spreadsheets.

As you can see both have the same columns but:

  • There are rows that are on both sheets with no changes (all the row is the same)
  • There are rows that are on both sheets but there are changes (in orange on my example)
  • There are rows that are only in the December sheet (I don't need to identify these lines)
  • There are rows that are only in the January sheet (in orange on my example)

The orange color is exactly what I want, but in this example I can do it manually: comparing December and January. In the real spreadsheets I cannot do it manually as there are too many changes, data, lines, columns, etc. and I will do this monthly.

There is only one column which can never change: the column A.

  • FYI, people don’t like to download files, especially when posted by unknown users. I’m not saying that you shouldn’t upload files and post links to them, but try to make your question stand by itself — it should be possible to understand it without looking at files. I actually believe that your question is fine as is; I was able to answer it without looking at the file. But, of course, questions (especially Excel questions) are clearer with sample data. … (Cont’d) – Scott Jan 27 '18 at 5:27
  • 1
    (Cont’d) …  We prefer that you post a textual representation of your data, as was done here and here; use the Format Text as Table site or the Plain Text Tables Generator site if you want. I suggest that the data I used in my answer are just the right amount: one row in each of the four categories that you identified in the question. – Scott Jan 27 '18 at 5:27
  • There's a nice Excel Compare tool out there. It can compare 2 excel files and log out differences. You can also try Araxis Merge, although it is not free. – Kromster Feb 2 '18 at 14:29
3

It’s convenient that your spreadsheet uses 50 columns, because that means that columns #51, #52, …, are available.  Your problem is fairly easily solved with the use of a “helper column”, which we can put into Column AZ (which is column #52).  I’ll assume that row 1 on each of your sheets contains headers (the wordsID, Name, Address, etc.) so you don’t need to compare those (since your columns are in the same order in both sheets).  I’ll also assume that the ID (unique identifier) is in Column A.  (If it isn’t, the answer becomes a little bit more complicated, but still fairly easy.)  In cell AZ2 (the available column, in the first row used for data), enter

=B2&C2&D2&…&X2&Y2&Z2&AA2&AB2&AC3&…&AX2

listing all the cells from B2 through AX2& is the text concatenation operator, so if B2 contains Andy and C2 contains New York, then B2&C2 will evaluate to AndyNew York.  Similarly, the above formula will concatenate all the data for a row (excluding the ID), giving a result that might look something like this:

AndyNew York1342 Wall StreetInvestment BankerElizabeth2catcollege degreeUCLA…

The formula is long and cumbersome to type, but you only need to do it once (but see the note below before you actually do so).  I showed it going through AX2 because Column AX is column #50.  Naturally, the formula should cover every data column other than ID.  More specifically, it should include every data column that you want to compare.  If you have a column for the person’s age, then that will (automatically?) be different for everybody, every year, and you won’t want that to be reported.  And of course the helper column, which contains the concatenation formula, should be somewhere to the right of the last data column.

Now select cell AZ2, and drag/fill it down through all 1000 rows.  And do this on both worksheets.

Finally, on the sheet where you want the changes to be highlighted (I guess, from what you say, that this is the more recent sheet), select all the cells that you want to be highlighted.  I don’t know whether this is just Column A, or just Column B, or the entire row (i.e., A through AX).  Select these cells on rows 2 through 1000 (or wherever your data might eventually reach), and go into “Conditional Formatting” → “New Rule…”, select “Use a formula to determine which cells to format”, and enter

=IFERROR(VLOOKUP($A2,'December 2017'!$A$2:$AZ$1000,52,FALSE), "") <> $AZ2

into the “Format values where this formula is true box”.  This takes the ID value from the current row of the current (“January 2018”) sheet (in cell $A2), searches for it in Column A of the previous (“December 2017”) sheet, gets the concatenated data value from that row and compares it to the concatenated data value on this row.  (Of course AZ is the helper column, 52 is the column number of the helper column, and 1000 is the last row on the “December 2017” sheet that contains data — or somewhat higher; e.g., you could enter 1200 rather than worrying about being exact.)  Then click on “Format” and specify the conditional formatting that you want (e.g., orange fill).

I did an example with only a few rows and only a few data columns, with the helper column in Column H:

previous month/year (December 2017) sheet

current month/year (January 2018) sheet, with changes highlighted in orange

Observe that Andy’s row is colored orange, because he moved from New York to Los Angeles, and Debra’s row is colored orange, because she is a new entry.

Note:  If a row might have values like the and react in two consecutive columns, and this could change in the following year to there and act, this would not be reported as a difference, because we’re just comparing the concatenated value, and that (thereact) is the same on both sheets.  If you’re concerned about this, pick a character that is unlikely to ever be in your data (e.g., |), and insert it between the fields.  So your helper column would contain

=B2&"|"&C2&"|"&D2&"|"&…&"|"&X2&"|"&Y2&"|"&Z2&"|"&AA2&"|"&AB2&"|"&AC3&"|"&…&"|"&AX2

resulting in data that might look like this:

Andy|New York|1342 Wall Street|Investment Banker|Elizabeth|2|cat|college degree|UCLA|…

and the change will be reported, because the|react ≠ there|act.  You probably should be concerned about this, but, based on what your columns actually are, you might have reason to be confident that this will never be an issue.

Once you get this working, you can hide the helper columns.

1

Go to the View tab and click New Window. Go to the new Window and click side by side. You can synchronize scrolling by clicking Synchronous Scrolling. Check the attached image: Excel sheets side by side

Synchronous Scrolling gives you the advantage of scrolling different worksheets simultaneously. Toggling the button would enable you to view both together and scroll only one worksheet.

  • I have more than 1 000 lines in each spreadsheet... 50 columns.. some data are verry hard to compare by viewing (Bank datas, phone number, dates...) It's too long to proceed like that and there is a risk of error / Omission I will have to do this monthly .. There is no faster solution ? :( – CILUEA Jan 25 '18 at 12:13
  • In the same view, you can apply filters to the columns. Add lookup formulas on either of the worksheets. – Cetza Nido Jan 25 '18 at 12:19
  • I don't want to apply filters to the columns... I have two spreadsheets (one line =one employee) and I want to higlight the data changed (adress, phone..) for each employee, between the old spreadsheet (last month) and the new spreadsheet (this month) I cannot do it manually each month.. It's more than 1 000 lines in each spreadsheet and 50 columns – CILUEA Jan 25 '18 at 13:39
  • You can use lookup OR index+match formula along with conditional formatting. Is it possible to share your sheet with some sample data? – Cetza Nido Jan 25 '18 at 14:01
  • I ve edited my post with an example and more explanations..Thanks! – CILUEA Jan 25 '18 at 14:55
1

Ok so you can do one of two things.

1.- The OOTB solution is to go to "Data"->"Data Tools"->"Delete(This is the direct translation from spanish) Duplicates". In this way you have to append both tables, and you will end up with only the data that has been changed and only uniques. So to filter out use CountIf on your uniqueids, deselect what's been counted as 1. Order by uniqueId and you will clearly see what's been changed.

2.-Use this formula

IF(Index("rangetoreturn";Match("CellofUniqueID";"Rangeofuniqueidtolookinto";0))<>"OneOftheCellthatcontainsthesameasRangeToReturn";"Info Has Changed";"InfoHasNotChanged")

This formula can be draged to the right and down. So you will get a table with the if condition, with the same quantity of lines as the table of "CellofUniqueID". The position of the "Info Has changed", will tell you what cell in the other table has changed it's content. So use this formula in a new sheet and build a new table. Then look at the filters and deselect "InfoHasNotChanged"(or whatever word you want to use).

"rangetoreturn" and "Rangeofuniqueidtolookinto" are from one table(or sheet), and "CellofUniqueID" is from the other table.

Cheers

  • Thanks ! The first solution could be the easyest one but it doesnt work : (or maybe I didn't understand well ?) The option "delete duplicate" will delete some lines, and I want to keep all the lines in both sheets Moreover, I only have one column on the sheet with unique datas, All the others columns have many lines with the same data inside (for example, two persons with the same date of birth, or the same age..) – CILUEA Jan 25 '18 at 13:43
  • Option 2 : what are rangetoreturn / Rangeofuniqueidtolookinto / OneOftheCellthatcontainsthesameasRangeToReturn ? – CILUEA Jan 25 '18 at 13:46
  • @CILUEA for option 2, "rangeofreturn" that's the range of information that you want to bring back. "Rangeofuniqueidtolookinto" that's literally the range of uniqueids where you will be looking for your unique id. "OneOftheCellthatcontainsthesameasRangeToReturn " this is the information that you will compare with the one your bringing back, so if it's different it will tell you. Sadly there is no comfy answer unless you know a little of VBA, which doesn't seem like it. – dmb Jan 25 '18 at 14:02
  • @CILUEA for option 1, you have to make a new table from both tables. Then delete duplicates. Then You can also use CountIf and filter by 2 ocurrences, when you have 1 ocurrence means that information has not changed. Lastly, order by your unique id. Also throw a flag in a new columns column, so you know from wich table your information came from. Now you can clearly see what the differences are – dmb Jan 25 '18 at 14:08
  • @Scott "Out of the Box"...Made a mistake there it should be OOTB, sorry english is not my first language. – dmb Jan 25 '18 at 20:36

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.