Place several groups of cells beneath each other

[basketpickleball]

Hi

I have to make a summary of the different times it takes to add a certain product. Right now all the values I have are sorted by date and every date has all the steps. (

https://imgur.com/a/39zmLeJ

)
I would like to put all the steps that are the same underneath each other so I can make a scatter plot.

Is it possible to do this automatically in excel, or do I have to do it by hand?

I know this isn't a good explanation, but English isn't my first language.

Thanks!

 

[GraH]

Hi basketpickleball

You might try this Power Query solution.

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    TransposeTable = Table.Transpose(Source),
    FilterOutNullRows = Table.SelectRows(TransposeTable, each ([Column1] <> null)),
    AddIndexBase1 = Table.AddIndexColumn(FilterOutNullRows, "Index", 1, 1),
    KeepIndexForDates = Table.AddColumn(AddIndexBase1, "SectionIndex", each if [Column4] = null then [Index] else null),
    FillDownSectionIndex = Table.FillDown(KeepIndexForDates,{"SectionIndex"}),
    RemoveIndex = Table.RemoveColumns(FillDownSectionIndex,{"Index"}),
    GroupOnSectionIndex = Table.Group(RemoveIndex, {"SectionIndex"}, {{"All", each _, type table [Column1=text, Column2=anynonnull, Column3=anynonnull, Column4=text, Index=number, SectionIndex=number]}}),
    TransformSectionTable = Table.AddColumn(GroupOnSectionIndex, "AllTransposed", each Table.PromoteHeaders(Table.Transpose(Table.SelectColumns([All], {"Column1","Column2","Column3","Column4"})),[PromoteAllScalars=true])),
    ExpandAllTransposed = Table.ExpandTableColumn(TransformSectionTable, "AllTransposed", {"Date", "step", "duration", "Δ"}, {"Date", "step", "duration", "Δ"}),
    RemoveSectionIndexAll = Table.RemoveColumns(ExpandAllTransposed,{"SectionIndex", "All"}),
    FillDownDate = Table.FillDown(RemoveSectionIndexAll,{"Date"}),
    SortOnStepAsc = Table.Sort(FillDownDate,{{"step", Order.Ascending}})
in
    SortOnStepAsc

From your Excel start as followed

1. Select the cells containing the data (I even selected the full column range)
2. on the Data Ribbon, Section Get&Transform choose "From Table/Range" -> this opens PQ
3. On the transform tab, click on Transpose Table
4. (If you have selected more columns, just like in Excel filter out "nulls")
5. On the add column tab, click Index Column
6. Add a custom column, name it "SectionIndex" and type in this formula "=if [Column4] = null then [Index] else null"
7. Select this new column and on the transform tab, click on Fill and choose fill down
8. Remove the Index Column (select it, right click with the mouse, from the context menu select "Remove Column")
9. Select the SectionIndex column and on the transform tab click "Group By"
10. Name the aggregation "All" and in Operation choose All Rows
11. Add a custom column again and type in this formula "=Table.PromoteHeaders(Table.Transpose(Table.SelectColumns([All], {"Column1","Column2","Column3","Column4"})),[PromoteAllScalars=true])"
12. Click on the expand button on the column header (arrows pointing up and to the side) and select all the column names
13. Finally remove the helper columns (same as step 8)
14. Select the date and fill down (as in step 7)
15. Sort on the column Step Ascending (like in Excel)
16. Load to Excel

 

[basketpickleball]

Hi basketpickleball

You might try this Power Query solution.

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    TransposeTable = Table.Transpose(Source),
    FilterOutNullRows = Table.SelectRows(TransposeTable, each ([Column1] <> null)),
    AddIndexBase1 = Table.AddIndexColumn(FilterOutNullRows, "Index", 1, 1),
    KeepIndexForDates = Table.AddColumn(AddIndexBase1, "SectionIndex", each if [Column4] = null then [Index] else null),
    FillDownSectionIndex = Table.FillDown(KeepIndexForDates,{"SectionIndex"}),
    RemoveIndex = Table.RemoveColumns(FillDownSectionIndex,{"Index"}),
    GroupOnSectionIndex = Table.Group(RemoveIndex, {"SectionIndex"}, {{"All", each _, type table [Column1=text, Column2=anynonnull, Column3=anynonnull, Column4=text, Index=number, SectionIndex=number]}}),
    TransformSectionTable = Table.AddColumn(GroupOnSectionIndex, "AllTransposed", each Table.PromoteHeaders(Table.Transpose(Table.SelectColumns([All], {"Column1","Column2","Column3","Column4"})),[PromoteAllScalars=true])),
    ExpandAllTransposed = Table.ExpandTableColumn(TransformSectionTable, "AllTransposed", {"Date", "step", "duration", "Δ"}, {"Date", "step", "duration", "Δ"}),
    RemoveSectionIndexAll = Table.RemoveColumns(ExpandAllTransposed,{"SectionIndex", "All"}),
    FillDownDate = Table.FillDown(RemoveSectionIndexAll,{"Date"}),
    SortOnStepAsc = Table.Sort(FillDownDate,{{"step", Order.Ascending}})
in
    SortOnStepAsc

From your Excel start as followed

1. Select the cells containing the data (I even selected the full column range)
2. on the Data Ribbon, Section Get&Transform choose "From Table/Range" -> this opens PQ
3. On the transform tab, click on Transpose Table
4. (If you have selected more columns, just like in Excel filter out "nulls")
5. On the add column tab, click Index Column
6. Add a custom column, name it "SectionIndex" and type in this formula "=if [Column4] = null then [Index] else null"
7. Select this new column and on the transform tab, click on Fill and choose fill down
8. Remove the Index Column (select it, right click with the mouse, from the context menu select "Remove Column")
9. Select the SectionIndex column and on the transform tab click "Group By"
10. Name the aggregation "All" and in Operation choose All Rows
11. Add a custom column again and type in this formula "=Table.PromoteHeaders(Table.Transpose(Table.SelectColumns([All], {"Column1","Column2","Column3","Column4"})),[PromoteAllScalars=true])"
12. Click on the expand button on the column header (arrows pointing up and to the side) and select all the column names
13. Finally remove the helper columns (same as step 8)
14. Select the date and fill down (as in step 7)
15. Sort on the column Step Ascending (like in Excel)
16. Load to Excel

Hi, thanks for the reply!
But I think I didn't explain it right. In the excel file, the 4 columns that are separate is what I want to achieve. The top columns and rows is what I have as data.

 

[GraH]

Hi, no worries, it is not always easy to explain something clear.

Is this the source data then?

PS: hier kan je in je moedertaal posten als dat makkelijker gaat

 

[basketpickleball]

Yes that should be it!

 

[Fluff]

Cross posted Place several groups of cells beneath each other

While we do allow Cross-Posting on this site, we do ask that you please mention you are doing so and provide links in each of the threads pointing to the other thread (see rule 13 here along with the explanation: Forum Rules). This way, other members can see what has already been done in regards to a question, and do not waste time working on a question that may already be answered.