Double vlookup solution? Pivot table solution?

[girlfenn]

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!

 

[Claymationator]

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
	A	B	C
1	Order	Ingredient	Amount
2	105	salt	1 tsp
3	105	oreg	1 tsp
4	105	water	1 cup
5	205	salt	1 tsp
6	205	water	1 cup
7	205	juice	0.5 cup
8	226	oreg	2 tsp
9	226	water	1 cup
10	226	juice	1 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
	F	G	H	I	J	K	L
1	Count of Amount		Ingredient
2	Order	Amount	juice	oreg	salt	water	Grand Total
3	105	1 cup				1	1
4		1 tsp		1	1		2
5	205	0.5 cup	1				1
6		1 cup				1	1
7		1 tsp			1		1
8	226	1 cup	1			1	2
9		2 tsp		1			1
10	Grand Total		2	2	2	3	9
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]

girlfenn,

Welcome to the MrExcel forum.


What version of Excel are you using?

 

[hiker95]

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
	A	B	C
1	order	ingredi	usage
2	105	oreg	1 tsp
3	105	salt	1 tps
4	105	water	1 cup
5	205	juice	.5 cup
6	205	salt	1 tsp
7	205	water	1 cup
8	226	juice	1 cup
9	226	oreg	2 tsp
10	226	water	1 cup
11
Sheet1

After the macro in a new worksheet Results:

Excel Workbook
	A	B	C	D	E
1	order	juice	oreg	salt	water
2	105		1 tsp	1 tps	1 cup
3	205	.5 cup		1 tsp	1 cup
4	226	1 cup	2 tsp		1 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.

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.