Hi Guys,
Can someone help me figure out how to extract and transform data within an array. I have the following data:
Year Input
2017 FY-17
2018 FY-18
2019 H1-19
2019 H2-19
2020 Q1-20
2020 Q2-20
2020 Q3-20
2020 Q4-20
2021 Jan-21
2021 Feb-21
2021 Mar-21
2021 Apr-21
2021 May-21
2021 Jun-21
2021 Jul-21
2021 Aug-21
2021 Sep-21
2021 Oct-21
2021 Nov-21
2021 Dec-21
2022 FY-22
2023 FY-23
I want to write a piece of code on this set of data that adds an "FY-" year N to the end of the dataset if the data is not expressed as FY to begin with. Here's what it would like:
Output
FY-17
FY-18
H1-19
H2-19
FY-19
Q1-20
Q2-20
Q3-20
Q4-20
FY-20
Jan-21
Feb-21
Mar-21
Apr-21
May-21
Jun-21
Jul-21
Aug-21
Sep-21
Oct-21
Nov-21
Dec-21
FY-21
FY-22
FY-23
I'm sure its pretty simple but can't seem to figure this out. The format of the year might change, so this formula needs to be something I can drag down if, for example, 2018 is expressed as Q1-18, Q2-18, Q3-18 and Q4-18 or H1-18 and H2-18.
I've attached an excel sheet below to provide a bit more context.
Thanks guys!
Larry
Bookmarks