Consolidating and preparing status based on Field content and header

[scorpy]

I have pasted a sample data set below with required output format, Kindly let me know if the desired output can be generated via formula/Macro.

In my dataset fields are populated with 3 Keywords - Found, Not Found, Updated. I have to consolidate the headers for output field based on the keywords present for each row. Format is "Keywords" followed by "field names" with same keyword seperated by comma and it should end with "fixed." Thanks for any help.

<style type="text/css"><!--td {border: 1px solid #ccc;}br {mso-data-placement:same-cell;}--></style>[TABLE="width: 0"]
<colgroup><col style="width: 48px"><col width="68"><col width="68"><col width="68"><col width="68"><col width="68"><col width="68"><col width="480"></colgroup><tbody>[TR]
[TD="bgcolor: #538ed5"]File No[/TD]
[TD="bgcolor: #538ed5"]Name[/TD]
[TD="bgcolor: #538ed5"]Street[/TD]
[TD="bgcolor: #538ed5"]city[/TD]
[TD="bgcolor: #538ed5"]state[/TD]
[TD="bgcolor: #538ed5"]country[/TD]
[TD="bgcolor: #538ed5"]zip[/TD]
[TD="bgcolor: #a5a5a5"]Expected_Output[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD]Found[/TD]
[TD]Not Found[/TD]
[TD]Updated[/TD]
[TD]Found[/TD]
[TD]Not Found[/TD]
[TD]Updated[/TD]
[TD]Found Name, State fixed. Not Found Street, Country fixed. Updated City, Zip fixed.[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD]Updated[/TD]
[TD]Found[/TD]
[TD]Not Found[/TD]
[TD]Updated[/TD]
[TD]Found[/TD]
[TD]Not Found[/TD]
[TD]Found Street, Country fixed. Not Found City, Zip fixed. Updated Name, State fixed.[/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD]Not Found[/TD]
[TD]Updated[/TD]
[TD]Found[/TD]
[TD]Not Found[/TD]
[TD]Updated[/TD]
[TD]Found[/TD]
[TD]Found City, Zip fixed. Not Found Name, State fixed. Updated Street, Country fixed.[/TD]
[/TR]
[TR]
[TD="align: right"]4[/TD]
[TD]Found[/TD]
[TD]Updated[/TD]
[TD]Not Found[/TD]
[TD]Not Found[/TD]
[TD]Not Found[/TD]
[TD]Updated[/TD]
[TD]Found Name fixed. Not Found City, state, Country fixed. Updated Steet, Zip fixed.[/TD]
[/TR]
</tbody>[/TABLE]
<style type="text/css">

<!--td {border: 1px solid #ccc;}br {mso-data-placement:same-cell;}--></style>

 

[sandy666]

is that what you want?
Done with PowerQuery

[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td=bgcolor:#5B9BD5]File No[/td][td=bgcolor:#5B9BD5]Name[/td][td=bgcolor:#5B9BD5]Street[/td][td=bgcolor:#5B9BD5]city[/td][td=bgcolor:#5B9BD5]state[/td][td=bgcolor:#5B9BD5]country[/td][td=bgcolor:#5B9BD5]zip[/td][td][/td][td=bgcolor:#70AD47]File No[/td][td=bgcolor:#70AD47]Result[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]

1​

[/td][td=bgcolor:#DDEBF7]Found[/td][td=bgcolor:#DDEBF7]Not Found[/td][td=bgcolor:#DDEBF7]Updated[/td][td=bgcolor:#DDEBF7]Found[/td][td=bgcolor:#DDEBF7]Not Found[/td][td=bgcolor:#DDEBF7]Updated[/td][td][/td][td=bgcolor:#E2EFDA]

1​

[/td][td=bgcolor:#E2EFDA]Found Name, state fixed, Not Found Street, country fixed, Updated city, zip fixed[/td][/tr]

[tr=bgcolor:#FFFFFF][td]

2​

[/td][td]Updated[/td][td]Found[/td][td]Not Found[/td][td]Updated[/td][td]Found[/td][td]Not Found[/td][td][/td][td]

2​

[/td][td]Found Street, country fixed, Not Found city, zip fixed, Updated Name, state fixed[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]

3​

[/td][td=bgcolor:#DDEBF7]Not Found[/td][td=bgcolor:#DDEBF7]Updated[/td][td=bgcolor:#DDEBF7]Found[/td][td=bgcolor:#DDEBF7]Not Found[/td][td=bgcolor:#DDEBF7]Updated[/td][td=bgcolor:#DDEBF7]Found[/td][td][/td][td=bgcolor:#E2EFDA]

3​

[/td][td=bgcolor:#E2EFDA]Found city, zip fixed, Not Found Name, state fixed, Updated Street, country fixed[/td][/tr]

[tr=bgcolor:#FFFFFF][td]

4​

[/td][td]Found[/td][td]Updated[/td][td]Not Found[/td][td]Not Found[/td][td]Not Found[/td][td]Updated[/td][td][/td][td]

4​

[/td][td]Found Name fixed, Not Found city, state, country fixed, Updated Street, zip fixed[/td][/tr]
[/table]

M code

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"File No", Int64.Type}, {"Name", type text}, {"Street", type text}, {"city", type text}, {"state", type text}, {"country", type text}, {"zip", type text}}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"File No"}, "Attribute", "Value"),
    #"Grouped Rows" = Table.Group(#"Unpivoted Other Columns", {"File No", "Value"}, {{"Count", each _, type table}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each List.Distinct(Table.Column([Count],"Attribute"))),
    #"Extracted Values" = Table.TransformColumns(#"Added Custom", {"Custom", each Text.Combine(List.Transform(_, Text.From), ", "), type text}),
    #"Removed Columns" = Table.RemoveColumns(#"Extracted Values",{"Count"}),
    #"Added Custom1" = Table.AddColumn(#"Removed Columns", "Custom.1", each "fixed"),
    #"Merged Columns" = Table.CombineColumns(#"Added Custom1",{"Value", "Custom", "Custom.1"},Combiner.CombineTextByDelimiter(" ", QuoteStyle.None),"Merged"),
    #"Grouped Rows1" = Table.Group(#"Merged Columns", {"File No"}, {{"Count", each _, type table}}),
    #"Added Custom2" = Table.AddColumn(#"Grouped Rows1", "Result", each List.Sort(List.Distinct(Table.Column([Count],"Merged")),Order.Ascending)),
    #"Extracted Values1" = Table.TransformColumns(#"Added Custom2", {"Result", each Text.Combine(List.Transform(_, Text.From), ", "), type text}),
    #"Removed Columns1" = Table.RemoveColumns(#"Extracted Values1",{"Count"})
in
    #"Removed Columns1"