vlookup on items and adding duplicates

[bmpreston]

I'm trying to add up a quantity in a column, if the first column contains like numbers, also if another column contains a set number. As an example from the report I pulled, see below:

Column B Column H 323 100579538	Col I
323 100088098	1
323 100088214	3
323 100088222	3
323 100088277	1
323 100503009	1
323 100505300	1
323 100523351	1
323 100523884	1
323 100583204	1
323 100584112	1
323 100088371	6
323 100567650	682
323 100503017	20
323 100511750	1
324 100517484	20
324 100517961	1
324 100088197	1
324 100583446	2
324 100579538	2
324 100579538	1
324 100046955	2
324 100513078	2

<colgroup><col><col></colgroup><tbody>
</tbody>

This didn't come over as a table, but it is three columns. Columns B, H, and I (Other columns are filled, but irrelevant information)

These columns are then sent to a new table, where I'm trying to accomplish the following:

Column B is a technician number, on this report of roughly 100,000 rows, there could be 100 or 150 unique numbers, for this example, I'm showing two numbers (323, and 324). Column H is a part number and these can be duplicated 10's of 1000's of times per report, and per technician number. Finally, column I is a quantity.

The way the report essentially works, Technician 323 uses part number X, Y times, rinse and repeat.

I'm trying to report this to a new table to show, the quantity of part X for each technician.

I want to declare a tech number, then it takes total quantity (I column counts) per part number.

To be clear on this, the report can be 100,000 rows, approximately 3000 unique part numbers (Column H), repeating at different intervals), and the total unique technician numbers is approx 100-150.

I hoping the output to just be a very simple:

Technician 323:

Part Number	Quantity
100503021	3 (Total Count of Column I for Part Number used by tech 323)
100511074	7 (Total count of Column I for part number used by tech 323)

<tbody>
</tbody>

Thanks in advance

 

[sgroath]

A pivot table will handle this for you very easily. Highlight all of your data including the column headers and select Insert, PivotTable. In the pivot table field list that will show up:
- pull your Technician field (or whatever you called your header for column B) to the Report Filter area below
- pull Part Number (or whatever you called your header for column H) down to the Row Labels area
- pull Quantity (or whatever you called your header for column I) down to the Values area.

You should have your working report if you go up to the top and select Technician 323, for example.

 

[bmpreston]

This works quite well. Only think I'm not certain of is how to add the results together. It's giving me multiple counts and I'm unaware how to total up those counts.

(2 here, 4 there)

I'm looking for 6 used.

Thanks

 

[sgroath]

not sure what you mean specifically but have you tried using subtotals or grand totals (see the design tab of pivottable tools).

 

[bmpreston]

So I rolled my own. I used an IF statement to check for tech number: Then used an array formula to check for each part number and bring the results into a table.

I then used a unique only advanced filter to bring me each part number into a new set of columns right next to it, finally performing an add with a SUMIFS out of the columns to get a total.

Thanks to all who helped, but the pivottable thing blew my mind. More than I could handle.