How do I 'nudge' data over depending on a transit time

[mickyd67]

Hi all

I have a number of countries I ship product to and have 12 columns, one for each month, showing the dispatch quantity for that given month

Each country has a different transit time - some are 1 month, some can be 3 months etc

I want to have another 12 columns of data that shows the 'arrival month' based on the transit time.

Is there a way to do that so I can take the dispatch quantity in, say, April and have it show in May based on it being a 1 month transit time?

The April dispatch quantity is in Cell M7 and the arrival month should be in Cell AD7 (May)

 

[awoohaw]

I think this may work for you. But, it is pretty clunky, there maybe more succinct formulas.
It also requires a "Starting Date" cell. Which you could eliminate by putting a date calculation in each cell, but I find this easier.

Book3
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z
1	Starting Date:	2024-01-01
2	Country	Transit Time (Months)	Disp Qty Jan	Disp Qty Feb	Disp Qty Mar	Disp Qty Apr	Disp Qty May	Disp Qty Jun	Disp Qty Jul	Disp Qty Aug	Disp Qty Sep	Disp Qty Oct	Disp Qty Nov	Disp Qty Dec	Arriv Qty Jan	Arriv Qty Feb	Arriv Qty Mar	Arriv Qty Apr	Arriv Qty May	Arriv Qty Jun	Arriv Qty Jul	Arriv Qty Aug	Arriv Qty Sep	Arriv Qty Oct	Arriv Qty Nov	Arriv Qty Dec
3	UK	1	38	27	41	43	27	44	30	44	43	22	31	34		38	27	41	43	27	44	30	44	43	22	31
4	USA	2	27	32	34	43	50	34	38	23	34	32	44	28			38	27	41	43	27	44	30	44	43	22
5	AUS	3	25	33	27	37	37	45	20	34	27	39	27	24				38	27	41	43	27	44	30	44	43
Sheet2

Cell Formulas
Range	Formula
C2:N2	C2	="Disp Qty " & TEXT(EDATE($B$1,COLUMN()-3),"mmm")
O2:Z2	O2	="Arriv Qty " & TEXT(EDATE($B$1,COLUMN()-3),"mmm")
O3:Z5	O3	=IF(MONTH(EDATE($B$1,COLUMN()-3))-$B3<1,"",INDEX($C$3:$N$3,1,MONTH(EDATE($B$1,COLUMN()-3))-$B3))

 

[mickyd67]

Hi there - many thanks for this. I must admit I'm struggling a little and wonder if there are any other formulas / options out there to make this a bit easier?

 

[awoohaw]

My thought is to retool your worksheet and have a lookup table for your shipping times. and then create the data like this.
If you need a cross tab report you can build it off of this in another worksheet:

