+ Reply to Thread
Results 1 to 3 of 3

Transforming Data Within An Array

  1. #1
    Registered User
    Join Date
    03-28-2010
    Location
    New York
    MS-Off Ver
    Excel 2007
    Posts
    53

    Transforming Data Within An Array

    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
    Attached Files Attached Files

  2. #2
    Forum Expert Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    Thailand
    MS-Off Ver
    MS 365
    Posts
    3,636

    Re: Transforming Data Within An Array

    Please try at F2

    =IFERROR(IF(MOD(SMALL(IF(1+($B$2:$B$98<>$B$3:$B$99)*(LEFT($C$2:$C$98)<>"F")>{0,1},ROW($B$2:$B$98)*1000+{0,1}),ROWS(F$2:F2)),1000),"FY-"&RIGHT(F1,2),INDEX(C:C,SMALL(IF(1+($B$2:$B$98<>$B$3:$B$99)*(LEFT($C$2:$C$98)<>"F")>{0,1},ROW($B$2:$B$98)),ROWS(F$2:F2)))),"")

    Ctrl+Shift+Enter
    Attached Files Attached Files

  3. #3
    Registered User
    Join Date
    03-28-2010
    Location
    New York
    MS-Off Ver
    Excel 2007
    Posts
    53

    Re: Transforming Data Within An Array

    Wow. Thanks.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Transforming Data Set
    By d7882 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 03-25-2019, 12:16 PM
  2. Transforming Data to Another Sheet
    By shtmd in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 03-22-2019, 11:42 AM
  3. [SOLVED] Trouble transforming data
    By Paddymaw in forum Excel Formulas & Functions
    Replies: 11
    Last Post: 02-11-2019, 10:04 AM
  4. [SOLVED] Transforming Data
    By Ruadan in forum Excel General
    Replies: 8
    Last Post: 02-05-2019, 09:03 AM
  5. Transforming array multiplication from Excel to VBA function
    By gren in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 03-22-2012, 09:56 AM
  6. Transforming data to lie between 0 and 1
    By zgall1 in forum Excel General
    Replies: 2
    Last Post: 03-20-2010, 06:40 PM
  7. Transforming Data
    By Murtaza in forum Excel General
    Replies: 4
    Last Post: 08-23-2005, 03:05 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1