Consulting

Results 1 to 12 of 12

Thread: check for the values in the sheet 2 and count the values

  1. 03-07-2009, 10:53 PM #1
    sindhuja
    sindhuja is offline
    VBAX Mentor
    Joined
    Sep 2007
    Posts
    405
    Location

    Smile check for the values in the sheet 2 and count the values

    Hi,

    Here is my requirement…

    I have an excel with some sample data…
    I want the counts of all the accounts with the below criteria excluding the accounts in the sheet 2. Also want the counts in the sheet 3.

    < -100k , (-100k) to (-500k) , > -500k , 100k to 500k , >500k

    Also another set of account to be distinguished separately from others.
    Search for the accounts – corresponding subtotal and the cell value next to the subtotal cell…

    Am not sure whether this can be done using macro… any help on this will be highly appreciated..

    Immediate help will be highly helpful…

    -Sindhuja
    Reply With Quote Reply With Quote
  2. 03-08-2009, 03:07 AM #2
    mdmackillop
    mdmackillop is offline
    Administrator
    VP-Knowledge Base     VBAX Grand Master mdmackillop's Avatar
    Joined
    May 2004
    Location
    Scotland
    Posts
    14,489
    Location
    This would be simpler if your Column A data on sheets 1 and Columns A, C on sheet 3 were split into separate columns. Can you rearrange it in this fashion or do you have to work with it as presented?

    Which column is providing the criteria for the count?
    MVP (Excel 2008-2010)

    Post a workbook with sample data and layout if you want a quicker solution.

    To help indent your macros try Smart Indent

    Please remember to mark threads 'Solved'
    Reply With Quote Reply With Quote
  3. 03-08-2009, 04:07 AM #3
    Bob Phillips
    Bob Phillips is offline
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,453
    Location
    If you add a helper column with a header and a simple formula of

    =TRIM(LEFT(SUBSTITUTE(A4," ",REPT(" ",100)),200))

    and then you could pivot it.
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber
    Reply With Quote Reply With Quote
  4. 03-10-2009, 03:50 AM #4
    sindhuja
    sindhuja is offline
    VBAX Mentor
    Joined
    Sep 2007
    Posts
    405
    Location
    Hi..

    I will be working the way i presented.. For the counts i will be using the column A in sheet 1 provided i have to exclude the Column A values in sheet 2 . And the count to be entered in Sheet 3 - Column B.

    Hope i made it clear....

    -Sindhuja
    Last edited by sindhuja; 03-10-2009 at 04:16 AM.
    Reply With Quote Reply With Quote
  5. 03-16-2009, 10:19 AM #5
    sindhuja
    sindhuja is offline
    VBAX Mentor
    Joined
    Sep 2007
    Posts
    405
    Location
    Any help on this will be highly helpful.... pls..
    Reply With Quote Reply With Quote
  6. 03-24-2009, 01:25 PM #6
    sindhuja
    sindhuja is offline
    VBAX Mentor
    Joined
    Sep 2007
    Posts
    405
    Location
    Hi All,

    This is something very urgent... any hand on this will be highly appreciated.

    -Sindhuja
    Reply With Quote Reply With Quote
  7. 03-24-2009, 01:47 PM #7
    Aussiebear
    Aussiebear is offline
    Moderator VBAX Wizard Aussiebear's Avatar
    Joined
    Dec 2005
    Location
    Queensland
    Posts
    5,059
    Location
    Did you try XLD's suggestion?
    Remember To Do the Following....
    Use [Code].... [/Code] tags when posting code to the thread.
    Mark your thread as Solved if satisfied by using the Thread Tools options.
    If posting the same issue to another forum please show the link
    Reply With Quote Reply With Quote
  8. 03-25-2009, 08:21 AM #8
    sindhuja
    sindhuja is offline
    VBAX Mentor
    Joined
    Sep 2007
    Posts
    405
    Location
    Yes……..I tried XLD’s sugessions.. but its not giving me the results I expected…
    Reply With Quote Reply With Quote
  9. 03-25-2009, 02:54 PM #9
    Aussiebear
    Aussiebear is offline
    Moderator VBAX Wizard Aussiebear's Avatar
    Joined
    Dec 2005
    Location
    Queensland
    Posts
    5,059
    Location
    Post a worksheet with some dummy data on sheet 1 & on sheet 2 the layout as you require it to be
    Remember To Do the Following....
    Use [Code].... [/Code] tags when posting code to the thread.
    Mark your thread as Solved if satisfied by using the Thread Tools options.
    If posting the same issue to another forum please show the link
    Reply With Quote Reply With Quote
  10. 03-26-2009, 07:04 AM #10
    sindhuja
    sindhuja is offline
    VBAX Mentor
    Joined
    Sep 2007
    Posts
    405
    Location
    Hi...

    Have already attached the sheet with dummy data's.

    -Sindhuja
    Reply With Quote Reply With Quote
  11. 04-05-2009, 11:53 PM #11
    sindhuja
    sindhuja is offline
    VBAX Mentor
    Joined
    Sep 2007
    Posts
    405
    Location
    Any help on this pls....
    Reply With Quote Reply With Quote
  12. 04-06-2009, 01:06 AM #12
    Bob Phillips
    Bob Phillips is offline
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,453
    Location
    Post the example with my formula, with the pivot (you said you tried it), and then tell is in what way it fails to meet your needs.
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber
    Reply With Quote Reply With Quote
« Previous Thread | Next Thread »

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