VBA to extract latest entries of multiple unique rows

Tashat

Board Regular
Joined
Jan 12, 2005
Messages
65
Office Version
  1. 365
Platform
  1. Windows
Hi – I think I need some VBA code to do what I’m trying to achieve…if anyone can help, I’d be very grateful. I’ll try to explain…

I have a spreadsheet with thousands of rows and I need to extract a “clean” sheet with the latest version of each row, so I am only analysing the most recent data.

I have the following columns headers:

A Date and Time Stamp (mm/dd/yyyy hh/mm/ss)

B Football team name

C District council

D:Z (other relevant data for each row)

There are multiple entries for each combination of football team name and district council (plus the other relevant data in D2:Z2) each row entry is date and time stamped. I need to extract only the latest rows for each unique combination of football team/district council. However, I cannot define all the possible combinations in advance – there will be hundreds if not thousands. So…this is my thought processes:

I have tried concatenating B2 and C2, then doing an advanced filter to give me the unique entries. This does work and could possibly work as a key / unique lookup list. But then I need to run something (perhaps a MAX lookup formula?) to find the latest entry based on date and timestamp of that unique combination of football team name and district council and give the full row of data for each of those instances, in a fresh sheet.

Can anyone help me with this? I am not experienced with writing VBA code, but I think VBA is probably the way to go.
 

Some videos you may like

Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.

Tashat

Board Regular
Joined
Jan 12, 2005
Messages
65
Office Version
  1. 365
Platform
  1. Windows
Is anyone able to help with this?
 

mumps

Well-known Member
Joined
Apr 11, 2012
Messages
10,238
It would be easier to help if you could use the XL2BB add-in (icon in the menu) to attach a screenshot (not a picture) of your sheet. Alternately, you could upload a copy of your file to a free site such as www.box.com or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. Explain in detail what you want to do referring to specific cells, rows, columns and sheets using a few examples from your data (de-sensitized if necessary).
 

Tashat

Board Regular
Joined
Jan 12, 2005
Messages
65
Office Version
  1. 365
Platform
  1. Windows
It would be easier to help if you could use the XL2BB add-in (icon in the menu) to attach a screenshot (not a picture) of your sheet. Alternately, you could upload a copy of your file to a free site such as www.box.com or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. Explain in detail what you want to do referring to specific cells, rows, columns and sheets using a few examples from your data (de-sensitized if necessary).
Thank you. I didn't realise. I've changed the data to fruit and trees (all nonsense), but the principle is the same. Imagine 1000s of rows though.

Based on the table below. I want to receive an extract sheet which returns the latest/most recent record for each unique combination of fruit and tree concatonated.

E.g. appleoak, orangeacorn, plumacer... some of these combinations appear more than once in the list, but I only want to extra the row of data for the latest record.

e.g. appleoak appears twice in the list. But I want my extracted sheet to only "pull" the one on the 16/10/2020 09:47:19 because that's the most recent data. There will be thousands of rows and I can't predefine all the unique combinations of fruit and tree. So hard to explain.

Example Data.xlsx
ABCDEF
1FruitTreeAgeRatingFruitness
215/10/2020 18:57:31appleoak11-201high
315/10/2020 19:33:22peacheucalyptus11-203very high
415/10/2020 19:34:18orangeacorn0-54low
515/10/2020 20:53:31plumacer100-2001
615/10/2020 20:53:32cherrybeech0-51
715/10/2020 08:11:12bananabeechover 503low
816/10/2020 08:13:22cherrybeechover 2005moderate
916/10/2020 08:33:32orangeaspenover 2002low
1016/10/2020 09:41:32orangeash100-2001
1116/10/2020 09:43:29applefir100-2005very high
1216/10/2020 09:47:18cherryash51-604low
1316/10/2020 09:47:19appleoak0-51
1416/10/2020 10:13:22orangebeech11-202
1516/10/2020 10:33:32bananafir0-51low
1616/10/2020 14:41:32plumacerover 200N/Avery high
1716/10/2020 14:43:29bananaaspen51-603
1816/10/2020 15:47:18cherrybirch20-302low
1916/10/2020 15:47:19applefir51-604
2018/10/2020 08:13:22orangebeech51-601low
2118/10/2020 08:33:32plumaspen51-601low
2218/10/2020 09:41:32cherrybirch11-204very high
2318/10/2020 09:43:29orangefir0-53
2418/10/2020 09:47:18bananaacer11-204high
2518/10/2020 09:47:19bananaacer51-604
2618/10/2020 10:13:22appleaspen100-200N/Avery high
2718/10/2020 10:33:32orangefir11-20N/Ahigh
2818/10/2020 14:41:32plumacer11-204
2918/10/2020 14:43:29applebirch100-2003very high
3018/10/2020 15:47:18orangeash100-2001high
3118/10/2020 15:47:19plumbeech51-605high
Sheet1
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,432
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

