Update Excel Worksheet for Access

[silentwolf]

Hi Guys I need some help with following situation,

I got this list from a Excel Data and need to do following.

Column A, B, C, H & I, is data relavant to the "Product"

Column D:G is relavant to the "Prices" "PreisNational" or PreisPartner are in the picture not but can vary from different "PriceLists"

Now I like to update that data so I get a ID number in Column "A" so add new column to the table in Column A so it is unique to each "Material" then I need to create two new lists.
One list ID; Material; Materialkurztext; Gruppe; Materialstatus; Vertriebstatus;
Another list with ID; Preis; Preisliste;

On the second list I need to get all data for each Preislist below eachother

So every List gets its ID number and all the relavant Data below each other when done then go to the next "Preislist" so the next column and add it below the secont list,

I would need that so I can then transfer the Data easily to Access

Hope I did explain it good enough?


Could someone please help me with this.

Many thanks in advance!!

Albert

 

[silentwolf]

Hi guys,

first of all I like to say that I did not expect someone to write me a code for this just a hint how to go about it would be great.

However in the meantime I did some research and came up with the power pivot to start of with.

I am sure for many of you this is a daily task but I never used it before and never needet it either.

Now I did experiment with power query and I am sure it goes the right way of what I need. But I do have few questions regarding power pivot you might be
able to answer.

In power pivot the unpivot is already giving me the right outcome I would need to insert it back to Access Db.

When I do create the power pivot I do get about 600 Errors. I guess it has to do that in the table there are a few records with a price of € 0,00
So my question is how to I check weather that is the case and how can I tell power pivot that those values should not be includet in the power query?

And one more thing can power query also be used within VBA? So I can reuse the same query for the next time I like or I get updatet Pricelists so to say.

Sorry just very new to this topic.

Many thanks in advance for some help!
Cheers

Albert