+ Reply to Thread
Results 1 to 4 of 4

Querying Large Series of Arrays And Displaying Unique Values in Arrays [Hard Index Match?]

  1. #1
    Registered User
    Join Date
    03-28-2010
    Location
    New York
    MS-Off Ver
    Office 365
    Posts
    57

    Querying Large Series of Arrays And Displaying Unique Values in Arrays [Hard Index Match?]

    Hi Guys,

    It's been a while since I've been on the forum fortunately, but I've finally run into a roadblock and was hoping someone could share some insight about how to approach this problem. I essentially have a series of cells were I specific a feed into my model a series of 6 years that have each been mapped with a series of four alternatives: Yearly, Semi-Annually, Quarterly or Monthly. All of these are interchangeable between years. The data looks as follows currently.

    2017 - Yearly
    2018 - Yearly
    2019 - Semi-Annually
    2020 - Quarterly
    2021 - Monthly
    2022 - Yearly
    2023 - Yearly

    What I want to do is create a formula in excel that displays the data for each of the six years in the format I want, so the output would look something like this:

    DESIRED OUTPUT:
    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 have mapped a series of "helper columns" below which classify each data to its most granular and which in theory could be linked by some sort of an index match function that just cross-references the year by the desired format. My question is, how do I link all of this together by cross-referencing the year to the desired format and create a function that looks up the format and returns the unique instances of that format for each year? For example, Quarterly 2020 would look against this table and look up Q1-20, Q2-20, Q3-20, Q4-20 and then move on to the next year. I have attached a spreadsheet with everything in there hopefully easily laid out but any guidance on how to approach this would be hugely helpful.

    Link to spreadsheet Attached.

    Helper Columns:
    Monthly Periods Back Months Monthly Quarterly Semi-Annually Yearly
    1/31/2017 1 Jan-17 Q1-17 H1-17 FY-17
    2/28/2017 2 Feb-17 Q1-17 H1-17 FY-17
    3/31/2017 3 Mar-17 Q1-17 H1-17 FY-17
    4/30/2017 4 Apr-17 Q2-17 H1-17 FY-17
    5/31/2017 5 May-17 Q2-17 H1-17 FY-17
    6/30/2017 6 Jun-17 Q2-17 H1-17 FY-17
    7/31/2017 7 Jul-17 Q3-17 H2-17 FY-17
    8/31/2017 8 Aug-17 Q3-17 H2-17 FY-17
    9/30/2017 9 Sep-17 Q3-17 H2-17 FY-17
    10/31/2017 10 Oct-17 Q4-17 H2-17 FY-17
    11/30/2017 11 Nov-17 Q4-17 H2-17 FY-17
    12/31/2017 12 Dec-17 Q4-17 H2-17 FY-17
    .....
    .....

  2. #2
    Forum Guru Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    Thailand
    MS-Off Ver
    MS 365
    Posts
    5,773

    Re: Querying Large Series of Arrays And Displaying Unique Values in Arrays [Hard Index Mat

    Please try at
    J3
    =IFERROR(SMALL(IF(LOOKUP(LEFT($C$3:$C$9),{"M";"Q";"S";"Y"},{12;4;2;1})>=COLUMN($A$1:$L$1),$D$3:$D$9),ROWS(J$3:J3)),"")
    Press Ctrl+Shift+Enter

    K3
    =IF(J3="","",CHOOSE(MATCH(COUNTIF($J$3:$J$30,J3),{1,2,4,12}),"FY-"&RIGHT(J3,2),"H"&COUNTIF(J$3:J3,J3)&"-"&RIGHT(J3,2),"Q"&COUNTIF(J$3:J3,J3)&"-"&RIGHT(J3,2),TEXT(DATE(J3,COUNTIF(J$3:J3,J3),1),"mmm-yy")))

  3. #3
    Registered User
    Join Date
    03-28-2010
    Location
    New York
    MS-Off Ver
    Office 365
    Posts
    57

    Re: Querying Large Series of Arrays And Displaying Unique Values in Arrays [Hard Index Mat

    You are a dangerous, dangerous man. Thank you!
    Last edited by AliGW; 05-28-2020 at 04:53 PM. Reason: Please donít quote unnecessarily!

  4. #4
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    47,489

    Re: Querying Large Series of Arrays And Displaying Unique Values in Arrays [Hard Index Mat

    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

    Administrative Note:

    Please don't quote whole posts, especially when you are responding to the one immediately preceding your own - it's just clutter. It's OK to quote if you are responding to a post out of sequence, but limit quoted content to a few relevant lines that makes clear to whom and what you are responding. Thanks!

    For normal conversational replies, try using the QUICK REPLY box below or the REPLY button instead of REPLY WITH QUOTE.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!

    Forum Rules (updated September 2018): please read them here.
    How to use the Power Query code you've been given: help here. More about the Power suite here.
    Don't forget to say "thank you" to those who have helped you in your thread. If you wish, you can also reward them by clicking on their reputation star bottom left.

+ 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] Index/Match two arrays
    By T86157 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 01-22-2017, 04:46 PM
  2. Using arrays alongside INDEX and MATCH
    By lostest in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 08-27-2016, 02:27 PM
  3. [SOLVED] Match-Index in stead of Index-Match lookup Array among Arrays
    By Numnum in forum Excel General
    Replies: 2
    Last Post: 10-15-2015, 02:08 PM
  4. [SOLVED] Application.Match vs very large data arrays - A.M looses
    By Rhudi in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 11-25-2014, 04:35 PM
  5. Replies: 2
    Last Post: 12-04-2013, 09:45 PM
  6. Sum Index Match Arrays
    By CaesarBob in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 09-19-2012, 04:38 AM
  7. Arrays - declaration, adding values to arrays and calculation
    By Maxi in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 08-17-2006, 11:15 AM

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