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, index and match to no avail.

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

Cheers,
TF

[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" wrote:

>
> 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
>
>
> --
> TheFarmer42
> ------------------------------------------------------------------------
> TheFarmer42's Profile: http://www.excelforum.com/member.php...o&userid=34102
> View this thread: http://www.excelforum.com/showthread...hreadid=538751
>
>

[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" wrote:

>
> 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!
>
>
> --
> TheFarmer42
> ------------------------------------------------------------------------
> TheFarmer42's Profile: http://www.excelforum.com/member.php...o&userid=34102
> View this thread: http://www.excelforum.com/showthread...hreadid=538751
>
>

[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.

"TheFarmer42" wrote:

>
> 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
>
>
> --
> TheFarmer42
> ------------------------------------------------------------------------
> TheFarmer42's Profile: http://www.excelforum.com/member.php...o&userid=34102
> View this thread: http://www.excelforum.com/showthread...hreadid=538751
>
>