Place several groups of cells beneath each other

basketpickleball

New Member
Joined
Apr 29, 2020
Messages
3
Office Version
365
Platform
Windows
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. ( )
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!
 

Attachments

Some videos you may like

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.

GraH

Well-known Member
Joined
Mar 22, 2020
Messages
523
Office Version
365, 2016
Platform
Windows
Hi basketpickleball

You might try this Power Query solution.
Code:
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

New Member
Joined
Apr 29, 2020
Messages
3
Office Version
365
Platform
Windows
Hi basketpickleball

You might try this Power Query solution.
Code:
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

Well-known Member
Joined
Mar 22, 2020
Messages
523
Office Version
365, 2016
Platform
Windows
Hi, no worries, it is not always easy to explain something clear.

Is this the source data then?
1588161251802.png


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

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
42,786
Office Version
365
Platform
Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,102,546
Messages
5,487,489
Members
407,604
Latest member
jortronm

This Week's Hot Topics

Top