Alternate column alert

[Raph85]

Hello,



I have a data, basically to count product availability per day. I want to put an alert or something like that on the first 2 column for example in column B, that if there's any of the product on any given day is less than 5 then it will list all the dates that are on low availability.

I tried to use Countif and If formula but it doesn't work as the availability column are alternate between sold column. Can anyone help me to find the best way to solve this please. Thanks


http://Excel 2016 (Windows) 32 bit

	B	C	D	E	F	G	H	I	J	K	L
4	Products	1/01/2020		2/01/2020		3/01/2020		4/01/2020		5/01/2020
5		Sold	Available	Sold	Available	Sold	Available	Sold	Available	Sold	Available
6	Batteries	25	25	30	20	22	28	48	2	46	4
7	Pencils	45	5	45	5	15	35	35	15	47	3
8	Books	10	30	10	30	10	30	15	25	20	20
9	Calculators		30	28	2	18	12	30	0	28	2
10	Pens	5	45		50	15	35		50		50

<tbody>
</tbody>

I want it to come have the alert as per below on column B3. something like that.

http://Excel 2016 (Windows) 32 bit

	B	C	D	E	F	G	H	I	J	K	L
3	Alert!!! 02/01/2020,04/01/2020,05/01/2020
4	Products	1/01/2020		2/01/2020		3/01/2020		4/01/2020		5/01/2020
5		Sold	Available	Sold	Available	Sold	Available	Sold	Available	Sold	Available
6	Batteries	25	25	30	20	22	28	48	2	46	4
7	Pencils	45	5	45	5	15	35	35	15	47	3
8	Books	10	30	10	30	10	30	15	25	20	20
9	Calculators		30	28	2	18	12	30	0	28	2
10	Pens	5	45		50	15	35		50		50

<tbody>
</tbody>

 

[Peter_SSs]

In your sample you have 5 dates across row 4. How many are there, or might there be, in your actual data?

 

[Raph85]

Hi Peter,

This will be for the whole month as it would be recorded monthly. So the date is going to be from 1 - 31.

Thanks

 

[Peter_SSs]

You could try this user-defined function. To implement ..
1. Right click the sheet name tab and choose "View Code".
2. In the Visual Basic window use the menu to Insert|Module
3. Copy and Paste the code below into the main right hand pane that opens at step 2.
4. Close the Visual Basic window.
5. Enter the formula as shown in the screen shot below.
6. Your workbook will need to be saved as a macro-enabled workbook (*.xlsm)

Function CheckAlert(rData As Range, sHeader As String, dBelow As Double) As String
  Dim Col As Range
  Dim rws As Long
  
  sHeader = LCase(sHeader)
  rws = rData.Rows.Count - 2
  For Each Col In rData.Columns
    If LCase(Col.Cells(2).Value) = sHeader Then
      If WorksheetFunction.CountIf(Col.Offset(2).Resize(rws), "<" & dBelow) > 0 Then CheckAlert = CheckAlert & ", " & Col.Cells(1, 0).Text
    End If
  Next Col
  If Len(CheckAlert) Then CheckAlert = "Alert!!" & Mid(CheckAlert, 2)
End Function

Excel Workbook
	B	C	D	E	F	G	H	I	J	K	L	M	N
3	Alert!! 2/01/2020, 4/01/2020, 5/01/2020
4	Products	1/01/2020		2/01/2020		3/01/2020		4/01/2020		5/01/2020		6/01/2020
5		Sold	Available	Sold	Available	Sold	Available	Sold	Available	Sold	Available	Sold	Available
6	Batteries	25	25	30	20	22	28	48	2	46	4
7	Pencils	45	5	45	5	15	35	35	15	47	3
8	Books	10	30	10	30	10	30	15	25	20	20
9	Calculators		30	28	2	18	12	30	0	28	2
10	Pens	5	45		50	15	35		50		50
Alert

 

[Raph85]

Hi Peter,

Thank you so much that's great!

just wondering would there be an option without using the VBA ?

Thanks

 

[Peter_SSs]

Hi Peter,

Thank you so much that's great!

You're welcome.

just wondering would there be an option without using the VBA ?

If we can use a helper row? (I've used the rest of row 3)
If you don't like what is visible in C3:BL3 then you could either ..
- Format that range with white font so the dates are not visible, or
- use a different row and hide that row once your formulas have been inserted.

Formula in C3 is copied across to BL

Excel Workbook
	B	C	D	E	F	G	H	I	J	K	L	M	N
3	Alert!! 2/01/2020, 4/01/2020, 5/01/2020				2/01/2020				4/01/2020		5/01/2020
4	Products	1/01/2020		2/01/2020		3/01/2020		4/01/2020		5/01/2020		6/01/2020
5		Sold	Available	Sold	Available	Sold	Available	Sold	Available	Sold	Available	Sold	Available
6	Batteries	25	25	30	20	22	28	48	2	46	4
7	Pencils	45	5	45	5	15	35	35	15	47	3
8	Books	10	30	10	30	10	30	15	25	20	20
9	Calculators		30	28	2	18	12	30	0	28	2
10	Pens	5	45		50	15	35		50		50
Alert (2)