Pulling the latest data for a specific data

RMJ

New Member
Joined
Jul 28, 2020
Messages
10
Office Version
  1. 2010
Hello,

I'm working with 2 spreadsheets and trying to pull the latest entry for a specific data group. I can successfully pull the last row of data from the 1st spreadsheet into the 2nd spreadsheet using the LOOKUP formula; but having an issue with pulling the first 2 lines of data that are from the latest hour. Please see example below:
I'm unable to pull data from rows 5 & 6. I have tried Vlookup, Index & Match, Offset and a couple of more scenarios; but don't get the data to transfer over
On line 7, I use the LOOKUP formula that looks for the last data entry for the day.; but not having luck figuring out how to bring the data over from lines 5 &6 (or reference the other 2 workgroups in the scenario

Please let me know if you have any questions.

Thank you,
RMJ

DateNameTime AZEST TimePen:Int# of AccountsCalls ConnectedAgents logged inAban OB Count/RateOffered CallsAban IB Count/Rate
03/30/21Corres9:5412:54:00 PM94%172%50040(5.01%)144(10%)70(0.00%)
03/30/21Corres X29:5412:54:00 PM99%146%46625(7.74%)142(8.0%)N/AN/A
03/30/21Corres x79:5412:54:00 PM97%190%34232(4.22%)144 (4.12%)N/AN/A
03/30/21Corres12:003:00:00 PM95%211%50046(4.68%)185(10.86%)142(16.31%)
03/30/21Corres X212:003:00:00 PM99%148%46625(7.65%)182 (8%)N/AN/A
03/30/21Corres x712:003:00:00 PM98%222%34239(4.39%)184 (10.25%)N/AN/A
 

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.

Toadstool

Well-known Member
Joined
Mar 5, 2018
Messages
1,919
Office Version
  1. 2016
Platform
  1. Windows
Hi RMJ,

As long as the data is in time within date sequence then this would work:

RMJ2.xlsx
ABCDEFGHIJKLMNO
1DateNameTime AZEST TimePen:Int# of AccountsCalls ConnectedAgents logged inAban OB Count/RateOffered CallsAban IB Count/RateDateName on Last 3
23/30/2021Corres9:5412:54:00 PM94%172%50040(5.01%)144(10%)70(0.00%)3/30/2021Corres
33/30/2021Corres X29:5412:54:00 PM99%146%46625(7.74%)142(8.0%)N/AN/ACorres X2
43/30/2021Corres x79:5412:54:00 PM97%190%34232(4.22%)144 (4.12%)N/AN/ACorres x7
53/30/2021Corres12:003:00:00 PM95%211%50046(4.68%)185(10.86%)142(16.31%)
63/30/2021Corres X212:003:00:00 PM99%148%46625(7.65%)182 (8%)N/AN/A
73/30/2021Corres x712:003:00:00 PM98%222%34239(4.39%)184 (10.25%)N/AN/A
8
Sheet1
Cell Formulas
RangeFormula
O2:O4O2=INDEX($B$2:$B$9999,AGGREGATE(14,6,ROW($A$2:$A$9999)-ROW($A$1)/($A$2:$A$9999=$N$2),ROW($O$1)+4-ROW()))
 

RMJ

New Member
Joined
Jul 28, 2020
Messages
10
Office Version
  1. 2010
Not sure what I'm doing wrong; but I get a #NUM message, instead of having the data transfer from the 1st spreadsheet to the 2nd spreadsheet.
 

Toadstool

Well-known Member
Joined
Mar 5, 2018
Messages
1,919
Office Version
  1. 2016
Platform
  1. Windows
It will give #NUM if no match is found. Here, I've put it on a new sheet but make sure you name them Sheet1 and Sheet2

RMJ2.xlsx
ABCDEFGHIJKL
1DateNameTime AZEST TimePen:Int# of AccountsCalls ConnectedAgents logged inAban OB Count/RateOffered CallsAban IB Count/Rate
23/30/2021Corres9:5412:54:00 PM94%172%50040(5.01%)144(10%)70(0.00%)
33/30/2021Corres X29:5412:54:00 PM99%146%46625(7.74%)142(8.0%)N/AN/A
43/30/2021Corres x79:5412:54:00 PM97%190%34232(4.22%)144 (4.12%)N/AN/A
53/30/2021Corres12:003:00:00 PM95%211%50046(4.68%)185(10.86%)142(16.31%)
63/30/2021Corres X212:003:00:00 PM99%148%46625(7.65%)182 (8%)N/AN/A
73/30/2021Corres x712:003:00:00 PM98%222%34239(4.39%)184 (10.25%)N/AN/A
Sheet1


RMJ2.xlsx
AB
1DateName on Last 3
23/30/2021Corres
3Corres X2
4Corres x7
Sheet2
Cell Formulas
RangeFormula
B2:B4B2=IFERROR(INDEX(Sheet1!$B$2:$B$9999,AGGREGATE(14,6,ROW(Sheet1!$A$2:$A$9999)-ROW(Sheet1!$A$1)/(Sheet1!$A$2:$A$9999=$A$2),ROW($B$1)+4-ROW())),"No match")
 

Forum statistics

Threads
1,141,315
Messages
5,705,699
Members
421,406
Latest member
kluna90

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