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

I

ibesmond

New Member
Joined
Nov 26, 2010
Messages
17
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.

[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD][TABLE="width: 67"]
<tbody>[TR]
[TD="width: 67"]Customer[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 59"]
<tbody>[TR]
[TD="width: 59"]Part[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64"]Price List[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 69"]
<tbody>[TR]
[TD="width: 69"]Qty Break[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 69"]
<tbody>[TR]
[TD="width: 69"]Each Price[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 88"]
<tbody>[TR]
[TD="width: 88"]Case Price[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 67"]
<tbody>[TR]
[TD="width: 67"]BGONE[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 59"]
<tbody>[TR]
[TD="class: xl65, width: 59"]AB90257[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64"]100 More[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][TABLE="width: 69"]
<tbody>[TR]
[TD="class: xl65, width: 69, align: right"]$89.60[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][TABLE="width: 67"]
<tbody>[TR]
[TD="width: 67"]BGONE[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 59"]
<tbody>[TR]
[TD="class: xl65, width: 59"]AB90257[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64"]BGONE[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 69"]
<tbody>[TR]
[TD="width: 69, align: right"]150[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 69"]
<tbody>[TR]
[TD="class: xl65, width: 69, align: right"][TABLE="width: 69"]
<tbody>[TR]
[TD="class: xl65, width: 69, align: right"]$90.00[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 88"]
<tbody>[TR]
[TD="class: xl65, width: 88, align: right"][TABLE="width: 88"]
<tbody>[TR]
[TD="class: xl65, width: 88, align: right"]$75.00[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 67"]
<tbody>[TR]
[TD="width: 67"]BGONE[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 59"]
<tbody>[TR]
[TD="class: xl65, width: 59"]AB90257[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64"]BGONE[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 69"]
<tbody>[TR]
[TD="width: 69, align: right"]250[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 69"]
<tbody>[TR]
[TD="class: xl65, width: 69, align: right"]$92.95[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 88"]
<tbody>[TR]
[TD="class: xl65, width: 88, align: right"]$75.00[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 67"]
<tbody>[TR]
[TD="width: 67"]BGONE[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 59"]
<tbody>[TR]
[TD="class: xl65, width: 59"]AB90257[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64"]BGONE[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 69"]
<tbody>[TR]
[TD="width: 69, align: right"]200[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 69"]
<tbody>[TR]
[TD="class: xl65, width: 69, align: right"]$92.95[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 88"]
<tbody>[TR]
[TD="class: xl65, width: 88, align: right"]$78.00[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 67"]
<tbody>[TR]
[TD="width: 67"]BGONE[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 59"]
<tbody>[TR]
[TD="class: xl65, width: 59"]AB90257[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64"]PROMO[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 69"]
<tbody>[TR]
[TD="width: 69, align: right"]150[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 69"]
<tbody>[TR]
[TD="class: xl65, width: 69, align: right"][TABLE="width: 69"]
<tbody>[TR]
[TD="class: xl65, width: 69, align: right"]$92.95[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 88"]
<tbody>[TR]
[TD="class: xl65, width: 88, align: right"][TABLE="width: 88"]
<tbody>[TR]
[TD="class: xl65, width: 88, align: right"]$80.00[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]












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

[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD][TABLE="width: 67"]
<tbody>[TR]
[TD="width: 67"]Customer[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 59"]
<tbody>[TR]
[TD="width: 59"]Part[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64"]Price List[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 69"]
<tbody>[TR]
[TD="width: 69"]Each Price[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 69"]
<tbody>[TR]
[TD="width: 69"]Price List[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 88"]
<tbody>[TR]
[TD="width: 88"]1st Qty Break[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 98"]
<tbody>[TR]
[TD="width: 98"]1st Break Price[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 62"]
<tbody>[TR]
[TD="width: 62"]Price List[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 94"]
<tbody>[TR]
[TD="width: 94"]2nd Qty Break[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 103"]
<tbody>[TR]
[TD="width: 103"]2nd Break Price[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 62"]
<tbody>[TR]
[TD="width: 62"]Price List[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 90"]
<tbody>[TR]
[TD="width: 90"]3rd Qty Break[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 100"]
<tbody>[TR]
[TD="width: 100"]3rd Break Price[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 100"]
<tbody>[TR]
[TD="width: 100"]Price List[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 90"]
<tbody>[TR]
[TD="width: 90"]4th Qty Break[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 100"]
<tbody>[TR]
[TD="width: 100"]4th Break Price[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 67"]
<tbody>[TR]
[TD="width: 67"]BGONE[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 59"]
<tbody>[TR]
[TD="class: xl65, width: 59"]AB90257[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64"]100 More[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 69"]
<tbody>[TR]
[TD="class: xl65, width: 69, align: right"]$89.60[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 69"]
<tbody>[TR]
[TD="width: 69"]BGONE[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 88"]
<tbody>[TR]
[TD="width: 88, align: right"]150[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 98"]
<tbody>[TR]
[TD="class: xl65, width: 98, align: right"]$80.00[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 62"]
<tbody>[TR]
[TD="width: 62"]PROMO[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 94"]
<tbody>[TR]
[TD="width: 94, align: right"]150[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 103"]
<tbody>[TR]
[TD="class: xl65, width: 103, align: right"]$75.00[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 62"]
<tbody>[TR]
[TD="width: 62"]BGONE[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 90"]
<tbody>[TR]
[TD="width: 90, align: right"]200[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 100"]
<tbody>[TR]
[TD="class: xl65, width: 100, align: right"]$78.00[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 100"]
<tbody>[TR]
[TD="width: 100"]BGONE[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 90"]
<tbody>[TR]
[TD="width: 90, align: right"]250[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 100"]
<tbody>[TR]
[TD="class: xl65, width: 100, align: right"]$75.00[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]






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.:confused:
 

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.
You must log in or register to reply here.

Similar threads

M
  • Question Question
Multiple Criteria Index Match with One Exact and the Other closest but not greater than....
Replies
6
Views
986
AlanY
A
V
  • Question Question
Using a lookup or index match function to sum list of values with different reference names
Replies
1
Views
574
JayBurn
J
8
  • Question Question
Perform an analysis of inventory variance against a Pre & Post count list.
Replies
1
Views
887
AngelEG
A
X
  • Question Question
Help please! need a complex formula for excel
Replies
0
Views
824
Xeon478
X
J
  • Question Question
How to autopopulate multiple records (rows) from one drop down list
2
Replies
14
Views
1K
jaimem00
J

Forum statistics

Threads
1,223,886
Messages
6,175,198
Members
452,616
Latest member
intern444

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top