Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Matching check to payment amounts | Excel Worksheet Functions | |||
Need to find matching criteria in 1 column, then add amounts in a | Excel Worksheet Functions | |||
Matching columns and removing duplicates | Excel Worksheet Functions | |||
matching values in columns that contain duplicates | Excel Discussion (Misc queries) | |||
matching dupliclates and then looking up the corresponding amounts | Excel Discussion (Misc queries) |