need help with data that contains 2 set of columns that i need to pull data from

[Taz Wolf]

First off - thank you all for the great help this place provides. I am a self taught consumer of excel, and usually can find the answers I need after a bit (sometimes a lot) of research.
This time I am stuck and out of my league

I get data from several venders in multiple formats, and most of them I can get setup in a way that I can pull out what I need.
This vender is really frustrating me.
the data comes in with 2 sections side by side and I need to be able to pull numbers based upon the values in the first row and the 3rd or 6th row.

for example
(CO AURORA) + (MID GRD 3-WAY (RVP870) should return 3.207
(CO AURORA) + (ETHANOL) should return 2.72
(CO DUPONT) + (ETHANOL) should return 2.91

I can easily pull the mid grd numbers, but the ethanol from the 2nd set of columns is causing me to have fits.
in the example I am only using 2 locations, but in reality I have 7 locations with 10 different fuels each, so I really need to be able to pull the data as it is formatted if possible.

CO AURORA	2/17/2013	UL RVP 7.8# 10% ET850	-0.0150	3.095	E-85 70% Eth/30% U825	-0.0189	2.61
CO AURORA	2/18/2013	MID GRD 3-WAY (RVP870	-0.0100	3.207	PUL RVP 7.8#      885	-0.015	3.41
CO AURORA	2/19/2013	PUL RVP 7.8# 10% E910	-0.0150	3.4225	ETHANOL           	0	2.72
CO DUPONT	2/20/2013	UL RVP 7.8# 10% ET850	0.0000	5.16	E-85 70% Eth/30% U825	-0.015	3.25
CO DUPONT	2/21/2013	MID GRD 3-WAY (RVP870	-0.0150	3.0267	PUL RVP 7.8#      885	-0.015	3.25
CO DUPONT	2/22/2013	PUL RVP 7.8# 10% E910	-0.0150	3.0307	ETHANOL           	-0.015	2.91

looking for the code that I can use to pull from the above example to this new sheet I am making up

	(PUL RVP 7.8# 10% E910)	(BIO DIESEL)	(ETHANOL)  (and so on)
CO AURORA	
MN ALEXANDRIA			
CO DUPONT			
CO COMMERCE CITY

Thank you once again for any and all help / ideas.

Taz

 

[sheetspread]

Are you trying to go from:

CO AURORA	2/17/2013	UL RVP 7.8# 10% ET850	-0.0150	3.095	E-85 70% Eth/30% U825	-0.0189	2.61
CO AURORA	2/18/2013	MID GRD 3-WAY (RVP870	-0.0100	3.207	PUL RVP 7.8#      885	-0.015	3.41
CO AURORA	2/19/2013	PUL RVP 7.8# 10% E910	-0.0150	3.4225	ETHANOL           	0	2.72
CO DUPONT	2/20/2013	UL RVP 7.8# 10% ET850	0.0000	5.16	E-85 70% Eth/30% U825	-0.015	3.25
CO DUPONT	2/21/2013	MID GRD 3-WAY (RVP870	-0.0150	3.0267	PUL RVP 7.8#      885	-0.015	3.25
CO DUPONT	2/22/2013	PUL RVP 7.8# 10% E910	-0.0150	3.0307	ETHANOL           	-0.015	2.91

to this:

Excel 2010
	A	B	C	D	E	F	G
3	Sum of No2	Column Labels
4	Row Labels	E-85 70% Eth/30% U825	ETHANOL	MID GRD 3-WAY (RVP870	PUL RVP 7.8# 885	PUL RVP 7.8# 10% E910	UL RVP 7.8# 10% ET850
5	CO AURORA	2.61	2.72	3.207	3.41	3.4225	3.095
6	CO DUPONT	3.25	2.91	3.0267	3.25	3.0307	5.16
Sheet8

?

 

[Taz Wolf]

yes i am.

 

[sheetspread]

The number of locations/fuels isn't a problem, and if the number of column groups is just two, you can copy the second group, paste it under the first like this:

Excel 2010
	A	B	C	D	E
1	Loc	Date	Type1	No1	No2
2	CO AURORA	2/17/2013	UL RVP 7.8# 10% ET850	-0.015	3.095
3	CO AURORA	2/18/2013	MID GRD 3-WAY (RVP870	-0.01	3.207
4	CO AURORA	2/19/2013	PUL RVP 7.8# 10% E910	-0.015	3.4225
5	CO DUPONT	2/20/2013	UL RVP 7.8# 10% ET850	0	5.16
6	CO DUPONT	2/21/2013	MID GRD 3-WAY (RVP870	-0.015	3.0267
7	CO DUPONT	2/22/2013	PUL RVP 7.8# 10% E910	-0.015	3.0307
8	CO AURORA	2/17/2013	E-85 70% Eth/30% U825	-0.019	2.61
9	CO AURORA	2/18/2013	PUL RVP 7.8# 885	-0.015	3.41
10	CO AURORA	2/19/2013	ETHANOL	0	2.72
11	CO DUPONT	2/20/2013	E-85 70% Eth/30% U825	-0.015	3.25
12	CO DUPONT	2/21/2013	PUL RVP 7.8# 885	-0.015	3.25
13	CO DUPONT	2/22/2013	ETHANOL	-0.015	2.91
Sheet6 (2)

then set a pivot table with loc as row, type1 as columns, No2 as values:

Excel 2010
	A	B	C	D	E	F	G
3	Sum of No2	Column Labels
4	Row Labels	E-85 70% Eth/30% U825	ETHANOL	MID GRD 3-WAY (RVP870	PUL RVP 7.8# 885	PUL RVP 7.8# 10% E910	UL RVP 7.8# 10% ET850
5	CO AURORA	2.61	2.72	3.207	3.41	3.4225	3.095
6	CO DUPONT	3.25	2.91	3.0267	3.25	3.0307	5.16
Sheet8

Tell me if I missed something, in which case you might need a macro to unpivot the original table.

 

[Taz Wolf]

first off - thank you for the help
I was hoping NOT to have to move the data around like this, but I think it will have to be done...
so that being said... I just started researching getpivotdata function

How do I use this to pull custom data?

I have started with =GETPIVOTDATA("PRICE",Frontier!M5,"name",B$30,"PRODUCT - BRANDED",$A31)

the "A" column will have different locations and the "30" row will have different products NOT in the same order and fewer types then the pivot table(s) will have.

thank you again for the help and hand leading LOL

 

[Taz Wolf]

ooppppsss... I made a major mistake and swapped my variable locations...

=GETPIVOTDATA("PRICE",Frontier!M5,"name",$A31,"PRODUCT - BRANDED",B$30
is now corrected

1 last question for the experts
I have a certain product lets say unleaded, which can also be known by NL, NL 10%, NL10%, 87NL10%, and so on.....
can I create a way / table that understands when looking for matching "Product" of Unleaded, it will also look up the aliases of it as well??

 

[sheetspread]

Well you can't use wildcards in GETPIVOTDATA as far as I know, but you can with lookup and conditional sum/counting formulas.

 

[Taz Wolf]

wildcards will not work for my issues as there are too many variables, I need to be able to use aliases for a single name as 1 name might have 6-10 different aliases depending on the number of different suppliers... and there might be more needed if a supplier is added or if they change their wording of a product..
In my mind cell A1 would have a name of "Unleaded" which could also mean NL, NL 10%, NL10%, 87NL10% which should be all listed in cells D4-D7 as the aliases of A1

not too sure if this is possible..

 

[sheetspread]

If you have a table of aliases then a formula will work:

Excel 2010
	A	B	C	D	E	F	G
1	Color	Number				Alias	Color
2	Silver	2				Silver	White
3	Blue	6				White	White
4	Orange	5				Gray	Gray
5	White	3				LightGray	Gray
6	Gray	2				DarkGray	Gray
7	LightGray	1				Tan	Tan
8	DarkGray	1				Beige	Tan
9	Tan	9				LightBrown	Tan
10	Beige	6				MediumBrown	Tan
11	LightBrown	2
12
13	Gray	4
Sheet12

Cell Formulas
Range	Formula
B13	{=SUM(ISNUMBER(MATCH($A$2:$A$11,IF($G$2:$G$10=A13,$F$2:$F$10),0))*$B$2:$B$11)}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[Taz Wolf]

i will look at implementing that as another step -
thanks to your help I have almost completed my project - then its verifying and tweaking