need help with a formula

[hollaholla]

Hello everyone, I need a little help creating a formula. I've been messing with some different functions but can't seem to find one that does what I'm trying to get it to.

Here's what I'm trying to do...


<table border="0" cellpadding="0" cellspacing="0" width="179"> <col style="width: 86pt;" width="115"> <col style="width: 48pt;" width="64"> <tbody><tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt; width: 86pt;" width="115" height="17">UNIQUE PART #S</td> <td style="width: 48pt;" width="64">QUANTITY</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">AD027018</td> <td>
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">AD042058</td> <td>
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">AD027018</td> <td>
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">AD027018</td> <td>
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">B2969640</td> <td>
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">AD042058</td> <td>
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">B2969640</td> <td>
</td> </tr> </tbody> </table>

I have unique part numbers on the left. This list is being created by me using a bar code scanner to scan parts into the spreadsheet. Since the parts are not in order then when I'm done scanning I will sort the list manually, and go down it and count duplicates then put a number quantity to the right. Then once I put the quantity I delete duplicates and blank rows moving the data up so that I know have one of each part number with the total quantity of that part.

It looks like this when I'm done doing everything:


<table style="width: 179px; height: 109px;" border="0" cellpadding="0" cellspacing="0"><tbody><tr height="17"></tr><tr style="height: 12.75pt;" height="17"><td style="height: 12.75pt; width: 86pt;" width="115" height="17">UNIQUE PART #S</td><td style="width: 48pt;" width="64">QUANTITY</td></tr><tr style="height: 12.75pt;" height="17"><td style="height: 12.75pt;" height="17">AD027018</td><td> 3
</td></tr><tr style="height: 12.75pt;" height="17"><td style="height: 12.75pt;" height="17">AD042058</td><td> 2
</td></tr><tr style="height: 12.75pt;" height="17"><td style="height: 12.75pt;" height="17">B2969640</td><td> 2
</td></tr><tr style="height: 12.75pt;" height="17"><td style="height: 12.75pt;" height="17">
</td><td>
</td></tr><tr style="height: 12.75pt;" height="17"><td style="height: 12.75pt;" height="17">
</td><td>
</td></tr><tr style="height: 12.75pt;" height="17"><td style="height: 12.75pt;" height="17">
</td><td>
</td></tr><tr style="height: 12.75pt;" height="17"><td style="height: 12.75pt;" height="17">
</td><td>
</td></tr></tbody></table>So the final result is ONE part number with a Quantity in column "b". I'm doing all of this manually. Is there a way to automate this so that when I scan the list it will automatically detect and delete duplicates/blank rows and enter me a number count in the "B" column?

 

[MrKowz]

Try using a Pivot Table. Put the part numbers into Row Field, and also place part numbers into the data field.