Sort list by date, expanded selection?

[mattbnorris]

Book1
	N	O	P	Q	R
1	Date	ID	Cost		Sorted
2	17/11/1992	#36454	£34		10/08/1990
3	10/08/1990	#36455	£6,754		17/11/1992
4	10/03/2004	#36456	£453		07/01/1995
5	14/06/2017	#36457	£543		08/01/1996
6	03/01/2004	#36458	£8,788		03/01/2004
7	08/01/1996	#36459	£9,000		10/03/2004
8	11/05/2010	#36460	£9,000		18/05/2006
9	27/07/2006	#36461	£545		27/07/2006
10	18/05/2006	#36462	£55		11/05/2010
11	07/01/1995	#36463	£1,222		14/06/2017
Sheet2

Cell Formulas
Range	Formula
R2:R11	R2	=IFERROR(INDEX($N$2:$N$20, MATCH(ROWS($N$2:N2), COUNTIF($N$2:$N$20, "<="&$N$2:$N$20), 0)), "")
Press CTRL+SHIFT+ENTER to enter array formulas.

Above is an example of my data.

The formula i'm using in Column R is able to sort the dates in Column N, but I want to also "expand" the function to the columns adjacent; so when the dates are sorted, the ID and Cost also follow with the correct date they are attached to. Is this possible to do using a formula? - I want to avoid VLOOKUP, as there are duplicate dates in the full dataset which would cause issues.

Ideally I would be able to paste the data into a section of the sheet and then, either on another sheet or next to the pasted data, the formulas will be able to sort everything in chronological date order.

Thanks for the help

 

[kweaver]

Please change your profile to indicate which version of Excel you are running. If you're using 365, won't the SORT function do what you're looking for?

 

[mattbnorris]

Please change your profile to indicate which version of Excel you are running. If you're using 365, won't the SORT function do what you're looking for?

Hello, updated this now thanks. I only have access to 2016 excel unfortunately!

 

[mohadin]

Hi

S2=VLOOKUP($R$2:$R$20,$N$2:$P$20,COLUMN()-17,0)

Drag down and right

 

[mattbnorris]

Hi

S2=VLOOKUP($R$2:$R$20,$N$2:$P$20,COLUMN()-17,0)

Drag down and right

Book1
	N	O	P	Q	R	S
1	Date	ID	Cost		Sorted
2	17/11/1992	#36454	£34		10/08/1990	#36455
3	10/08/1990	#36455	£6,754		17/11/1992	#36454
4	10/03/2004	#36456	£453			#N/A
5	14/06/2017	#36457	£543		07/01/1995	#36463
6	03/01/2004	#36458	£8,788		08/01/1996	#36459
7	08/01/1996	#36459	£9,000		03/01/2004	#36458
8	11/05/2010	#36460	£9,000		10/03/2004	#36456
9	27/07/2006	#36461	£545		18/05/2006	#36462
10	18/05/2006	#36462	£55		27/07/2006	#36461
11	07/01/1995	#36463	£1,222		11/05/2010	#36460
12	07/01/1995	#36464	£855		14/06/2017	#36457
Sheet2

Cell Formulas
Range	Formula
S2:S20	S2	=VLOOKUP($R$2:$R$20,$N$2:$P$20,COLUMN()-17,0)
R2:R12	R2	=IFERROR(INDEX($N$2:$N$20, MATCH(ROWS($N$2:N2), COUNTIF($N$2:$N$20, "<="&$N$2:$N$20), 0)), "")
Press CTRL+SHIFT+ENTER to enter array formulas.
Dynamic array formulas.

I have just realised, my original index formula won't work in my scenario due to duplicate dates. I have updated the example above and included a duplicate date; one of the records shows up but the other record is blank.

This is also why VLOOKUP won't work as VLOOKUP only picks up the the first record of the date and won't account for duplicates.

Any ideas? Thanks

 

[mohadin]

Sorry
My formula knowledge is limited, maybe other member can help
Sorry Again