Excel - conditionally transpose rows to columns

Question

I've got an extract of an online spreadsheet where it's laid out :

1   date   author   modified   comment
1   date   author   modified   comment2
1   date   author   modified   comment3
1   date   author   modified   commentn
2   date   author   modified   comment
2   date   author   modified   comment2
2   date   author   modified   comment3
2   date   author   modified   commentn
3   date   author   modified   comment
3   date   author   modified   comment2
3   date   author   modified   comment3
3   date   author   modified   commentn
....
3000 date   author modified   comment60

The number of comments associated with each index varies, and we have a few thousand lines. For the life of me, I can't work out how to transpose these with a formula

Any thoughts on how I can achieve this in Excel?

I'd like to get it laid out as:

1 author concat(date, comment)  concat(date, comment)  concat(date, comment)  
2 author concat(date, comment)  concat(date, comment)  concat(date, comment)

...

So author would be the same with with all instances for one ID? — JvdV
– JvdV, Commented May 30, 2019 at 7:26

JvdV · Accepted Answer · 2019-05-30 07:59:11Z

1

Here is one way using some formulas:

Formula in A2:

=IFERROR(INDEX(Sheet1!$A$1:$A$12,MATCH(0,COUNTIF($A$1:A1,Sheet1!$A$1:$A$12),0)),"")

Enter through CtrlShiftEnter

Drag down...

Formula in B2:

=INDEX(Sheet!$C$1:$C$12,MATCH(A2,Sheet1!$A$1:$A$12,0))

Drag down...

Formula in C2:

=IFERROR(INDEX(Sheet1!$B$1:$B$12,SMALL((Sheet1!$A$1:$A$12=$A2)*ROW(Sheet1!$A$1:$A$12),COUNTIF(Sheet1!$A$1:$A$12,"<>"&$A2)+(COLUMN()-2)))&", "&INDEX(Sheet1!$E$1:$E$12,SMALL((Sheet1!$A$1:$A$12=$A2)*ROW(Sheet1!$A$1:$A$12),COUNTIF(Sheet1!$A$1:$A$12,"<>"&$A2)+(COLUMN()-2))),"")

Enter through CtrlShiftEnter

Drag right and down...

And if that would return numbers instead of dates for you, than swap this part: INDEX(Sheet1!$B$1:$B$12 with: INDEX(TEXT(Sheet1!$B$1:$B$12,"DD-MM-YYYY") or whatever dateformat you might want to use.

Your first step could also just to copy the first columns of ID's on sheet1, paste them on sheet2 and remove duplicates. In that case you will have to adjust ranges on the other formulas to start with A1 instead of A2.

edited May 30, 2019 at 7:59

answered May 30, 2019 at 7:37

JvdV

76.8k8 gold badges48 silver badges90 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Scott Over a year ago

Thank you - that worked for me after a tweak to the columns- I had a couple of different authors that I wanted to group, so I was able to do that.

Error 1004 · Accepted Answer · 2019-05-30 07:55:47Z

You could try:

Option Explicit

Sub test()

    Dim LastRowA As Long, i As Long, Count As Long, j As Long, k As Long, LastRowG As Long, LastColumn As Long, StartingPoint As Long
    Dim arr As Variant
    Dim strAuthor As String, strNextAuthor As String, strFullRecord As String

    With ThisWorkbook.Worksheets("Sheet1")

        LastRowA = .Cells(.Rows.Count, "A").End(xlUp).Row

        arr = .Range("A1:E" & LastRowA)

        StartingPoint = 1

        For i = LBound(arr) To UBound(arr)

            If StartingPoint = i Then

                j = i + 1

                strAuthor = arr(i, 1)
                strNextAuthor = arr(j, 1)

                    Do Until strAuthor <> strNextAuthor

                        j = j + 1

                        If j > LastRowA Then
                            Exit Do
                        Else
                            strNextAuthor = arr(j, 1)
                        End If

                    Loop

                LastRowG = .Cells(.Rows.Count, "G").End(xlUp).Row

                If LastRowG = 1 And .Range("G1").Value = "" Then
                    LastRowG = 1
                Else
                    LastRowG = LastRowG + 1
                End If

                For k = i To j - 1

                    LastColumn = .Cells(LastRowG, .Columns.Count).End(xlToLeft).Column

                    If .Range("G" & LastRowG).Value = "" Then
                        .Range("G" & LastRowG).Value = arr(k, 1)
                        .Range("H" & LastRowG).Value = arr(k, 3)
                        .Range("I" & LastRowG).Value = "(" & arr(k, 2) & ", " & arr(k, 5) & ")"
                    Else
                        .Cells(LastRowG, LastColumn + 1) = "(" & arr(k, 2) & ", " & arr(k, 5) & ")"
                    End If

                Next k

                StartingPoint = j

            End If

        Next i

    End With

End Sub

Results:

I couldn't get that working, but I will have another stab at that this afternoon! Thank you both!

Collectives™ on Stack Overflow

Excel - conditionally transpose rows to columns

2 Answers 2

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related