How can i have all alike product codes in column A be matched with like cities in column B and then

G

genesisoxygen

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
 
D

daddylonglegs

Try this formula in D1 copied down

=IF((A1=A2)*(B1=B2),"",SUM(INDEX(C$1:C1,MATCH(1,INDEX((A$1:A1=A1)*(B$1:B1=B1),0),0)):C1))
 
D

daddylonglegs

Try this formula in D1 copied down

=IF((A1=A2)*(B1=B2),"",SUM(INDEX(C$1:C1,MATCH(1,INDEX((A$1:A1=A1)*(B$1:B1=B1),0),0)):C1))
 
J

Jim Thomlinson

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...
 
G

genesisoxygen

daddylonglegs said:
Try this formula in D1 copied down

=IF((A1=A2)*(B1=B2),"",SUM(INDEX(C$1:C1,MATCH(1,INDEX((A$1:A1=A1)*(B$1:B1=B1),0),0)):C1))


Thank You, Thank You, And Thank You!!!!
You guys are a life saver
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top