VBA - IF formula with data that changes weekly

D

darkybot

New Member
Joined
Feb 13, 2022
Messages
16
Office Version
  1. 365
Platform
  1. Windows
Hi,

As the title says, I'm trying to figure it out a macro that will detect if there is a change from red to green (awesome if it can be the other way around as well). This data weekly. The result should drop down through the column.
If there is any more info needed, please let me know.

The alert is the result of the macro that I need.

Here is an example of what I need.

WK41WK41WK42WK42WK43WK43Alert
0red50green45green"Back to stock WK42"
0red0red60green"Back to stock WK43"

Thanks!
 

Attachments

  • Excel_ifmaybe.png
    Excel_ifmaybe.png
    14.9 KB · Views: 12

Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.
Sort by date Sort by votes
You could use a simple formula:

ABCDEFGHIJKLMNOP
1Wk 1Wk 1Wk 2Wk 2Wk 3Wk 3Wk 4Wk 4Wk 5Wk 5Wk 6Wk 6Red->GreenGreen->Red
2BlahBlahAmountColourAmountColourAmountColourAmountColourAmountColourAmountColour
3BlahBlahRedRedRedGreenGreenRedWk 4Wk 6
4BlahBlahRedGreenRedGreenGreenRedWk 2,Wk 4Wk 3,Wk 6
5BlahBlahGreenGreenGreenGreenGreenGreen
6BlahBlahGreenGreenRedRedRedRedWk 3
7
Sheet1
Cell Formulas
RangeFormula
O3:O6O3=BYROW(IF((D3:L6="Red")*(F3:N6="Green"),F1:N1,""),LAMBDA(r,TEXTJOIN(",",1,r)))
P3:P6P3=BYROW(IF((D3:L6="Green")*(F3:N6="Red"),F1:N1,""),LAMBDA(r,TEXTJOIN(",",1,r)))
Dynamic array formulas.
 
Upvote 0
Thanks for the reply!
I tried and didn't work, it gaves me an N/A or Spill error message
 
Upvote 0
Did you know you can click here to copy to the clipboard, so that you can paste directly into A1 of a blank worksheet?
1697762445272.png


If you can't get the formula working, can you please post your layout using the XL2BB add-in?
 
