Machine software generated Spreadsheet issues

Question

Hi&nbsp;My boss has asked me to find a solution to this problem and I'm flummoxed to say the least.&nbsp; A company we subcontracted creates a Spreadsheet with multiple and tiny columns, I believe they are using software that automatically creates this sheet from data they input.&nbsp; We are Quantity Surveyors.&nbsp; The sheet replicates some information in all the narrow multiple columns which is odd.&nbsp; All I need to do is condense this information into 4 columns and without spending hours doing it by hand and copying bits at a time into our standard 4 column sheet what can I do?&nbsp;&nbsp;&nbsp;I would be grateful for any advice

joe weischedel · Answer

Teessiderang&nbsp;Hello Teesiderang,I am not sure exactly what you are trying to do. Is it a formatting issue, or is there data that is involved?If the issue is the extra space alone and getting rid of the extra cells or space (no data involved) then try saving a copy, and open the new one to try out edits to the formatting itself, preserving the integrity of your original.Click above rows on the top (use shift and arrow then if multiple consecutive rows need to be selected) and delete as needed.Same with rows just on the leftmost side.Make sure to do this on your copy in case of any errors.If there is data involved it gets a bit more involved depending on what you want to do.Have you checked to see if there is data in the tiny columns, is it hidden? Try expanding the size of the columns by hovering the cursor where they meet up top and it turns into a double arrow. Double click there and it should resize to reveal.&nbsp;Hope this helps.&nbsp;&nbsp;

craig hatmaker · Answer

Teessiderang&nbsp;If you have Power Query...1) Click in the report,2) Data &gt; From Table/Range - That brings up Power Query3) Click Advanced Editor.4) Copy/Paste the code at bottom into Advanced Editor5) Click Done &gt; Close &amp; Load&nbsp;let&nbsp;&nbsp;&nbsp; Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],&nbsp;&nbsp;&nbsp; RmvCols = Table.RemoveColumns(Source,{"Column6", "Column7", "Column8", "Column9", "Column10", "Column11", "Column12", "Column13", "Column3", "Column4", "Column1", "Column14", "Column16", "Column17", "Column18", "Column20", "Column21", "Column22", "Column23", "Column24", "Column25", "Column26"}),&nbsp;&nbsp;&nbsp; RmvBlkRows = Table.SelectRows(RmvCols, each not List.IsEmpty(List.RemoveMatchingItems(Record.FieldValues(_), {"", null}))),&nbsp;&nbsp;&nbsp; RmvRows1 = Table.SelectRows(RmvBlkRows, each ([Column19] &lt;&gt; null)),&nbsp;&nbsp;&nbsp; KeepToss = Table.AddColumn(RmvRows1, "Keep", each if Text.Contains([Column19], "/") or&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Text.Contains([Column19], ".") or&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Text.Contains([Column19], ":")&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; then "Toss" else "Keep"),&nbsp;&nbsp;&nbsp; RmvRowsToss = Table.SelectRows(KeepToss, each ([Keep] = "Keep")),&nbsp;&nbsp;&nbsp; RmvColKeepToss = Table.RemoveColumns(RmvRowsToss,{"Keep"}),&nbsp;&nbsp;&nbsp; RmvRowsCol2Null = Table.SelectRows(RmvColKeepToss, each ([Column2] &lt;&gt; null)),&nbsp;&nbsp;&nbsp; RmvRows2 = Table.SelectRows(RmvRowsCol2Null, each ([Column5] &lt;&gt; "1.1 SUBSTRUCTURE" and [Column5] &lt;&gt; "ASSUMPTIONS AND CLARIFICATIONS") and ([Column2] &lt;&gt; "2/1"))in&nbsp;&nbsp;&nbsp; RmvRows2&nbsp;&nbsp;&nbsp;

Forum Discussion

Machine software generated Spreadsheet issues

2 Replies

Resources