(I'm sure there are many different ways to do this, and I may do it differently if I looked at it tomorrow. the OFFSET function, though volatile is probably another tool to use.)

Book1
	A	B	C	D	E	F	G	H
1	Country	Month	Disp Qty	Arriv Qty			Country	Transit Time (Months)
2	UK	Jan	38				UK	1
3	UK	Feb	27	38			USA	2
4	UK	Mar	41	27			AUS	3
5	UK	Apr	43	41
6	UK	May	27	43
7	UK	Jun	44	27
8	UK	Jul	30	44
9	UK	Aug	44	30
10	UK	Sep	43	44
11	UK	Oct	22	43
12	UK	Nov	31	22
13	UK	Dec	34	31
14	USA	Jan	27
15	USA	Feb	32
16	USA	Mar	34	27
17	USA	Apr	43	32
18	USA	May	50	34
19	USA	Jun	34	43
20	USA	Jul	38	50
21	USA	Aug	23	34
22	USA	Sep	34	38
23	USA	Oct	32	23
24	USA	Nov	44	34
25	USA	Dec	28	32
26	AUS	Jan	25
27	AUS	Feb	33
28	AUS	Mar	27
29	AUS	Apr	37	25
30	AUS	May	37	33
31	AUS	Jun	45	27
32	AUS	Jul	20	37
33	AUS	Aug	34	37
34	AUS	Sep	27	45
35	AUS	Oct	39	20
36	AUS	Nov	27	34
37	AUS	Dec	24	27
Sheet3

Cell Formulas
Range	Formula
D2:D37	D2	=IF(MONTH(B2)<(XLOOKUP(A2,$G$2:$G$4,$H$2:$H$4,0)),"", TAKE(FILTER($A$2:$C$37, ($A$2:$A$37=$A2)* ($B$2:$B$37=EDATE($B2,-XLOOKUP(A2,$G$2:$G$4,$H$2:$H$4,0))),""),,-1))

 

[Edgar_]

wonder if there are any other formulas / options out there to make this a bit easier?

I wonder if you would find it easier if you turned it into a simple index/match search. The approach is as follows:
1. Months go from 1 to 12 to make it easy to calculate
2. Add a column with the transit time in months. Any number you enter there applies to the entire row.
3. In the arrival side, you enter an index/match formula. You want to return the value where
current month - arrival time in months = quantity in the shipping side
So if you have an arrival time of 1 months, and you entered a quantity in april, then:
april - 1 months = return quantity in march returns march's quantity
may - 1 months = return quantity in april returns april's quantity

That looks like this:

Libro1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH	AI	AJ	AK
1
2
3
4
5										Ene	Feb	Mar	Abr	May	Jun	Jul	Ago	Sep	Oct	Nov	Dic					Ene	Feb	Mar	Abr	May	Jun	Jul	Ago	Sep	Oct	Nov	Dic
6	Transit months									1	2	3	4	5	6	7	8	9	10	11	12					1	2	3	4	5	6	7	8	9	10	11	12
7	1												999													#N/A	0	0	0	999	0	0	0	0	0	0	0
8	2												999													#N/A	#N/A	0	0	0	999	0	0	0	0	0	0
9	2												999													#N/A	#N/A	0	0	0	999	0	0	0	0	0	0
10	2												999													#N/A	#N/A	0	0	0	999	0	0	0	0	0	0
11	3												999													#N/A	#N/A	#N/A	0	0	0	999	0	0	0	0	0
12	4												999													#N/A	#N/A	#N/A	#N/A	0	0	0	999	0	0	0	0
13	10												999													#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	0	0
No Month cycling

Cell Formulas
Range	Formula
Z7:AK13	Z7	=INDEX($J7:$U7,MATCH(Z$6-$A7,$J$6:$U$6,0))

However, we quickly encounter a problem. If the arrival starts in a new year, we run out of month columns, we need a way to cycle in a way that the month after december is january. You do that using MOD. So your formula should be slightly modified as follows:

Libro1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH	AI	AJ	AK
1
2
3
4
5										Ene	Feb	Mar	Abr	May	Jun	Jul	Ago	Sep	Oct	Nov	Dic					Ene	Feb	Mar	Abr	May	Jun	Jul	Ago	Sep	Oct	Nov	Dic
6	Transit months									1	2	3	4	5	6	7	8	9	10	11	12					1	2	3	4	5	6	7	8	9	10	11	12
7	1												999													0	0	0	0	999	0	0	0	0	0	0	0
8	2												999													0	0	0	0	0	999	0	0	0	0	0	0
9	2												999													0	0	0	0	0	999	0	0	0	0	0	0
10	2												999													0	0	0	0	0	999	0	0	0	0	0	0
11	3												999													0	0	0	0	0	0	999	0	0	0	0	0
12	4												999													0	0	0	0	0	0	0	999	0	0	0	0
13	10												999													0	999	0	0	0	0	0	0	0	0	0	0
Month cycling

Cell Formulas
Range	Formula
Z7:AK13	Z7	=INDEX($J7:$U7,MATCH(MOD(Z$6-$A7-1,12)+1,$J$6:$U$6,0))

 

[awoohaw]

@Edgar_ ,
As I said in my earlier post there are many way to accomplish this.
However, I'm a fan of keeping input data and row relevant calculated fields separate from reporting whenever possible.

Take a look at his video just posted yesterday by Minda Treacy at MyOnlineTrainingHub .

 

[Edgar_]

As I said in my earlier post there are many way to accomplish this.
However, I'm a fan of keeping input data and row relevant calculated fields separate from reporting whenever possible.

I call that "stop doing data entry on your reports". Excel users often fall in that trap. I'm sure I've talked about that in all the few threads I've responded, but the OP seems to have a working worksheet already.

He may just need a formula to nudge his quantities, so the approach is adequate.

Shall the worksheet design change, I'd suggest a worksheet with a ListObject table for the countries, another for the countries and transit times, another for the products, and another for the shipping, with calculated arrivals. A pivot table or something else would take care of the view.

 

[awoohaw]

in my world whenever possible reports don't exist before data entry.

I gave OP a suggested solution very similar to yours in POST #2, and OP responded by asking for something simpler.
Un-pivoting cross tab semi-reports into tablulated data is a lot simpler these days with Power Query.

All other reporting in many different views and perspectives is much easier.

 

[Edgar_]

You're free to suggest whatever you want, man. I don't have a problem with your suggestions, my approach was to just work within the OP's constraints the simplest I could imagine and a simple index/match is simpler than, say, having to learn the whole Power whatever there is in store right now.

 

[mickyd67]

Hi everyone - thanks for your help I'll have a look and see what one works best for me