Merging Duplicate Rows and time entries

H

Heeby24

New Member
Joined
Sep 14, 2023
Messages
6
Office Version
  1. 365
Platform
  1. Windows
Is there a formula that I can make use of that would do the following:

1. find duplicate rows (date/activity/time) and merge them into a single row and add the time value of all rows. (i.e. 5 rows with the same content, each valued at 0.1. Merge to 1 row with a value of 0.5); or
2. Find row with 'Email sent to John Smith on January 1, 2024' and if there is another row with 'Email from John Smith on January 1, 2024' then merge row to read 'Emails exchanged with John Smith on January 1, 2024'

For example, take these entries:

Original Data.png



And then turn it into this:
What I want.png



Any guidance would be appreciated!

Many Thanks!
 

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off
Sort by date Sort by votes
resolved with Power Query
Power Query:
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Grouped Rows" = Table.Group(Source, {"Date", "Activity"}, {{"total time", each List.Sum([Time]), type number}})
in
#"Grouped Rows"

Book1
ABCDEFG
1DateActivityTimeDateActivitytotal time
229/05/202424-0195: Email sent to Jane Smith;0.129/05/202424-0195: Email sent to Jane Smith;0.2
329/05/202424-0195: Email sent to Jane Smith;0.129/05/202423-0165: Email sent to Jane Smith;0.2
429/05/202423-0165: Email sent to Jane Smith;0.129/05/202422-0166: Email sent to Nathan Jones;0.1
529/05/202423-0165: Email sent to Jane Smith;0.129/05/202423-9658: Email sent to Paul Brown;0.1
629/05/202422-0166: Email sent to Nathan Jones;0.129/05/202424-0658: File Assignment; Review of file materials, including correspondence;0.2
729/05/202423-9658: Email sent to Paul Brown;0.129/05/202423-0166: Email from Christine Travers;0.1
829/05/202424-0658: File Assignment; Review of file materials, including correspondence;0.229/05/202423-0166: Email sent to John Hill;0.1
929/05/202423-0166: Email from Christine Travers;0.129/05/202423-1278.- Email sent to Jane Smith;0.1
1029/05/202423-0166: Email sent to John Hill;0.129/05/202423-1278: Email sent to Jane Smith;0.1
1129/05/202423-1278.- Email sent to Jane Smith;0.129/05/202423-1278.• Email sent to John Hill;0.1
1229/05/202423-1278: Email sent to Jane Smith;0.129/05/202424-0186: Email from Christine Travers;0.1
1329/05/202423-1278.• Email sent to John Hill;0.129/05/202424-0186: Email from John Hill;0.2
1429/05/202424-0186: Email from Christine Travers;0.129/05/202424-0186: Email sent to Christine Travers;0.1
1529/05/202424-0186: Email from John Hill;0.129/05/202424-0186: Email sent to John Hill;0.1
1629/05/202424-0186: Email from John Hill;0.129/05/202424-0333: Email from Audrey Carcmichael;0.1
1729/05/202424-0186: Email sent to Christine Travers;0.129/05/202424-0333: Email from Joe McDonald;0.1
1829/05/202424-0186: Email sent to John Hill;0.129/05/202424-0333: Email to Vito Marques;0.1
1929/05/202424-0333: Email from Audrey Carcmichael;0.129/05/202424-0333: Email from Vito Marques;0.1
2029/05/202424-0333: Email from Joe McDonald;0.1
2129/05/202424-0333: Email to Vito Marques;0.1
2229/05/202424-0333: Email from Vito Marques;0.1
Sheet1
 
Upvote 0
Formula option.
Book1
ABCDEFG
1DateActivityTimeDateActivityTime
229/05/202424-0195: Email sent to Jane Smith;0.129/05/202424-0195: Email sent to Jane Smith;0.2
329/05/202424-0195: Email sent to Jane Smith;0.129/05/202423-0165: Email sent to Jane Smith;0.2
429/05/202423-0165: Email sent to Jane Smith;0.129/05/202422-0166: Email sent to Nathan Jones;0.1
529/05/202423-0165: Email sent to Jane Smith;0.129/05/202423-9658: Email sent to Paul Brown;0.1
629/05/202422-0166: Email sent to Nathan Jones;0.129/05/202424-0658: File Assignment; Review of file materials, including correspondence;0.2
729/05/202423-9658: Email sent to Paul Brown;0.129/05/202423-0166: Email from Christine Travers;0.1
829/05/202424-0658: File Assignment; Review of file materials, including correspondence;0.229/05/202423-0166: Email sent to John Hill;0.1
929/05/202423-0166: Email from Christine Travers;0.129/05/202423-1278.- Email sent to Jane Smith;0.1
1029/05/202423-0166: Email sent to John Hill;0.129/05/202423-1278: Email sent to Jane Smith;0.1
1129/05/202423-1278.- Email sent to Jane Smith;0.129/05/202423-1278.• Email sent to John Hill;0.1
1229/05/202423-1278: Email sent to Jane Smith;0.129/05/202424-0186: Email from Christine Travers;0.1
1329/05/202423-1278.• Email sent to John Hill;0.129/05/202424-0186: Email from John Hill;0.2
1429/05/202424-0186: Email from Christine Travers;0.129/05/202424-0186: Email sent to Christine Travers;0.1
1529/05/202424-0186: Email from John Hill;0.129/05/202424-0186: Email sent to John Hill;0.1
1629/05/202424-0186: Email from John Hill;0.129/05/202424-0333: Email from Audrey Carcmichael;0.1
1729/05/202424-0186: Email sent to Christine Travers;0.129/05/202424-0333: Email from Joe McDonald;0.1
1829/05/202424-0186: Email sent to John Hill;0.129/05/202424-0333: Email to Vito Marques;0.1
1929/05/202424-0333: Email from Audrey Carcmichael;0.129/05/202424-0333: Email from Vito Marques;0.1
2029/05/202424-0333: Email from Joe McDonald;0.1
2129/05/202424-0333: Email to Vito Marques;0.1
2229/05/202424-0333: Email from Vito Marques;0.1
Sheet3
Cell Formulas
RangeFormula
E2:G19E2=LET(u,UNIQUE(A2:B22),HSTACK(u,BYROW(u,LAMBDA(b,SUMIFS(C2:C22,A2:A22,INDEX(b,1),B2:B22,INDEX(b,2))))))
Dynamic array formulas.
 
