Update Excel Worksheet for Access

silentwolf

Well-known Member
Joined
May 14, 2008
Messages
1,164
Office Version
  1. 2016
AgmaExcel.JPG


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
 

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.

silentwolf

Well-known Member
Joined
May 14, 2008
Messages
1,164
Office Version
  1. 2016
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
 

Watch MrExcel Video

Forum statistics

Threads
1,129,486
Messages
5,636,611
Members
416,929
Latest member
Nitil

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top