Sort list by date, expanded selection?

mattbnorris

New Member
Joined
Sep 17, 2019
Messages
22
Office Version
  1. 2016
Platform
  1. Windows
Book1
NOPQR
1DateIDCostSorted
217/11/1992#36454£3410/08/1990
310/08/1990#36455£6,75417/11/1992
410/03/2004#36456£45307/01/1995
514/06/2017#36457£54308/01/1996
603/01/2004#36458£8,78803/01/2004
708/01/1996#36459£9,00010/03/2004
811/05/2010#36460£9,00018/05/2006
927/07/2006#36461£54527/07/2006
1018/05/2006#36462£5511/05/2010
1107/01/1995#36463£1,22214/06/2017
Sheet2
Cell Formulas
RangeFormula
R2:R11R2=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 :)
 

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.

kweaver

Well-known Member
Joined
May 8, 2018
Messages
2,105
Office Version
  1. 365
  2. 2010
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

New Member
Joined
Sep 17, 2019
Messages
22
Office Version
  1. 2016
Platform
  1. Windows
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

Well-known Member
Joined
Mar 22, 2015
Messages
1,091
Office Version
  1. 2013
Platform
  1. Windows
Hi
Excel Formula:
S2=VLOOKUP($R$2:$R$20,$N$2:$P$20,COLUMN()-17,0)
Drag down and right
 

mattbnorris

New Member
Joined
Sep 17, 2019
Messages
22
Office Version
  1. 2016
Platform
  1. Windows
Hi
Excel Formula:
S2=VLOOKUP($R$2:$R$20,$N$2:$P$20,COLUMN()-17,0)
Drag down and right
Book1
NOPQRS
1DateIDCostSorted
217/11/1992#36454£3410/08/1990#36455
310/08/1990#36455£6,75417/11/1992#36454
410/03/2004#36456£453 #N/A
514/06/2017#36457£54307/01/1995#36463
603/01/2004#36458£8,78808/01/1996#36459
708/01/1996#36459£9,00003/01/2004#36458
811/05/2010#36460£9,00010/03/2004#36456
927/07/2006#36461£54518/05/2006#36462
1018/05/2006#36462£5527/07/2006#36461
1107/01/1995#36463£1,22211/05/2010#36460
1207/01/1995#36464£85514/06/2017#36457
Sheet2
Cell Formulas
RangeFormula
S2:S20S2=VLOOKUP($R$2:$R$20,$N$2:$P$20,COLUMN()-17,0)
R2:R12R2=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

Well-known Member
Joined
Mar 22, 2015
Messages
1,091
Office Version
  1. 2013
Platform
  1. Windows
Sorry
My formula knowledge is limited, maybe other member can help
Sorry Again
 

Forum statistics

Threads
1,141,072
Messages
5,704,133
Members
421,329
Latest member
mippy

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