Need help creating a formula

Screen Shot 2017-09-21 at 10.47.56.png


Hi I need help,

I need the Sample.ID to correspond to date.

i.e, Date - 2009 = PACE01-612
Date - 2011 = PACE02-612
Date - 2012 = PACE03-612

The spreadsheet is much bigger than screenshot, I have been trying to do this manually,but I'm sure there is a formula I can use to help.

I would appreciate your help

Thanks,
Jordan

Hello and welcome to the forum.

A sample worksheet will help us much more than a screenshot.

That being said, assuming that your dates are in column C starting in C2, try this in B2

=LOOKUP(YEAR(C2),{2009,2011,2012},{"PACE01-612","PACE02-612","PACE03-612"})

You are changing the requirements.

To prevent us from going back and fourth until you find a solution that you like, please upload a small representative sample of your workbook along with the desired results (manually entered) of a formula.

This will help us get to a solution faster than looking at screenshots.

https://www.excelforum.com/attachmen...7&d=1506015684

I dont know if that link will work. I uploaded an attachment. I cant get it attached to this thread though.

No, that link did not work.

To upload a workbook, click on Go Advanced > Manage Attachments > Choose File > Upload > Close this window.

I have followed these instruction and have attached 'Workbook 1'

I cannot seem to find any correlation between your desired outcome and the other data in your sample.

There are some instances where the Sample.ID matches the Desired Outcome exactly and other instances where the Desired Outcome is increasing by 1.

Maybe I am missing something. I am tapping out. Hopefully someone else will be able to assist you.

The Study ID have have up to 3 different collection years (hence PACE01-..., PACE02-... and PACE03-...)

For the very first collection year it has to PACE01 and then increase by 1 for each later year.


Hope this makes sense, sorry if I didnt make it compleetly clear.

I have PACE01 for them all at present which does match the 1st Year collection but nothing else

Thanks,
Jordan

Here is an updated workbook, hopefully this makes things clearer

Hi sandy666,

I have removed screenshots now. I have added an example excel spreadsheet with what I want to achieve. I want the PACE01, PACE02, PACE03, PACE04 to respectively match the first sample date right up to the last and for the Study.ID to be incorporated as well.

Thanks for your help.

I need the order of unique aliquot id's to remain the same as I am loading this into a biorepository database.

What is (should be) the pattern between Study.ID and PACE.... ? last 2-3 digits? or something more?