matching duplicates then looking up corresponding amounts
 

I need to find the sum of an item for the month. For one month there
are mutiple invoices. Each invoice can have the same item (In this
example it will be a black goggle). On invoice #1 there is a black
goggle and on invoice #2 there is a black goggle. A "count if" formula
would result in counting 2 black goggles. Here is the dilema. invoice
#1 has a black goggle, but it is for a quantity of 4 and on invoice #2
there is a black goggle but for the quantity of 3. So by doing the
"count if" i get 2 black goggles and a "vlookup" for the black goggle
would tell me that i have 4 goggles because it reads the first black
goggle on on the list of data which is invoice #1.

What i would like to create is a formula or macro, if needed, that
would count up every black goggle item in the month and match them with
the corresponding quantity. In my example the correct amount i want to
find is 7 goggles. It counts the black goggles in invoice #1 as 4 and
counts the black goggles in invoice #2 as 3, resulting in seven.

below is a screen shot of what my data looks like.

Invoice Item Unit Qty
1125
*TDM-SF-121 22.00 *
1126
ATT-PR-003 1.00
TDM-PIFD-121 1.00
TDS-MTW-126 1.00
TDS-MTBK-123 1.00
*TDM-SF-121 1.00 *
TDS-PI-125 1.00
TSN-BLKM 1.00
BEANIE-BLK 1.00
TDM-CF-121 1.00

So TDM-SF-121 reads twice in the data dump, so i would like to create
something that accounts for the duplicate item number and then accounts
for the 23 ordered that month.


--
Dklunchoo
------------------------------------------------------------------------
Dklunchoo's Profile: http://www.excelforum.com/member.php...o&userid=27215
View this thread: http://www.excelforum.com/showthread...hreadid=467267

matching duplicates then looking up corresponding amounts
 

Hi,
put this code in a module and save it as an *.xla.


Function vertSUM(aTable As Range, aCell As Range) As double
Dim tmp As Range
Dim A As Integer

Set tmp = aTable.Find(aCell.Text, LookIn:=xlValues)
If Not tmp Is Nothing Then
A = tmp.Row

Do
vertSUM = vertSUM + tmp.Offset(, 1).Value
Set tmp = aTable.Find(aCell.Text, tmp, LookIn:=xlValues)
If tmp Is Nothing Then Exit Function
Loop While tmp.Row < A

End If

End Function


let me know,
Regards,
stefano