Upvote 0
Joe_test2 - Try - Copy.xlsm
AFAGAHAIAJAKALAMANAOAPAQARASATAUAVAWAXAYAZBA
1202341202342202343202344202345202346202347202348202349202350Red->GreenGreen->Red
249.3GREEN46GREEN43.1GREEN40GREEN36.5GREEN32.2GREEN23.2GREEN14GREEN50.9GREEN42.1GREEN#SPILL!
3#SPILL!
417.7GREEN15.6GREEN13.7GREEN11.5GREEN9GREEN6.7GREEN4.5GREEN50.4GREEN48.3GREEN46.3GREEN#SPILL!
5347.9BLUE347.8BLUE347.7BLUE347.6BLUE347.5BLUE346.8BLUE343.8BLUE340.7BLUE337.8BLUE335.1BLUE#SPILL!
683.3GREEN73.4GREEN64.7GREEN55.1GREEN43.9GREEN128.1BLUE110.7BLUE92.8GREEN75GREEN57.7GREEN#SPILL!
768.9BLUE67.9BLUE67BLUE66BLUE64.8BLUE63.7BLUE62.7BLUE61.7BLUE60.8BLUE59.9BLUE#SPILL!
8#SPILL!
955.7GREEN53.5GREEN51.6GREEN49.5GREEN47GREEN44.7GREEN42.6GREEN40.5GREEN38.4GREEN36.3GREEN#SPILL!
1048.7GREEN38.1GREEN28.7GREEN66.1GREEN53.5GREEN40.8GREEN24.1GREEN54.9GREEN37.6GREEN20.8GREEN#SPILL!
11252.5GREEN191.9GREEN138.8GREEN311.8GREEN242.5GREEN176.8GREEN105.6GREEN266.3GREEN194.8GREEN125.2GREEN#SPILL!
1237GREEN31.3GREEN26.3GREEN20.7GREEN14.1GREEN72.1GREEN66.9GREEN61.8GREEN56.6GREEN51.6GREEN#SPILL!
13#SPILL!
1423.4GREEN21GREEN48.9GREEN46.6GREEN44GREEN40.9GREEN35.1GREEN29.1GREEN23.2GREEN47.6GREEN#SPILL!
15#SPILL!
1626.9GREEN26.8GREEN26.7GREEN26.6GREEN26.5GREEN25.1GREEN18.5GREEN41.5GREEN34.6GREEN28GREEN#SPILL!
1716.9GREEN14.9GREEN13.1GREEN11.1GREEN8.8GREEN6.8GREEN5GREEN3.3GREEN43.8GREEN42.4GREEN#SPILL!
18873.2GREEN703.1GREEN552.7GREEN383.7GREEN855.7GREEN670.4GREEN492.2GREEN987.9GREEN811GREEN637.6GREEN#SPILL!
198.7GREEN6.5GREEN4.5GREEN44.3GREEN41.7GREEN39.2GREEN36.9GREEN34.6GREEN32.3GREEN30.1GREEN#SPILL!
2037.2GREEN28.1GREEN20.1GREEN11.1GREEN96.5GREEN87.3GREEN81.5GREEN76GREEN70.4GREEN64.8GREEN#SPILL!
2142.8GREEN92.6GREEN86.2GREEN79.6GREEN66.4GREEN111.2BLUE101.2BLUE96.7GREEN92.4GREEN88.2GREEN#SPILL!
22#SPILL!
2312.6GREEN11.3GREEN10.1GREEN8.8GREEN7.2GREEN5.8GREEN4.5GREEN3.3GREEN50.2GREEN49.2GREEN#SPILL!
24307.9GREEN296.1GREEN285.9GREEN274.9GREEN262.3GREEN251.1GREEN241.6GREEN232.5GREEN223.6GREEN215.1GREEN#SPILL!
25#SPILL!
26487.4BLUE477.5BLUE468.7BLUE458.9BLUE447.5BLUE437BLUE427.1BLUE417.4BLUE407.7BLUE398.3BLUE#SPILL!
2788.9BLUE80.5BLUE73.1GREEN64.8GREEN55.1GREEN45.3GREEN32.6GREEN19.6GREEN70.5GREEN57.7GREEN#SPILL!
2815.6GREEN52.9GREEN43.5GREEN33GREEN20.8GREEN57.4GREEN45.8GREEN34.2GREEN22.8GREEN59.8GREEN#SPILL!
2937.7GREEN24.3GREEN108.5GREEN95.2GREEN79.6GREEN65.5GREEN53.6GREEN41.9GREEN30.2GREEN18.8GREEN#SPILL!
3097.1GREEN91.8GREEN87.3GREEN82.5GREEN77GREEN72.3GREEN68.9GREEN65.7GREEN62.6GREEN59.6GREEN#SPILL!
3137.3GREEN76.5GREEN54.7GREEN30.5GREEN66.3GREEN39.3GREEN73.3GREEN43.2GREEN77.2GREEN47.9GREEN#SPILL!
32300.2BLUE297.6BLUE295.4BLUE293BLUE290.3BLUE287.3BLUE282.4BLUE277.5BLUE272.8BLUE268.5BLUE#SPILL!
33373.7GREEN354.6GREEN337.6GREEN318.3GREEN295.3GREEN273.7GREEN251.7GREEN229.8GREEN208GREEN186.9GREEN#SPILL!
34103.7GREEN221.9GREEN184.5GREEN142GREEN91.4GREEN204.5GREEN159.6GREEN115GREEN70.2GREEN186.5GREEN#SPILL!
Data
Cell Formulas
RangeFormula
AZ2AZ2=BYROW(IF((AI2:AW17549="Red")*(AK2:AY17549="Green"),AI1:AY1,""),LAMBDA(r,TEXTJOIN(",",1,r)))
AZ3:AZ34AZ3=BYROW(IF((AI3:AW17550="Red")*(AK3:AY17550="Green"),AH2:AY2,""),LAMBDA(r,TEXTJOIN(",",1,r)))
Cells with Conditional Formatting
CellConditionCell FormatStop If True
AF2:AY17549Cell Value="Blue"textYES
AF2:AY17549Cell Value="Green"textYES
AF2:AY17549Cell Value="Red"textYES
AF2:AY17549Cell Value="Yellow"textYES


As you can see it's either Spill or N/A, must be a very simple error on my end that I may be overthinking.
Points to take in consideration:
- Week headers are merged. (only this is merge)
- The 2nd week starts from AI ends in AY
- Range goes from AI2 to AY17443 (this may vary)
 
Upvote 0
Try:

(Blue --> Green) AZ2: =BYROW(IF((AG2:AW17443="Blue")*(AI2:AY17443="Green"),AH1:AX1,""),LAMBDA(r,TEXTJOIN(",",1,r)))
(Green --> Blue) BA2: =BYROW(IF((AG2:AW17443="Green")*(AI2:AY17443="Blue"),AH1:AX1,""),LAMBDA(r,TEXTJOIN(",",1,r)))r)))

Both formulae will spill down the column. You don't need formulae in rows 3 to 17,443.
 
Upvote 1
Solution
StephenCrump said:
Try:

(Blue --> Green) AZ2: =BYROW(IF((AG2:AW17443="Blue")*(AI2:AY17443="Green"),AH1:AX1,""),LAMBDA(r,TEXTJOIN(",",1,r)))
(Green --> Blue) BA2: =BYROW(IF((AG2:AW17443="Green")*(AI2:AY17443="Blue"),AH1:AX1,""),LAMBDA(r,TEXTJOIN(",",1,r)))r)))

Both formulae will spill down the column. You don't need formulae in rows 3 to 17,443.
Click to expand...
How can I convert this to a macro?
 
Upvote 0
You must log in or register to reply here.

Similar threads

A
  • Question
VBA to change cell colour based on content
Replies
3
Views
193
Snakehips
Snakehips
G
  • Question
Converting and copying specific data
Replies
1
Views
146
GerrardSVK
G
D
  • Question
Importing data from web source
Replies
0
Views
656
Devvy
D
H
  • Question
Adjust an existing formula based on most recently added column and prior month end date.
Replies
1
Views
118
healeydm
H
K
  • Question
How to find stock data that Excel can't match to it's data source provider?
Replies
0
Views
66
kcmuppet
K

Forum statistics

Threads
1,215,165
Messages
6,123,391
Members
449,098
Latest member
ArturS75

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