Data Matching from Sage

[Kaltb]

Hi everyone!

New to the forum so apologies if this has been covered. I am seeking help on how to match data from a report exported from sage and converted to csv to match another excel spreadsheet.

So I have an excel master spreadsheet with names of customers with their monthly sales figures. Every month I have to update that spreadsheet with the current months figures from sage. So I export the csv file from sage which would contain the same customers or any new customers we have gained.

I basically sit there manually copy and pasting the data. I want a magic formula that can match the figures to the customer name from my sage spreadsheet to the main master spreadsheet. Baring in mind there are negative figures in the debit column which needs to match the customer name in the master spreadsheet with a negative figure.

Any idea how or what formula to use?

Many thanks, Kal

 

[SpillerBD]

It sounds like you may have a couple candidates.
If you have a one-to-one matching to then pull values, you could use VLOOKUP or the combination of INDEX and MATCH
If you have a one-to-many, SUMIFS.

If the export from Sage includes everything you need for your report, you can open the CSV with Excel or import the data into Excel. Then you can perform a summary with PivotTable.

ExcelIsFun with examples from Mike Girvin is one of the best places to go in-depth general instruction.