Sep 08 2022 09:38 AM
Hi,
I am looking to create two new columns for my report using column D and E. For example, I would like the "First Patient In Date" column to display dates for any milestones that say "First Subject In" (doesn't matter what is in parenthesis). I would like the same for the "last patient out date" column and then remove columns D & E. Is there an equation to do this?
Thanks!
Sep 08 2022 12:58 PM
Solution@cfitzg3 Hello, if you're looking to conditionally place the date found in column E into one of the two new columns you can use the following formula for First Subject In column F, for example:
=IF(ISNUMBER(SEARCH("First Subject In",D2)),E2,"")
And the following for column G
=IF(ISNUMBER(SEARCH("Last Subject Out",D2)),E2,"")
However, you can't delete the dependent columns that the formulas now reference unless you copy and past as values.
Depending on the source for this data, you could automate both the conditional placement of the date and deleting the original columns during the data import by using power query. I can elaborate, if this is something you'd like to try.
Dexter
Sep 08 2022 01:06 PM
@DexterG_III Thank you so much!!
Sep 08 2022 12:58 PM
Solution@cfitzg3 Hello, if you're looking to conditionally place the date found in column E into one of the two new columns you can use the following formula for First Subject In column F, for example:
=IF(ISNUMBER(SEARCH("First Subject In",D2)),E2,"")
And the following for column G
=IF(ISNUMBER(SEARCH("Last Subject Out",D2)),E2,"")
However, you can't delete the dependent columns that the formulas now reference unless you copy and past as values.
Depending on the source for this data, you could automate both the conditional placement of the date and deleting the original columns during the data import by using power query. I can elaborate, if this is something you'd like to try.
Dexter