+ Reply to Thread
Results 1 to 13 of 13

Merge Rows into Columns with more than one variable

  1. #1
    Registered User
    Join Date
    11-12-2007
    Posts
    9

    Merge Rows into Columns with more than one variable

    I have been scanning web forums for days and have not found a solution to my question. I have found similar, but unfortunately am not clever enough with Excel VBA to adapt, so I hope someone can help.

    I have two columns of data which I want merged into rows (a) by survey number and (b) by time slots. The data I have is similar to:

    Survey No.....Results
    12345...........9am-12pm
    12345...........3pm-6pm
    12345...........No answer
    56789...........6am-9am
    56789...........12pm-3pm
    56789...........6pm-8pm
    56789...........10pm-12am
    .
    .
    etc

    What I want is:

    Survey No......6am-9am.....9am-12pm.....12pm-3pm.....3pm-6pm.....6pm-8pm.....8pm-10pm.....10pm-12am
    12345.............................9am-12pm........................3pm-6pm.....No answer
    56789............6am-9am........................12pm-3pm......................6pm-8pm........................10pm-12am

    AND (if it's not stretching the friendship too far) an entry of "No answer" should go into the next blank column for that survey.

    I guess it doesn't actually have to repeat the times - just an indicator in the correct column would probably do!

    Can anyone offer me a VBA (or other) solution please? Sorry, the columns don't display too well, so I've used dots as separators!
    Last edited by Dimonds; 11-13-2007 at 12:26 AM.

  2. #2
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,464
    try
    Please Login or Register  to view this content.

  3. #3
    Registered User
    Join Date
    11-12-2007
    Posts
    9

    Merge Rows into Columns with more than one variable

    Thanks, Jindon. Unfortunately it didn't work - error message "Object doesn't support this property or method".

    Forgot to mention, I'm using Excel 2007 if it makes a difference?

  4. #4
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,464
    Quote Originally Posted by Dimonds
    Thanks, Jindon. Unfortunately it didn't work - error message "Object doesn't support this property or method".

    Forgot to mention, I'm using Excel 2007 if it makes a difference?
    OOps... a typo

    Please Login or Register  to view this content.
    should be
    Please Login or Register  to view this content.

  5. #5
    Registered User
    Join Date
    11-12-2007
    Posts
    9

    Merge Rows into Columns with more than one variable

    Thanks again, but still no luck! Now I'm getting "Subscript out of range - Error 9".

    The data I showed in my original thread was an example - the actual array should be:

    Please Login or Register  to view this content.
    and yes - the data is 10pm-12pm (some people can't tell the time!). Does this new array make a difference to the code you gave me?
    Last edited by VBA Noob; 11-15-2007 at 03:04 AM.

  6. #6
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,464
    Then change
    Please Login or Register  to view this content.
    to
    Please Login or Register  to view this content.

  7. #7
    Registered User
    Join Date
    11-12-2007
    Posts
    9

    Merge Rows into Columns with more than one variable

    Hi Jindon - yep, tried that but still get the "Subscript out of range" message

  8. #8
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,464
    Right
    Should be 11...

  9. #9
    Registered User
    Join Date
    11-12-2007
    Posts
    9

    Merge Rows into Columns with more than one variable

    Sorry - still no luck. I'm sure it's something really simple - I'll attach the file and see if that helps.
    Attached Files Attached Files

  10. #10
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,464
    Quote Originally Posted by Dimonds
    Sorry - still no luck. I'm sure it's something really simple - I'll attach the file and see if that helps.
    Sorry I can not open zipped file.

  11. #11
    Registered User
    Join Date
    11-12-2007
    Posts
    9

    Merge Rows into Columns with more than one variable

    Hmm, weird that I can't attach an Excel file in an Excel forum! I have pasted some of the data into a Word table - perhaps you can paste it back to Excel to test?
    Cheers
    Attached Files Attached Files

  12. #12
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,464
    Is the attached original sheet layout?

    If so, you don't need vba

    c2:
    =If($B2=C$1,$B2,"")
    copy right & down

  13. #13
    Registered User
    Join Date
    11-12-2007
    Posts
    9

    Merge Rows into Columns with more than one variable

    It is the original layout. I have tried all sorts of "ifs", "vlookups", etc, and can achieve the spread of data, but it's the merging I can't get - I am trying to get all the data for each survey displayed on the one row, and that's what I have failed to achieve so far. Thanks again.

+ 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