Summarize data from five different columns and maintain column labeling and format

[annieflanz]

Hello,

I have set of data of time spent on various activities, tasks, and projects for a set of clients over a period of time.

I'm looking to summarize that data to determine how much time was spent each day on each combination of the activity, task, project, and client. I tried a pivot table, but cannot figure out how to get the results in the same column format. The column format is necessary to upload into a specific time tracking/billing program. I'm thinking of something that would remove duplicate entries of the same date, client, project, task, and activity, but that also summarizes the time at the same time.

The example attached here has 50 rows of data, whereas the spreadsheet I am working with has 500,000+ rows of data, so anything too manual wouldn't be effective.

Any way to accomplish this goal? I appreciate any help or suggestions! Thank you!

Here's an example of the data:

Example.xlsx
	A	B	C	D	E	F
1	Date	Client	Project	Task	Activity	Hours
2	2022-01-01	Client A	Project X	Task 2	Activity 2	8
3	2022-01-01	Client A	Project Y	Task 3	Activity 3	2
4	2022-01-01	Client A	Project Z	Task 2	Activity 1	7
5	2022-01-01	Client A	Project Z	Task 4	Activity 3	9
6	2022-01-01	Client B	Project X	Task 4	Activity 2	8
7	2022-01-01	Client B	Project X	Task 4	Activity 3	3
8	2022-01-01	Client B	Project Y	Task 2	Activity 3	10
9	2022-01-01	Client B	Project Z	Task 5	Activity 3	2
10	2022-01-02	Client A	Project X	Task 4	Activity 3	8
11	2022-01-02	Client A	Project Y	Task 4	Activity 1	7
12	2022-01-02	Client A	Project Y	Task 4	Activity 1	4
13	2022-01-02	Client A	Project Z	Task 2	Activity 3	4
14	2022-01-02	Client A	Project Z	Task 5	Activity 3	2
15	2022-01-02	Client B	Project Y	Task 5	Activity 3	6
16	2022-01-02	Client C	Project X	Task 4	Activity 1	4
17	2022-01-02	Client C	Project X	Task 5	Activity 3	1
18	2022-01-02	Client C	Project Z	Task 1	Activity 3	9
19	2022-01-02	Client C	Project Z	Task 4	Activity 1	6
20	2022-01-02	Client D	Project X	Task 3	Activity 1	8
21	2022-01-02	Client D	Project Y	Task 5	Activity 3	1
22	2022-01-02	Client D	Project Y	Task 5	Activity 3	8
23	2022-01-03	Client A	Project X	Task 1	Activity 1	8
24	2022-01-03	Client A	Project X	Task 5	Activity 2	3
25	2022-01-03	Client A	Project Y	Task 4	Activity 3	7
26	2022-01-03	Client C	Project X	Task 5	Activity 2	3
27	2022-01-03	Client C	Project Y	Task 2	Activity 1	10
28	2022-01-03	Client D	Project Z	Task 2	Activity 1	1
29	2022-01-04	Client A	Project X	Task 2	Activity 2	1
30	2022-01-04	Client A	Project X	Task 2	Activity 2	6
31	2022-01-04	Client A	Project Y	Task 4	Activity 3	2
32	2022-01-04	Client A	Project Y	Task 5	Activity 1	7
33	2022-01-04	Client A	Project Z	Task 4	Activity 1	6
34	2022-01-04	Client B	Project Y	Task 1	Activity 2	2
35	2022-01-04	Client C	Project Y	Task 4	Activity 2	3
36	2022-01-04	Client C	Project Z	Task 4	Activity 2	3
37	2022-01-04	Client D	Project Z	Task 2	Activity 3	6
38	2022-01-05	Client A	Project Y	Task 2	Activity 1	10
39	2022-01-05	Client A	Project Z	Task 2	Activity 1	5
40	2022-01-05	Client B	Project X	Task 1	Activity 2	4
41	2022-01-05	Client B	Project X	Task 2	Activity 2	2
42	2022-01-05	Client B	Project Y	Task 1	Activity 1	9
43	2022-01-05	Client B	Project Y	Task 5	Activity 2	2
44	2022-01-05	Client B	Project Z	Task 4	Activity 2	9
45	2022-01-05	Client C	Project Y	Task 1	Activity 1	4
46	2022-01-05	Client C	Project Z	Task 4	Activity 2	7
47	2022-01-05	Client D	Project X	Task 1	Activity 3	7
48	2022-01-05	Client D	Project Y	Task 1	Activity 2	3
49	2022-01-05	Client D	Project Y	Task 5	Activity 3	7
50	2022-01-05	Client D	Project Z	Task 3	Activity 2	5
51	2022-01-05	Client D	Project Z	Task 4	Activity 2	8
Desired Results

