• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

systematically data arrange

bosco_yip

Excel Ninja
In G5, formula copied across and down :

=IFERROR(INDEX(($C:$C,$D:$D),AGGREGATE(15,6, ROW($B$5:$B$13)/($B$5:$B$13=$F5),INT((COLUMN(A$1)-1)/2)+1),0,MOD(COLUMN(A$1)-1,2)+1),"")

Or,

=IFERROR(INDEX($C$5:$D$13,AGGREGATE(15,6, ROW($B$5:$B$13)-ROW($B$4)/($B$5:$B$13=$F5),INT((COLUMN(A$1)-1)/2)+1),MOD(COLUMN(A$1)-1,2)+1),"")

72992
 

Attachments

Last edited:

sambit

Member
In G5, formula copied across and down :

=IFERROR(INDEX(($C:$C,$D:$D),AGGREGATE(15,6, ROW($B$5:$B$13)/($B$5:$B$13=$F5),INT((COLUMN(A$1)-1)/2)+1),0,MOD(COLUMN(A$1)-1,2)+1),"")

Or,

=IFERROR(INDEX($C$5:$D$13,AGGREGATE(15,6, ROW($B$5:$B$13)-ROW($B$4)/($B$5:$B$13=$F5),INT((COLUMN(A$1)-1)/2)+1),MOD(COLUMN(A$1)-1,2)+1),"")

View attachment 72992
Bosco_yip Sir,
Thank you very much.
 

p45cal

Well-Known Member
A Power Query offering in the attached.
Right-click the table at cell F16 and choose Refresh to update the table.
This table will expand/contract both vertically and horizontally automatically according to the data - simulate this by dragging your source table's (the table at cell B4) grab-handle (bottom right) to adjust its extent, then you can refresh the result table.
Note also that I hid the table's headers but you may prefer to see them, in which case select any cell in the result table and in the ribbon tick the Header Row box in the Table Style Options section of the Table Design tab.

ps. Those dates in your data (column D) aren't real Excel dates but just text; the query converts them to real Excel dates and makes the assumption they're dd/mm/yyy (not mm/dd/yyyy).
 

Attachments

Last edited:

Peter Bartholomew

Well-Known Member
This is simply an exploration of new functionality available within Excel 365 beta channel.

73046

The FILTER function is referencing one of the Invoice numbers highlighted in purple to filter the payment data highlighted in blue. The LIST turns the array of data associated with a given invoice number into a horizontal list of alternating document numbers and dates The 'nuts and bolts' of that process are hidden within a LAMBDA function that is capable of applying the same formula to multiple inputs.

Code:
= LAMBDA(tbl,
    LET(
      items, COUNTA(tbl),
      k, SEQUENCE(1,items,0),
      r, 1+QUOTIENT(k,2),
      c, 1+MOD(k,2),
      INDEX(tbl,r,c)
  ) )
The code implements the LIST function. It is passed the filtered (n x 2) array, creates a column counter k and then, for each k, determines the row and column of the data to return.

It may be a long time before I have the opportunity to use the new techniques on real problems, but there is no harm in being prepared!
 
Top