I am looking to calculate difference between set of a data in a column for every row on an excel spreadsheet. A table/matrix would ordinarily be the best way of achieving that where i create a column and row with the dataset and create a simple formula to calculate the difference for every combination. However, with a very large dataset (10,000rows x 10,000columns) excel gets very unstable and keeps crashing. Is there anyway I can achieve this efficiently through pivot table or any other method?
1 Answer
Generally I would think if you are trying to calculate this many differences, you're calculating too much! The only reason you would need to display all that data is for someone to look at it.
There's no way a person would be able to look at that much data all at once. They could achieve similar results from just calculating the differences of a subset of the items or indeed just refine the problem a little and head directly to the result without calculating a table containing 100M cells.
All of that said, here's a way to create such a join. The following will theoretically be valid for lists fewer than 1000 items. A Cartesian join of a set larger than that with itself will produce more rows than can be shown in a spreadsheet.
Use Data>Get & Transform Data>From Table/Range to create a query on your list of numbers.
In the Power Query Editor, expand the list of Queries on the left, then right-click the query you just created and choose "Reference".
Now select this newly created query, and use Add Column>Custom Column and configure it like this (i.e. put the name of the original query in the formula):
When you've done that, you'll see a new column with the word 'Table' in every row. Click the double-arrow at the top of that column to expand the list.
This has now created a cartesian join between the table and itself.
Now add a column to calculate the difference between the two columns. Use Add Column>Custom Column, like this:
After that, you'll have the difference between every item in the set and every item in the set (including itself). You could optionally filter the output to not include a comparison between an item and itself.
Now you can use Home>Close & Load to put the data back into the workbook. For a list of 1000 items, this took my computer a few seconds to load.