Here is an example of the results I am looking for:

Example.xlsx
	A	B	C	D	E	F
1	Date	Client	Project	Task	Activity	Total Hours For Day
2	2022-01-01	Client A	Project X	Task 2	Activity 2	<TOTAL HOURS FOR DAY>
3	2022-01-01	Client A	Project Y	Task 3	Activity 3	<TOTAL HOURS FOR DAY>
4	2022-01-01	Client A	Project Z	Task 2	Activity 1	<TOTAL HOURS FOR DAY>
5	2022-01-01	Client A	Project Z	Task 4	Activity 3	<TOTAL HOURS FOR DAY>
6	2022-01-01	Client B	Project X	Task 4	Activity 2	<TOTAL HOURS FOR DAY>
7	2022-01-01	Client B	Project X	Task 4	Activity 3	<TOTAL HOURS FOR DAY>
8	2022-01-01	Client B	Project Y	Task 2	Activity 3	<TOTAL HOURS FOR DAY>
9	2022-01-01	Client B	Project Z	Task 5	Activity 3	<TOTAL HOURS FOR DAY>
10	2022-01-02	Client A	Project X	Task 4	Activity 3	<TOTAL HOURS FOR DAY>
11	2022-01-02	Client A	Project Y	Task 4	Activity 1	<TOTAL HOURS FOR DAY>
12	2022-01-02	Client A	Project Z	Task 2	Activity 3	<TOTAL HOURS FOR DAY>
13	2022-01-02	Client A	Project Z	Task 5	Activity 3	<TOTAL HOURS FOR DAY>
14	2022-01-02	Client B	Project Y	Task 5	Activity 3	<TOTAL HOURS FOR DAY>
15	2022-01-02	Client C	Project X	Task 4	Activity 1	<TOTAL HOURS FOR DAY>
16	2022-01-02	Client C	Project X	Task 5	Activity 3	<TOTAL HOURS FOR DAY>
17	2022-01-02	Client C	Project Z	Task 1	Activity 3	<TOTAL HOURS FOR DAY>
18	2022-01-02	Client C	Project Z	Task 4	Activity 1	<TOTAL HOURS FOR DAY>
19	2022-01-02	Client D	Project X	Task 3	Activity 1	<TOTAL HOURS FOR DAY>
20	2022-01-02	Client D	Project Y	Task 5	Activity 3	<TOTAL HOURS FOR DAY>
21	2022-01-03	Client A	Project X	Task 1	Activity 1	<TOTAL HOURS FOR DAY>
22	2022-01-03	Client A	Project X	Task 5	Activity 2	<TOTAL HOURS FOR DAY>
23	2022-01-03	Client A	Project Y	Task 4	Activity 3	<TOTAL HOURS FOR DAY>
24	2022-01-03	Client C	Project X	Task 5	Activity 2	<TOTAL HOURS FOR DAY>
25	2022-01-03	Client C	Project Y	Task 2	Activity 1	<TOTAL HOURS FOR DAY>
26	2022-01-03	Client D	Project Z	Task 2	Activity 1	<TOTAL HOURS FOR DAY>
27	2022-01-04	Client A	Project X	Task 2	Activity 2	<TOTAL HOURS FOR DAY>
28	2022-01-04	Client A	Project Y	Task 4	Activity 3	<TOTAL HOURS FOR DAY>
29	2022-01-04	Client A	Project Y	Task 5	Activity 1	<TOTAL HOURS FOR DAY>
30	2022-01-04	Client A	Project Z	Task 4	Activity 1	<TOTAL HOURS FOR DAY>
31	2022-01-04	Client B	Project Y	Task 1	Activity 2	<TOTAL HOURS FOR DAY>
32	2022-01-04	Client C	Project Y	Task 4	Activity 2	<TOTAL HOURS FOR DAY>
33	2022-01-04	Client C	Project Z	Task 4	Activity 2	<TOTAL HOURS FOR DAY>
34	2022-01-04	Client D	Project Z	Task 2	Activity 3	<TOTAL HOURS FOR DAY>
35	2022-01-05	Client A	Project Y	Task 2	Activity 1	<TOTAL HOURS FOR DAY>
36	2022-01-05	Client A	Project Z	Task 2	Activity 1	<TOTAL HOURS FOR DAY>
37	2022-01-05	Client B	Project X	Task 1	Activity 2	<TOTAL HOURS FOR DAY>
38	2022-01-05	Client B	Project X	Task 2	Activity 2	<TOTAL HOURS FOR DAY>
39	2022-01-05	Client B	Project Y	Task 1	Activity 1	<TOTAL HOURS FOR DAY>
40	2022-01-05	Client B	Project Y	Task 5	Activity 2	<TOTAL HOURS FOR DAY>
41	2022-01-05	Client B	Project Z	Task 4	Activity 2	<TOTAL HOURS FOR DAY>
42	2022-01-05	Client C	Project Y	Task 1	Activity 1	<TOTAL HOURS FOR DAY>
43	2022-01-05	Client C	Project Z	Task 4	Activity 2	<TOTAL HOURS FOR DAY>
44	2022-01-05	Client D	Project X	Task 1	Activity 3	<TOTAL HOURS FOR DAY>
45	2022-01-05	Client D	Project Y	Task 1	Activity 2	<TOTAL HOURS FOR DAY>
46	2022-01-05	Client D	Project Y	Task 5	Activity 3	<TOTAL HOURS FOR DAY>
47	2022-01-05	Client D	Project Z	Task 3	Activity 2	<TOTAL HOURS FOR DAY>
48	2022-01-05	Client D	Project Z	Task 4	Activity 2	<TOTAL HOURS FOR DAY>
Data

 

