Alternate column alert

R

Raph85

New Member
Joined
Dec 20, 2015
Messages
26
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
BCDEFGHIJKL
Products
Batteries
Pencils
Books
Calculators
Pens

<tbody>
[TD="align: center"]4[/TD]

[TD="align: center"]1/01/2020[/TD]
[TD="align: center"][/TD]
[TD="align: center"]2/01/2020[/TD]
[TD="align: center"][/TD]
[TD="align: center"]3/01/2020[/TD]
[TD="align: center"][/TD]
[TD="align: center"]4/01/2020[/TD]
[TD="align: center"][/TD]
[TD="align: center"]5/01/2020[/TD]
[TD="align: center"][/TD]

[TD="align: center"]5[/TD]
[TD="align: right"][/TD]
[TD="align: center"]Sold[/TD]
[TD="align: center"]Available[/TD]
[TD="align: center"]Sold[/TD]
[TD="align: center"]Available[/TD]
[TD="align: center"]Sold[/TD]
[TD="align: center"]Available[/TD]
[TD="align: center"]Sold[/TD]
[TD="align: center"]Available[/TD]
[TD="align: center"]Sold[/TD]
[TD="align: center"]Available[/TD]

[TD="align: center"]6[/TD]

[TD="align: right"]25[/TD]
[TD="align: right"]25[/TD]
[TD="align: right"]30[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]22[/TD]
[TD="align: right"]28[/TD]
[TD="align: right"]48[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]46[/TD]
[TD="align: right"]4[/TD]

[TD="align: center"]7[/TD]

[TD="align: right"]45[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]45[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]15[/TD]
[TD="align: right"]35[/TD]
[TD="align: right"]35[/TD]
[TD="align: right"]15[/TD]
[TD="align: right"]47[/TD]
[TD="align: right"]3[/TD]

[TD="align: center"]8[/TD]

[TD="align: right"]10[/TD]
[TD="align: right"]30[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]30[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]30[/TD]
[TD="align: right"]15[/TD]
[TD="align: right"]25[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]20[/TD]

[TD="align: center"]9[/TD]

[TD="align: right"][/TD]
[TD="align: right"]30[/TD]
[TD="align: right"]28[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]18[/TD]
[TD="align: right"]12[/TD]
[TD="align: right"]30[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]28[/TD]
[TD="align: right"]2[/TD]

[TD="align: center"]10[/TD]

[TD="align: right"]5[/TD]
[TD="align: right"]45[/TD]
[TD="align: right"][/TD]
[TD="align: right"]50[/TD]
[TD="align: right"]15[/TD]
[TD="align: right"]35[/TD]
[TD="align: right"][/TD]
[TD="align: right"]50[/TD]
[TD="align: right"][/TD]
[TD="align: right"]50[/TD]

</tbody>


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

http://Excel 2016 (Windows) 32 bit
BCDEFGHIJKL
Alert!!! 02/01/2020,04/01/2020,05/01/2020
Products
Batteries
Pencils
Books
Calculators
Pens

<tbody>
[TD="align: center"]3[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]

[TD="align: center"]1/01/2020[/TD]
[TD="align: center"][/TD]
[TD="align: center"]2/01/2020[/TD]
[TD="align: center"][/TD]
[TD="align: center"]3/01/2020[/TD]
[TD="align: center"][/TD]
[TD="align: center"]4/01/2020[/TD]
[TD="align: center"][/TD]
[TD="align: center"]5/01/2020[/TD]
[TD="align: center"][/TD]

[TD="align: center"]5[/TD]
[TD="align: right"][/TD]
[TD="align: center"]Sold[/TD]
[TD="align: center"]Available[/TD]
[TD="align: center"]Sold[/TD]
[TD="align: center"]Available[/TD]
[TD="align: center"]Sold[/TD]
[TD="align: center"]Available[/TD]
[TD="align: center"]Sold[/TD]
[TD="align: center"]Available[/TD]
[TD="align: center"]Sold[/TD]
[TD="align: center"]Available[/TD]

