Forum Discussion

MohsinMasoodACCA's avatar
MohsinMasoodACCA
Copper Contributor
May 17, 2023

Value and Respective Status in One Cell / Report According to Status

I have Values in one Column and Status/Comments in other column is it possible that i can have both in same cell like value and below comment in same cell or any other solution    For Example below...
excel
Formulas and Functions
bosinander's avatar
bosinander
Iron Contributor
May 18, 2023

Hi MohsinMasoodACCA 

Attached is one way to do it. You may look inte the different parts and find out how it is done. Or just use it if it suits your needs 🙂 Good luck!

 

Assuming you will need A full year

I'ld suggest to first use Power Query to unpivot the data into table "unpivoted":

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Who", type text}, {"Status", type text}, {"Jan", Int64.Type}, {"Feb", Int64.Type}, {"Mar", Int64.Type}, {"Apr", Int64.Type}}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Who", "Status"}, "Attribute", "Value"),
    #"Renamed Columns" = Table.RenameColumns(#"Unpivoted Other Columns",{{"Attribute", "When"}})
in
    #"Renamed Columns"

 

Pick the unique values

F3=UNIQUE(unpivoted[Who])

G2=Table1[[#Headers],[Jan]:[Dec]]

 

Get the sums

G3=SUMIFS(unpivoted[Value],unpivoted[Who],F3#,unpivoted[When],G2#)

 

Get the months in a background calculation

V2=TRANSPOSE(UNIQUE(unpivoted[Status]))

V3:X4=IFERROR(TEXTJOIN(" & ";;FILTER($C:$C;($A:$A=$F3)*($B:$B=V$2)));"")

 

Join the texts to create the comments.

S3:S4=TEXTJOIN(" ";1;V3;IF(V3="";"";V$1);W3;IF(W3="";"";W$1);X3;IF(X3="";"";X$1))

 

Possibly use command Data:Group to hide empty months

 

 

Resources