Upvote 0
Solution
another formula option:
工作簿1
ABCDEFGH
1DateActivityTimeDateActivityTime
229/05/202424-0195: Email sent to Jane Smith;0.129/05/202422-0166: Email sent to Nathan Jones;0.1
329/05/202424-0195: Email sent to Jane Smith;0.129/05/202423-0165: Email sent to Jane Smith;0.2
429/05/202423-0165: Email sent to Jane Smith;0.129/05/202423-0166: Email from Christine Travers;0.1
529/05/202423-0165: Email sent to Jane Smith;0.129/05/202423-0166: Email sent to John Hill;0.1
629/05/202422-0166: Email sent to Nathan Jones;0.129/05/202423-1278.- Email sent to Jane Smith;0.1
729/05/202423-9658: Email sent to Paul Brown;0.129/05/202423-1278.• Email sent to John Hill;0.1
829/05/202424-0658: File Assignment; Review of file materials, including correspondence;0.229/05/202423-1278: Email sent to Jane Smith;0.1
929/05/202423-0166: Email from Christine Travers;0.129/05/202423-9658: Email sent to Paul Brown;0.1
1029/05/202423-0166: Email sent to John Hill;0.129/05/202424-0186: Email from Christine Travers;0.1
1129/05/202423-1278.- Email sent to Jane Smith;0.129/05/202424-0186: Email from John Hill;0.2
1229/05/202423-1278: Email sent to Jane Smith;0.129/05/202424-0186: Email sent to Christine Travers;0.1
1329/05/202423-1278.• Email sent to John Hill;0.129/05/202424-0186: Email sent to John Hill;0.1
1429/05/202424-0186: Email from Christine Travers;0.129/05/202424-0195: Email sent to Jane Smith;0.2
1529/05/202424-0186: Email from John Hill;0.129/05/202424-0333: Email from Audrey Carcmichael;0.1
1629/05/202424-0186: Email from John Hill;0.129/05/202424-0333: Email from Joe McDonald;0.1
1729/05/202424-0186: Email sent to Christine Travers;0.129/05/202424-0333: Email from Vito Marques;0.1
1829/05/202424-0186: Email sent to John Hill;0.129/05/202424-0333: Email to Vito Marques;0.1
1929/05/202424-0333: Email from Audrey Carcmichael;0.129/05/202424-0658: File Assignment; Review of file materials, including correspondence;0.2
2029/05/202424-0333: Email from Joe McDonald;0.1
2129/05/202424-0333: Email to Vito Marques;0.1
2229/05/202424-0333: Email from Vito Marques;0.1
Sheet1
Cell Formulas
RangeFormula
E1:G19E1=GROUPBY(A1:B22,C1:C22,SUM,3,0)
Dynamic array formulas.
 
Upvote 0
You must log in or register to reply here.

Similar threads

K
  • Solved
Merging duplicate values
Replies
1
Views
188
DanteAmor
DanteAmor
S
  • Solved
Comparing time stamp values to remove 'duplicate' time entries (rows).
Replies
7
Views
331
felixstraube
F
T
  • Solved
Looking for VBA Code to Conditionally Format Time Tracker on Entry
Replies
3
Views
199
Tara_A_Data
T
M
  • Question Question
Macro or Power Automate to Identify and Move "Washes" from the "Master" Sheet to the "Washes" Sheet
Replies
0
Views
192
Madam_Xavier
M
G
  • Question Question
Cannot create a new time-in entry
Replies
2
Views
185
genesix
G

Forum statistics

Threads
1,224,817
Messages
6,181,149
Members
453,021
Latest member
Justyna P

Share this page

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