Consolidate Table To Remove Blank values month by month

[asharris90]

Hello,

having a real challenge trying to solve this so hope someone can help. I have the attached table and what I now want to do is consolidate the table into a new table so that it removes any customer rows that have 0’s within the whole year period. So only keeping customers that have an order value at some point in the year ensuring to return their order value in the same month.
Finding it hard to describe what I mean so I hope this makes sense and thanks in advance.

 

[sandy666]

something like this?

 

[sandy666]

or post expected result

 

[ManiacB]

Try this code on a COPY of your worksheet

Sub ConsRemBlnk()
Dim i, j, lr As Long

lr = Cells(Rows.Count, "B").End(xlUp).Row

For i = lr To 5 Step -1
    If WorksheetFunction.Sum(Range("C" & i & ":N" & i)) = 0 Then
        Cells(i, 1).EntireRow.Delete
    End If
Next i

End Sub

 

[asharris90]

something like this?

View attachment 25269

Yes exactly like that. Any ideas?

 

[sandy666]

use Power Query (Get&Transform)

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Leading0 = Table.AddColumn(Source, "Customer", each Text.PadStart(Text.Select([BP], {"0".."9"}),2,"0")),
    Customer = Table.AddColumn(Leading0, "Custom", each Text.Select([BP], {"a".."z","A".."Z", " ", "-"})),
    Replace = Table.ReplaceValue(Customer,"00","",Replacer.ReplaceText,{"Customer"}),
    RC = Table.RemoveColumns(Replace,{"BP"}),
    TCC = Table.CombineColumns(RC,{"Custom", "Customer"},Combiner.CombineTextByDelimiter("", QuoteStyle.None),"BP"),
    Unpivot = Table.UnpivotOtherColumns(TCC, {"BP"}, "Attribute", "Value"),
    Filter = Table.SelectRows(Unpivot, each ([Value] <> 0))
in
    Filter

then Close&Load

 

[sandy666]

I forgot to say, you'll need to change your source Table a bit because of your double headers

BP	April 2020	May 2020	June 2020	July 2020	August 2020	Septembe 2020	October 2020	November 2020	December 2020	January 2021	February 2021	March 2021
Customer 1	0	0	0	0	0	0	0	0	0	0	0	0
Customer 2	0	0	0	0	0	0	0	0	0	0	0	0
Customer 3	0	0	0	0	0	0	0	0	0	0	0	0
Customer 4	0	0	0	0	0	0	0	0	0	0	0	0
Customer 5	0	0	0	0	0	0	0	0	0	0	0	0
Customer 6	0	0	0	0	0	0	0	0	0	0	0	0
Customer 7	45000	0	0	0	0	0	0	0	0	0	0	0
Customer 8	0	30000	0	0	0	0	0	0	0	0	0	0
Customer 9	0	0	0	34000	0	0	0	0	0	0	0	0
Customer 10	0	0	0	0	0	350000	0	0	0	0	0	0
Customer 11	0	0	0	0	0	15000	0	0	0	0	0	0
Customer 12	0	0	0	0	0	317000	0	0	0	0	0	0
Customer 13	0	0	0	0	0	0	30000	0	0	0	0	0
Customer 14	0	0	0	0	0	0	90000	0	0	0	0	0
Customer 15	0	0	0	0	0	0	0	90000	0	0	0	0
Customer 16	0	0	0	0	0	0	0	734000	0	0	0	0
Customer 17	0	0	0	0	0	0	0	0	65000	0	0	0
Sagentia Ltd - C	0	0	0	0	0	0	0	0	0	360000	0	0

 

[asharris90]

use Power Query (Get&Transform)

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Leading0 = Table.AddColumn(Source, "Customer", each Text.PadStart(Text.Select([BP], {"0".."9"}),2,"0")),
    Customer = Table.AddColumn(Leading0, "Custom", each Text.Select([BP], {"a".."z","A".."Z", " ", "-"})),
    Replace = Table.ReplaceValue(Customer,"00","",Replacer.ReplaceText,{"Customer"}),
    RC = Table.RemoveColumns(Replace,{"BP"}),
    TCC = Table.CombineColumns(RC,{"Custom", "Customer"},Combiner.CombineTextByDelimiter("", QuoteStyle.None),"BP"),
    Unpivot = Table.UnpivotOtherColumns(TCC, {"BP"}, "Attribute", "Value"),
    Filter = Table.SelectRows(Unpivot, each ([Value] <> 0))
in
    Filter

then Close&Load

Perfect thank you

 

[sandy666]

You are welcome