Extract Newest Case Update and Paste in New Sheet

K

krisalis903

New Member
Joined
Feb 22, 2023
Messages
3
Office Version
  1. 365
Platform
  1. Windows
Hello, I'm still fairly new to using Excel and Access and I have some data that I'm not quite sure how to transform. Essentially what I have here is a data dump of all active Salesforce cases including last modified dates. I need to be able to analyze the latest modified date for all our open cases. I think if I can somehow automatically copy the row with the newest last modified date for each case and paste that into a new sheet, I'd at least have something I can work with. However I am open to ideas. Any thoughts on how to clean this up? The sheet is thousands of lines long, but I've created a mini sheet with just a portion so you can see what I'm working with.

Sample Data.xlsx
ABCDEFG
1Case NumberAgeStatusDate/Time OpenedIRL AGECase Last Modified AliasCase Comment Last Modified Date
2024997625723Pending Agent/Seller Response6/29/2022 2:02 AM238.45vmeht8/7/2022 11:39 PM
3024997625723Pending Agent/Seller Response6/29/2022 2:02 AM238.45vmeht11/9/2022 2:43 PM
4024997625723Pending Agent/Seller Response6/29/2022 2:02 AM238.45vmeht11/10/2022 11:40 PM
5024997625723Pending Agent/Seller Response6/29/2022 2:02 AM238.45vmeht8/4/2022 5:40 PM
6024997625723Pending Agent/Seller Response6/29/2022 2:02 AM238.45vmeht11/4/2022 12:46 AM
7024997625723Pending Agent/Seller Response6/29/2022 2:02 AM238.45vmeht7/15/2022 3:14 AM
8024997625723Pending Agent/Seller Response6/29/2022 2:02 AM238.45vmeht1/19/2023 6:48 PM
9024997625723Pending Agent/Seller Response6/29/2022 2:02 AM238.45vmeht2/8/2023 11:48 PM
10024997625723Pending Agent/Seller Response6/29/2022 2:02 AM238.45vmeht12/13/2022 4:24 PM
11024997625723Pending Agent/Seller Response6/29/2022 2:02 AM238.45vmeht12/13/2022 4:32 PM
12024997625723Pending Agent/Seller Response6/29/2022 2:02 AM238.45vmeht12/26/2022 4:21 AM
13028052572069Pending Agent/Seller Response11/28/2022 6:25 AM86.22vmeht1/27/2023 3:57 PM
14028052572069Pending Agent/Seller Response11/28/2022 6:25 AM86.22vmeht1/28/2023 3:20 AM
15028052572069Pending Agent/Seller Response11/28/2022 6:25 AM86.22vmeht12/9/2022 10:02 PM
16028052572069Pending Agent/Seller Response11/28/2022 6:25 AM86.22vmeht12/9/2022 12:24 PM
17028052572069Pending Agent/Seller Response11/28/2022 6:25 AM86.22vmeht12/17/2022 5:42 PM
18028052572069Pending Agent/Seller Response11/28/2022 6:25 AM86.22vmeht12/20/2022 4:47 AM
19028052572069Pending Agent/Seller Response11/28/2022 6:25 AM86.22vmeht12/26/2022 12:52 AM
20028074482046Pending Agent/Seller Response11/29/2022 6:11 AM85.23vmeht2/16/2023 5:42 PM
21028074482046Pending Agent/Seller Response11/29/2022 6:11 AM85.23vmeht2/17/2023 3:22 AM
22028074482046Pending Agent/Seller Response11/29/2022 6:11 AM85.23vmeht2/20/2023 9:58 PM
23028074482046Pending Agent/Seller Response11/29/2022 6:11 AM85.23vmeht1/25/2023 2:57 PM
24028074482046Pending Agent/Seller Response11/29/2022 6:11 AM85.23vmeht1/12/2023 8:13 PM
25028074482046Pending Agent/Seller Response11/29/2022 6:11 AM85.23vmeht12/9/2022 10:08 PM
26028074482046Pending Agent/Seller Response11/29/2022 6:11 AM85.23vmeht12/9/2022 12:22 PM
27025553414923Pending Agent/Seller Response8/1/2022 9:35 AM205.14mwill2/13/2023 12:12 PM
28025553414923Pending Agent/Seller Response8/1/2022 9:35 AM205.14mwill8/10/2022 4:42 PM
29025553414923Pending Agent/Seller Response8/1/2022 9:35 AM205.14mwill9/2/2022 1:07 PM
30025553414923Pending Agent/Seller Response8/1/2022 9:35 AM205.14mwill9/8/2022 3:42 PM
31025553414923Pending Agent/Seller Response8/1/2022 9:35 AM205.14mwill10/13/2022 12:13 PM
32025553414923Pending Agent/Seller Response8/1/2022 9:35 AM205.14mwill11/10/2022 12:57 PM
33025553414923Pending Agent/Seller Response8/1/2022 9:35 AM205.14mwill8/5/2022 11:15 AM
34025553414923Pending Agent/Seller Response8/1/2022 9:35 AM205.14mwill1/10/2023 1:26 PM
35025553414923Pending Agent/Seller Response8/1/2022 9:35 AM205.14mwill12/14/2022 1:39 PM
36025553414923Pending Agent/Seller Response8/1/2022 9:35 AM205.14mwill12/16/2022 8:02 AM
37025553414923Pending Agent/Seller Response8/1/2022 9:35 AM205.14mwill12/16/2022 1:30 PM
OPEN SEO Content Cases with Las
 

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.
Sort by date Sort by votes
With Power Query, group by case number to find max date/time

Power Query: 
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Grouped Rows" = Table.Group(Source, {"Case Number"}, {{"Latest Update", each List.Max([Case Comment Last Modified Date]), type datetime}})
in
    #"Grouped Rows"

Case NumberLatest Update
24997622/8/2023 23:48
28052571/28/2023 3:20
28074482/20/2023 21:58
25553412/13/2023 12:12
 
Upvote 0
Solution
Wow! That worked perfectly, thank you! I've never dabbled in Power Query before, but now I'm intrigued and will have to learn more. Thank you again :)
 

Attachments

  • Screenshot_4.png
    Screenshot_4.png
    55.2 KB · Views: 6
Upvote 0
check out the books in MrE's bookstore. The one by Ken Puls and Miguel Escobar is really very good.
 
Upvote 1
Found it. I think it's exactly what I need! I've been fumbling around in Power BI for the past 8 months and i've done some learning modules here and there, but I could definitely use more resources. I'm glad this one covers both Power Query in Excel and Power BI. Thanks for the rec!
 
Upvote 0
You must log in or register to reply here.

Similar threads

SaraWitch
  • Solved
Extract individual lines from cell
Replies
6
Views
213
SaraWitch
SaraWitch
H
  • Solved
Search for topics based on their groups.
Replies
7
Views
127
Peter_SSs
Peter_SSs
J
  • Question
Is it possible to make filters expandable / unfoldable with a button?
Replies
2
Views
214
jellevansoelen
J
B
  • Question
countifs and remove data completed
2
Replies
10
Views
361
MuchToLearn
M
C
  • Solved
Dynamic Array For Dates Using SEQUENCE()
Replies
4
Views
284
Cubist
C

Forum statistics

Threads
1,214,392
Messages
6,119,255
Members
448,879
Latest member
oksanana

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