Formula or VBA help needed, please

[Mike Goldman]

Not sure if I need a formula or a macro to complete the task at hand,
but this is what I want to do with a 5,000-plus line spreadsheet. I'm
an Excel newbie, forgive me for my lack of proper terminology:

In the list below, I want all Dogs to appear on the same row, with the
proper price in the proper column. Note that the 10,20,30,40 in the
Loc column match up with the 10,20,30,40 in the header row.....

Loc Item 10 20 30 40
10 Dog 4.45 xxx xxx xxx
30 Dog xxx xxx 4.45 xxx
10 Cat 15.75 xxx xxx xxx
30 Cat xxx xxx 15.75 xxx
40 Cat xxx xxx xxx 15.75
40 Horse xxx xxx xxx 10.50
10 Camel 5.75 xxx xxx xxx
30 Camel xxx xxx 5.75 xxx

So, the final list after it has been functioned or macroed will look
like this (with the Loc column now hidded because it is irrelevant in
the final data):

Item 10 20 30 40
Dog 4.45 xxx 4.45 xxx
Cat 15.75 xxx 15.75 15.75
Horse xxx xxx xxx 10.50
Camel 5.75 xxx 5.75 xxx

Is this doable?
Thanks in advance.

 

[JE McGimpsey]

So, the final list after it has been functioned or macroed will look
like this (with the Loc column now hidded because it is irrelevant in
the final data):

Item 10 20 30 40
Dog 4.45 xxx 4.45 xxx
Cat 15.75 xxx 15.75 15.75
Horse xxx xxx xxx 10.50
Camel 5.75 xxx 5.75 xxx

Is this doable?
Thanks in advance.

One way is to use a Pivot Table. See Debra Dalgleish's tutorial

http://peltiertech.com/Excel/Pivots/pivotstart.htm

and I've put an example sheet with your data at

ftp://ftp.mcgimpsey.com/excel/mgoldmandemo.xls

It may seem complicated at first, but it's a *very* powerful technique.