Pivot, Vlookup, Index and Match, Powerpivot? What is the best method???

[ibesmond]

Hello,

Looking for some advise on the the best method for accomplishing this task.

I'm working on a project that involves pricing. We want to be able to analyze some pricing, but to do so, I need to transpose multiple lines of data into one line.

Here is an example of the data.

Customer - Part - Price List - Each Price - Case Qty - Case Price - Pallet Quantity - Pallet Price

The problem that I am running into is there are multiple part/Price List combinations and I want to pull the minimal price off each break.

Customer <tbody> </tbody>	Part <tbody> </tbody>	Price List <tbody> </tbody>	Qty Break <tbody> </tbody>	Each Price <tbody> </tbody>	Case Price <tbody> </tbody>
BGONE <tbody> </tbody>	AB90257 <tbody> </tbody>	100 More <tbody> </tbody>		$89.60 <tbody> </tbody>
BGONE <tbody> </tbody>	AB90257 <tbody> </tbody>	BGONE <tbody> </tbody>	150 <tbody> </tbody>	$90.00 <tbody> </tbody> <tbody> </tbody>	$75.00 <tbody> </tbody> <tbody> </tbody>
BGONE <tbody> </tbody>	AB90257 <tbody> </tbody>	BGONE <tbody> </tbody>	250 <tbody> </tbody>	$92.95 <tbody> </tbody>	$75.00 <tbody> </tbody>
BGONE <tbody> </tbody>	AB90257 <tbody> </tbody>	BGONE <tbody> </tbody>	200 <tbody> </tbody>	$92.95 <tbody> </tbody>	$78.00 <tbody> </tbody>
BGONE <tbody> </tbody>	AB90257 <tbody> </tbody>	PROMO <tbody> </tbody>	150 <tbody> </tbody>	$92.95 <tbody> </tbody> <tbody> </tbody>	$80.00 <tbody> </tbody> <tbody> </tbody>

<tbody>
</tbody>

And I need to transpose it to pull the minimum value at each break.

Customer <tbody> </tbody>

Part <tbody> </tbody>

Price List <tbody> </tbody>

Each Price <tbody> </tbody>

Price List <tbody> </tbody>

1st Qty Break <tbody> </tbody>

1st Break Price <tbody> </tbody>

Price List <tbody> </tbody>

2nd Qty Break <tbody> </tbody>

2nd Break Price <tbody> </tbody>

Price List <tbody> </tbody>

3rd Qty Break <tbody> </tbody>

3rd Break Price <tbody> </tbody>

Price List <tbody> </tbody>

4th Qty Break <tbody> </tbody>

4th Break Price <tbody> </tbody>

BGONE <tbody> </tbody>

AB90257 <tbody> </tbody>

100 More <tbody> </tbody>

$89.60 <tbody> </tbody>

BGONE <tbody> </tbody>

150 <tbody> </tbody>

$80.00 <tbody> </tbody>

PROMO <tbody> </tbody>

150 <tbody> </tbody>

$75.00 <tbody> </tbody>

BGONE <tbody> </tbody>

200 <tbody> </tbody>

$78.00 <tbody> </tbody>

BGONE <tbody> </tbody>

250 <tbody> </tbody>

$75.00 <tbody> </tbody>

<tbody>
</tbody>

I started off by building concatenated fields to combine:

1. The customer & part
2. The customer, part & price list
3. The customer, part, price list & quantity break

To get the lowest price I did a sort by lowest each price, then lowest case price.

I then did a VLOOKUP based on the concatenated field (Customer & part) to pull the lowest each price. Works for the each price, but afterwards it gets a little tricky.

With the VLOOKUP when you go to the case price, it picks the first line and returns "[Blank]" off the 100 more price list.

I tried using a IF(Min(D:D=D2,I2)) but with close to 500,000 lines, the file kept crashing. Then I did it about 100K lines at a time, and it just got stuck processing for hours.

If the quantity breaks were all standard, I would try to pivot it, but the quantity breaks are part specific. Fore instance.

Part 1 - each only
Part 2 - each & 4
Part 3 - each & 12
Part 4 - each, 100, 150, 200 & 250
Part 5 - each, 67, 144, & 288
Part 6 - each, 12, 24 & 48
etc

There may just not be a way to do it? I don't know. I sorted the data by customer by part by price list by quantity, and then used a bunch of "if" statements to pull the data into another column so I could to vlookups off the one line, but if two each match or two quantity breaks match, it would only pull the first record and not the lowest. Any thoughts are appreciated.