Place several groups of cells beneath each other

basketpickleball

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

  • TEST.PNG
    TEST.PNG
    27.4 KB · Views: 11

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.
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
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,531
Messages
6,114,172
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top