Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
How can i have all alike product codes in column A be matched with like cities in column B and then add the totals that are in column C
How can i have all alike product codes in column (A) be matched with
like cities in column (B) and then add the totals that are in column (C) Please help me I have 20,000 rows that needs done Example: SR1015 New Boston 10 SR1015 New Boston 5 15 SR1015 Coal Grove 5 SR1015 Coal Grove 20 SR1015 Coal Grove 5 30 BD0600 New Boston 30 BD0600 New Boston 10 BD0600 New Boston 10 BD0600 New Boston 10 60 BD0600 Coal Grove 250 BD0600 Coal Grove 50 300 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
How can i have all alike product codes in column A be matched with like cities in column B and then add the totals that are in column C
Try this formula in D1 copied down =IF((A1=A2)*(B1=B2),"",SUM(INDEX(C$1:C1,MATCH(1,IN DEX((A$1:A1=A1)*(B$1:B1=B1),0),0)):C1)) -- daddylonglegs ------------------------------------------------------------------------ daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486 View this thread: http://www.excelforum.com/showthread...hreadid=567246 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
How can i have all alike product codes in column A be matched with like cities in column B and then add the totals that are in column C
Try this formula in D1 copied down =IF((A1=A2)*(B1=B2),"",SUM(INDEX(C$1:C1,MATCH(1,IN DEX((A$1:A1=A1)*(B$1:B1=B1),0),0)):C1)) -- daddylonglegs ------------------------------------------------------------------------ daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486 View this thread: http://www.excelforum.com/showthread...hreadid=567246 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
How can i have all alike product codes in column A be matched with
My perference for doing something like that is to use a pivot table. Place
the active cell in the middle of your data (I assume that you have headings at the top of the data). Select Data - Pivot Tables and a wizard should open up for you. You can either follow the wizard or just select finish (chances are you can just select finish). Drag the Product Codes to the left hand column and the column B heading to the left hand column. Drag the amounts into the middle and that should do it. If you want to get fancy then add an auto format to the table. Drag the column headings around a bit to best suit what you want... Note this will only work if you have 8,000 or less unique product codes... -- HTH... Jim Thomlinson " wrote: How can i have all alike product codes in column (A) be matched with like cities in column (B) and then add the totals that are in column (C) Please help me I have 20,000 rows that needs done Example: SR1015 New Boston 10 SR1015 New Boston 5 15 SR1015 Coal Grove 5 SR1015 Coal Grove 20 SR1015 Coal Grove 5 30 BD0600 New Boston 30 BD0600 New Boston 10 BD0600 New Boston 10 BD0600 New Boston 10 60 BD0600 Coal Grove 250 BD0600 Coal Grove 50 300 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
How can i have all alike product codes in column A be matched with like cities in column B and then add the totals that are in column C
daddylonglegs wrote: Try this formula in D1 copied down =IF((A1=A2)*(B1=B2),"",SUM(INDEX(C$1:C1,MATCH(1,IN DEX((A$1:A1=A1)*(B$1:B1=B1),0),0)):C1)) -- daddylonglegs ------------------------------------------------------------------------ daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486 View this thread: http://www.excelforum.com/showthread...hreadid=567246 Thank You, Thank You, And Thank You!!!! You guys are a life saver |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Most frequent string in a column matched against a value in its ro | Excel Discussion (Misc queries) | |||
Forming a binary column from a more complicated column of diagnostic codes | Excel Discussion (Misc queries) | |||
Return Title to matched column | New Users to Excel | |||
Landcodes in column A transferred to Continent codes in column B | Excel Discussion (Misc queries) | |||
Calculating totals in a column based on a lookup in another column | Excel Worksheet Functions |