Results 1 to 5 of 5
Like Tree1Likes
  • 1 Post By Krishnakumar

Converting multiple rows to single rows

This is a discussion on Converting multiple rows to single rows within the Excel Questions forums, part of the Question Forums category; I have a file with this structure: User1, date User1, date User1, date User2, date User3, date User3, date ... ...

  1. #1
    New Member
    Join Date
    Sep 2006
    Posts
    4

    Default Converting multiple rows to single rows

    I have a file with this structure:

    User1, date
    User1, date
    User1, date
    User2, date
    User3, date
    User3, date ...

    In other words, a list of people with dates against them. Each person may have 1-24 dates and this is currently appearing as multiple rows.

    I'm trying to get to the following structure:

    User1, date, date, date
    User2, date
    User3, date, date ...

    That is, one line per user, with all recorded dates on the same line.

    Any suggestions appreciated.

    Thanks.

  2. #2
    Board Regular jbeaucaire's Avatar
    Join Date
    May 2002
    Location
    Bakersfield, CA
    Posts
    5,997

    Default Re: Converting multiple rows to single rows

    The answer I gave in this thread appears to be literally exactly what you need, too.

    http://www.mrexcel.com/forum/showthread.php?t=367946#5

    That uses two formulas to create the unique list of IDs in one column, then the JoinAll function from Jindon to create the dates in the next column, separated by commas.

    Sheet3

     ABCDE
    1User11/1/2009 User11/1/2009, 1/2/2009, 1/3/2009
    2User11/2/2009 User21/4/2009, 1/5/2009, 1/6/2009
    3User11/3/2009 User31/7/2009, 1/8/2009
    4User21/4/2009   
    5User21/5/2009   
    6User21/6/2009   
    7User31/7/2009   
    8User31/8/2009   

    Spreadsheet Formulas
    CellFormula
    D1{=INDEX($A$1:$A$1000,MATCH(0,COUNTIF($A$1:$A$1000,"<"&$A$1:$A$1000),0))}
    E1=joinall(D1,$A$1:$B$8,", ")
    D2{=IF(COUNTIF($A$1:$A$1000,">"&D1),INDEX($A$1:$A$1000,MATCH(COUNTIF($A$1:$A$1000,"<="&D1),COUNTIF($A$1:$A$1000,"<"&$A$1:$A$1000),0)),"")}
    Formula Array:
    Produce enclosing
    { } by entering
    formula with CTRL+SHIFT+ENTER!


    Excel tables to the web >> Excel Jeanie HTML 4
    Last edited by jbeaucaire; Jan 31st, 2009 at 07:43 AM.
    Microsoft MVP 2010 - Excel
    Jerry Beaucaire's Excel Tools

    "Actually I *am* a rocket scientist." -- JB

  3. #3
    Board Regular
    Join Date
    Oct 2003
    Posts
    1,325

    Default Re: Converting multiple rows to single rows

    Assuming your list of User, date is in Column A, you could put a list of user names in Column B and then run something like the following:

    Code:
    Dim iCTR As Integer
        For iCTR = 1 To 10
            Select Case Split(Range("A" & iCTR).Value, ",")(0)
                Case "User1"
                    Range("B1").Value = Range("B1").Value & ", " & Split(Range("A" & iCTR).Value, ",")(1)
                Case "User2"
                    Range("B2").Value = Range("B2").Value & ", " & Split(Range("A" & iCTR).Value, ",")(1)
                Case "User3"
                    Range("B3").Value = Range("B3").Value & ", " & Split(Range("A" & iCTR).Value, ",")(1)
            End Select
        Next iCTR

  4. #4
    Board Regular
    Join Date
    Jan 2008
    Posts
    12,123

    Default Re: Converting multiple rows to single rows

    Hi, Try this:-
    Names in column "A" , Dates Column "B"
    Code:
    Dim cl As Range, oNm, Rng As Range, Mch, Ray
    Dim Nray(), c As Integer, Rw As Integer
    Set Rng = Range(Range("B2"), Range("B" & Rows.Count).End(xlUp))
    Ray = Rng
    c = 0
    For Each cl In Rng
        Rw = cl.Row
            For Mch = (UBound(Ray) + 1) To 1 Step -1
                If Range("B" & Mch).Row >= cl.Row _
                    And cl.Offset(, -1).Value = Range _
                    ("A" & Mch).Value Then
                    ReDim Preserve Nray(c)
                    Nray(c) = Format(Range("B" & Mch).Value, "dd/mm/yy")
                    c = c + 1
                    If Rw < Mch Then Range("B" & Mch).EntireRow.Delete
                End If
           Next Mch
           cl.Resize(, c).Value = Nray
        
    Erase Nray
    c = 0
    Next cl
    Regards Mick

  5. #5

    Join Date
    Feb 2003
    Location
    Gurgaon/Thrissur
    Posts
    2,615

    Default Re: Converting multiple rows to single rows

    Hi,

    Code:
    Sub kTest()
    Dim a, k(), q(), i As Long, n As Long, lCol As Long
    a = Range("a1").CurrentRegion.Resize(, 2)
    ReDim k(1 To UBound(a, 1), 1 To Columns.Count)
    With CreateObject("scripting.dictionary")
        .comparemode = vbTextCompare
        For i = 1 To UBound(a, 1)
            If Not .exists(a(i, 1)) Then
                n = n + 1
                k(n, 1) = a(i, 1): k(n, 2) = a(i, 2)
                .Add a(i, 1), Array(n, 2)
            Else
                q = .Item(a(i, 1)): q(1) = q(1) + 1
                k(q(0), q(1)) = a(i, 2)
                lCol = Application.Max(lCol, q(1))
                .Item(a(i, 1)) = q
            End If
        Next
    End With
    With Range("d1")
        .Resize(n, lCol).Value = k
    End With
    End Sub
    HTH
    DocAElstein likes this.

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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


DMCA.com