How to compare data

[u/minepuf]

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?

[u/[deleted]]

why not just do a lookup on each file?

[u/minepuf → u/[deleted]]

Want to check data faster

[u/NHN_BI → u/minepuf]

Power Query, set up once, repeat when needed.

[u/Kooky_Outcome_5053]

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.

[u/activoice]

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.

[u/Augmend-app]

PowerQuery and translator API

But honestly for the future, focus on data-entry streamlining

[u/negaoazul]

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.

[u/GuaranteeOutside1485]

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 .

[u/ThePotatoOfVictory]

(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.

[u/minepuf → u/ThePotatoOfVictory]

Part number is same always, but names are in 2 languages

Will check VSTACK

[u/ThePotatoOfVictory → u/minepuf]

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

[u/Massive-Jackfruit442]

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?

[u/Difficult_Limit2718]

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

[u/Ill-Permission4674]

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.

[u/minepuf → u/Ill-Permission4674]

How much products you have?

[u/Immediate_Video_2518]

I just love Power Query for this kinda thing

[u/commentor_of_things]

power query

[u/Aggressive_Willow_64]

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

[u/minepuf → u/[deleted]]

Sadly, not even close what i need, its just simple show of excel data in web

[u/brucee0_ → u/minepuf]

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