Transposing Info From Columns to Rows - Irregular Pattern

[SimonHughes]

Hello, I have a data set which is in column format and I need to transpose this to row format. There are four columns which could but will not always, have nil, 1, 2 3 or 4 entries and these need to be transposed in rows. The snip below shows original table and the desired outcome. Any help appreciated, many thanks.

Whitbread Remedials Temp.xlsx
	B	C	D	E	F	G
2	Ref#	Location	Comment 1	Comment 2	Comment 3	Comment 4
3	1	AD	Repair link		Remove port
4	2	FD
5	3	SW	Repair link	Clean duct
6	4	AB		Clean duct		Replace hatch
7
8
9	Ref#	Location	Action
10	1	AD	Repair link
11	1	AD	Remove port
12	3	SW	Repair link
13	3	SW	Clean duct
14	4	AB	Clean duct
15	5	AB	Replace hatch
Sheet2

 

[SimonHughes]

Two clicks in power query

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    unPiv = Table.UnpivotOtherColumns(Source, {"Ref#", "Location"}, "Attribute", "Action"),
    delCol = Table.RemoveColumns(unPiv,{"Attribute"})
in
    delCol

Hi Jec, I do not know enough about Power Query to understand what I am supposed to do with your suggestion - I just do not know what the first step is but I am going to see what I can learn (I do know what it does and how to do basic things with PQ).

 

[SimonHughes]

Here's a macro :

Sub Transpose()

Dim Ws1 As Worksheet: Set Ws1 = Sheets("Sheet1") 'Change as required
Dim Ws2 As Worksheet: Set Ws2 = Sheets("Sheet2") 'Change as required
Dim P1 As Range: Set P1 = Ws1.UsedRange
Dim T1: T1 = P1
Dim T2()
Dim a%: a = 1
Dim i%, j%
ReDim Preserve T2(1 To 3, 1 To a)
T2(1, a) = "Ref#"
T2(2, a) = "Location"
T2(3, a) = "Action"
a = a + 1

For i = 2 To UBound(T1)
    For j = 3 To UBound(T1, 2)
        If T1(i, j) = "" Then GoTo n
        ReDim Preserve T2(1 To 3, 1 To a)
        T2(1, a) = T1(i, 1)
        T2(2, a) = T1(i, 2)
        T2(3, a) = T1(i, j)
        a = a + 1
n:  Next j
Next i

Ws2.[A1].Resize(UBound(T2, 2), UBound(T2)) = Application.Transpose(T2)
End Sub

Hi Footoo, I used this but it gave me very jumbled results. I will see what I can do with formula and PQ first but thanks you anyway.

 

[Peter_SSs]

Can I upload a worksheet somehow?

Use XL2BB as you did in post #1 to give us small but representative samples of the various sheets/ranges & explain again in relation to the new sample data.

 

[alansidman]

this may help you with the PQ solution

Power Query is a free AddIn for Excel 2010 and 2013, and is built-in functionality from Excel 2016 onwards (where it is referred to as "Get & Transform Data").

It is a powerful yet simple way of getting, changing and using data from a broad variety of sources, creating steps which may be easily repeated and refreshed. I strongly recommend learning how to use Power Query - it's among the most powerful functionalities of Excel.

- Follow this link to learn how to install Power Query in Excel 2010 / 2013.

- Follow this link for an introduction to Power Query functionality.

- Follow this link for a video which demonstrates how to use Power Query code provided.

 

[SimonHughes]

this may help you with the PQ solution

Power Query is a free AddIn for Excel 2010 and 2013, and is built-in functionality from Excel 2016 onwards (where it is referred to as "Get & Transform Data").

It is a powerful yet simple way of getting, changing and using data from a broad variety of sources, creating steps which may be easily repeated and refreshed. I strongly recommend learning how to use Power Query - it's among the most powerful functionalities of Excel.

- Follow this link to learn how to install Power Query in Excel 2010 / 2013.

- Follow this link for an introduction to Power Query functionality.

- Follow this link for a video which demonstrates how to use Power Query code provided.

Hi Alan, I am working my way through the PQ vids and have already sorted out how I can use it for the major work that I do - and it is fantastic so thanks for the steer. I am still working out how to transpose info with it but will plod on until I get it right. Again, thanks

 

[RoryA]

Load the table into PQ. Select the first two columns, right-click a header and choose 'Unpivot other columns'.

 

[SimonHughes]

Load the table into PQ. Select the first two columns, right-click a header and choose 'Unpivot other columns'.

Hi Rory, being looking and learning on Power Query and this method works a treat. PQ is going to be used a lot more in the future, many thanks.

 

[alansidman]

The primer I used to get started was Master Your Data with Power Query in Excel and Power BI

 

[SimonHughes]

The primer I used to get started was Master Your Data with Power Query in Excel and Power BI

Many thanks, very handy