How To Populate Last "N" Entries Conditionally?

X

xlmaniac

Well-known Member
Joined
Jul 2, 2009
Messages
531
Office Version
  1. 2010
Platform
  1. Windows
Dear All,
I do have a sample data-set across A1:C11 as follows.

[TABLE="width: 213"]
<tbody>[TR]
[TD]SKU[/TD]
[TD]Location[/TD]
[TD]Posting Date[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD]A[/TD]
[TD="align: right"]12-Jan[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="align: right"]14-Jan[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="align: right"]18-Jan[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="align: right"]19-Jan[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="align: right"]20-Jan[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="align: right"]23-Jan[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD]D[/TD]
[TD="align: right"]13-Dec[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="align: right"]19-Dec[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="align: right"]28-Dec[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="align: right"]04-Jan[/TD]
[/TR]
</tbody>[/TABLE]

I would like to populate the last 3 posting date from the data set.
The desired result across F1:J3.
[TABLE="width: 535"]
<tbody>[TR]
[TD]SKU[/TD]
[TD]Region[/TD]
[TD]Last Posting Date[/TD]
[TD]2nd Last Posting Date[/TD]
[TD]3rd Last Posting Date[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]A[/TD]
[TD]23-Jan[/TD]
[TD]20-Jan[/TD]
[TD]19-Jan[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]D[/TD]
[TD]04-Jan[/TD]
[TD]28-Dec[/TD]
[TD]19-Dec[/TD]
[/TR]
</tbody>[/TABLE]


Pls help in the formulae which can yield the desired result

Regards
Xlmainac
 

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.
Sort by date Sort by votes
You should first alter the data so that each record is complete, otherwise this is too difficult. It's also wise to have the dates display fully.

Copy row3 down as necessary for each SKU and Location.

ABCDEFGHIJ
AA
AD
A
A
A
A
D
D
D
D

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="bgcolor: #FFF2CC"]SKU[/TD]
[TD="bgcolor: #FFF2CC"]Location[/TD]
[TD="bgcolor: #FFF2CC"]Posting Date[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #FCE4D6"]SKU[/TD]
[TD="bgcolor: #FCE4D6"]Region[/TD]
[TD="bgcolor: #FCE4D6"]Last Posting Date[/TD]
[TD="bgcolor: #FCE4D6"]2nd Last Posting Date[/TD]
[TD="bgcolor: #FCE4D6"]3rd Last Posting Date[/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]1[/TD]

[TD="align: right"]1/12/18[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]1[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E2EFDA]#E2EFDA[/URL] , align: right"]1/23/18[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E2EFDA]#E2EFDA[/URL] , align: right"]1/20/18[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E2EFDA]#E2EFDA[/URL] , align: right"]1/19/18[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]1[/TD]

[TD="align: right"]1/14/18[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]2[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E2EFDA]#E2EFDA[/URL] , align: right"]1/04/18[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E2EFDA]#E2EFDA[/URL] , align: right"]12/28/17[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E2EFDA]#E2EFDA[/URL] , align: right"]12/19/17[/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]1[/TD]

[TD="align: right"]1/18/18[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]
[TD="align: right"]1[/TD]

[TD="align: right"]1/19/18[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]
[TD="align: right"]1[/TD]

[TD="align: right"]1/20/18[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]
[TD="align: right"]1[/TD]

[TD="align: right"]1/23/18[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]
[TD="align: right"]2[/TD]

[TD="align: right"]12/13/17[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]9[/TD]
[TD="align: right"]2[/TD]

[TD="align: right"]12/19/17[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]10[/TD]
[TD="align: right"]2[/TD]

[TD="align: right"]12/28/17[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]11[/TD]
[TD="align: right"]2[/TD]

[TD="align: right"]1/04/18[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>
Sheet3​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]H2[/TH]
[TD="align: left"]{=LARGE(IF(($F2=$A$2:$A$11)*($G2=$B$2:$B$11),$C$2:$C$11,""),1)}[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]I2[/TH]
[TD="align: left"]{=LARGE(IF(($F2=$A$2:$A$11)*($G2=$B$2:$B$11),$C$2:$C$11,""),2)}[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]J2[/TH]
[TD="align: left"]{=LARGE(IF(($F2=$A$2:$A$11)*($G2=$B$2:$B$11),$C$2:$C$11,""),3)}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try to enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
Thanks a lot for your help.
Really appreciate the same.
Regards
Xlmaniac
 
Upvote 0
Dear DRSteele,
I am getting the following error with the folrmula.
COuld you pls check once?

[TABLE="width: 357"]
<colgroup><col><col><col><col><col></colgroup><tbody>[TR]
[TD]SKU[/TD]
[TD]Region[/TD]
[TD]Last Posting Date[/TD]
[TD]2nd Last Posting Date[/TD]
[TD]3rd Last Posting Date[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]A[/TD]
[TD="align: right"]01-12-2018[/TD]
[TD="align: center"]#NUM![/TD]
[TD="align: center"]#NUM![/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]D[/TD]
[TD="align: right"]01-04-2018[/TD]
[TD="align: center"]#NUM![/TD]
[TD="align: center"]#NUM!

Regards
Xlmaniac[/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
You must log in or register to reply here.

Similar threads

J
  • Question Question
How to allocate project hours between specific dates using specific daily hour burn rates and including awkward calendar month start/end+holidays
2
Replies
11
Views
3K
felixstraube
F
A
  • Solved
Help Changing Cell Color to Based of Date referring to a specific Interior Cell Color
Replies
8
Views
528
SanjayGMusafir
SanjayGMusafir
K
  • Question Question
Dynamic Address for Use in Function Always returns ERROR
Replies
3
Views
549
Eric W
Eric W
K
  • Solved
Earliest date in row if the text in the adjacent columns matches specific
Replies
7
Views
461
Fluff
Fluff
willow1985
  • Solved
Count the most recent x number of dates that meet certain criteria
Replies
3
Views
420
Fluff
Fluff

Forum statistics

Threads
1,224,823
Messages
6,181,181
Members
453,022
Latest member
Mohamed Magdi Tawfiq Emam

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