Double vlookup solution? Pivot table solution?

girlfenn

New Member
Joined
Mar 8, 2011
Messages
1
I have a huge spreadsheet with about 3000 orders listed down the first 3000 rows. Beside each order in the next 2 columns is an ingredient and a usage level. If the order uses more than one ingredient (almost all do), the next row lists the same order number but with the next ingredient and usage level beside it, and again until all the ingredients used in that order are listed. Then the next order starts. So in total of the 3000 rows, there are really only about 800 unique orders, and a total of about 400 ingredients. I am trying to find a way to create a summary sheet which lists the orders down the first column (no repeats) and the list of ingredients heading each of the following columns. Problem is I have too many ingredients for a pivot table. I am stumped! Below is a short version of what I have:
order ingredi usage
105 salt 1tps
105 oreg 1 tsp
105 water 1 cup
205 salt 1 tsp
205 water 1 cup
205 juice .5 cup
226 oreg 2 tsp
226 water 1 cup
226 juice 1 cup

and would like it to look llike:
order salt oreg water juice
105 1 tsp 1 tsp 1 cup
205 1 tsp 1 cup .5 cup
226 2 tsp 1 cup 1 cup

ANY HELP??
THanks in advance!
 

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".

Claymationator

Well-known Member
Joined
Sep 26, 2006
Messages
705
It doesn't appear that you have too many ingredients to show in a PivotTable. If I use the sample data that you provided in this format:
Excel Workbook
ABC
1OrderIngredientAmount
2105salt1 tsp
3105oreg1 tsp
4105water1 cup
5205salt1 tsp
6205water1 cup
7205juice0.5 cup
8226oreg2 tsp
9226water1 cup
10226juice1 cup
Sheet1
Excel 2010

I can create a pivot table on top of that with the Order and Amount in the row labels, and the Ingredients in the column labels. Then with that layout, do a count of Amount as the values. This will give a view like this:
Excel Workbook
FGHIJKL
1Count of AmountIngredient
2OrderAmountjuiceoregsaltwaterGrand Total
31051 cup11
41 tsp112
52050.5 cup11
61 cup11
71 tsp11
82261 cup112
92 tsp11
10Grand Total22239
Sheet1
Excel 2010

I did the Amount in both the rows, and values because in the PivotTable you need to have a number in order for the table to sum up the amount...with it being text you can only get a count of occurences, but you can't determine by looking if it should be a tsp, cup, etc. So, by putting that in the row labels, and then a count in the values, you can see all of the detail in the pivot.

I hope that makes sense.
 

hiker95

Well-known Member
Joined
Apr 8, 2009
Messages
17,649
girlfenn,

Welcome to the MrExcel forum.


What version of Excel are you using?
 

hiker95

Well-known Member
Joined
Apr 8, 2009
Messages
17,649
girlfenn,

If you are using Excel 2003 or earlier you will not be able to list a total of about 400 ingredients as titles in row 1.


If you are NOT using Excel 2003 or earlier, try the following.


Sample data before the macro in worksheet Sheet1:


Excel Workbook
ABC
1orderingrediusage
2105oreg1 tsp
3105salt1 tps
4105water1 cup
5205juice.5 cup
6205salt1 tsp
7205water1 cup
8226juice1 cup
9226oreg2 tsp
10226water1 cup
11
Sheet1





After the macro in a new worksheet Results:


Excel Workbook
ABCDE
1orderjuiceoregsaltwater
21051 tsp1 tps1 cup
3205.5 cup1 tsp1 cup
42261 cup2 tsp1 cup
5
Results





Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).


1. Copy the below code, by highlighting the code and pressing the keys CTRL + C
2. Open your workbook
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. Press the keys ALT + I to activate the Insert menu
5. Press M to insert a Standard Module
6. Where the cursor is flashing, paste the code by pressing the keys CTRL + V
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel, open the workbook, and press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.


Code:
Option Explicit
Sub ReorgData()
' hiker95, 03/09/2011
' http://www.mrexcel.com/forum/showthread.php?t=534582
Dim w1 As Worksheet, wR As Worksheet
Dim LR As Long, LR2 As Long, SR As Long, ER As Long, a As Long, aa As Long
Dim c As Range, FC As Long, NC As Long
Application.ScreenUpdating = False
Set w1 = Worksheets("Sheet1")
If Not Evaluate("ISREF(Results!A1)") Then Worksheets.Add(After:=w1).Name = "Results"
Set wR = Worksheets("Results")
wR.UsedRange.Clear
Set wR = Worksheets("Results")
LR = w1.Cells(Rows.Count, 1).End(xlUp).Row
w1.Range("A2:C" & LR).Sort Key1:=w1.Range("A2"), Order1:=xlAscending, Key2:=w1.Range("B2") _
  , Order2:=xlAscending, Key3:=w1.Range("C2"), Order3:=xlAscending, Header:= _
  xlNo, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
  DataOption1:=xlSortNormal, DataOption2:=xlSortNormal, DataOption3:=xlSortNormal
w1.Columns(1).AdvancedFilter Action:=xlFilterCopy, CopyToRange:=wR.Columns(1), Unique:=True
w1.Columns(2).AdvancedFilter Action:=xlFilterCopy, CopyToRange:=wR.Columns(2), Unique:=True
LR2 = wR.Cells(Rows.Count, 2).End(xlUp).Row
wR.Range("B2:B" & LR2).Sort Key1:=wR.Range("B2"), Order1:=xlAscending, Header:=xlGuess, _
  OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal
wR.Range("B1").Resize(, LR2 - 1).Value = Application.Transpose(wR.Range("B2:B" & LR2).Value)
wR.Range("B2:B" & LR2).ClearContents
For Each c In wR.Range("A2", wR.Range("A" & Rows.Count).End(xlUp))
  SR = 0: ER = 0
  On Error Resume Next
  SR = Application.Match(c, w1.Columns(1), 0)
  ER = Application.Match(c, w1.Columns(1), 1)
  On Error GoTo 0
  For a = SR To ER Step 1
    FC = Application.Match(w1.Range("B" & a), wR.Rows(1), 0)
    wR.Cells(c.Row, FC).Value = w1.Range("C" & a).Value
  Next a
Next c
wR.UsedRange.Columns.AutoFit
wR.Activate
Application.ScreenUpdating = True
End Sub


Then run the ReorgData macro.
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,130,167
Messages
5,640,534
Members
417,151
Latest member
ChickenTenderer

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
Top