auto repeat by date

[JOEE1979]

I have numbers in "column B"
there is a date in "column J"
is it possible to make the number in "B" auto repeat back into column "B" but with the date in column "J"?
I hope I'm making sense.

I'm hoping this can be done without VBA, but if I don't have a choice, then VBA is ok I guess.

Thank you

schedule 4 tracker.xlsx
	A	B	C	D	E	F	G	H	I	J
3	Date	Unit	Oil	WMA	Other	Other2	Scheduled Date	Start Date	End Date	Expired Date
4	1-Jan-24	8545					1-Jan-24			31-Jan-24
5		2654					1-Jan-24			31-Jan-24
6		8564					1-Jan-24			31-Jan-24
7		3695					1-Jan-24			31-Jan-24
8		7458					1-Jan-24			31-Jan-24
9		7485					1-Jan-24			31-Jan-24
10		5245					1-Jan-24			31-Jan-24
11		1564					1-Jan-24			31-Jan-24
12		9565					1-Jan-24			31-Jan-24
13		6656					1-Jan-24			31-Jan-24
14		2136					1-Jan-24			31-Jan-24
15		3256					1-Jan-24			31-Jan-24
16		2154					1-Jan-24			31-Jan-24
17		3416					1-Jan-24			31-Jan-24
18		8461					1-Jan-24			31-Jan-24
Master

Cell Formulas
Range	Formula
G4	G4	=A4
G5	G5	=A4
G6	G6	=A4
G7	G7	=A4
G8	G8	=A4
G9	G9	=A4
G10	G10	=A4
G11	G11	=A4
G12	G12	=A4
G13	G13	=A4
G14	G14	=A4
G15	G15	=A4
G16	G16	=A4
G17	G17	=A4
G18	G18	=A4
J4:J18	J4	=[@[Scheduled Date]]+30

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
C18:F31	Other Type	Icon set		NO
C4:F17	Other Type	Icon set		NO
A4:A5493	Expression	=($L$2=A4)	text	NO
B4:B5493	Expression	=AND($L$2=G4, (ISBLANK(I4)))	text	NO
B4:B5493	Expression	=AND($L$2>G4, (ISBLANK(I4)))	text	NO
B4:B5493	Expression	=AND($L$2>G4, NOT(ISBLANK(I4)))	text	NO

Cells with Data Validation
Cell	Allow	Criteria
B3	Any value
F3	Any value
G3	Any value
H3	Any value
I3:J3	Any value

 

[awoohaw]

What value in J would need to be there for you to recalculate B?

 

[JOEE1979]

What value in J would need to be there for you to recalculate B?

Column "A" is a date, this goes on for the remainder of the year.
in this example column "B" unit 8545 -> column "J" says 31Jan24
So I would like unit 8545 to reappear in column "B" where column "A" is 31Jan24

hope this clears it up better

 

[awoohaw]

you have merged cells in Column A. and different values in Column B. How are you determining which value in Column B to use?
Do you want to make all the column B values for the same date the same as the first record in column B for that date?


Or are you asking to fill the values down for 1/31/24 based upon the row sequence ? Do you have the same number of records for each date?

A larger sample of disparate rows of values in A and J would be very helpful.

