Comparing data from two set of data, highlight blank cells, count and insert average

[Musto85]

Hi experts, I have a new challenge ahead of me and would be grateful you could help me with.

As per mini sheet below I have a set of data at the bottom (A35:U59) with cells filled with numbers in between certain time intervals.

What I would like to obtain is:

1. Comparing the data at the bottom (A35:U59) with the data at the top (A1:R25) ensuring the relevant columns are compared with the same label row i.e. LAR01 with LAR01, LAR06 with LAR06 etc…(as in between the two data sets some can be misplaced in different columns)
2. Highlight with colour (yellow in the example sheet) only where the corresponding time interval has no data (compared with the data at the bottom). i.e. LAR01 (B35:B59) has numbers between 6:00pm and 1:00 am (even zeros count as numbers). In the data at the top (A1:R25) in the same column with corresponding label LAR01, there are numbers missing at 9:00pm, 10:00pm and 01:00am.
3. Subsequently count those highlighted cells (blank cells) individually by column and place this count anywhere at the bottom of each column (B27,C27 etc..)
4. Finally, fill those highlighted cells with the average between the number in the row above and the number in the row below. (i.e. average between B16 and B19 = 3, average between B23 and B25 = 2 etc..

I’ve filled and highlighted as example until LAR07. As you will notice there might be additional columns at the bottom (A35:U59), those will need to be skipped as they're not needed.


Sorry for the explanation if it's confusing, please feel free to ask for more examples if needed.

Thank you in advance!!

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V
1	Label	LAR01	LAR05	LAR06	LAR07	LAR08	LAR09	LAR19	LBJ05	LBJ06	LBJ07	LBJ10	LBJ18	LPB01	LPB02	LPB08	LTCP01	LTCP02
2	6:00 AM				2	2					1			0			1
3	7:00 AM				0	0											1
4	8:00 AM				0	0					0			0			0
5	9:00 AM				2	2					1			0			0
6	10:00 AM				2	2					3			2			0
7	11:00 AM				5	5					3			2
8	12:00 PM			2	3	3					2			5			2
9	1:00 PM			2	5	4				3	2			6			2
10	2:00 PM			4	7	8			0	2	4		0	13	6		3
11	3:00 PM		3	5	6	2				3	3		0				1
12	4:00 PM		8	8	5					5			1	9	8
13	5:00 PM		8	11	5			1		5				14	3
14	6:00 PM	8	4	2	4	4		0		6	5						3
15	7:00 PM	4	9	8	8	9				2	6			11	15	2	6
16	8:00 PM	5	5	2	7	5	2		4	3	4			6	6	4	2
17	9:00 PM	3	4	3	6	1	3				3			2	4	8	4
18	10:00 PM	3	3	4	3	1			4	2	6	0	0	2	2	5	5
19	11:00 PM	0	4	3	6	2	2		3	0	5	5	1	5	4	4	2	1
20	12:00 AM	4	4	4	3	0	4		4	4	0	3	1	0	2	4	1
21	1:00 AM		5	5	3	2			4	5			2		4	4	3	3
22	2:00 AM		4	5	0				4	4			0		4	3	2	1
23	3:00 AM		4	4	2	0			2	2			0		6		1	1
24	4:00 AM		2	0	1	1			4	0					3		3	1
25	5:00 AM			0	0	0		1	4	1					6		0	0
26
27
28
29
30
31
32
33
34
35	Label	LAR01	LAR03	LAR05	LAR06	LAR07	LAR08	LAR09	LAR18	LAR19	LBJ05	LBJ06	LBJ07	LBJ09	LBJ10	LBJ18	LPB01	LPB02	LPB08	LTCP01	LTCP02
36	6:00 AM					1400	1550						65				0			0
37	7:00 AM					0	0						0				0			0
38	8:00 AM					0	0						0				0			0
39	9:00 AM					700	1210						510				0			0
40	10:00 AM					1050	1150						100				605			0
41	11:00 AM					1700	2420						550				100			100
42	12:00 PM				1650	800	1705						700				1410	0		1700
43	1:00 PM				1050	3030	1850					1045	350				2540			300
44	2:00 PM				2260	180	4240				100	910	270			0	2370	760		1000
45	3:00 PM			1260	11700	520	1240				150	820	920			0	400	1910		570
46	4:00 PM			3700	5800	3510	1200					2370	660			3000	1900	1570		600
47	5:00 PM			1890	4900	2180	1795			85		1640	640			0	2990	1530		250
48	6:00 PM	2910		940	795	1130	250	0		0		1735	1120				1450	415		1100
49	7:00 PM	2500		1540	1300	2800	1550					590	460				150	870	1600	80
50	8:00 PM	1050		750	1340	820	1730	3590			1555	765	165				530	400	3400	270
51	9:00 PM	850		1010	4315	1310	1860	1180			860	1115	440				1040	495	0	440
52	10:00 PM	2200	800	390	300	850	2100	3750			1020	840	750		850	0	3200	340	1000	210	950
53	11:00 PM	815	0	570	1310	3275	1590	400			520	300	295	0	300	300	50	950	300	360	1550
54	12:00 AM	360		895	3360	6100	730	1845			510	490	785		450	1100	50	85	0	1300	3950
55	1:00 AM	320		725	1370	4155	3835	1950			770	310	811		200	150	0	1070	0	40	400
56	2:00 AM			1930	2215	4735	140				1000	1510			0	80		145	0	410	0
57	3:00 AM			700	3700	220	450				880	960				0		455	0	1100	0
58	4:00 AM		0	0	2560	810	750		0	0	1200	550	0	0				100		0	0
59	5:00 AM				850	455	0			2950	0	0	0			0	0	70		0	0
60
Sheet1

 

[Musto85]

Forgot to say...

I'm currently using the code below that works only on the data set at the top by detecting for each column first and last row with numbers, highlights the blank cells in between, puts the count of highlighted cells at the bottom and inserts the average rounded up to the nearest whole.

Sub test()
    Dim a, i As Long
    With Cells(1).CurrentRegion
        ReDim a(1 To .Columns.Count - 1)
        For i = 2 To .Columns.Count
            CountBlanks .Columns(i), .Columns(i).Cells(.Rows.Count + 2)
        Next
    End With
End Sub

Sub CountBlanks(r As Range, rr As Range)
    Dim x, y, myAreas As Areas, c As Range
    x = r.Parent.Evaluate("address(min(if(isnumber(" & r.Address & "),row(" & r.Address & ")))," & r.Column & ")")
    y = r.Parent.Evaluate("address(max(if(isnumber(" & r.Address & "),row(" & r.Address & ")))," & r.Column & ")")
    If Not IsError(x) Then
        rr = r.Parent.Evaluate("countblank(" & x & ":" & y & ")")
        If x <> y Then
            On Error Resume Next
            Set myAreas = r.Parent.Range(x, y).SpecialCells(4).Areas
            On Error GoTo 0
            If Not myAreas Is Nothing Then
                For Each c In myAreas
                    c.Interior.Color = vbRed
                    c.Value = Fix((c(0) + c(c.Count + 1)) / 2)
                Next
            End If
        End If
    End If
End Sub