Formula to enter a specific text into each row of another column until the reference item changes, then enter no data until the specific text is ident

mp0691

New Member
Joined
Jan 19, 2014
Messages
18
I need a formula that will enter a specific text in column B, row after row until when a specific date is in row A. When a row in column A has a different date then the specific text, then in needs to leave the cell in column B blank until the specific date appears again.

1626805973320.png
 

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
13,504
Office Version
  1. 365
Platform
  1. Windows
Noting that column A is off of the side of the sheet, I'm going to assume that 1/9/2021 is the specific date referred to.

Your question as it stands is to vague for anyone to provide you with a useful answer but from observations on your screen capture, I would expect the layout to make the task difficult.
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
58,912
Office Version
  1. 365
Platform
  1. Windows
MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in. It would be great if you could post your data using this "XL2BB" tool, which will show the cell addresses and formulas.

You should post two images, one of your raw data, and the other of your expected results based on that raw data.
 

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
13,504
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Unlikely that it is what is needed but best guess without more information, assuming first date in A2, first formula in B2 with US date format.
Excel Formula:
=IFERROR(IF(LOOKUP(1e+100,A$2:A2/(A$2:A2>=DATE(2020,1,1)))=DATE(2021,1,9),"Specific Text",""),"")
Note that 1/1/2020 has been used in the formula as the earliest possible date to find in the column (adjust as needed) without this there is no way to separate the dates from the times and other numeric values in the same column.
 

mp0691

New Member
Joined
Jan 19, 2014
Messages
18
MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in. It would be great if you could post your data using this "XL2BB" tool, which will show the cell addresses and formulas.

You should post two images, one of your raw data, and the other of your expected results based on that raw data.
Book4
AIAJ
1Column33Column34
2
3
4
5
61/9/2021
7 
8
9MONTUE
101010
111010
12
13
14
15
162020
170:0006:30:39
180:0016:30:04
19L A B O R C O D E a n d H O U R S
20337336
211010
2299
2311
241010
251010
01-09-2021
Cell Formulas
RangeFormula
AJ7AJ7=IFERROR(IF(LOOKUP(1E+100,AI$6:AI7/(A$6:AI7>=DATE(2021,1,9)))=DATE(2021,1,9),"Specific Text",""),"")
 

mp0691

New Member
Joined
Jan 19, 2014
Messages
18
Book4
AIAJ
1Column33Column34
2
3
4
5
61/9/2021
7 
8
9MONTUE
101010
111010
12
13
14
15
162020
170:0006:30:39
180:0016:30:04
19L A B O R C O D E a n d H O U R S
20337336
211010
2299
2311
241010
251010
01-09-2021
Cell Formulas
RangeFormula
AJ7AJ7=IFERROR(IF(LOOKUP(1E+100,AI$6:AI7/(A$6:AI7>=DATE(2021,1,9)))=DATE(2021,1,9),"Specific Text",""),"")
When the date (1/9/21) appears in the left column, I want the same date in each cell of the right column until the date changes to a different date,. Then I want the formula to leave the cell blank until the 1/9/21 date appears again in the left column.
 

mp0691

New Member
Joined
Jan 19, 2014
Messages
18
When the date (1/9/21) appears in the left column, I want the same date in each cell of the right column until the date changes to a different date,. Then I want the formula to leave the cell blank until the 1/9/21 date appears again in the left column.
Also, I believe that the date might not always be in a date format. Sometimes a number or text.
 

Forum statistics

Threads
1,140,924
Messages
5,703,180
Members
421,280
Latest member
Jaycee01

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
Top