• 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.

Cluster Data to Single Table

Hi Gurus
Sorry If I am repeatedly posting more or less similar content.
As I am inbetween a report generation this confusion.Need formula to arrive data like this table in left hand side.

Sample file attached.
80473
 

Attachments

  • Chandoo.xlsx
    11 KB · Views: 4
Basically, it appears that you wish to filter the 'Revision Level's by Object/part and transpose the resulting column,
Code:
Part
= UNIQUE(Object);

= TRANSPOSE(FILTER(Revision_Level, Object=@Part))
Something I do not understand is where revision level "B" has gone.
80475
To return the entire table as a single 365 spilt range is insanely difficult and I would not recommend it unless you are in to heavy programming (and have access to the latest versions of 365).
Code:
= LET(
      part,      UNIQUE(Object),
      revisonsϑ, MAP(UNIQUE(Object), LAMBDA(p,
         LET(
            matches,  Object=p,
            partRevs, FILTER(Revision_Level, matches),
            LAMBDA(TRANSPOSE(partRevs))
         )
      )),
      revArr,   REDUCE(hdr, revisonsϑ, LAMBDA(a,ϑ, VSTACK(a,ϑ() ))),
      partHdr,  VSTACK("Object",part),
      revTable, HSTACK(partHdr, revArr),
      IF(ISERROR(revTable), "", IF(revTable=0,"Zero",revTable))
   )
 

Attachments

  • Chandoo (2).xlsx
    16.3 KB · Views: 2
Try,

In C2, array (CSE) formula, copied across and down :

=IFERROR(INDEX(IF($K$2:$K$10="",0,$K$2:$K$10),AGGREGATE(15,6,ROW($A$1:$A$9)/($I$2:$I$10=$B2),COLUMN(A1))),"")

80477
 

Attachments

  • Chandoo (BY).xlsx
    11.6 KB · Views: 2
Basically, it appears that you wish to filter the 'Revision Level's by Object/part and transpose the resulting column,
Code:
Part
= UNIQUE(Object);

= TRANSPOSE(FILTER(Revision_Level, Object=@Part))
Something I do not understand is where revision level "B" has gone.
View attachment 80475
To return the entire table as a single 365 spilt range is insanely difficult and I would not recommend it unless you are in to heavy programming (and have access to the latest versions of 365).
Code:
= LET(
      part,      UNIQUE(Object),
      revisonsϑ, MAP(UNIQUE(Object), LAMBDA(p,
         LET(
            matches,  Object=p,
            partRevs, FILTER(Revision_Level, matches),
            LAMBDA(TRANSPOSE(partRevs))
         )
      )),
      revArr,   REDUCE(hdr, revisonsϑ, LAMBDA(a,ϑ, VSTACK(a,ϑ() ))),
      partHdr,  VSTACK("Object",part),
      revTable, HSTACK(partHdr, revArr),
      IF(ISERROR(revTable), "", IF(revTable=0,"Zero",revTable))
   )
OOps sorry my bad i missed "B" will this code work in MS Office excel?
 
Or,

If you wish to use Small+If instead of Aggregate function,

In C2, array (CSE) formula, copied across and down :

=IFERROR(INDEX(IF($K$2:$K$10="",0,$K$2:$K$10),SMALL(IF(($I$2:$I$10=$B2),ROW($I$2:$I$10)-ROW($I$1)),COLUMN(A$1))),"")

Regards
 
OOps sorry my bad i missed "B" will this code work in MS Office excel?
It only works with Excel 365 and Excel 2021. Moving to 365 was a positive decision for me because I dislike traditional spreadsheet methods. That said, there are members of this forum that are absolutely superb in what they can achieve using traditional spreadsheet techniques. In the present case, @bosco_yip 's solution will work with older versions of Excel. A custom number format can be used to display the '0's as 'Zero's
 
Back
Top