Converting one long column into multiple columns based on specific variable name in column

[raynejan]

Hello,
I have a very large dataset (i.e., >10k rows) which contains two columns: the first column contains the variables of interest and the second the values corresponding to those variables. These variables are repeated several times over one column (rather than split up). To demonstrate, I have included a screenshot of a pseudo dataset in which we have several variables of interest (named as A to G) in the first column with their respective values in the second column.

What I am hoping to do is split the long two columns every time the variable "A" appears. I've seen suggestions in the forum where you split based on the number of rows (i.e., split every 15th row, for example). The reason I haven't applied this is because sometimes the column contains more than the initial variables of interest. For example, rather than having A to G, you might have A to M appear in some instances along the column. Therefore, ideally, I'd like to split the large column into smaller columns every time the variable "A" appears.

Any recommendations would be greatly appreciated. Thank you in advance!

 

[alansidman]

Power Query Solution

Book18
	A	B	C	D	E	F	G	H
2	A	0.293177			A	0.293177	0.244166	93
3	B	1.063529			B	1.063529	0.55622	151
4	c	1.101265			D	0.244166	0.035328	1.910847
5	D	0.244166			E	0.311802	283.1702	30
6	E	0.311802			F	3.149207	0.039033	2.839609
7	F	3.149207			G	12.89781	17.17765	197
8	G	12.89781			c	1.101265	0.059897	25309149
9	A	0.244166
10	B	0.55622
11	c	0.059897
12	D	0.035328
13	E	283.1702
14	F	0.039033
15	G	17.17765
16	A	93
17	B	151
18	c	25309149
19	D	1.910847
20	E	30
21	F	2.839609
22	G	197
Sheet1

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Grouped Rows" = Table.Group(Source, {"Column1"}, {{"Data", each _, type table [Column1=text, Column2=number]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.AddIndexColumn([Data],"Index",1,1)),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Data"}),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Removed Columns", "Custom", {"Column2", "Index"}, {"Custom.Column2", "Custom.Index"}),
    #"Pivoted Column" = Table.Pivot(Table.TransformColumnTypes(#"Expanded Custom", {{"Custom.Index", type text}}, "en-US"), List.Distinct(Table.TransformColumnTypes(#"Expanded Custom", {{"Custom.Index", type text}}, "en-US")[Custom.Index]), "Custom.Index", "Custom.Column2")
in
    #"Pivoted Column"

 

[Peter_SSs]

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)


Also better if you can give us sample data in a form that we can copy from to test with.
MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.


If you wanted to try a macro approach, give this a go with a copy of your data.

Sub Split_To_Cols()
  Dim a As Variant, b As Variant
  Dim i As Long, j As Long, blocksize As Long, col As Long

  a = Range("A1", Range("B" & Rows.Count).End(xlUp)).Value
  blocksize = Columns("A").Find(What:=Range("A1").Value, LookAt:=xlWhole).Row - 1
  ReDim b(1 To blocksize, 1 To UBound(a) / blocksize * 2)
  col = -1
  For i = 1 To UBound(a) Step blocksize
    col = col + 2
    For j = 1 To blocksize
      b(j, col) = a(i + j - 1, 1)
      b(j, col + 1) = a(i + j - 1, 2)
    Next j
  Next i
  Range("E1").Resize(UBound(b, 1), UBound(b, 2)).Value = b
End Sub

 

[alansidman]

@Peter_SSs
I just found a new feature in O365 Insider that allows you to import a picture into a spreadsheet. I used in this particular case. Thought you might be interested. Here is the link

Import data from picture in Excel for Windows

Check out this cool new feature: Data from Picture in Excel for Windows. It allows you to turn images with table data into data you can edit.

insider.office.com

 

[Peter_SSs]

Hi Alan
I assume that you mentioned that because I had asked for an XL2BB sample.
Yes, I was already aware of the new feature you mentioned and have done quite a few test with it. Whilst it is not bad it has not been 100% reliable in recognising characters for me so I would still rather the reliability of XL2BB where possible.

 

[alansidman]

Peter,
I concur with you on the XL2BB. I prefer it. Just trying to be helpful.

Alan

 

[Peter_SSs]

OK, cheers.