+ Reply to Thread
Results 1 to 4 of 4

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

  1. #1
    genesisoxygen@gmail.com
    Guest

    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. #2
    Forum Moderator daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,676
    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))

  3. #3
    Jim Thomlinson
    Guest

    RE: 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


    "genesisoxygen@gmail.com" 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
    >
    >


  4. #4
    genesisoxygen@gmail.com
    Guest

    Re: 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,INDEX((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 to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1