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

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.
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

D
  • Question Question
How to Copy rows from one sheet to another based on the criteria in the second sheet
Replies
9
Views
177
dsubash
D
M
  • Question Question
Formula help
Replies
2
Views
104
Peter_SSs
Peter_SSs
D
  • Question Question
Help with Lookup
Replies
4
Views
58
Sergius
S
O
  • Solved
Put amount in DEBIT,CREDIT columns based on matching part of item
Replies
9
Views
207
Omran Y
O
D
  • Question Question
Number format color cells
Replies
6
Views
68
doriannjeshi
D

Forum statistics

Threads
1,224,823
Messages
6,181,176
Members
453,021
Latest member
Justyna P

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