+ Reply to Thread
Results 1 to 10 of 10

Splitting input / transposing issue

  1. 04-16-2012, 06:43 AM #1
    kieranbop
    kieranbop is offline
    Forum Contributor
    Join Date
    09-08-2011
    Location
    Leicester, England
    MS-Off Ver
    Excel 2007
    Posts
    157

    Splitting input / transposing issue

    test.xls


    In this attachment I have a sheet of data where I want to get it to read column M2 onwards and split the information found in that column into there subheadings highlighted in grey.
    Each sub-heading corresponds to a section of information in column M and my aim is to get the information after the headings like parallel.q under qname etc then write it accross all the sub-headings then when we find the ================ row increment down the next row and start all the information writing out again.

    I have no idea how to do this, if someone could help get me started or show me I would be deeply appreciated.

    Thanks K
    Last edited by kieranbop; 04-17-2012 at 08:57 AM. Reason: issue
    Reply With QuoteRegister To Reply
  2. 04-16-2012, 06:59 AM #2
    Andrew-R
    Andrew-R is offline
    Forum Expert
    Join Date
    07-16-2010
    Location
    Northumberland, UK
    MS-Off Ver
    Excel 2007 (home), Excel 2010 (work)
    Posts
    3,054

    Re: Splitting input / transposing issue

    In cell S2 try this formula:

    =TRIM(MID(OFFSET($M$1,((ROW(1:1)-1)*43+COLUMN(A:A)),0),FIND(" ",OFFSET($M$1,((ROW(1:1)-1)*43+COLUMN(A:A)),0)),255))

    And drag across and down.
    Reply With QuoteRegister To Reply
  3. 04-16-2012, 07:20 AM #3
    TMS
    TMS is online now
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    48,957

    Re: Splitting input / transposing issue

    Or maybe:

    S2: =IF(LEFT($M2,FIND(" ",$M2)-1)=S$1,TRIM(SUBSTITUTE($M2,LEFT($M2,FIND(" ",$M2)),"")),"")


    Regards, TMS
    Trevor Shuttleworth - Retired Excel/VBA Consultant

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke

    Reply With QuoteRegister To Reply
  4. 04-16-2012, 07:23 AM #4
    nilem
    nilem is offline
    Forum Expert nilem's Avatar
    Join Date
    10-22-2011
    Location
    Ufa, Russia
    MS-Off Ver
    2013
    Posts
    3,377

    Re: Splitting input / transposing issue

    another formula is 
    Please Login or Register  to view this content.
    Reply With QuoteRegister To Reply
  5. 04-16-2012, 07:49 AM #5
    kieranbop
    kieranbop is offline
    Forum Contributor
    Join Date
    09-08-2011
    Location
    Leicester, England
    MS-Off Ver
    Excel 2007
    Posts
    157

    Re: Splitting input / transposing issue

    thankyou very much you three, got it working how I need now thankyou very much
    Reply With QuoteRegister To Reply
  6. 04-16-2012, 10:17 AM #6
    TMS
    TMS is online now
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    48,957

    Re: Splitting input / transposing issue

    You're welcome. Thanks for the rep.
    Reply With QuoteRegister To Reply
  7. 04-17-2012, 07:50 AM #7
    kieranbop
    kieranbop is offline
    Forum Contributor
    Join Date
    09-08-2011
    Location
    Leicester, England
    MS-Off Ver
    Excel 2007
    Posts
    157

    Re: Splitting input / transposing issue

    quick question: after further looking at it I noticed a problem, I used nilem's code as it was in place for me to automate through vba making it faster instead of dragging accross but with my data if its something like the dates for example:
    qsub_time Wed Oct 5 14:37:20 2011
    start_time Wed Oct 5 15:11:21 2011
    end_time Thu Oct 6 04:12:09 2011

    it will only copy over the 2011 to the cells, how can I get it to copy over the full date? as it's key for me to produce my graphs etc
    Reply With QuoteRegister To Reply
  8. 04-17-2012, 08:37 AM #8
    jindon
    jindon is offline
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,835

    Re: Splitting input / transposing issue

    No need to set all the headings beforehand
    Please Login or Register  to view this content.
    Reply With QuoteRegister To Reply
  9. 04-17-2012, 08:41 AM #9
    nilem
    nilem is offline
    Forum Expert nilem's Avatar
    Join Date
    10-22-2011
    Location
    Ufa, Russia
    MS-Off Ver
    2013
    Posts
    3,377

    Re: Splitting input / transposing issue

    try to change this line in my code above
    Please Login or Register  to view this content.
    Reply With QuoteRegister To Reply
  10. 04-17-2012, 08:57 AM #10
    kieranbop
    kieranbop is offline
    Forum Contributor
    Join Date
    09-08-2011
    Location
    Leicester, England
    MS-Off Ver
    Excel 2007
    Posts
    157

    Re: Splitting input / transposing issue

    thanks both of you, they both work and copy over everything I need after triple checking either's results
    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