How to convert data in one column to two columns
Category: [Excel] Demo Available
How to convert data in one column to two columns
Requirement:
The user has data exported but data is in only in one column. There are 2 types of data (email and No of emails sent) and follow each other:
[email protected]
77
[email protected]
88
[email protected]
968
....
The user would like to convert this convert into two columns, so that the user can make table and reports from this data.
Solution:
Code:
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type any}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "Number", each Value.Is(Value.FromText([Column1]), type number)),
#"Added Custom1" = Table.AddColumn(#"Added Custom", "Custom", each if [Number] = true then [Column1] else null),
#"Filled Up" = Table.FillUp(#"Added Custom1",{"Custom"}),
#"Filtered Rows" = Table.SelectRows(#"Filled Up", each ([Number] = false)),
#"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Number"})
in
#"Removed Columns"
If anyone needs a formula solution, try this:
With your data in Sheet1, column A, then on Sheet 2 create these formulae:
A1: =INDEX(Sheet1!A:A,ROW()*2-1,1)
B1: =INDEX(Sheet1!A:A,ROW()*2-,1)
Copy these down as far as needed, until 0's appear.
Obtained from the OzGrid Help Forum.
Solution provided by AlanSidman (code) and mikeopolo (formula).
See also: Index to Excel VBA Code and Index to Excel Freebies and Lesson 1 - Excel Fundamentals and Index to how to… providing a range of solutions and Index to new resources and reference sheets
See also:
| How to combine rows with the same ID# but different columns |
| How to sum up columns in each row and highlight until that value |
| How to use Excel VBA macro to convert multiple columns to multiple rows |
Click here to visit our Free 24/7 Excel/VBA Help Forum where there are thousands of posts you can get information from, or you can join the Forum and post your own questions.