Excel Group rows and add minimum and maximum from two different columns within the group

0 votes

I have the following excel table:

+-------+----------+------------+----------+
| event | category | start-time | end-time |
+-------+----------+------------+----------+
| a     | Z        |          0 |        9 |
| a     | Z        |          2 |       10 |
| a     | Z        |          2 |       11 |
| b     | Y        |          2 |        7 |
| c     | X        |          3 |        8 |
| c     | X        |          2 |        7 |
| c     | W        |          4 |        8 |
| c     | W        |          5 |       10 |
+-------+----------+------------+----------+

I need to group the rows by event and category and then find the start and end-time of that event-group and finally calculate the time difference. Resulting in the following table:

+-------+----------+------------+----------+------+
| event | category | start-time | end-time | time |
+-------+----------+------------+----------+------+
| a     | Z        |          0 |       11 |   11 |
| b     | Y        |          2 |        7 |    5 |
| c     | X        |          2 |        8 |    6 |
| c     | W        |          4 |       10 |    6 |
+-------+----------+------------+----------+------+

Is there an elegant solution, without using VBA?

Dec 19, 2022 in Others by Kithuzzz
• 28,700 points
70 views

1 answer to this question.

0 votes

You can accomplish your goal with Power Query, available in Windows Excel 2010+ and Office 365.

To use Power Query

  • Select some cell in your Data Table
  • Data => Get&Transform => from Table/Range
  • When the PQ Editor opens: Home => Advanced Editor
  • Make note of the Table Name in Line 2
  • Paste the M Code below in place of what you see
  • Change the Table name in line 2 back to what was generated originally.
  • Read the comments and explore the Applied Steps to understand the algorithm

M Code

let

//read in the raw data
//change table name in next line to actual table name in your workbook
    Source = Excel.CurrentWorkbook(){[Name="Table17"]}[Content],

//set appropriate data types
    #"Changed Type" = Table.TransformColumnTypes(Source,{
        {"event", type text}, {"category", type text}, {"start-time", Int64.Type}, {"end-time", Int64.Type}
            }),

//Group by event and category
//extract min start time and max end time
//calculate time duration
    #"Grouped Rows" = Table.Group(#"Changed Type", {"event", "category"}, {
        {"Start", each List.Min([#"start-time"]), type nullable number}, 
        {"End", each List.Max([#"end-time"]), type nullable number},
        {"Time", each List.Max([#"end-time"]) - List.Min([#"start-time"]), type nullable number}
        })
    
in
    #"Grouped Rows"

enter image description here


If your version of Excel has the UNIQUE and FILTER functions, you can do this with formulas:

eg:

A29:    =UNIQUE($A$8:$B$15)
C29:    =MIN(FILTER($C$8:$C$15,($A$8:$A$15=A29)*($B$8:$B$15=B29)))
D29:    =MAX(FILTER($D$8:$D$15,($A$8:$A$15=A29)*($B$8:$B$15=B29)))
E29:    =D29-C29

Select C29:E29 and fill down
enter image description here

answered Dec 19, 2022 by narikkadan
• 53,160 points

Related Questions In Others

0 votes
1 answer

Removing duplicates from Excel rows by adding values of some columns

Copy the first three columns to another ...READ MORE

answered Sep 23, 2022 in Others by narikkadan
• 53,160 points
113 views
0 votes
1 answer

Excel formula for searching two text in one cell and return values based on the result

You can include a second IF within ...READ MORE

answered Oct 10, 2022 in Others by narikkadan
• 53,160 points
201 views
0 votes
1 answer

Comparing two Excel files in R and returning different titles

Solution: library(tidyverse) dat <- read_xlsx("Book1.xlsx") dat2 <- read_xlsx("Book2.xlsx") book1_output <- anti_join(dat,dat2, ...READ MORE

answered Oct 14, 2022 in Others by narikkadan
• 53,160 points
266 views
0 votes
1 answer

Excel Formula to convert from cm to inches and feet & round off to two digits

Use the TEXT function to format, and ...READ MORE

answered Oct 15, 2022 in Others by narikkadan
• 53,160 points
124 views
0 votes
1 answer

Compare 2 columns in same excel sheet in pandas

Try this: import pandas as pd import numpy as ...READ MORE

answered Dec 16, 2022 in Others by narikkadan
• 53,160 points
121 views
0 votes
1 answer

DYNAMIC EXCEL REPORT - Make Excel Formula automatically add itself when new rows added

This is your formula: =COUNTIF(Sheet1!$A$2:$B$15,Sheet2!B1) Chan ...READ MORE

answered Jan 6 in Others by narikkadan
• 53,160 points
64 views
0 votes
1 answer

Pivoting 4x4 into 16x1

Try this: df <- tibble::tribble( ~ID, ~sit1, ...READ MORE

answered Jan 30 in Others by narikkadan
• 53,160 points
27 views
0 votes
1 answer

Excel: How to merge two columns into one (from different sheets or separated columns)

This equation is completely adjustable. Your two ...READ MORE

answered Oct 7, 2022 in Others by narikkadan
• 53,160 points
147 views
0 votes
1 answer

How to find out how many rows and columns to read from an Excel file with PHPExcel?

Solution: $file_name = htmlentities($_POST['file_name']); $sheet_name = htmlentities($_POST['sheet_name']); $number_of_columns = htmlentities($_POST['number_of_columns']); $number_of_rows ...READ MORE

answered Oct 23, 2022 in Others by narikkadan
• 53,160 points
1,460 views
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP