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

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.
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
183
DanteAmor
DanteAmor
S
  • Solved
Comparing time stamp values to remove 'duplicate' time entries (rows).
Replies
7
Views
315
felixstraube
F
T
  • Solved
Looking for VBA Code to Conditionally Format Time Tracker on Entry
Replies
3
Views
192
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
182
Madam_Xavier
M
G
  • Question Question
Cannot create a new time-in entry
Replies
2
Views
183
genesix
G

Forum statistics

Threads
1,223,886
Messages
6,175,198
Members
452,616
Latest member
intern444

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