What version of Excel are you using?
Please update your account details to show this, as it affects which functions you can use.
 

Tashat

Board Regular
Joined
Jan 12, 2005
Messages
65
Office Version
  1. 365
Platform
  1. Windows
What version of Excel are you using?
Please update your account details to show this, as it affects which functions you can use.
Apologies- it's been a while. I'm on Microsoft Office ProPlus on Windows. I have updated my profile. Thank you.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,432
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Thanks for that, as you have 365, how about
+Fluff v2.xlsm
ABCDEFGHIJKLMNO
1FruitTreeAgeRatingFruitnessFruitTreeAgeRatingFruitness
215/10/2020 18:57appleoakNov-201high15/10/2020 19:33peacheucalyptus441363very high
315/10/2020 19:33peacheucalyptusNov-203very high15/10/2020 19:34orangeacorn0-54low
415/10/2020 19:34orangeacorn0-54low15/10/2020 08:11bananabeechover 503low
515/10/2020 20:53plumacer100-200116/10/2020 08:13cherrybeechover 2005moderate
615/10/2020 20:53cherrybeech0-5116/10/2020 08:33orangeaspenover 2002low
715/10/2020 08:11bananabeechover 503low16/10/2020 09:47cherryash51-604low
816/10/2020 08:13cherrybeechover 2005moderate16/10/2020 09:47appleoak0-510
916/10/2020 08:33orangeaspenover 2002low16/10/2020 10:33bananafir0-51low
1016/10/2020 09:41orangeash100-200116/10/2020 14:43bananaaspen51-6030
1116/10/2020 09:43applefir100-2005very high16/10/2020 15:47applefir51-6040
1216/10/2020 09:47cherryash51-604low18/10/2020 08:13orangebeech51-601low
1316/10/2020 09:47appleoak0-5118/10/2020 08:33plumaspen51-601low
1416/10/2020 10:13orangebeechNov-20218/10/2020 09:41cherrybirch441364very high
1516/10/2020 10:33bananafir0-51low18/10/2020 09:47bananaacer51-6040
1616/10/2020 14:41plumacerover 200N/Avery high18/10/2020 10:13appleaspen100-200N/Avery high
1716/10/2020 14:43bananaaspen51-60318/10/2020 10:33orangefir44136N/Ahigh
1816/10/2020 15:47cherrybirch20-302low18/10/2020 14:41plumacer4413640
1916/10/2020 15:47applefir51-60418/10/2020 14:43applebirch100-2003very high
2018/10/2020 08:13orangebeech51-601low18/10/2020 15:47orangeash100-2001high
2118/10/2020 08:33plumaspen51-601low18/10/2020 15:47plumbeech51-605high
2218/10/2020 09:41cherrybirchNov-204very high
2318/10/2020 09:43orangefir0-53
2418/10/2020 09:47bananaacerNov-204high
2518/10/2020 09:47bananaacer51-604
2618/10/2020 10:13appleaspen100-200N/Avery high
2718/10/2020 10:33orangefirNov-20N/Ahigh
2818/10/2020 14:41plumacerNov-204
2918/10/2020 14:43applebirch100-2003very high
3018/10/2020 15:47orangeash100-2001high
3118/10/2020 15:47plumbeech51-605high
32
Main
Cell Formulas
RangeFormula
J2:O21J2=FILTER(A2:F31,A2:A31=MAXIFS(A2:A31,B2:B31,B2:B31,C2:C31,C2:C31))
Dynamic array formulas.
 

