Find the longest string of uncoloured cells

[Marvo]

Is it possible to create a formula to find the longest string of uncoloured cells?

The result I'm after are shown in Column AC and row 82. I've put the longest string in bold to show where they are.

Many thanks.

Suduko.xlsx
	S	T	U	V	W	X	Y	Z	AA	AB	AC
67	2824	2925-2926	3015-3017	3160-3162	3260	3353	3483	3579	3673		2
68	2828	2928	3019	3163	3263	3354	3486	3593	3674		2
69	2831	2930	3021	3168	3264	3355	3488-3489	3595	3675		3
70	2836-2838	2931	3022	3169	3269	3357	3492	3596-3597	3676		2
71	2839	2932	3024-3025	3170-3172	3270	3358	3496	3600	3677		3
72	2840	2933	3035-3036	3173	3271	3359	3497	3601	3679		3
73	2841	2935	3038	3174	3272	3361	3498	3602	3680		4
74	2842	2936	3040	3182-3184	3273	3362-3367	3499	3603	3684		3
75	2843	2937	3042-3043	3189	3276	3369	3500-35011	3605	3685		2
76	2844	2938	3045-3046	3190	3279	3370	3502	3607	3686		4
77	2845	2939	3048-3049	3194-3195	3281	3373	3505	3610	3688		5
78	2846	2940	3050	3196	3284	3374	3506	3611	3689		4
79	2847-2848	2941	3051-3052	3198-3200	3286	3384	3508-3510	3612	3693		2
80	2850	2942	3053	3201	3287	3386	3512-3513	3613	3694		4
81
82	3	4	2	2	4	3	5	4	5
Hard

 

[JEC]

You can't use formula's based on color. You will need VBA unless you can specify the condition for getting the green cells

 

[Marvo]

Well the condition is I've completed the problems (Suduko) in the green cells, the uncoloured cells have still to be solved. Is there something else I could use instead of colour to define those cells solved that could be used in a formula? Note: The sheet is obviously a lot bigger than the snippet I posted on here.

 

[Peter_SSs]

Is there something else I could use instead of colour to define those cells solved that could be used in a formula?

Yes prefix/suffix the cells with a distinctive character. I have prefixed with "x"
(If you want them coloured as well you could use conditional formatting for that as I have done below. Then as you add the "x" prefix to additional cells they will automatically change to green and the "longest string" formulas will automatically update)

Marvo.xlsm
	S	T	U	V	W	X	Y	Z	AA	AB	AC
67	2824	x2925-2926	x3015-3017	x3160-3162	3260	3353	x3483	3579	3673		2
68	2828	x2928	3019	3163	x3263	x3354	x3486	3593	3674		2
69	2831	2930	3021	x3168	3264	3355	x3488-3489	3595	3675		3
70	x2836-2838	2931	x3022	3169	3269	x3357	3492	x3596-3597	x3676		2
71	2839	x2932	x3024-3025	x3170-3172	3270	3358	3496	x3600	3677		3
72	x2840	2933	x3035-3036	3173	3271	x3359	3497	3601	3679		3
73	2841	2935	x3038	x3174	x3272	3361	3498	3602	3680		4
74	2842	2936	3040	x3182-3184	3273	x3362-3367	3499	3603	x3684		3
75	x2843	2937	x3042-3043	3189	x3276	3369	x3500-35011	3605	3685		2
76	2844	x2938	x3045-3046	3190	3279	3370	3502	x3607	3686		4
77	2845	2939	x3048-3049	x3194-3195	3281	3373	3505	3610	3688		5
78	2846	2940	3050	3196	x3284	x3374	x3506	3611	3689		4
79	x2847-2848	x2941	x3051-3052	x3198-3200	3286	3384	x3508-3510	3612	3693		2
80	2850	2942	3053	3201	x3287	3386	x3512-3513	x3613	x3694		4
81
82	3	4	2	2	4	3	5	4	5
Sheet1

Cell Formulas
Range	Formula
AC67:AC80	AC67	=MAX(FREQUENCY(IF(LEFT(S67:AA67,1)<>"x",COLUMN(S67:AA67)),IF(LEFT(S67:AA67,1)="x",COLUMN(S67:AA67))))
S82:AA82	S82	=MAX(FREQUENCY(IF(LEFT(S67:S80,1)<>"x",ROW(S67:S80)),IF(LEFT(S67:S80,1)="x",ROW(S67:S80))))

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
S67:AA80	Expression	=LEFT(S67,1)="x"	text	NO

 

[Marvo]

Thank you very much Peter. I've amended that to include a ✔ on the right of each completed cell. Works a treat.

 

[Peter_SSs]

You're welcome. Glad you could adapt it to something that suited you. Thanks for letting us know.

 

[Peter_SSs]

BTW, those formulas can be shortened a bit if your want. For my data format ..

Marvo.xlsm
	S	T	U	V	W	X	Y	Z	AA	AB	AC
67	2824	x2925-2926	x3015-3017	x3160-3162	3260	3353	x3483	3579	3673		2
68	2828	x2928	3019	3163	x3263	x3354	x3486	3593	3674		2
69	2831	2930	3021	x3168	3264	3355	x3488-3489	3595	3675		3
70	x2836-2838	2931	x3022	3169	3269	x3357	3492	x3596-3597	x3676		2
71	2839	x2932	x3024-3025	x3170-3172	3270	3358	3496	x3600	3677		3
72	x2840	2933	x3035-3036	3173	3271	x3359	3497	3601	3679		3
73	2841	2935	x3038	x3174	x3272	3361	3498	3602	3680		4
74	2842	2936	3040	x3182-3184	3273	x3362-3367	3499	3603	x3684		3
75	x2843	2937	x3042-3043	3189	x3276	3369	x3500-35011	3605	3685		2
76	2844	x2938	x3045-3046	3190	3279	3370	3502	x3607	3686		4
77	2845	2939	x3048-3049	x3194-3195	3281	3373	3505	3610	3688		5
78	2846	2940	3050	3196	x3284	x3374	x3506	3611	3689		4
79	x2847-2848	x2941	x3051-3052	x3198-3200	3286	3384	x3508-3510	3612	3693		2
80	2850	2942	3053	3201	x3287	3386	x3512-3513	x3613	x3694		4
81
82	3	4	2	2	4	3	5	4	5
Sheet1

Cell Formulas
Range	Formula
AC67:AC80	AC67	=LET(d,S67:AA67,p,LEFT(d,1),c,COLUMN(d),MAX(FREQUENCY(IF(p<>"x",c),IF(p="x",c))))
S82:AA82	S82	=LET(d,S67:S80,p,LEFT(d,1),r,ROW(d),MAX(FREQUENCY(IF(p<>"x",r),IF(p="x",r))))

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
S67:AA80	Expression	=LEFT(S67,1)="x"	text	NO

 

[Marvo]

Thanks again. I'm not really sure about the LET function operates but I can see how the first formula offered works so I'll stick with that. Cheers.

 

[Peter_SSs]

OK, no problem.