Multiple axis lookup to fill table

[Lunatik]

Hi Guys and Gals
I am improving my database project by adding a multiple axis lookup to create some analyticals. I am trying to fill a table in worksheet4 from the results of the look up from worksheet3


Database example:
Excel 2010

	A	B	C	D	E
1	Date	Product	Data 1	Data2	Data3
2	1-Dec	L/S	5	4	1
3	2-Dec	L/S	4	7	4
4	3-Dec	C/S	6	9	3
5	4-Dec	L/S	4	0	6
6	5-Dec	C/S	5	2	7

<tbody>
</tbody>

Sheet3​

On worksheet4 I will have three Dropdown menus with the following information.
Dropdown1: Date in Month (December)
Dropdown2: Product (L/S, C/S)
Dropdown3: Data (Data1, Data2, Data3)

If I select (December, L/S, Data1) the results will populate in another table like this:
Excel 2010

	A	B	C
12	1-Dec	L/S	5
13	2-Dec	L/S	4
14	4-Dec	L/S	4

<colgroup><col style="width: 25pxpx"><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet3​

Because I have to show some analytical from the search results I will like for this result to show starting in "A10" or so row.

Thanks in advance
Regards

 

[FranzV]

You could solve this using a Pivot Table. You would have to rearange your data to include a DataType column and add a Month column to be able to use a Report Filter by Month on your Pivot Table.

This is how your data table would look:

Worksheet Formulas Cell Formula E3 =MONTH([@Date]) <tbody> </tbody>

<tbody>
</tbody>

Date​	Product​	DataType​	Data​	Month​
01-dic​	L/S​	1​	5​	12​
02-dic​	L/S​	1​	4​	12​
03-dic​	C/S​	1​	6​	12​
04-dic​	L/S​	1​	4​	12​
05-dic​	C/S​	1​	5​	12​
01-dic​	L/S​	2​	4​	12​
02-dic​	L/S​	2​	7​	12​
03-dic​	C/S​	2​	9​	12​
04-dic​	L/S​	2​	0​	12​
05-dic​	C/S​	2​	2​	12​
01-dic​	L/S​	3​	1​	12​
02-dic​	L/S​	3​	4​	12​
03-dic​	C/S​	3​	3​	12​
04-dic​	L/S​	3​	6​	12​
05-dic​	C/S​	3​	7​	12​

<tbody>
</tbody>

And you would have to arrange your Pivot as follows:

• Filters: DataType, Month
• Rows: Date, Product
• Values: Data (You can use SUM, MAX, MIN, or AVERAGE to better suit your needs)

DataType	1
Month	12
Date	Product	Data
01-dic​	L/S	5​
02-dic​	L/S	4​
03-dic​	C/S	6​
04-dic​	L/S	4​
05-dic​	C/S	5​
Total		24​

<tbody>
</tbody>

This way you will have dropdowns for Month, Product and DataType.

Hope it helps.

 

[Lunatik]

Sorry for my late feedback, this works... I will try to build a translator to re-arrange the table for this Pivot to work. Because the data entry happens by another person, but the data analysis is performed by me, I can control the way the I arrange the data before running the Pivot.
Appreciate the help.

 

[FranzV]

I would suggest you use Power Query to unpivot your data (unpivoting manually is tedious and using VBA can be tricky). Ken Puls has a good article to get this done in a simple manner.

Un-Pivoting Data in Power Query

 

[Lunatik]

Sweet, this is a great write up. I always was curious about this procedure of Un-Pivoting Data.
THanks for the help
Sincerely