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 can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.
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.
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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",""),"")
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,551
Messages
6,114,273
Members
448,559
Latest member
MrPJ_Harper

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