Anything in Excel that would put the attached into three columns

Occasional 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



Solution 02 - Recommended [Power Query]


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



    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"})
    #"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 (Occasional Contributor)



This is a common Excel problem where we need to Unstack records (rows ► columns)
You watch 4 different ways to do that:
Hope that helps

Nabil Mourad

In this Amazing tutorial I show you how to unstack records: What is Unstacking?We commonly have data in Excel where records are piled on top of each other in...

@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? 


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.