Nov 22 2020 08:05 AM
I want the list below to be put in a format like the top three (without the gaps)
Nov 22 2020 10:55 PM
Hi @16Teams
Kindly note that there are two different solution for your query. Attached is workbook for your ready reference.
Solution 01 - Manual approach
Copy & paste the data as values
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
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.
Nov 26 2020 08:59 AM
@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.
Nov 26 2020 04:52 PM
SolutionHi,
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
Nov 27 2020 09:16 AM
@nabilmourad That worked a treat, thanks very much!
Nov 27 2020 12:52 PM
@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!
Nov 30 2020 05:31 AM
@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?
Nov 30 2020 08:19 AM
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.
Dec 02 2020 03:45 AM
@Peter Bartholomew The attached S/S with the formula =MOD(ROW()-1,3) does all what I wanted. Thanks for your input. Merry Christmas.
Nov 26 2020 04:52 PM
SolutionHi,
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