How to Clean and organize data scattered across a sheet into a single column

[ammykhan]

Employee Details​
EID Number				784-1993-0510862-9		784-1989-9675912-1
								784-1999-1456913-4
				784-1990-3515862-4		784-2001-5675912-3		784-1995-7456328-7
				784-1991-3315666-8		784-1997-5945912-5		784-1992-1214627-9
				784-1995-3545342-1		784-1997-4945342-7		784-1996-4954317-4

 

[alansidman]

Unpivot your data using Power Query/Get and Transform. Found on the Data Tab

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Unpivoted Only Selected Columns" = Table.Unpivot(Source, {"Column1", "Column2", "Column3", "Column4", "Column5", "Column6", "Column7", "Column8", "Column9"}, "Attribute", "Value"),
    #"Removed Columns" = Table.RemoveColumns(#"Unpivoted Only Selected Columns",{"Attribute"})
in
    #"Removed Columns"

Book1
	A	B	C	D	E	F	G	H	I
1	Column1	Column2	Column3	Column4	Column5	Column6	Column7	Column8	Column9
2	784-1993-0510862-9		784-1989-9675912-1
3									784-1999-1456913-4
4					784-1990-3515862-4		784-2001-5675912-3		784-1995-7456328-7
5					784-1991-3315666-8		784-1997-5945912-5		784-1992-1214627-9
6
7					784-1995-3545342-1		784-1997-4945342-7		784-1996-4954317-4
8
9
10	Value
11	784-1993-0510862-9
12	784-1989-9675912-1
13	784-1999-1456913-4
14	784-1990-3515862-4
15	784-2001-5675912-3
16	784-1995-7456328-7
17	784-1991-3315666-8
18	784-1997-5945912-5
19	784-1992-1214627-9
20	784-1995-3545342-1
21	784-1997-4945342-7
22	784-1996-4954317-4
Sheet1