SOLVED

Anything in Excel that would put the attached into three columns

%3CLINGO-SUB%20id%3D%22lingo-sub-1921767%22%20slang%3D%22en-US%22%3EAnything%20in%20Excel%20that%20would%20put%20the%20attached%20into%20three%20columns%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1921767%22%20slang%3D%22en-US%22%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-left%22%20image-alt%3D%22Screenshot%202020-11-22%20155643.png%22%20style%3D%22width%3A%20158px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F235461i9751E05F7F6F8DBD%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22Screenshot%202020-11-22%20155643.png%22%20alt%3D%22Screenshot%202020-11-22%20155643.png%22%20%2F%3E%3C%2FSPAN%3EI%20want%20the%20list%20below%20to%20be%20put%20in%20a%20format%20like%20the%20top%20three%20(without%20the%20gaps)%26nbsp%3B%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1921767%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1922481%22%20slang%3D%22en-US%22%3ERe%3A%20Anything%20in%20Excel%20that%20would%20put%20the%20attached%20into%20three%20columns%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1922481%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F385033%22%20target%3D%22_blank%22%3E%4016Teams%3C%2FA%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EKindly%20note%20that%20there%20are%20two%20different%20solution%20for%20your%20query.%20Attached%20is%20workbook%20for%20your%20ready%20reference.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSTRONG%3ESolution%2001%20-%20Manual%20approach%26nbsp%3B%3C%2FSTRONG%3E%3C%2FP%3E%3COL%3E%3CLI%3EApply%20if%20function%20copy%20the%20above%20cell%20value.%3C%2FLI%3E%3CLI%3E%3CP%3ECopy%20%26amp%3B%20paste%20the%20data%20as%20values%3C%2FP%3E%3C%2FLI%3E%3CLI%3E%3CP%3ERemove%20the%20blank%20delete%20the%20blank%20cell%20by%20apply%20the%20filter%20value%20is%20null%2Fblank%3C%2FP%3E%3C%2FLI%3E%3C%2FOL%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Snag_1f255b52.png%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F235510iB59926A4BC0A6F21%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22Snag_1f255b52.png%22%20alt%3D%22Snag_1f255b52.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSTRONG%3ESolution%2002%20-%20Recommended%20%5BPower%20Query%5D%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYou%20can%20use%20Power%20Query%20to%20clear%20your%20data%20and%20shape%20it%20as%20you%20need%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Snag_1f2db89e.png%22%20style%3D%22width%3A%20551px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F235511i6A15AC2583265B71%2Fimage-dimensions%2F551x440%3Fv%3D1.0%22%20width%3D%22551%22%20height%3D%22440%22%20role%3D%22button%22%20title%3D%22Snag_1f2db89e.png%22%20alt%3D%22Snag_1f2db89e.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%E2%80%83%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-applescript%22%3E%3CCODE%3Elet%0A%20%20%20%20Source%20%3D%20Excel.CurrentWorkbook()%7B%5BName%3D%22Table1%22%5D%7D%5BContent%5D%2C%0A%20%20%20%20%23%22Added%20Conditional%20Column%22%20%3D%20Table.AddColumn(Source%2C%20%22Custom%22%2C%20each%20if%20%5BColumn3%5D%20%3D%20null%20then%20%5BColumn1%5D%20else%20null)%2C%0A%20%20%20%20%23%22Filled%20Down%22%20%3D%20Table.FillDown(%23%22Added%20Conditional%20Column%22%2C%7B%22Custom%22%7D)%2C%0A%20%20%20%20%23%22Filtered%20Rows%22%20%3D%20Table.SelectRows(%23%22Filled%20Down%22%2C%20each%20(%5BColumn3%5D%20%26lt%3B%26gt%3B%20null))%2C%0A%20%20%20%20%23%22Removed%20Columns%22%20%3D%20Table.RemoveColumns(%23%22Filtered%20Rows%22%2C%7B%22Column2%22%7D)%2C%0A%20%20%20%20%23%22Reordered%20Columns%22%20%3D%20Table.ReorderColumns(%23%22Removed%20Columns%22%2C%7B%22Column1%22%2C%20%22Custom%22%2C%20%22Column3%22%7D)%0Ain%0A%20%20%20%20%23%22Reordered%20Columns%22%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3ERegards%2C%20Faraz%20Shaikh%20%7C%20MCT%2C%20MIE%2C%20MOS%20Master%2C%20Excel%20Expert%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CEM%3E%3CFONT%20color%3D%22%23808080%22%3EIf%20you%20find%20the%20above%20solution%20resolved%20your%20query%20don't%20forget%20mark%20as%26nbsp%3B%3CSPAN%3EOfficial%2FBest%20Answer%20%26amp%3B%20like%20it%20to%20help%20the%20other%20members%20find%20it%20more.%3C%2FSPAN%3E%3C%2FFONT%3E%3C%2FEM%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1934538%22%20slang%3D%22en-US%22%3ERe%3A%20Anything%20in%20Excel%20that%20would%20put%20the%20attached%20into%20three%20columns%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1934538%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F156456%22%20target%3D%22_blank%22%3E%40Faraz%20Shaikh%3C%2FA%3E%26nbsp%3BI%20wasn't%20able%20to%20work%20on%20your%20last%20formula%20(NAMES)%20Can%20we%20do%20this%20another%20way%3F%20Going%20by%20the%20Snip%20can%20you%20give%20me%20a%20formula%20that%20does%3A%3C%2FP%3E%3CP%3E%26nbsp%3BMOVE%20B2%20to%20C1%20AND%20B3%20TO%20A1.%20Next%20line%20would%20be%20the%20same%20ie%3A%20B4%20would%20move%20to%20B2%20B5%20would%20move%20to%20C2%20and%20B6%20to%20A2.%20Anything%20in%20capitals%20is%20surplus%20to%20the%20chart%20so%20they%20will%20be%20deleted.%26nbsp%3B%20I%20have%20over%20600%20rows%20in%20one%20column%20so%20I%20would%20be%20grateful%20for%20your%20suggestions.%26nbsp%3B%20%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1935079%22%20slang%3D%22en-US%22%3ERe%3A%20Anything%20in%20Excel%20that%20would%20put%20the%20attached%20into%20three%20columns%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1935079%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F385033%22%20target%3D%22_blank%22%3E%4016Teams%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EHi%2C%3C%2FP%3E%0A%3CP%3EThis%20is%20a%20common%20Excel%20problem%20where%20we%20need%20to%20Unstack%20records%20(rows%20%E2%96%BA%20columns)%3CBR%20%2F%3EYou%20watch%204%20different%20ways%20to%20do%20that%3A%3CBR%20%2F%3E%3CA%20href%3D%22https%3A%2F%2Fwww.youtube.com%2Fwatch%3Fv%3DLzEGcRWEmRw%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fwww.youtube.com%2Fwatch%3Fv%3DLzEGcRWEmRw%3C%2FA%3E%3CBR%20%2F%3EHope%20that%20helps%3C%2FP%3E%0A%3CP%3ENabil%20Mourad%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1936534%22%20slang%3D%22en-US%22%3ERe%3A%20Anything%20in%20Excel%20that%20would%20put%20the%20attached%20into%20three%20columns%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1936534%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F365248%22%20target%3D%22_blank%22%3E%40nabilmourad%3C%2FA%3E%26nbsp%3BThat%20worked%20a%20treat%2C%20thanks%20very%20much!%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
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
Highlighted

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.

Highlighted

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

Highlighted
Best Response confirmed by 16Teams (Occasional 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

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...
Highlighted

@nabilmourad That worked a treat, thanks very much!

Highlighted

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

Highlighted

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

Highlighted

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

Highlighted

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