Hits, misses and false alarms monthly summary

[drefiek2]

I need help creating a 'verification' monthly summary table and I will try to explain what I want using an example.
I have a spreadsheet with dates in column B starting from row 6 (1st Jan 2025 - 31st Dec 2025), these dates are merged cells over two rows, so rather than 365 rows there are 730 rows.
I have cities in Europe along row 5 columns D to J.
For each date I have 'Sunny Forecast' and 'Observation', what someone will do is forecast whether it will be sunny in those cities on that date and either put a 0 for yes (green flag) or a 1 for no (red flag).
The next day they will verify whether it was sunny or not, by again putting a 0 or 1.
If both are 0 for that date then it is a 'hit', if both are 1 for that date then it is also a 'hit', if the forecast is a 0 but the observation is a 1 then it is a 'miss', if the forecast is a 1 but the observation is a 0 then it is a 'false alarm'. This probably sounds strange but it is just an example!
Essentially what I want to be able to do is in a blank table on another sheet, select a month from a drop down box above the table, and then for the table to auto-populate whether for each date and for each city it was a hit, miss or false alarm, by automatically entering either a H, M or FA in the cell. Again I'd like cities along the top and dates along the side.
I suppose there needs to be an IF formula (with some ISNUMBER statements too) checking whether cells are equal, more than or less than 0 or 1? Or is there a better way to do it, I've got no idea, so any help would be very much appreciated.
Hopefully that makes sense, I've attached a screenshot of the top of the spreadsheet for reference.

 

[drefiek2]

A slight update for that, look for this part of the code and add the blank entry. Please cross check the code in box. {Also you can try the formula above}
View attachment 123059

If IsEmpty(forecastVal) Or IsEmpty(observationVal) Then
                        result = ""  ' Blank entry
                   
                    ElseIf IsNumeric(forecastVal) And IsNumeric(observationVal) Then
                        If forecastVal = 0 And observationVal = 0 Then
                            result = "H"

Thanks very much, it works

 

[drefiek2]

If using your 365 version, would you be interested in a formula approach? Could probably be adapted if you need it to work in your 2021 version as well.
(This is a smaller data size but easily expandable to your full data ranges.)

drefiek2.xlsm
	B	C	D	E	F	G	H	I	J
5	Date	Type	London	Berlin	Paris	Madrid	Athens	Lisbon	Rome
6	1/01/2025	Sunny Forecast	0	0	1	1	0	1	1
7		Observation	1	0	1	0	0	1	1
8	2/01/2025	Sunny Forecast	0	1	0	0	1	0	0
9		Observation	1	1	0	1	1	0	1
10	3/02/2025	Sunny Forecast	1	1	0		1	1	0
11		Observation	1	0			0	0	1
12	4/02/2025	Sunny Forecast	0	0	1	1	0		1
13		Observation	1	0	1	0	0	1	1
14	5/02/2025	Sunny Forecast	0	1	0	0	1	0	0
15		Observation	1	1	0	1	1	0	1
16	3/03/2025	Sunny Forecast	1	1	0	0	1	1	0
17		Observation	1	0	1	1	0	0	1
Sheet1

Cell A1 below would be the month drop-down.

drefiek2.xlsm
	A	B	C	D	E	F	G	H
1	February
2
3		London	Berlin	Paris	Madrid	Athens	Lisbon	Rome
4	3/02/2025	H	FA			FA	FA	M
5	4/02/2025	M	H	H	FA	H		H
6	5/02/2025	M	H	H	M	H	H	M
7
Sheet2

Cell Formulas
Range	Formula
B3:H3	B3	=Sheet1!D5:J5
A4:H6	A4	=LET(b,Sheet1!B6:B17,x,Sheet1!D6:J17,y,IF(x="",2,x),d,TOCOL(IF(b="",1/0,IF(TEXT(b,"mmmm")=A1,b,1/0)),2),q,MATCH(A1,TEXT(b,"mmmm"),0)+SEQUENCE(ROWS(d),,,2),HSTACK(d,IFNA(SWITCH(--(CHOOSEROWS(y,q-1)&CHOOSEROWS(y,q)),0,"H",1,"M",10,"FA",11,"H"),"")))
Dynamic array formulas.

Thanks a lot, a good solution as well, nice to have options!! Much appreciated!!

 

[drefiek2]

If using your 365 version, would you be interested in a formula approach? Could probably be adapted if you need it to work in your 2021 version as well.
(This is a smaller data size but easily expandable to your full data ranges.)

