Converting multiple rows to single rows

Thanks:  0
Likes:  0

# Thread: Converting multiple rows to single rows

1. ## 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. ## Re: Converting multiple rows to single rows

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

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

 A B C D E 1 User1 1/1/2009 User1 1/1/2009, 1/2/2009, 1/3/2009 2 User1 1/2/2009 User2 1/4/2009, 1/5/2009, 1/6/2009 3 User1 1/3/2009 User3 1/7/2009, 1/8/2009 4 User2 1/4/2009 5 User2 1/5/2009 6 User2 1/6/2009 7 User3 1/7/2009 8 User3 1/8/2009

 Cell Formula 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

3. ## 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. ## 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. ## 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)
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

## User Tag List

#### Posting Permissions

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