+ Reply to Thread
Results 1 to 11 of 11

Matching categories and Summing

  1. 10-28-2009, 12:52 PM #1
    Pasha81
    Pasha81 is offline
    Forum Contributor
    Join Date
    05-13-2009
    Location
    London, England
    MS-Off Ver
    Excel XP
    Posts
    174

    Matching categories and Summing

    Hi guys, need your help badly

    I’ve hidden a lot of irrelevant columns. The ones we need to focus on are Columns A,C, H, and AG. Daily this sheet ‘Raw’ will be filtered and data sent to sheets 100,200,300.
    In the Raw sheet we have many rows of data, but when this is transferred to the other sheets we will only see one row of data in each sheet.


    1. The Date from Cell(C2) should be copied to Cell ‘A3 or next empty cell on column A’ on all 3 sheets(100,200,300)

    2.In Column A there are 3 accounts – 101,201,301
    If Column A value = 101 then send the corresponding Column H (P&L) value to Sheet”100”, If Column A =201 then send to sheet ‘200’, If 301 then to sheet ‘300’

    Now that’s not quite what I need, the Column H values in some cases have to be added up – by category. You can see this on sheets 100 – 300. Columns B to K on Sheet(100) have the different categories of products. What I want is a code that would go through Column AG in Sheet(Raw) match the category name to the category name in the Range(B2:K2) and copy the P&L figure (Column H value in ‘Raw’) there.
    e.g. I need all the rows pertaining to account 101 with the same label ‘Interest Rate’ to have the P&L (Column H) added up. So I would get the value 188760 in Sheet ‘100’ Cell D3. (I just added H2,H3,H12,H13)

    Please let me know if its not clear.

    Would really appreciate it if someone can look into this or guide me a bit

    thanks
    Attached Files Attached Files
    Last edited by Pasha81; 10-29-2009 at 11:58 AM.
    Reply With QuoteRegister To Reply
  2. 10-28-2009, 01:01 PM #2
    StephenR
    StephenR is offline
    Forum Guru
    Join Date
    08-26-2007
    Location
    London
    Posts
    4,606

    Re: Matching categories and Summing

    Is the date always the same for every row in the file each day? And is it always just those three sheets or is that just an illustration?
    Reply With QuoteRegister To Reply
  3. 10-28-2009, 01:07 PM #3
    Pasha81
    Pasha81 is offline
    Forum Contributor
    Join Date
    05-13-2009
    Location
    London, England
    MS-Off Ver
    Excel XP
    Posts
    174

    Re: Matching categories and Summing

    the date is the same in every row, because i'll get a new file everyday for only previous day's data. Its just an illustration, there could be upto 5,6 of those sheets--But I could make the sheet names the same as the account no. - would that make it more efficient?
    Reply With QuoteRegister To Reply
  4. 10-28-2009, 03:39 PM #4
    StephenR
    StephenR is offline
    Forum Guru
    Join Date
    08-26-2007
    Location
    London
    Posts
    4,606

    Re: Matching categories and Summing

    Yes, it would make it more efficient. So those sheets will already have been created and you are not expecting the macro to create them - is that right?
    Reply With QuoteRegister To Reply
  5. 10-28-2009, 03:57 PM #5
    Pasha81
    Pasha81 is offline
    Forum Contributor
    Join Date
    05-13-2009
    Location
    London, England
    MS-Off Ver
    Excel XP
    Posts
    174

    Re: Matching categories and Summing

    thats correct Stephen, those sheets are already there with those column headings, the macro just needs to send the right figures under those headings on a daily basis.

    thanks for looking into this
    Reply With QuoteRegister To Reply
  6. 10-28-2009, 04:15 PM #6
    StephenR
    StephenR is offline
    Forum Guru
    Join Date
    08-26-2007
    Location
    London
    Posts
    4,606

    Re: Matching categories and Summing

    Ok, try this with the proviso that sheet names amended. Nt sure if you want data added to the bottom of this or how this fits with your daily updates, but that could probably be incorporated. I got bogged down using AutoFilter so went for a formula approach in the end - not very elegant but it works I think.
    Please Login or Register  to view this content.
    Reply With QuoteRegister To Reply
  7. 10-28-2009, 05:36 PM #7
    Pasha81
    Pasha81 is offline
    Forum Contributor
    Join Date
    05-13-2009
    Location
    London, England
    MS-Off Ver
    Excel XP
    Posts
    174

    Re: Matching categories and Summing

    wow that is quite good, I was expecting a massive code. Everything does seem to be working, i will test it out a bit more and confirm it as SOLVED tomorrow.

    By the way, slight adjustment required...When I put in new data it overwrites the existing one, i need to keep old data -------so a line of code to make everything go into the next empty row would be good, any suggestions?
    Reply With QuoteRegister To Reply
  8. 10-28-2009, 05:40 PM #8
    Pasha81
    Pasha81 is offline
    Forum Contributor
    Join Date
    05-13-2009
    Location
    London, England
    MS-Off Ver
    Excel XP
    Posts
    174

    Re: Matching categories and Summing

    Oh and btw there are other worksheets in the original workbook, other worksheets that have nothing to do with this code, I'll probably have to adjust this a little
    Reply With QuoteRegister To Reply
  9. 10-29-2009, 04:50 AM #9
    Pasha81
    Pasha81 is offline
    Forum Contributor
    Join Date
    05-13-2009
    Location
    London, England
    MS-Off Ver
    Excel XP
    Posts
    174

    Re: Matching categories and Summing

    Instead of

    If ws.Name <> "Raw" Then


    I used

    If ws.NAme Like "*01" Then

    and that worked, it doesnt try to copy anything on other sheets. Is this the best way to do it?
    Reply With QuoteRegister To Reply
  10. 10-29-2009, 01:40 PM #10
    StephenR
    StephenR is offline
    Forum Guru
    Join Date
    08-26-2007
    Location
    London
    Posts
    4,606

    Re: Matching categories and Summing

    If it works and covers everything you need then I would say it is a good way, probably as good as any other. If you didn't work out the overwriting problem, try this:
    Please Login or Register  to view this content.
    Reply With QuoteRegister To Reply
  11. 11-10-2009, 01:52 PM #11
    Pasha81
    Pasha81 is offline
    Forum Contributor
    Join Date
    05-13-2009
    Location
    London, England
    MS-Off Ver
    Excel XP
    Posts
    174

    Re: Matching categories and Summing

    Wow the answer was always here, I see it now Stephen! thanks
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next 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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1