Is there a way to transpose certain columns while keeping others and duplicating them in Excel?

Question

I am needing to transpose only a few columns (ex: C:G) into a single column, while maintaining the information in Column A and B. So if Joe made a sale on Monday Then "Monday" would be in the new Day Column. Then the next row would have Joe again, but this time "Wednesday" in the Day Column. Then, if there are no more values, then move to Steve and do the same thing. I have been stuck on this for a very long time so any advice or new approaches to this would be greatly appreciated. I don't care if it's with a formula or VBA code.

|   |   A   |   B   |   C    |    D    |     E     |    F     |   G    |
+---+-------+-------+--------+---------+-----------+----------+--------+
| 1 | Names | Sales | Monday | Tuesday | Wednesday | Thurday  | Friday |
| 2 | Joe   | 24500 | Monday |         | Wednesday |          |        |
| 3 | Steve | 15454 |        | Tuesday |           |          |        |
| 4 | Emily | 58421 |        | Tuesday | Wednesday | Thursday |        |
| 5 | Marie | 24582 | Monday |         |           |          | Friday |
+---+-------+-------+--------+---------+-----------+----------+--------+


+---+-------+-------+-----------+
|   |   A   |   B   |     C     |
+---+-------+-------+-----------+
| 1 | Names | Sales | Day       |
| 2 | Joe   | 24500 | Monday    |
| 3 | Joe   | 24500 | Wednesday |
| 4 | Steve | 15454 | Tuesday   |
| 5 | Emily | 58421 | Tuesday   |
| 6 | Emily | 58421 | Wednesday |
| 7 | Emily | 58421 | Thursday  |
| 8 | Marie | 24582 | Monday    |
| 9 | Marie | 24582 | Friday    |
+---+-------+-------+-----------+

I believe that power pivot will do this. See the second answer here: https://stackoverflow.com/questions/20541905/convert-matrix-to-3-column-table-reverse-pivot-unpivot-flatten-normal — Scott Craner, Sep 22 '20 at 19:23
VBA: https://stackoverflow.com/questions/36365839/transpose-multiple-columns-to-multiple-rows-with-vba/36366394#36366394 — Tim Williams, Sep 22 '20 at 19:32

score 2 · Accepted Answer · answered Sep 22 '20 at 19:58

2

Wanted to test the new LET() function.

If one has LET() in Office 365 (as of this writing only available to certain insiders)

Put this in the first cell of the output and Excel will spill the results:

=LET(RNG_1,A2:INDEX(A:A,MATCH("zzz",A:A)),RNG_2,B2:INDEX(B:B,MATCH("zzz",A:A)),RNG_3,C2:INDEX(G:G,MATCH("zzz",A:A)),RW,ROWS(RNG_3),CLM,COLUMNS(RNG_3),SEQ,SEQUENCE(RW*CLM,,0),TOT,CHOOSE({1,2,3},INDEX(RNG_1,INT(SEQ/CLM)+1),INDEX(RNG_2,INT(SEQ/CLM)+1),INDEX(RNG_3,INT(SEQ/CLM)+1,MOD(SEQ,CLM)+1)&""),FILTER(TOT,INDEX(TOT,0,3)<>""))

answered Sep 22 '20 at 19:58

Scott Craner

148,073
10
49
81

Solid. Didn't even realize `LET` was a thing. – BigBen Sep 22 '20 at 20:08
2

I only got it this week. I see it as a game changer. it will definitely shorten many a formula. – Scott Craner Sep 22 '20 at 20:12
Worth studying and certainly a *game changer* for the future :+) ... FYI Posted a late answer based on *Office 365's* `Worksheetfunction.Filter()` as VBA approach which might demonstrate how to manipulate the resulting function array (*me too was guided by the intention to test new alternatives*). – T.M. Sep 23 '20 at 19:11

score 0 · Answer 2 · answered Sep 23 '20 at 04:34

'VBA Macro solution

Sub Transps()

Range("l2:n100").ClearContents
LstRw = Application.WorksheetFunction.CountA(Range("a:a"))

writeRw = 2
For rw = 2 To LstRw
    For col = 3 To 7
        If Not IsEmpty(Cells(rw, col)) Then
            Cells(writeRw, 12) = Cells(rw, 1)
            Cells(writeRw, 13) = Cells(rw, 2)
            Cells(writeRw, 14) = Cells(rw, col)
            writeRw = writeRw + 1
        End If
    
    Next col
Next rw

End Sub

score 0 · Answer 3 · answered Sep 23 '20 at 18:59

Just to offer a further solution based on Office 365 I demonstrate a VBA approach using ►Worksheetfunction.Filter() to write transposed blocks of weekday data back to any target:

Sub UnpivotWeekdays()
Dim DataRange As Range
Set DataRange = Sheet1.Range("A2:G6")   ' << change to your needs referring to a sheet's Code(Name)

With Sheet2.Range("A2")                 ' << change to any wanted target cell
    Dim i As Long, ii As Long
    For i = 1 To DataRange.Rows.Count
        'get data blocks resized to 1..5 rows (here: Monday..Friday)
        Dim commoninfo:  commoninfo = getCommonInfo(DataRange, i)
        Dim weekdays:    weekdays = getWeekdays(DataRange, i)
        Dim cnt As Long: cnt = UBound(weekdays)
        
        'write identical common data to first two columns
        .Offset(ii).Resize(cnt, 2).Value = commoninfo
        'write weekdays to single column
        .Offset(ii, 2).Resize(cnt, 1) = Application.Transpose(weekdays)
        'increment current target offsets
        ii = ii + cnt
    Next
End With
End Sub

Help functions

The help function getWeekdays() uses Worksheetfunction.Filter() and returns a "flat" array of non-empty findings which will be transposed to vertical entries in the calling procedure:

Function getWeekdays(rng As Range, ByVal myRow As Long, Optional startColumn As Long = 3) As Variant()
'Purpose: filter valid weekdays (i.e. return only cells <> "")
'Note   : assuming day data in 3rd range column (defaulting startColumn = 3)
    Const DaysOfWeek = 5            ' here: Monday .. Friday
    Dim ad As String: ad = rng.Offset(0, startColumn).Resize(1, DaysOfWeek).Rows(myRow).Address
    On Error Resume Next
    getWeekdays = Evaluate("Filter(" & ad & ", " & ad & "<>"""")")
    If Err.Number <> 0 Then GoTo NOENTRIES
Exit Function
NOENTRIES:
    Dim tmp: ReDim tmp(1 To 1)
    getWeekdays = tmp
End Function

The function getCommonInfo() returns an array of the identifying data in the first two columns to be repeated as often as there exist weekday entries:

Function getCommonInfo(rng As Range, ByVal myRow As Long) As Variant()
'Purpose: get common info from first two range columns
    getCommonInfo = rng.Offset(myRow - 1).Resize(1, 2).Value
End Function

Is there a way to transpose certain columns while keeping others and duplicating them in Excel?

3 Answers3