Tashat

Board Regular
Joined
Jan 12, 2005
Messages
65
Office Version
  1. 365
Platform
  1. Windows
Thanks for that, as you have 365, how about
+Fluff v2.xlsm
ABCDEFGHIJKLMNO
1FruitTreeAgeRatingFruitnessFruitTreeAgeRatingFruitness
215/10/2020 18:57appleoakNov-201high15/10/2020 19:33peacheucalyptus441363very high
315/10/2020 19:33peacheucalyptusNov-203very high15/10/2020 19:34orangeacorn0-54low
415/10/2020 19:34orangeacorn0-54low15/10/2020 08:11bananabeechover 503low
515/10/2020 20:53plumacer100-200116/10/2020 08:13cherrybeechover 2005moderate
615/10/2020 20:53cherrybeech0-5116/10/2020 08:33orangeaspenover 2002low
715/10/2020 08:11bananabeechover 503low16/10/2020 09:47cherryash51-604low
816/10/2020 08:13cherrybeechover 2005moderate16/10/2020 09:47appleoak0-510
916/10/2020 08:33orangeaspenover 2002low16/10/2020 10:33bananafir0-51low
1016/10/2020 09:41orangeash100-200116/10/2020 14:43bananaaspen51-6030
1116/10/2020 09:43applefir100-2005very high16/10/2020 15:47applefir51-6040
1216/10/2020 09:47cherryash51-604low18/10/2020 08:13orangebeech51-601low
1316/10/2020 09:47appleoak0-5118/10/2020 08:33plumaspen51-601low
1416/10/2020 10:13orangebeechNov-20218/10/2020 09:41cherrybirch441364very high
1516/10/2020 10:33bananafir0-51low18/10/2020 09:47bananaacer51-6040
1616/10/2020 14:41plumacerover 200N/Avery high18/10/2020 10:13appleaspen100-200N/Avery high
1716/10/2020 14:43bananaaspen51-60318/10/2020 10:33orangefir44136N/Ahigh
1816/10/2020 15:47cherrybirch20-302low18/10/2020 14:41plumacer4413640
1916/10/2020 15:47applefir51-60418/10/2020 14:43applebirch100-2003very high
2018/10/2020 08:13orangebeech51-601low18/10/2020 15:47orangeash100-2001high
2118/10/2020 08:33plumaspen51-601low18/10/2020 15:47plumbeech51-605high
2218/10/2020 09:41cherrybirchNov-204very high
2318/10/2020 09:43orangefir0-53
2418/10/2020 09:47bananaacerNov-204high
2518/10/2020 09:47bananaacer51-604
2618/10/2020 10:13appleaspen100-200N/Avery high
2718/10/2020 10:33orangefirNov-20N/Ahigh
2818/10/2020 14:41plumacerNov-204
2918/10/2020 14:43applebirch100-2003very high
3018/10/2020 15:47orangeash100-2001high
3118/10/2020 15:47plumbeech51-605high
32
Main
Cell Formulas
RangeFormula
J2:O21J2=FILTER(A2:F31,A2:A31=MAXIFS(A2:A31,B2:B31,B2:B31,C2:C31,C2:C31))
Dynamic array formulas.
Oh my goodness! That is amazing. Thank you so much. I've just changed careers and I'm back working with spreadsheets after an 8 year break doing other things. They've moved on a lot!
 

Watch MrExcel Video

Forum statistics

Threads
1,127,387
Messages
5,624,389
Members
416,026
Latest member
melvic69

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
Top