drefiek2.xlsm
	B	C	D	E	F	G	H	I	J
5	Date	Type	London	Berlin	Paris	Madrid	Athens	Lisbon	Rome
6	1/01/2025	Sunny Forecast	0	0	1	1	0	1	1
7		Observation	1	0	1	0	0	1	1
8	2/01/2025	Sunny Forecast	0	1	0	0	1	0	0
9		Observation	1	1	0	1	1	0	1
10	3/02/2025	Sunny Forecast	1	1	0		1	1	0
11		Observation	1	0			0	0	1
12	4/02/2025	Sunny Forecast	0	0	1	1	0		1
13		Observation	1	0	1	0	0	1	1
14	5/02/2025	Sunny Forecast	0	1	0	0	1	0	0
15		Observation	1	1	0	1	1	0	1
16	3/03/2025	Sunny Forecast	1	1	0	0	1	1	0
17		Observation	1	0	1	1	0	0	1
Sheet1

Cell A1 below would be the month drop-down.

drefiek2.xlsm
	A	B	C	D	E	F	G	H
1	February
2
3		London	Berlin	Paris	Madrid	Athens	Lisbon	Rome
4	3/02/2025	H	FA			FA	FA	M
5	4/02/2025	M	H	H	FA	H		H
6	5/02/2025	M	H	H	M	H	H	M
7
Sheet2

Cell Formulas
Range	Formula
B3:H3	B3	=Sheet1!D5:J5
A4:H6	A4	=LET(b,Sheet1!B6:B17,x,Sheet1!D6:J17,y,IF(x="",2,x),d,TOCOL(IF(b="",1/0,IF(TEXT(b,"mmmm")=A1,b,1/0)),2),q,MATCH(A1,TEXT(b,"mmmm"),0)+SEQUENCE(ROWS(d),,,2),HSTACK(d,IFNA(SWITCH(--(CHOOSEROWS(y,q-1)&CHOOSEROWS(y,q)),0,"H",1,"M",10,"FA",11,"H"),"")))
Dynamic array formulas.

How do I adapt the Sheet1!B6:B735 and Sheet1!D6:J735 parts of the LET formula so that they reference a cell containing a drop down list with the names of different sheets? Is it an INDIRECT formula perhaps, although I'm not sure exactly what it needs to be

 

[Peter_SSs]

How do I adapt the Sheet1!B6:B735 and Sheet1!D6:J735 parts of the LET formula so that they reference a cell containing a drop down list with the names of different sheets? Is it an INDIRECT formula perhaps, although I'm not sure exactly what it needs to be

With the sheet name in, say, A2 try ..

drefiek2.xlsm
	A	B	C	D	E	F	G	H
1	February
2	Sheet1
3		London	Berlin	Paris	Madrid	Athens	Lisbon	Rome
4	3/02/2025	H	FA			FA	FA	M
5	4/02/2025	M	H	H	FA	H		H
6	5/02/2025	M	H	H	M	H	H	M
7
Sheet2

Cell Formulas
Range	Formula
B3:H3	B3	=INDIRECT("'"&A2&"'!D5:J5")
A4:H6	A4	=LET(b,INDIRECT("'"&A2&"'!B6:B1000"),x,INDIRECT("'"&A2&"'!D6:J1000"),y,IF(x="",2,x),d,TOCOL(IF(b="",1/0,IF(TEXT(b,"mmmm")=A1,b,1/0)),2),q,MATCH(A1,TEXT(b,"mmmm"),0)+SEQUENCE(ROWS(d),,,2),HSTACK(d,IFNA(SWITCH(--(CHOOSEROWS(y,q-1)&CHOOSEROWS(y,q)),0,"H",1,"M",10,"FA",11,"H"),"")))
Dynamic array formulas.

 

[drefiek2]

With the sheet name in, say, A2 try ..

drefiek2.xlsm
	A	B	C	D	E	F	G	H
1	February
2	Sheet1
3		London	Berlin	Paris	Madrid	Athens	Lisbon	Rome
4	3/02/2025	H	FA			FA	FA	M
5	4/02/2025	M	H	H	FA	H		H
6	5/02/2025	M	H	H	M	H	H	M
7
Sheet2

