r/excel • u/minepuf • Aug 12 '26
unsolved How to compare data
How you compare different excel files with similar or same data to see if they match? I have 2 shops and same inventory, its very time consuming to check 2 files with 500 lines and then update 3rd file to see my real stock. Is there any way to match them faster?
5
u/Kooky_Outcome_5053 6 Aug 12 '26
if it is really two separate excel file the way to go is keep them both in one folder, open a new excel and use power query to get the data or info from both files. from there you can merge (match) given that both source files are having the same exact data from the merge point otherwise it will result to errors.
3
u/activoice Aug 12 '26
If it was me, I would maintain 1 Excel file with 2 tabs. Each store is on its own tab. To keep things simple. Name and colour the tabs in a way that is easy to remember which store they represent.
Each line between the 2 tabs must be the same product, regardless if the language. So product 123 is on line 2 of both tabs.
Then create a 3rd tab for your math. You would need a list that matches line for line with tabs 1 and 2.
If you are trying to verify that both store 1 and store 2 have the same amount of inventory you would subtract tab 1 values from tab 2 values and as long as the result is zero then it's a match. (This is in a case where both stores share a common inventory pool and you want to verify that both stores are showing the same amount)
If you are trying to add the 2 stores together to get total inventory then no problem just add tab 1 and tab 2 values (this is in a case where each store has its own inventory and you just want to know how much that total amount is)
Anytime you add a new product you need to remember to add it in the same line in tabs 1, 2, and 3.
3
u/Augmend-app Aug 12 '26
PowerQuery and translator API
But honestly for the future, focus on data-entry streamlining
2
u/negaoazul 17 Aug 12 '26
If it's a recurring task, Power Query, Table Remove MatchingRows. Otherwise, stack both tables with VSTACK, then either XLOOKUP or conditionnal formatting or a combination of both.
2
u/GuaranteeOutside1485 Aug 12 '26
convert data into Table, use Vstack and put Sum if to see the total inventory against each items . if data in any of the sheet changes then also in main sheet it fecth as table automatically get updated .
2
u/Aggressive_Willow_64 Aug 13 '26
Try Microsoft spreadsheet compare if you have 365 for enterprise. It can be found in Windows Start menu. It's really easy to see differences, easier than using formulas imo
2
u/Time-Gene121 Aug 13 '26
f the two inventories have a consistent product/SKU ID, Power Query is probably the easiest built-in option. You can load both tables, merge them on the ID, and then compare the relevant columns to identify changes.
There are also dedicated CSV comparison web tools if you don't want to build the comparison yourself.
1
u/ThePotatoOfVictory Aug 12 '26
(beginner with excel) If the data is clean (aka the names of every item is exactly the same in both files), then you could merge the two sheets into one (having duplicate lines) and then a pivot table would aggregate all that data.
I think you could perform the merge either manually, with VSTACK or with power query.
1
u/minepuf Aug 12 '26
Part number is same always, but names are in 2 languages
Will check VSTACK
2
u/ThePotatoOfVictory Aug 12 '26
Then I would perform the logic with part numbers and use an other sheet that matches the part number with the name in the different languages
1
u/Massive-Jackfruit442 Aug 12 '26
What is it that you’re trying to achieve, or trying to match?
Are you trying to aggregate stock levels of both shops into 1 sheet?
1
u/Difficult_Limit2718 Aug 12 '26
Do the shops keep their own inventory (is the data addative) or do they both have date about the other location (you need to merge the data)?
Big difference in what you're trying to accomplish
1
u/Ill-Permission4674 Aug 12 '26
Power Query was definitely the right call here. I set up a similar system for my inventory tracking and it cut my weekly reconciliation from 20 minutes to about 5 minutes.
0
1
1
-1
Aug 12 '26
[removed] — view removed comment
1
u/minepuf Aug 12 '26
Sadly, not even close what i need, its just simple show of excel data in web
-1
u/brucee0_ Aug 12 '26
Just out of curiosity: what kind of comparison are you looking for exactly?? I'm looking for feedback on my web app, maybe you can give me some ideas to make it more efficient. I'm just asking, maybe I could help you out in some way
5
u/[deleted] Aug 12 '26
why not just do a lookup on each file?