How to update Excel?

[JpDp]

Hi all,
I am very new to EasyMorph.
I have a requirement from a customer who has been using some python scripts to update an Excel spreadsheet, which is used as a pricelist for IT components.
The key requirement is to be able to, from within the spreadsheet see which supplier is selling which item for the least or the most etc.
All items have a Vendor Part Number (VPN), which is on each supplier's pricelist.
Most of the pricelists are in csv's and stored on the suppliers' websites. EG Ingram Micro is an example.

The formatting of the original spreadsheet is to remain the same:

1 = Vendor Part Number
2 = Current sell price
3 = columns which need to be updated. There's normally two columns for each supplier; one for the price and the other for the stock level. If a supplier is out of stock, then their price may be ignored.
There's about 8 suppliers in total.

I am able to download and extract the various pricelists; that's OK.
And I can create a new spreadsheet, but the format is very different.

Here's what EasyMorph can output:

1 = the imported prices from the supplier, which is OK.
2 = the original formatting isn't kept.

So, my question is:
Can I easily manipulate the original spreadsheet and just add additional columns, or should I add a new sheet and use in built Excel functions like Xlookup() to update the columns?

Thanks,
Jp.

[dgudkov]

Hi John-Paul, and welcome to the Community!

As of today, EasyMorph can't update existing sheets while preserving the formatting. Instead, you can update a (hidden) sheet with unformatted (raw) data. Then, on the formatted sheet, use a pivot table that uses the raw sheet as the source. The benefit of using a pivot table is that it can update automatically on workbook opening.

Another way of solving the problem is to use queries in Excel to port data from an unformatted sheet into a formatted table on another sheet. It's described here: Using EM to populate an existing spreadsheet