Cell Formulas
Range	Formula
B3:H3	B3	=INDIRECT("'"&A2&"'!D5:J5")
A4:H6	A4	=LET(b,INDIRECT("'"&A2&"'!B6:B1000"),x,INDIRECT("'"&A2&"'!D6:J1000"),y,IF(x="",2,x),d,TOCOL(IF(b="",1/0,IF(TEXT(b,"mmmm")=A1,b,1/0)),2),q,MATCH(A1,TEXT(b,"mmmm"),0)+SEQUENCE(ROWS(d),,,2),HSTACK(d,IFNA(SWITCH(--(CHOOSEROWS(y,q-1)&CHOOSEROWS(y,q)),0,"H",1,"M",10,"FA",11,"H"),"")))
Dynamic array formulas.

Thanks very much it works!

 

[Peter_SSs]

Cheers. You are welcome. Thanks for confirming.

 

[Peter_SSs]

So, if you wanted a shorter vba approach you could use the features of the formula to do it without any looping.
Included in the code is an adjustment to check how many columns and how many rows are used in the relevant data sheet.

Sub HMFA()
  Dim wsData As Worksheet
  Dim sMonth As String, DataRng As String
  Dim lr As Long, lc As Long
  
  With Sheets("Summary")
    sMonth = .Range("A1").Value
    Set wsData = Sheets(.Range("A2").Value)
    With wsData
      lr = .Range("C" & Rows.Count).End(xlUp).Row
      lc = .Cells(5, Columns.Count).End(xlToLeft).Column
      DataRng = .Range("D6").Resize(lr - 5, lc - 3).Address(0, 0)
    End With
    .UsedRange.Offset(2).ClearContents
    wsData.Range("D5").Resize(, lc - 3).Copy Destination:=.Range("B3")
    .Range("A4").Formula2 = Replace(Replace(Replace(Replace("=LET(b,INDIRECT(""'#'!B6:B%""),x,INDIRECT(""'#'!^""),y,IF(x="""",2,x),d," _
        & "TOCOL(IF(b="""",1/0,IF(TEXT(b,""mmmm"")=""@"",b,1/0)),2),q,MATCH(""@"",TEXT(b,""mmmm""),0)+SEQUENCE(ROWS(d),,,2)," _
        & "HSTACK(d,IFNA(SWITCH(--(CHOOSEROWS(y,q-1)&CHOOSEROWS(y,q)),0,""H"",1,""M"",10,""FA"",11,""H""),"""")))", _
        "#", wsData.Name), "%", lr), "^", DataRng), "@", sMonth)
    .Range("A4").SpillingToRange.Value = .Range("A4").SpillingToRange.Value
  End With
End Sub

 

[drefiek2]

So, if you wanted a shorter vba approach you could use the features of the formula to do it without any looping.
Included in the code is an adjustment to check how many columns and how many rows are used in the relevant data sheet.

Sub HMFA()
  Dim wsData As Worksheet
  Dim sMonth As String, DataRng As String
  Dim lr As Long, lc As Long
 
  With Sheets("Summary")
    sMonth = .Range("A1").Value
    Set wsData = Sheets(.Range("A2").Value)
    With wsData
      lr = .Range("C" & Rows.Count).End(xlUp).Row
      lc = .Cells(5, Columns.Count).End(xlToLeft).Column
      DataRng = .Range("D6").Resize(lr - 5, lc - 3).Address(0, 0)
    End With
    .UsedRange.Offset(2).ClearContents
    wsData.Range("D5").Resize(, lc - 3).Copy Destination:=.Range("B3")
    .Range("A4").Formula2 = Replace(Replace(Replace(Replace("=LET(b,INDIRECT(""'#'!B6:B%""),x,INDIRECT(""'#'!^""),y,IF(x="""",2,x),d," _
        & "TOCOL(IF(b="""",1/0,IF(TEXT(b,""mmmm"")=""@"",b,1/0)),2),q,MATCH(""@"",TEXT(b,""mmmm""),0)+SEQUENCE(ROWS(d),,,2)," _
        & "HSTACK(d,IFNA(SWITCH(--(CHOOSEROWS(y,q-1)&CHOOSEROWS(y,q)),0,""H"",1,""M"",10,""FA"",11,""H""),"""")))", _
        "#", wsData.Name), "%", lr), "^", DataRng), "@", sMonth)
    .Range("A4").SpillingToRange.Value = .Range("A4").SpillingToRange.Value
  End With
End Sub

Thanks, appreciated very much

 

[Peter_SSs]

Cheers. Glad to contribute.