(Also, using merged cells in worksheet formula references is not a best practice, formulas are easily corrupted, any solution I give you will have column A values unmerged. If that is a hard must for you, please say so, and I'll stop working on this).

 

[JOEE1979]

you have merged cells in Column A. and different values in Column B. How are you determining which value in Column B to use?
Do you want to make all the column B values for the same date the same as the first record in column B for that date?


Or are you asking to fill the values down for 1/31/24 based upon the row sequence ? Do you have the same number of records for each date?

A larger sample of disparate rows of values in A and J would be very helpful.

(Also, using merged cells in worksheet formula references is not a best practice, formulas are easily corrupted, any solution I give you will have column A values unmerged. If that is a hard must for you, please say so, and I'll stop working on this).

- all of column "B" would work, not jut a few
so basically I would have column "B" repeat itself every 30 days (in the same order for all the units)
- column "A" must stay the way it is, however column we can use column "G" as a reference date

schedule 4 tracker.xlsx
	A	B	C	D	E	F	G	H	I	J
3	Date	Unit	Oil	WMA	Other	Other2	Scheduled Date	Start Date	End Date	Expired Date
4	1-Jan-24	1231					1-Jan-24			31-Jan-24
5		351					1-Jan-24			31-Jan-24
6		51651					1-Jan-24			31-Jan-24
7		5165					1-Jan-24			31-Jan-24
8		21211					1-Jan-24			31-Jan-24
9		2121					1-Jan-24			31-Jan-24
10		4656					1-Jan-24			31-Jan-24
11		351					1-Jan-24			31-Jan-24
12		78546					1-Jan-24			31-Jan-24
13		5642					1-Jan-24			31-Jan-24
14		7452					1-Jan-24			31-Jan-24
15		85687					1-Jan-24			31-Jan-24
16		7896					1-Jan-24			31-Jan-24
17		1234					1-Jan-24			31-Jan-24
18		77778					1-Jan-24			31-Jan-24
19	2-Jan-24	7418					2-Jan-24			1-Feb-24
20		8528					2-Jan-24			1-Feb-24
21		9639					2-Jan-24			1-Feb-24
22		4100					2-Jan-24			1-Feb-24
23		523					2-Jan-24			1-Feb-24
24		6542					2-Jan-24			1-Feb-24
25		2315					2-Jan-24			1-Feb-24
26		9874					2-Jan-24			1-Feb-24
27		321					2-Jan-24			1-Feb-24
28		74569					2-Jan-24			1-Feb-24
29		21855					2-Jan-24			1-Feb-24
30		32186					2-Jan-24			1-Feb-24
31		9666					2-Jan-24			1-Feb-24
32		23012					2-Jan-24			1-Feb-24
33		87465					2-Jan-24			1-Feb-24
Master

Cell Formulas
Range	Formula
G4,G19	G4	=A4
G5,G20	G5	=A4
G6,G21	G6	=A4
G7,G22	G7	=A4
G8,G23	G8	=A4
G9,G24	G9	=A4
G10,G25	G10	=A4
G11,G26	G11	=A4
G12,G27	G12	=A4
G13,G28	G13	=A4
G14,G29	G14	=A4
G15,G30	G15	=A4
G16,G31	G16	=A4
G17,G32	G17	=A4
G18,G33	G18	=A4
J4:J33	J4	=[@[Scheduled Date]]+30

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
C32:F45	Other Type	Icon set		NO
C18:F31	Other Type	Icon set		NO
C4:F17	Other Type	Icon set		NO
A4:A5493	Expression	=($L$2=A4)	text	NO
B4:B5493	Expression	=AND($L$2=G4, (ISBLANK(I4)))	text	NO
B4:B5493	Expression	=AND($L$2>G4, (ISBLANK(I4)))	text	NO
B4:B5493	Expression	=AND($L$2>G4, NOT(ISBLANK(I4)))	text	NO

Cells with Data Validation
Cell	Allow	Criteria
B3	Any value
F3	Any value
G3	Any value
H3	Any value
I3:J3	Any value

 

[JOEE1979]

you have merged cells in Column A. and different values in Column B. How are you determining which value in Column B to use?
Do you want to make all the column B values for the same date the same as the first record in column B for that date?


Or are you asking to fill the values down for 1/31/24 based upon the row sequence ? Do you have the same number of records for each date?

A larger sample of disparate rows of values in A and J would be very helpful.

(Also, using merged cells in worksheet formula references is not a best practice, formulas are easily corrupted, any solution I give you will have column A values unmerged. If that is a hard must for you, please say so, and I'll stop working on this).

Hello, Just wondering if you looked at the most recent reply I put. Thanks

 

[JOEE1979]

Hello, Just wondering if you looked at the most recent reply I put. Thanks

Nevermind, I just got it going, thanks anyways

 

[awoohaw]

Nevermind, I just got it going, thanks anyways

sorry, i wasn't able to work on it. But, happy to know you figured it out.
But, you should post your solution so if other people come to the forum with a similar question they can find your approach.


Best Wishes!

 

[JOEE1979]

on the 30th day I used =(B4) and followed down the list