Excel- Getting Data from table into list

[wayneryeven]

Hey all
Bit hard to describe but will give it my best.

I have a sheet that works out how many of a product can be delivered on a certain day, and is laid out as follows:


Date: 15/05/09 | 16/05/09 | 17/05/09
Prod A: 50 60 70
Prod B: 0 10 50

What i want to do is to create a list similar to this:
15/05/09 Prod A 50
16/05/09 Prod A 60
16/05/09 Prod B 10
17/05/09 Prod A 70
17/05/09 Prod B 50

I have messed around with Query but not getting desired results. The quantities for each day are calculated for a series of IFs based on values- the calculation of which are too intense for this.

Is there anyway that i can retrieve the info from this table?

ANY ideas welcomed.

 

[SkipVought]

Hi,

Check out this FAQ I wrote.

faq68-5287

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[wayneryeven]

Thanks Skip
Thats a great article (link is broken though to table normalisation). I am already familiar with normalising data, but i wasnt sure how in excel to make this happen.

The data will chaneg daily, is there anyway to automate the task more? Clicking the bottom right hand cell is great and gives me what i need, however it creates it in a new sheet each time, and also the user would have to click this etc.

Just throwing some ideas around! Thanks for your help.

 

[SkipVought]

You could use MS Query and do multiple UNION queries. once a QueryTable is inserted into a sheet, all you have to do is refresh to get a new resutlset.

Yet another of my FAQ's faq68-5829

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}