How I can import data from one excel sheet to other-CodePudding

In my excel I have main sheet, that contains all the data that I need, but this data is not filtered(majority of the rows are empty), so to choose the values from particular column I need to uncheck 'Blanks' in filters and only then I can copy the data and paste it to another sheet(which should contain the data from each column). I do this for each column in my main sheet. Is there any way to import data(rows which have values values) from each column to another sheet? Because when I am doing it manually it takes a lot of time. Thx for help!

enter image description here

CodePudding user response：

If I rightly got your idea, you need such an expression:

=Sheet1!A26 Sheet1!B26

CodePudding user response：

highlight source data and bring into powerquery with data .. from table/range

in home... advanced/editor... paste code below, changing first row to reflect your range. OK

file... close and load

This works for any number of columns, "scrunching" the data toward the top of each column

let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Demoted Headers" = Table.DemoteHeaders(Source),
#"Added Index" = Table.AddIndexColumn(#"Demoted Headers", "Index", 0, 1),
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Added Index", {"Index"}, "Attribute", "Value"),
#"Removed Columns" = Table.RemoveColumns(#"Unpivoted Other Columns",{"Index"}),
#"Grouped Rows" =Table.Group(#"Removed Columns" , {"Attribute"}, {{"GRP", each Table.AddIndexColumn(_, "Index", 1, 1), type table}}),
#"Expanded GRP" = Table.ExpandTableColumn(#"Grouped Rows", "GRP", {"Value", "Index"}, {"Value", "Index"}),
#"Pivoted Column" = Table.Pivot(#"Expanded GRP", List.Distinct(#"Expanded GRP"[Attribute]), "Attribute", "Value"),
#"Removed Columns1" = Table.RemoveColumns(#"Pivoted Column",{"Index"}),
#"Promoted Headers" = Table.PromoteHeaders(#"Removed Columns1", [PromoteAllScalars=true])
in #"Promoted Headers"

CodePudding user response：

If you want to stack row values while removing blanks, it is fairly easy just to use the FILTER function which you paste in a cell then just copy across columns:

=FILTER(Sheet1!A2:A100,LEN(Sheet1!A2:A100)>0)

However, I notice you have time stamps on the left. If you want to filter out table rows of data that are fully blank while retaining all rows with data in at least one column and retaining table integrity (some blanks will still be present) then this can also be accomplished with a more complicated formula:

=LET(data, Sheet1!B1:E100, table, Sheet1!A1:E100, FILTER(table&"",MMULT(LEN(data),TRANSPOSE(--(COLUMN(data)>0)))>0))

Here you create the filter criteria from matrix multiplication of the length of all strings in the data field against a unit matrix which gives you the sum of all characters present in a row using MMULT(LEN(data),TRANSPOSE(--(COLUMN(data)>0))).

[reference: https://stackoverflow.com/a/61476755/3688861]

This array can then be tested to see if it is greater than 0 for the filter criteria.