Help required for Analysing large data in a pivot table

[Scrumpywill]

hello could anyone help with this please

i have a large customer list and I want to analyse which customers are down on spend month by month year I’ve year and show on a graph

i can do all of the above but the graph is too busy and it’s hard to see the results- out of the list I only want to see the customers that are down this year compared to last year

thank for your help

cheers steve

 

[oldbrewer]

[TABLE="width: 711"]
<colgroup><col><col span="10"></colgroup><tbody>[TR]
[TD][/TD]
[TD]cust1[/TD]
[TD]cust2[/TD]
[TD]cust3[/TD]
[TD]cust4[/TD]
[TD]cust5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]01/01/2017[/TD]
[TD="align: right"]1859[/TD]
[TD="align: right"]2663[/TD]
[TD="align: right"]232[/TD]
[TD="align: right"]4094[/TD]
[TD="align: right"]1171[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]01/02/2017[/TD]
[TD="align: right"]886[/TD]
[TD="align: right"]1110[/TD]
[TD="align: right"]969[/TD]
[TD="align: right"]1430[/TD]
[TD="align: right"]3344[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]01/03/2017[/TD]
[TD="align: right"]2264[/TD]
[TD="align: right"]3707[/TD]
[TD="align: right"]4933[/TD]
[TD="align: right"]84[/TD]
[TD="align: right"]4309[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]01/04/2017[/TD]
[TD="align: right"]2310[/TD]
[TD="align: right"]2330[/TD]
[TD="align: right"]4429[/TD]
[TD="align: right"]1368[/TD]
[TD="align: right"]1406[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]01/05/2017[/TD]
[TD="align: right"]2294[/TD]
[TD="align: right"]1639[/TD]
[TD="align: right"]4362[/TD]
[TD="align: right"]1579[/TD]
[TD="align: right"]4951[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]01/06/2017[/TD]
[TD="align: right"]3297[/TD]
[TD="align: right"]684[/TD]
[TD="align: right"]2354[/TD]
[TD="align: right"]1893[/TD]
[TD="align: right"]2460[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]01/07/2017[/TD]
[TD="align: right"]4547[/TD]
[TD="align: right"]123[/TD]
[TD="align: right"]612[/TD]
[TD="align: right"]3330[/TD]
[TD="align: right"]3700[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]01/08/2017[/TD]
[TD="align: right"]2680[/TD]
[TD="align: right"]2172[/TD]
[TD="align: right"]3435[/TD]
[TD="align: right"]652[/TD]
[TD="align: right"]288[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]01/09/2017[/TD]
[TD="align: right"]4863[/TD]
[TD="align: right"]4853[/TD]
[TD="align: right"]547[/TD]
[TD="align: right"]1505[/TD]
[TD="align: right"]1498[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]01/10/2017[/TD]
[TD="align: right"]4377[/TD]
[TD="align: right"]578[/TD]
[TD="align: right"]3634[/TD]
[TD="align: right"]4521[/TD]
[TD="align: right"]246[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]01/11/2017[/TD]
[TD="align: right"]1277[/TD]
[TD="align: right"]2712[/TD]
[TD="align: right"]2308[/TD]
[TD="align: right"]545[/TD]
[TD="align: right"]32[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]01/12/2017[/TD]
[TD="align: right"]1580[/TD]
[TD="align: right"]191[/TD]
[TD="align: right"]181[/TD]
[TD="align: right"]1707[/TD]
[TD="align: right"]539[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]01/01/2018[/TD]
[TD="align: right"]78[/TD]
[TD="align: right"]4110[/TD]
[TD="align: right"]4811[/TD]
[TD="align: right"]3349[/TD]
[TD="align: right"]1279[/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 2"]78 3349 red[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]01/02/2018[/TD]
[TD="align: right"]370[/TD]
[TD="align: right"]618[/TD]
[TD="align: right"]3458[/TD]
[TD="align: right"]4521[/TD]
[TD="align: right"]2165[/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 2"]370 618 2165 red[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]01/03/2018[/TD]
[TD="align: right"]4034[/TD]
[TD="align: right"]3448[/TD]
[TD="align: right"]4013[/TD]
[TD="align: right"]1590[/TD]
[TD="align: right"]4402[/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 2"]3448 4013 red[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]01/04/2018[/TD]
[TD="align: right"]1387[/TD]
[TD="align: right"]1390[/TD]
[TD="align: right"]4640[/TD]
[TD="align: right"]2420[/TD]
[TD="align: right"]2375[/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 2"]1387 1390 red[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]01/05/2018[/TD]
[TD="align: right"]4761[/TD]
[TD="align: right"]2830[/TD]
[TD="align: right"]3906[/TD]
[TD="align: right"]1002[/TD]
[TD="align: right"]4946[/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 2"]3906 1002 4946 red[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]01/06/2018[/TD]
[TD="align: right"]4738[/TD]
[TD="align: right"]4641[/TD]
[TD="align: right"]2121[/TD]
[TD="align: right"]3672[/TD]
[TD="align: right"]1500[/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 2"]2121 1500 red[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]01/07/2018[/TD]
[TD="align: right"]716[/TD]
[TD="align: right"]2180[/TD]
[TD="align: right"]1571[/TD]
[TD="align: right"]1235[/TD]
[TD="align: right"]2894[/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 2"]716 1235 2894 red[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="colspan: 7"]comparing a month with the same month last year is statistically suspect[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="colspan: 6"]you need to determine if sales figures are varying NORMALLY[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="colspan: 4"]or if there are SPECIAL CAUSES in play[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="colspan: 6"]the technique that can be used is statistical process control[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="colspan: 6"]since I generated the numbers using the RAND function[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="colspan: 4"]none are really significantly different[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="colspan: 6"]if you post a few real sets of data I will analyse them for you[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[Scrumpywill]

Hello,

unable to share the data due to GDPR

 

[oldbrewer]

you could anonymise them a bit eg customer1 customer2

 

[Scrumpywill]

you could anonymise them a bit eg customer1 customer2

Hello,

If I have the customers in column A and years in b , c,d would you be able to sort a formula to compare the 3 values and highlight the sales drop off in column c with is year 2017/2018

thanks again Steve

 

[oldbrewer]

[TABLE="width: 487"]
<colgroup><col><col><col><col span="4"></colgroup><tbody>[TR]
[TD]customer[/TD]
[TD]2016_2017[/TD]
[TD]2017_2018[/TD]
[TD]marker[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]cust1[/TD]
[TD="align: right"]608[/TD]
[TD="align: right"]9966[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]cust2[/TD]
[TD="align: right"]513[/TD]
[TD="align: right"]8829[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]cust3[/TD]
[TD="align: right"]4838[/TD]
[TD="align: right"]4601[/TD]
[TD]x[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]cust4[/TD]
[TD="align: right"]6639[/TD]
[TD="align: right"]4892[/TD]
[TD]x[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]cust5[/TD]
[TD="align: right"]4798[/TD]
[TD="align: right"]6535[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]cust6[/TD]
[TD="align: right"]7591[/TD]
[TD="align: right"]4318[/TD]
[TD]x[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]cust7[/TD]
[TD="align: right"]4752[/TD]
[TD="align: right"]9532[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]cust8[/TD]
[TD="align: right"]7551[/TD]
[TD="align: right"]1994[/TD]
[TD]x[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]cust9[/TD]
[TD="align: right"]2036[/TD]
[TD="align: right"]9655[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]cust10[/TD]
[TD="align: right"]7274[/TD]
[TD="align: right"]9624[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="colspan: 2"]is this what you mean[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="colspan: 4"]the formula would be in cond formatting of course[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="colspan: 3"]so cust3 , 4, 6 and 8 would be red[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[Scrumpywill]

[MENTION][/MENTION]

[TABLE="width: 487"]
<tbody>[TR]
[TD]customer[/TD]
[TD]2016_2017[/TD]
[TD]2017_2018[/TD]
[TD]marker[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]cust1[/TD]
[TD="align: right"]608[/TD]
[TD="align: right"]9966[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]cust2[/TD]
[TD="align: right"]513[/TD]
[TD="align: right"]8829[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]cust3[/TD]
[TD="align: right"]4838[/TD]
[TD="align: right"]4601[/TD]
[TD]x[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]cust4[/TD]
[TD="align: right"]6639[/TD]
[TD="align: right"]4892[/TD]
[TD]x[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]cust5[/TD]
[TD="align: right"]4798[/TD]
[TD="align: right"]6535[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]cust6[/TD]
[TD="align: right"]7591[/TD]
[TD="align: right"]4318[/TD]
[TD]x[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]cust7[/TD]
[TD="align: right"]4752[/TD]
[TD="align: right"]9532[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]cust8[/TD]
[TD="align: right"]7551[/TD]
[TD="align: right"]1994[/TD]
[TD]x[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]cust9[/TD]
[TD="align: right"]2036[/TD]
[TD="align: right"]9655[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]cust10[/TD]
[TD="align: right"]7274[/TD]
[TD="align: right"]9624[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="colspan: 2"]is this what you mean[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="colspan: 4"]the formula would be in cond formatting of course[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="colspan: 3"]so cust3 , 4, 6 and 8 would be red[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Spot on that would be very helpful

cheers Steve

 

[oldbrewer]

cond formatting formula in A2

=C2<B2

paint it down the column