Results 1 to 6 of 6

Thread: Sort Data in two columns to rows

  1. #1

    Sort Data in two columns to rows



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Hi,

    I have a set of data something like below. But its a huge data though.

    Please see the input below..


    S71
    A06-15789-000
    A03-52458-002
    S89
    A05-15789-000
    A08-57778-002
    A09-57785-003



    Please see the output below... All the sets populated horizontally.

    S71
    S89
    A06-15789-000
    A05-15789-000
    A03-52458-002
    A05-15789-000
    A09-57785-003



    Thanks a lot for your kind attention and any help is greatly appreciated..

  2. #2
    Super Moderator p45cal's Avatar
    Join Date
    Dec 2012
    Posts
    1,779
    Articles
    0
    Excel Version
    365
    kanta/krishnakashyap, you seem to have cross posted this question at http://www.mrexcel.com/forum/excel-q...ingle-row.html

    Cross posting is OK if you tell people everywhere you have cross posted to, at all the sites involved.
    Please have a read of http://www.excelguru.ca/content.php?184
    Ultimately it's to your benefit.

    I have prepared a small macro which may solve your problem. I will post it here when you have updated related thread(s) at other forum(s). (You may have difficulty posting links since you don't have many posts at the other site(s) but if you omit the http:// part of a link you should be able to do this.)

    If you leave another message in this thread I'll be alerted automatically with an email straightaway which will prompt me to reply again.

  3. #3
    I have updated the related thread at other forum stating that I have cross posted in this forum. Thanks for letting me do that. Would look forward for the macro that you have created.

    Thanks again.

  4. #4
    Super Moderator p45cal's Avatar
    Join Date
    Dec 2012
    Posts
    1,779
    Articles
    0
    Excel Version
    365
    This macro makes some assumptions since I have no workbook with your actual data in it. It creates a new sheet with the rearranged data:
    Code:
    Sub blah()
    Set SourceSht = ActiveSheet
    Set newsht = Sheets.Add(After:=Sheets(Sheets.Count))
    LastRow = SourceSht.Cells(SourceSht.Rows.Count, "B").End(xlUp).Row
    With SourceSht.Cells(1).Resize(LastRow)
      Set Headers = .SpecialCells(xlCellTypeConstants, 23)
      Headers.Copy
      newsht.Cells(1).PasteSpecial Transpose:=True
      colm = 1
      For Each cll In Headers.Cells
        ofst = 1
        Do Until Len(Application.Trim(cll.Offset(ofst).Value)) > 0 Or cll.Offset(ofst).Row > LastRow
          ofst = ofst + 1
        Loop
        Range(cll, cll.Offset(ofst - 1)).Offset(, 1).Copy newsht.Cells(2, colm)
        colm = colm + 1
      Next cll
    End With
    End Sub

  5. #5
    Different approach with array formua, 2 different color 2 different formula.......


    regards
    Attached Files Attached Files

  6. #6
    Thanks a lot p45cal and Rizky. Appreciate your help!!

Posting Permissions

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