Results 1 to 8 of 8

Thread: Extracting data

  1. #1
    Star Lounger
    Join Date
    Apr 2002
    Location
    Vlissingen, Netherlands
    Posts
    63
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Extracting data

    Hello all,

    The sheet contains data within borders; A-F.
    Would like to create a new table with all data for A en D en F without using VBA.

    Anybody has an idea?

    Thanks,
    Joop
    Attached Files Attached Files
    Last edited by jbw92; 2011-04-09 at 11:15.

  2. #2
    5 Star Lounger
    Join Date
    Dec 2009
    Location
    East Coast, USA
    Posts
    993
    Thanks
    8
    Thanked 43 Times in 43 Posts
    Does the attached do what is needed?
    Hold Ctrl Key then click on A,D,F.
    Copy and paste to new tab.


    PS: Did you mean B,D,F ?
    Did you want the formats also?
    Attached Files Attached Files
    Last edited by tfspry; 2011-04-09 at 12:16. Reason: added a PS:

  3. #3
    Star Lounger
    Join Date
    Apr 2002
    Location
    Vlissingen, Netherlands
    Posts
    63
    Thanks
    3
    Thanked 0 Times in 0 Posts
    Thanks for the reply.
    This is not what i mean. Want the data based upon the letter in the upper left corner of the square (in column B).
    Will prepare a new question later.

  4. #4
    5 Star Lounger
    Join Date
    Dec 2009
    Location
    East Coast, USA
    Posts
    993
    Thanks
    8
    Thanked 43 Times in 43 Posts
    Maybe this is what was meant?
    Attached Files Attached Files

  5. #5
    Star Lounger
    Join Date
    Apr 2002
    Location
    Vlissingen, Netherlands
    Posts
    63
    Thanks
    3
    Thanked 0 Times in 0 Posts
    Thanks,
    Assume this was done as described in your first post.

    Do you know a way how to do this using excel functions?
    Or is VBA the only method?
    The data as supplied in my attachement is not fixed; can be more can be less.
    Also the number of combinations is can be numerous.
    As an example:
    A subtotal for A and B has to be prepared (SUM_AB)
    A subtotal for C and D has to be prepared (SUM_CD)
    A subtotal for E and F has to be prepared (SUM_EF)
    A subtotal for SUM_CD and SUM_EF has to be prepared (SUM_CDEF)
    A subtotal for SUM_AB and SUM_CD and SUM_EF has to be prepared (SUM_ABCDEF)

    Thanks

  6. #6
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    It sounds like you want to extract just given A D F you can use:
    In C4:
    =INDEX(Blad1!C:C,MATCH(B3,Blad1!B:B,0)+1)

    Copy C4 to C7 & C10

    In D3
    =INDEX(Blad1!D:d,MATCH($B3,Blad1!$B:$B,0))
    Copy to E3:G3
    Copy D3:G3 to D6:G6 D9:G9

    Also since D4:G4 may or may not be related to the value in column C, that should be looked up as well. In D4:
    =INDEX(Blad1!D:d,MATCH($B3,Blad1!$B:$B,0)+1)

    Copy to E4:G4
    Copy D4:G4 to D7:G7 D10:G1o

    The other last rows depend on the values above so they do not need to be extracted, but can keep their formulas


    If that is not what you want you need to be a lot clearer on what you have and what you want...
    Steve

  7. The Following User Says Thank You to sdckapr For This Useful Post:

    jbw92 (2011-04-12)

  8. #7
    Star Lounger
    Join Date
    Apr 2002
    Location
    Vlissingen, Netherlands
    Posts
    63
    Thanks
    3
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by sdckapr View Post
    If that is not what you want you need to be a lot clearer on what you have and what you want...
    Steve
    I know :-)
    Have been experimenting with the INDEX formula but without good result.
    Will try your suggestion asap.

    Thanks a lot for the reply.

  9. #8
    Star Lounger
    Join Date
    Apr 2002
    Location
    Vlissingen, Netherlands
    Posts
    63
    Thanks
    3
    Thanked 0 Times in 0 Posts
    Hello Steve,

    Tried your suggestions=======>>>>>> perfect solution.

    Thanks very dutch

    Joop

Posting Permissions

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