Excel-How to manipulate this data? 1

[rcrecelius]

I have data this will come out of the database looking like this...

Location Line Part# PartType
1 GAT 1000 N
2 GAT 1000 S
3 GAT 1000 N

The locations represent the stores with Location 1 being the warehouse, 2 3 and so on are the retail stores. I need to identify part#'s that are PartType "S" at the retail locs that are NOT PartType "S" at the warehouse. If I could get the data to look like this...listing the each Location Part Type beside the Part#

Part# L1PT L2PT L3PT
1000 N S N

Will a Pivot table get me where I want to go?

 

[ajakeway]

rcrecelius,

A simple fix to this without a pivot table, if you prefer. I put that chart in cells B1:E4 (including the titles). In A2, put the formula:

=+B2&"P"&D2

The "P" is just a way to separate the location from the part - so P for Part#.

Then you can do a Vlookup on that column. With the following in G1:J3:

Part 1 2 3
1000
2000
3000

and so on. Cell H2:

=VLOOKUP(H$1&"P"&$G2,$A$2:$E$7,5,FALSE)

Which can be copied to the rest of the table.

Alex

 

[KenWright]

Can you expand your example a bit, and clarify roughly how many locations you might have. Are there only ever two values of N or S?

Regards
Ken..................

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission

----------------------------------------------------------------------------

 

[rcrecelius]

Ken,
There are 10 locations but I will only be analyzing Loc 2,3, and 10 against Loc 1. Part type can be S, N or P.

ajakeway,
I tried your solution and it seems to work good...I had never thought of using the Location # in the header as a lookup value...good tip!