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 is the Scenario 

SiteJanFebMarAprComments
ABC100200300400Jan & Feb Paid Mar Invoice Sent Apr Under Billing
XYZ500600700800Till March Paid Apr Under Billing
      
      
Now i have to make summary according to comments like below
     
 JanFebMarApr 
ABC Payments100100   
ABC Billed  300  
ABC Unbilled   400 
      
      
      
      
excel
Formulas and Functions
  • bosinander's avatar
    bosinander
    Steel 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