0

I have to compare different datasets to find deviations between them. Datasets are always a multicolumn table (up to 1000 columns).

Currently I use plain Excel workbook and compare 2 sheets cell-by-cell with a formula:

=IF(Master!A2=Test!A2);0;Master!A2&"/"&Test!A2)

So it is a zero if no deviation and 2 values separated with slash if they differ.

However, if the input (plain text/ CSV) files are too big, like containing >50k rows, Excel becomes veeery slow.

Is there any solution to do this using PowerPivot?

Shurov
  • 400
  • 1
  • 9
  • 20

1 Answers1

1

Powerpivot isn't created to compare excel files. If you have excel 2013, you could use the excel compare feature.

more info:

Technet article

Excel 2013 compare

WimV
  • 1,005
  • 6
  • 11
  • thanks for the method. However it process only up to 100mn cells. Is there any way to compare more? Possibly, some SQL-based solution? – Shurov May 24 '16 at 12:13
  • yes, if you can write sql code it will be easier. you have a lot of methods for this, this site explains a lot of popular methods: [https://www.simple-talk.com/sql/t-sql-programming/on-comparing-tables-in-sql-server/](https://www.simple-talk.com/sql/t-sql-programming/on-comparing-tables-in-sql-server/) If you are interested in paying tools: sql compare from redgate is popular compare tool. – WimV May 24 '16 at 13:50