Excel Question help needed

L

Legacy 465601

Guest
I have this spreadsheet and I need to build a formula to catch dates needed from a worksheet that has multiple dates and I need only the current and the one prior to the current Date.

I am using SSN # as my unique ID to vlookup the old and the new dates but some SSN’s have multiple dates and I don’t know how to catch only the current and the date prior to the current. Any suggestions will be appreciated!
 

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.
To begin, it is important that we understand your worksheet layout. Suggest you upload a representative sample of your worksheet and a mocked up solution of what you expect the outcome to look like. Use XL2BB to do this. See my signature to find instructions.
 
Upvote 0
Hello, I am having difficulties uploading my "mock-up" in hopes you can help me.
 
Upvote 0
THis is the best I can do to provide a "mock up". The FInal Report needs to have data from the "extracted from database" report to show the data from the current and the previous dates for each column? As you can see, it is split by Old Date and New Date and whatever data was on those dates need to be in the Final REport.
 

Attachments

  • extracted from database.PNG
    extracted from database.PNG
    53.7 KB · Views: 4
  • Final Report with formulas.PNG
    Final Report with formulas.PNG
    39.2 KB · Views: 5
Upvote 0
with XL2BB

SSNNetworkAccountGroupIDCodeFirst NameLast NameGenderDOBFrom DateEnd Date
123456780ABC123OPEN104UK1234567891104JohnSmithM11/11/197701/04/201728/02/2018
98112356ABC123OPEN5214JK1112131415604SallyDoeF12/11/196801/03/201431/12/2039
345678910ABC123OPEN3696JK1236789123404IvanRayM07/08/198408/06/201831/12/2039
75971908ABC123OPEN6435JK8527869312704RileyTraceF07/11/195201/10/201531/12/2039
75971908ABC123RETIREE6436LM9632156478306RileyTraceF07/11/195201/09/202031/12/2039
95393796ABC123OPEN7895JK3217896543104MaryChiF08/06/198201/04/202031/12/2039
93865135ABC123OPEN8525JK9638521474304TrancePungM08/02/195001/02/201930/04/2019
93865135ABC123OPEN8526JK5208549632104TrancePungM08/02/195001/02/201931/07/2019
93865135ABC123RETIREE8528LM9654896321206TrancePungM08/02/195001/09/202031/12/2039
93865135ABC123OPEN8527JK7410123463704TrancePungM08/02/195001/09/202031/12/2039
101213756ABC123OPEN7412JK7023651456304ReyAllenM10/05/197701/05/201931/12/2039
202134867ABC123OPEN2316JK5145632178904MikeAdkinsM03/04/197801/11/201031/05/2020
202134867ABC123COBRA2317MN2310235045605MikeAdkinsM03/04/197801/07/202031/12/2039
13579908ABC123OPEN7415JK2851654138504FrankMillerM04/07/199601/09/202031/12/2039

Member SSNOld NetworkOld AccountOld GroupOld IDOld CodeNew NetworkNew AccountNew GroupNew IDNew CodeFirst NameLast NameGenderDOBOLD From DateOld Thru DateNew From DateNew Thru Date
123456780
98112356
345678910
75971908
75971908
95393796
93865135
93865135
93865135
93865135
101213756
202134867
202134867
13579908
 
Upvote 0
To begin, it is important that we understand your worksheet layout. Suggest you upload a representative sample of your worksheet and a mocked up solution of what you expect the outcome to look like. Use XL2BB to do this. See my signature to find instructions.
I’m new to this, can you assist on the current upload? Thank you
To begin, it is important that we understand your worksheet layout. Suggest you upload a representative sample of your worksheet and a mocked up solution of what you expect the outcome to look like. Use XL2BB to do this. See my signature to find instructions.
 
Upvote 0
with XL2BB

SSNNetworkAccountGroupIDCodeFirst NameLast NameGenderDOBFrom DateEnd Date
123456780ABC123OPEN104UK1234567891104JohnSmithM11/11/197701/04/201728/02/2018
98112356ABC123OPEN5214JK1112131415604SallyDoeF12/11/196801/03/201431/12/2039
345678910ABC123OPEN3696JK1236789123404IvanRayM07/08/198408/06/201831/12/2039
75971908ABC123OPEN6435JK8527869312704RileyTraceF07/11/195201/10/201531/12/2039
75971908ABC123RETIREE6436LM9632156478306RileyTraceF07/11/195201/09/202031/12/2039
95393796ABC123OPEN7895JK3217896543104MaryChiF08/06/198201/04/202031/12/2039
93865135ABC123OPEN8525JK9638521474304TrancePungM08/02/195001/02/201930/04/2019
93865135ABC123OPEN8526JK5208549632104TrancePungM08/02/195001/02/201931/07/2019
93865135ABC123RETIREE8528LM9654896321206TrancePungM08/02/195001/09/202031/12/2039
93865135ABC123OPEN8527JK7410123463704TrancePungM08/02/195001/09/202031/12/2039
101213756ABC123OPEN7412JK7023651456304ReyAllenM10/05/197701/05/201931/12/2039
202134867ABC123OPEN2316JK5145632178904MikeAdkinsM03/04/197801/11/201031/05/2020
202134867ABC123COBRA2317MN2310235045605MikeAdkinsM03/04/197801/07/202031/12/2039
13579908ABC123OPEN7415JK2851654138504FrankMillerM04/07/199601/09/202031/12/2039

Member SSNOld NetworkOld AccountOld GroupOld IDOld CodeNew NetworkNew AccountNew GroupNew IDNew CodeFirst NameLast NameGenderDOBOLD From DateOld Thru DateNew From DateNew Thru Date
123456780
98112356
345678910
75971908
75971908
95393796
93865135
93865135
93865135
93865135
101213756
202134867
202134867
13579908
Thank you
 
Upvote 0

Forum statistics

Threads
1,213,534
Messages
6,114,186
Members
448,554
Latest member
Gleisner2

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