Cluster Data to Single Table

[Vignesh Veerasamy]

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.

 

[Peter Bartholomew]

Basically, it appears that you wish to filter the 'Revision Level's by Object/part and transpose the resulting column,

Part
= UNIQUE(Object);

= TRANSPOSE(FILTER(Revision_Level, Object=@Part))

Something I do not understand is where revision level "B" has gone.

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

= 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))
   )

 

[bosco_yip]

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))),"")

 

[Vignesh Veerasamy]

Basically, it appears that you wish to filter the 'Revision Level's by Object/part and transpose the resulting column,

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

= 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?

 

[bosco_yip]

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

 

[Peter Bartholomew]

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