Results 1 to 5 of 5

Dynamic Array Spill and Sort into 2 Columns

Threaded View

  1. #1
    Registered User
    Join Date
    07-20-2019
    Location
    Miami
    MS-Off Ver
    365
    Posts
    69

    Question Dynamic Array Spill and Sort into 2 Columns

    Hello,

    I'm not sure if this is possible with Dynamic Arrays but I'd like to confine the Spill into two columns using a maximum of 5 rows - while also Sorting on the sum of the Total. My spreadsheet is formatted in such a way that there are more Spill values than my layout will allow - hence the need to spill horizontally after 5 values are reached. I know I can use Sequence to limit the number of items in the Spill but I can't figure out how to make the second column pick up where the first left off. Furthermore, I'm not getting the SORT or SORTBY functions on the SUM of the Total column to work properly.

    SOURCE TABLE
    Name Dividend Date Total
    Arizona 1/31/21 $345.00
    Maine 4/15/21 $216.00
    Georgia 3/15/21 $267.00
    California 8/9/21 $589.00
    Illinois 9/7/21 $246.00
    Florida 12/1/21 $987.00
    Georgia 2/28/21 $147.00
    Alabama 4/15/21 $753.00
    Maine 6/12/21 $23.00
    Arizona 7/15/21 $237.00
    Illinois 3/13/21 $687.00
    Washington 11/23/21 $237.00
    New York 2/9/21 $975.00
    Maine 11/6/21 $764.00

    My current formula (invalid Sequence & Sort additions removed)
    Name Column
    Formula: copy to clipboard
    =UNIQUE(FILTER(Table1[Name],(Table1[Dividend Date] >= DATE(YEAR(TODAY()),1,1)) * (Table1[Dividend Date] <= DATE(YEAR(TODAY())+1,1,0)),"No Results"))


    Date Column
    Formula: copy to clipboard
    =SUMIFS(Table1[Total],Table1[Dividend Date], ">="&DATE(YEAR(TODAY()),1,1),Table1[Dividend Date], "<="&DATE(YEAR(TODAY())+1,1,0),Table1[Name],G10#)


    If Dynamic Arrays don't support this, is there another way to accomplish what I'm asking?
    Thanks
    Attached Files Attached Files

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Spill array starting from zero
    By Sordini2 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 02-14-2021, 09:37 AM
  2. Replies: 5
    Last Post: 01-27-2021, 12:07 PM
  3. Spill/array formula to calculate average for specified ranges?
    By ErikBerger in forum Excel Formulas & Functions
    Replies: 13
    Last Post: 01-27-2021, 07:22 AM
  4. [SOLVED] String-to-columns spill formula
    By ExcelFan117 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 12-10-2020, 07:09 AM
  5. [SOLVED] Excel Filter function matching to a Spill Array
    By ibuhary in forum Excel General
    Replies: 16
    Last Post: 10-25-2020, 11:05 PM
  6. Dynamic, Spilled, Named Ranges - how to override SPILL
    By TimB in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 02-24-2020, 01:53 PM
  7. Sort and Filter 2D Dynamic Array
    By willia97 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 10-23-2014, 07:34 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