Sorting / Lookup based on cell?

[markpalmer]

I posted this in the general forum with no luck and someone suggested trying powerpivot to accomplish this...

I am trying to look for a way to use a pivot table (?) to accomplish this lookup / sorting issue i have. Sheet one is a query which lists line item detail - item / quantity / invoice / price each.
Sheet two is where i want to sort the information in a column type of look. I want to be able to enter an invoice number in "c4" / "d4" - ect and have it then populate the rest of the items with the item / price and the appropriate quantity. Ideally i want only onw row per item as well as shown below. The number of rows will vary depending on the invoice

Any help or suggestions would be appreciated.



Excel 2010[TABLE="class: cms_table"]
<tbody>[TR="bgcolor: #DAE7F5"]
[TH][/TH]
[TH]A[/TH]
[TH]B[/TH]
[TH]C[/TH]
[TH]D[/TH]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD]item[/TD]
[TD]Quantity[/TD]
[TD]invoice[/TD]
[TD]Price[/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD]Blue Coat[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]1115[/TD]
[TD="align: right"]100[/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD]Red Coat[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]1115[/TD]
[TD="align: right"]100[/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD]Socks[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]1125[/TD]
[TD="align: right"]200[/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD]Shoes[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1125[/TD]
[TD="align: right"]200[/TD]
[/TR]
[TR]
[TD="align: center"]6[/TD]
[TD]Hat[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]1120[/TD]
[TD="align: right"]300[/TD]
[/TR]
[TR]
[TD="align: center"]7[/TD]
[TD]Blue Hat[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]1120[/TD]
[TD="align: right"]300[/TD]
[/TR]
[TR]
[TD="align: center"]8[/TD]
[TD]Blue Coat[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]1125[/TD]
[TD="align: right"]100[/TD]
[/TR]
[TR]
[TD="align: center"]9[/TD]
[TD]Blue Hat[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1115[/TD]
[TD="align: right"]300[/TD]
[/TR]
[TR]
[TD="align: center"]10[/TD]
[TD]Socks[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]1120[/TD]
[TD="align: right"]200[/TD]
[/TR]
</tbody>[/TABLE]

Sheet1


Excel 2010[TABLE="class: cms_table"]
<tbody>[TR="bgcolor: #DAE7F5"]
[TH][/TH]
[TH]A[/TH]
[TH]B[/TH]
[TH]C[/TH]
[TH]D[/TH]
[TH]E[/TH]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]invoice[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD="align: center"][/TD]
[TD="align: center"]price each[/TD]
[TD="align: center"]1115[/TD]
[TD="align: center"]1120[/TD]
[TD="align: center"]1125[/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD="align: center"]Blue Coat[/TD]
[TD="align: center"]100[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"][/TD]
[TD="align: center"]4[/TD]
[/TR]
[TR]
[TD="align: center"]6[/TD]
[TD="align: center"]Red Coat[/TD]
[TD="align: center"]100[/TD]
[TD="align: center"]4[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]7[/TD]
[TD="align: center"]Socks[/TD]
[TD="align: center"]200[/TD]
[TD="align: center"][/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]6[/TD]
[/TR]
[TR]
[TD="align: center"]8[/TD]
[TD="align: center"]Shoes[/TD]
[TD="align: center"]200[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]1[/TD]
[/TR]
[TR]
[TD="align: center"]9[/TD]
[TD="align: center"]Hat[/TD]
[TD="align: center"]300[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]10[/TD]
[/TR]
[TR]
[TD="align: center"]10[/TD]
[TD="align: center"]Blue Hat[/TD]
[TD="align: center"]300[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]4[/TD]
[TD="align: center"][/TD]
[/TR]
</tbody>[/TABLE]

Sheet2​

 

[MD610]

This can more or less be done pretty easily with a regular PivotTable. No real need on this to get PowerPivot involved.

1. Highlight your data in Sheet1 (A1:D10 in the example)
2. On the Insert tab in Excel, insert a PivotTable
3. By default, the data you want to analyze should be listed. Select the location where you want the PivotTable.
4. The PivotTable Field List should pop-up on the right. Drag and drop the fields into the boxes below like this:

a. Drop Item into Row Labels
b. Drop Price into Row Labels below Items
c. Drop Invoice into Column Labels
d. Drop Quantity into Values (it should automatically change to Sum of Quantity)​

5. Now just some minor formatting is required to clean it up. When the cursor is on any cell in the Pivot you should see new tabs in the ribbon called PivotTable Tools. Under Design > Layout > Report Layout, you want to select Show in Tabular Form
6. Then under Design > Layout > Subtotals you want to select Do Not Show Subtotals. You could also use the Grand Totals option next to Subtotals to turn on or off Grand Totals if you would like.

That's basically what you wanted. In your Pivot, there should be a field called Invoice with a little drop down arrow. You can click on the drop down to select one or more invoices.
If you want to get real fancy you could even create an Invoice Slicer to filter your Invoices. To do this, click inside the Pivot, go to the Insert tab, and select Slicer. Pick the Invoice field from the Insert Slicer dialog and then place/resize the Slicer where ever it makes sense. The slicer is basically just a filter button. Hold down Ctrl to pick more than one at a time.