[bebo021999]

What wold you like, overwrite column F, or extract into other sheet?

 

[Alex Blakenburg]

Have you considered using Power Query ?

 

[Peter_SSs]

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

With your real data, do you have any sort of idea about how many rows might be in the results?

BTW, have you accidentally put the wrong sheet names in your post 1 samples of data and results?

 

[annieflanz]

Extract into another sheet, please!

What wold you like, overwrite column F, or extract into other sheet?

 

[annieflanz]

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

With your real data, do you have any sort of idea about how many rows might be in the results?

BTW, have you accidentally put the wrong sheet names in your post 1 samples of data and results?

Thanks for the tip! Account details updated. I'm on 365 and on Mac.

My real data has 500,000+ rows of data.

I'm unsure how to answer your third question. I believe the sheet names are correct.

 

[annieflanz]

I hadn't! This is exactly what I'm looking for. I'm unfamiliar with Power Query, but can learn! Any tips would be helpful. thank you!

Have you considered using Power Query ?

View attachment 70029

ve no

 

[Peter_SSs]

Thanks for updating your details.
In this case your platform was important as the way I was considering doing this does not work on a Mac.

My real data has 500,000+ rows of data.

Yes, you had already stated that but you are wanting to summarize that data so I am assuming there will be less rows in the results, and that is what my question was about.

I'm unsure how to answer your third question.

You showed 2 Mini-sheets. The first had 51 rows and was prefaced by "Here's an example of the data:" but had a tab name as follows:

Your second sheet only had 48 rows and was prefaced by "Here is an example of the results I am looking for:" but had a tab name as follows:

Seemed like the tab names were the wrong way around to me.

 

[alansidman]

Power Query is a free AddIn for Excel 2010 and 2013, and is built-in functionality from Excel 2016 onwards (where it is referred to as "Get & Transform Data").

It is a powerful yet simple way of getting, changing and using data from a broad variety of sources, creating steps which may be easily repeated and refreshed. I strongly recommend learning how to use Power Query - it's among the most powerful functionalities of Excel.

- Follow this link to learn how to install Power Query in Excel 2010 / 2013.

- Follow this link for an introduction to Power Query functionality.

- Follow this link for a video which demonstrates how to use Power Query code provided.

 

[Alex Blakenburg]

I hadn't! This is exactly what I'm looking for. I'm unfamiliar with Power Query, but can learn! Any tips would be helpful. thank you!

Oh oh Mac OS.
It was released on the beta channel in May 2022. Can you click on the Data Tab and tell me if you see something that looks like the below:

If you have the below then take a quick look at the video at Excel Campus here at the 4:30 min mark as an intro and then @alansidman or I can walk you through the steps you need to summarise the data.