[TD="align: center"]6[/TD]

[TD="align: right"]25[/TD]
[TD="align: right"]25[/TD]
[TD="align: right"]30[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]22[/TD]
[TD="align: right"]28[/TD]
[TD="align: right"]48[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]46[/TD]
[TD="align: right"]4[/TD]

[TD="align: center"]7[/TD]

[TD="align: right"]45[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]45[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]15[/TD]
[TD="align: right"]35[/TD]
[TD="align: right"]35[/TD]
[TD="align: right"]15[/TD]
[TD="align: right"]47[/TD]
[TD="align: right"]3[/TD]

[TD="align: center"]8[/TD]

[TD="align: right"]10[/TD]
[TD="align: right"]30[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]30[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]30[/TD]
[TD="align: right"]15[/TD]
[TD="align: right"]25[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]20[/TD]

[TD="align: center"]9[/TD]

[TD="align: right"][/TD]
[TD="align: right"]30[/TD]
[TD="align: right"]28[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]18[/TD]
[TD="align: right"]12[/TD]
[TD="align: right"]30[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]28[/TD]
[TD="align: right"]2[/TD]

[TD="align: center"]10[/TD]

[TD="align: right"]5[/TD]
[TD="align: right"]45[/TD]
[TD="align: right"][/TD]
[TD="align: right"]50[/TD]
[TD="align: right"]15[/TD]
[TD="align: right"]35[/TD]
[TD="align: right"][/TD]
[TD="align: right"]50[/TD]
[TD="align: right"][/TD]
[TD="align: right"]50[/TD]

</tbody>



 
Last edited:

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.
Sort by date Sort by votes
In your sample you have 5 dates across row 4. How many are there, or might there be, in your actual data?
 
Upvote 0
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
 
Upvote 0
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)

Code: 
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
BCDEFGHIJKLMN
3Alert!! 2/01/2020, 4/01/2020, 5/01/2020
4Products1/01/20202/01/20203/01/20204/01/20205/01/20206/01/2020
5SoldAvailableSoldAvailableSoldAvailableSoldAvailableSoldAvailableSoldAvailable
6Batteries252530202228482464
7Pencils45545515353515473
8Books10301030103015252020
9Calculators302821812300282
10Pens5455015355050
Alert
 
Upvote 0
Hi Peter,

Thank you so much that's great!

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

Thanks
 
Upvote 0
Raph85 said:
Hi Peter,

Thank you so much that's great!
Click to expand...
You're welcome.


Raph85 said:
just wondering would there be an option without using the VBA ?
Click to expand...
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
BCDEFGHIJKLMN
3Alert!! 2/01/2020, 4/01/2020, 5/01/2020 2/01/20204/01/20205/01/2020
4Products1/01/20202/01/20203/01/20204/01/20205/01/20206/01/2020
5SoldAvailableSoldAvailableSoldAvailableSoldAvailableSoldAvailableSoldAvailable
6Batteries252530202228482464
7Pencils45545515353515473
8Books10301030103015252020
9Calculators302821812300282
10Pens5455015355050
Alert (2)
 
Upvote 0
You must log in or register to reply here.

Similar threads

O
  • Solved
Put amount in DEBIT,CREDIT columns based on matching part of item
Replies
9
Views
185
Omran Y
O
MrDB4Excel
  • Solved
Get the Correct Month
2
Replies
10
Views
140
Peter_SSs
Peter_SSs
D
  • Solved
Add content to empty cells in column - macro
Replies
6
Views
77
AFZAL SOHAIL
A
A
  • Solved
Track movement stock in listbox on userform by copy amounts from previous row to next row
Replies
8
Views
93
abdo meghari
A
D
  • Question Question
Copy range based on criteria in column - macro
2 3
Replies
24
Views
405
doriannjeshi
D

Forum statistics

Threads
1,223,893
Messages
6,175,240
Members
452,621
Latest member
Laura_PinksBTHFT

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top