SOLVED

Anything in Excel that would put the attached into three columns

Copper Contributor

Screenshot 2020-11-22 155643.pngI want the list below to be put in a format like the top three (without the gaps)  

8 Replies

Hi @16Teams

 

Kindly note that there are two different solution for your query. Attached is workbook for your ready reference.

 

Solution 01 - Manual approach 

  1. Apply if function copy the above cell value.
  2. Copy & paste the data as values

  3. Remove the blank delete the blank cell by apply the filter value is null/blank

Snag_1f255b52.png

 

Solution 02 - Recommended [Power Query]

 

You can use Power Query to clear your data and shape it as you need

 

Snag_1f2db89e.png

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Added Conditional Column" = Table.AddColumn(Source, "Custom", each if [Column3] = null then [Column1] else null),
    #"Filled Down" = Table.FillDown(#"Added Conditional Column",{"Custom"}),
    #"Filtered Rows" = Table.SelectRows(#"Filled Down", each ([Column3] <> null)),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Column2"}),
    #"Reordered Columns" = Table.ReorderColumns(#"Removed Columns",{"Column1", "Custom", "Column3"})
in
    #"Reordered Columns"

 

Regards, Faraz Shaikh | MCT, MIE, MOS Master, Excel Expert

If you find the above solution resolved your query don't forget mark as Official/Best Answer & like it to help the other members find it more.

@Faraz Shaikh I wasn't able to work on your last formula (NAMES) Can we do this another way? Going by the Snip can you give me a formula that does:

 MOVE B2 to C1 AND B3 TO A1. Next line would be the same ie: B4 would move to B2 B5 would move to C2 and B6 to A2. Anything in capitals is surplus to the chart so they will be deleted.  I have over 600 rows in one column so I would be grateful for your suggestions.   

best response confirmed by 16Teams (Copper Contributor)
Solution

@16Teams 

Hi,

This is a common Excel problem where we need to Unstack records (rows ► columns)
You watch 4 different ways to do that:
https://www.youtube.com/watch?v=LzEGcRWEmRw
Hope that helps

Nabil Mourad

@nabilmourad That worked a treat, thanks very much!

@16Teams Is there a formula that will move values from B4 into A4 (Deleting text in A4) I have about 600 rows I need to do!

@16TeamsAs I have had no replies to my screen shot below, I assume that there are no formulas that can move every third row in column B into the third  row in column A? 

@16Teams 

A simple lookup by index should do the job.  Given an index column 'k' for the output table and naming the 2-column input table 'data', the formula

= INDEX(data, {1,2,1} + 2*(k-1), {1,1,2})

would return the data in the layout required.

@Peter Bartholomew The attached S/S with the formula =MOD(ROW()-1,3) does all what I wanted. Thanks for your input. Merry Christmas. 

1 best response

Accepted Solutions
best response confirmed by 16Teams (Copper Contributor)
Solution

@16Teams 

Hi,

This is a common Excel problem where we need to Unstack records (rows ► columns)
You watch 4 different ways to do that:
https://www.youtube.com/watch?v=LzEGcRWEmRw
Hope that helps

Nabil Mourad

View solution in original post