Complicated value lookup

[TheFarmer42]

I have a set of data in this format:

a x 12
b x 14
c x 18
a y etc.
b y
c y
a z
b z
c z

And i want it in this table format:
x y z
a # # #
b # # #
c # # #


What is the relevant formulae? I've tried some mashups of offset, inde
and match to no avail.

An example spreadsheet is attached if you want to have a go.

Cheers,
T

+-------------------------------------------------------------------
|Filename: Complicated value lookup.zip
|Download: http://www.excelforum.com/attachment.php?postid=4726
+-------------------------------------------------------------------

 

[CaptainQuattro]

The easiest way to achieve your result would be as follows:

Insert an additional column between COLUMN and VALUE

In cell C3 enter formula =A3&B3

In cell H4 enter formula =VLOOKUP(G4&H$3,$C:$D,2,FALSE

 

[Gazzr]

Hi,

I think a pivot table might me easier.

Higlight the range of cells (A2 to C11).
Click Data > Pivot Table > Next > Next > Layout.

Drag the Row button to the Row section of the Pivot table, Column
button to Column section and Value to the data section (make sure it
says "Sum of Value")
Click OK > Finish and you should get the results you need.

Thanks
gazzr

 

[Gazzr]

Thats good Quattro, I'll make a note of that Vlookup formula.

Gazzr

 

[TheFarmer42]

Thanks Heaps Captain Quattro and Gazzr!
Both methods work (and are very easy) compared to what i was trying to
do.
Virtual Beers all round!
Cheers,
TF

 

[TheFarmer42]

Arghhh!

Just when i thought it was all hunky dory!

Pivot table method:
In the data i am now applying it too, the pivot table is too large to
fit on one worksheet (whichever way i do it).
I could probably do two pivot tables after splitting the table, but it
doesn't seem very neat.
It also alphabetised the column data as well, which is not desirable,
and my guess at a workaround is not very neat either.

&,Vlookup method:
There is not a data point for every possible combination of the data.
If there is no data, the default should be zero. Since the formula
returns N/A, i can't make an easy formula out of this. I am planning to
do a two step process - a first table with N/A's in it, and a second
table that uses info from the first table to put zeros instead of
N/A's. Neater solutions anyone?

Cheers,
TF

 

[Dominic]

You could probably nest the Captain's lookup formula in an if statement:

=if(isna(VLOOKUP(G4&H$3,$C:$D,2,FALSE)),"",VLOOKUP(G4&H$3,$C:$D,2,FALSE))

Untested but looks like it should work. ;-)

 

[TheFarmer42]

Thanks Dominic, that works.
I'd tried isna() before, but it didn't work - my syntax gets a bit
dodgy too late in the day!

 

[Dominic]

You're welcome Farmer.

Glad you got it worked out. Captain's vlookup was pretty slick.

 

[TheFarmer42]

You may be interested (or not) in what I used the techniques learned
here for. It was to establish a method of taking old (legacy) Linear
programming models in the common MPS format and making them available
for editing and solving in Excel.

http://www.johnquiggin.com/rsmg/wordpress/?p=28

Cheers,
TF

 

[Dominic]

Very nice. Looks like a big brain cramp.

Thanks for sharing.