Find time lap between 3 stores sold part code.

[motilulla]

I have 3 stores an important part code

1st store code are from 61 to 130, which are sold in to column "B" from range B4 to below.

2nd store code are from 80 to 150, which are sold in to column "C" from range C4 to below.

3rd store code are from 110 to 180, which are sold in to column "D" from range D4 to below.

My query is to find out part code time lap from the previous code sold within 3 stores.

I want time lap results in the columns F, G and H

Say for example if part is sold first time the time lap will be = 0
Next time if it is sold in the any of 3 columns B, c and D it will count Time Lap from 1st sold code. Next time will count time lap from latest sold code

Please see the attached imaged in which I have highlighted 3 numbers as an example number 89, 108 and 140 may help

If it is not clearer please ask a question I will try my best to answer and clarify it.

For a single store Time Lap query is solved here please refer the link below

Find time lap between sold part code lists.

Hello, I have an important part code lists from code 61 to 130 which as sold enter in the column "B" from range B4 to below. My query is to find out part code time lap from the previous code sold. Say for example if part is sold first time lap = 0 to have idea we take example of part code 66...

www.mrexcel.com

*	A	B	C	D	E	F	G	H
1						Time Lap	Time Lap	Time Lap
2		Store1	Store2	Store3		Among 3 Stores	Among 3 Stores	Among 3 Stores
3		Parts Code Sold	Parts Code Sold	Parts Code Sold		Store1	Store2	Store3
4		63	89	110		0	0	0
5		66	110	165		0	1	0
6		108	120	136		0	0	0
7		105	130	162		0	0	0
8		113	140	159		0	0	0
9		63	130	151		5	2	0
10		85	108	114		0	4	0
11		110	124	132		6	0	0
12		63	136	152		3	6	0
13		110	130	170		2	4	0
14		66	128	161		9	0	0
15		105	121	150		8	0	0
16		106	134	150		0	0	1
17		87	146	175		0	0	0
18		66	89	105		4	14	3
19		86	123	145		0	0	0
20		108	120	174		10	14	0
21		61	143	170		0	0	8
22		111	145	165		0	3	17
23		112	120	152		0	3	11
24		121	131	150		9	0	8
25		113	146	172		17	8	0
26		73	108	116		0	6	0
27		106	124	169		11	16	0
28		89	0	140		11	0	20
29		71	112	179		?	?	?
30		130	137	150		?	?	?
31		126	128	131		?	?	?
32		66	139	144		?	?	?
33		129	145	165		?	?	?
34		72	104	133		?	?	?
35		79	86	149		?	?	?
36		70	95	100		?	?	?
37		101	135	162		?	?	?
38		109	112	146		?	?	?
39		68	100	127		?	?	?
40		111	125	130		?	?	?
41		104	106	120		?	?	?
42		80	149	156		?	?	?
43		75	92	116		?	?	?
44		117	122	164		?	?	?
45		104	131	175		?	?	?
46		124	125	154		?	?	?
47		109	115	152		?	?	?
48		101	113	167		?	?	?
49
50

Thank you all.

I am using Excel 2000

Regards,
Moti

 

[jorismoerings]

Book1
	A	B	C	D	E	F	G	H	I	J	K	L
1						Time Lap	Time Lap	Time Lap
2		Store1	Store2	Store3		Among 3 Stores	Among 3 Stores	Among 3 Stores
3		Parts Code Sold	Parts Code Sold	Parts Code Sold		Store1	Store2	Store3
4		63	89	110		0	0	0		0	0	0
5		66	110	165		0	1	0		0	1	0
6		108	120	136		0	0	0		0	0	0
7		105	130	162		0	0	0		0	0	0
8		113	140	159		0	0	0		0	0	0
9		63	130	151		5	2	0		5	2	0
10		85	108	114		0	4	0		0	4	0
11		110	124	132		6	0	0		6	0	0
12		63	136	152		3	6	0		3	6	0
13		110	130	170		2	4	0		2	4	0
14		66	128	161		9	0	0		9	0	0
15		105	121	150		8	0	0		8	0	0
16		106	134	150		0	0	1		0	0	1
17		87	146	175		0	0	0		0	0	0
18		66	89	105		4	14	3		4	14	3
19		86	123	145		0	0	0		0	0	0
20		108	120	174		10	14	0		10	14	0
21		61	143	170		0	0	8		0	0	8
22		111	145	165		0	3	17		0	3	17
23		112	120	152		0	3	11		0	3	11
24		121	131	150		9	0	8		9	0	8
25		113	146	172		17	8	0		17	8	0
26		73	108	116		0	6	0		0	6	0
27		106	124	169		11	16	0		11	16	0
28		89	0	140		11	0	20		10	0	20
29		71	112	179		?	?	?		0	6	0
30		130	137	150		?	?	?		17	0	6
31		126	128	131		?	?	?		0	17	7
32		66	139	144		?	?	?		14	0	0
33		129	145	165		?	?	?		0	11	11
34		72	104	133		?	?	?		0	0	0
35		79	86	149		?	?	?		0	16	0
36		70	95	100		?	?	?		0	0	0
37		101	135	162		?	?	?		0	0	30
38		109	112	146		?	?	?		0	9	13
39		68	100	127		?	?	?		0	3	0
40		111	125	130		?	?	?		18	0	10
41		104	106	120		?	?	?		7	14	18
42		80	149	156		?	?	?		0	7	0
43		75	92	116		?	?	?		0	0	17
44		117	122	164		?	?	?		0	0	0
45		104	131	175		?	?	?		4	14	28
46		124	125	154		?	?	?		19	6	0
47		109	115	152		?	?	?		9	0	24
48		101	113	167		?	?	?		11	23	0
Sheet1

Cell Formulas
Range	Formula
J4:J48	J4	=IF(COUNTIF($B$4:D4,B4)=1,0,ROW()-MAX(ROW($B$3:D3)*(B4=$B$3:D3)))
K4:K48	K4	=IF(COUNTIF($B$4:D4,C4)=1,0,ROW()-MAX(ROW($B$3:D3)*(C4=$B$3:D3)))
L4:L48	L4	=IF(COUNTIF($B$4:D4,D4)=1,0,ROW()-MAX(ROW($B$3:D3)*(D4=$B$3:D3)))
Press CTRL+SHIFT+ENTER to enter array formulas.

No credits just worked upon earlier work from @Crystalyzer

 

[motilulla]

Book1
	A	B	C	D	E	F	G	H	I	J	K	L
1						Time Lap	Time Lap	Time Lap
2		Store1	Store2	Store3		Among 3 Stores	Among 3 Stores	Among 3 Stores
3		Parts Code Sold	Parts Code Sold	Parts Code Sold		Store1	Store2	Store3
4		63	89	110		0	0	0		0	0	0
5		66	110	165		0	1	0		0	1	0
6		108	120	136		0	0	0		0	0	0
7		105	130	162		0	0	0		0	0	0
8		113	140	159		0	0	0		0	0	0
9		63	130	151		5	2	0		5	2	0
10		85	108	114		0	4	0		0	4	0
11		110	124	132		6	0	0		6	0	0
12		63	136	152		3	6	0		3	6	0
13		110	130	170		2	4	0		2	4	0
14		66	128	161		9	0	0		9	0	0
15		105	121	150		8	0	0		8	0	0
16		106	134	150		0	0	1		0	0	1
17		87	146	175		0	0	0		0	0	0
18		66	89	105		4	14	3		4	14	3
19		86	123	145		0	0	0		0	0	0
20		108	120	174		10	14	0		10	14	0
21		61	143	170		0	0	8		0	0	8
22		111	145	165		0	3	17		0	3	17
23		112	120	152		0	3	11		0	3	11
24		121	131	150		9	0	8		9	0	8
25		113	146	172		17	8	0		17	8	0
26		73	108	116		0	6	0		0	6	0
27		106	124	169		11	16	0		11	16	0
28		89	0	140		11	0	20		10	0	20
29		71	112	179		?	?	?		0	6	0
30		130	137	150		?	?	?		17	0	6
31		126	128	131		?	?	?		0	17	7
32		66	139	144		?	?	?		14	0	0
33		129	145	165		?	?	?		0	11	11
34		72	104	133		?	?	?		0	0	0
35		79	86	149		?	?	?		0	16	0
36		70	95	100		?	?	?		0	0	0
37		101	135	162		?	?	?		0	0	30
38		109	112	146		?	?	?		0	9	13
39		68	100	127		?	?	?		0	3	0
40		111	125	130		?	?	?		18	0	10
41		104	106	120		?	?	?		7	14	18
42		80	149	156		?	?	?		0	7	0
43		75	92	116		?	?	?		0	0	17
44		117	122	164		?	?	?		0	0	0
45		104	131	175		?	?	?		4	14	28
46		124	125	154		?	?	?		19	6	0
47		109	115	152		?	?	?		9	0	24
48		101	113	167		?	?	?		11	23	0
Sheet1

Cell Formulas
Range	Formula
J4:J48	J4	=IF(COUNTIF($B$4:D4,B4)=1,0,ROW()-MAX(ROW($B$3:D3)*(B4=$B$3:D3)))
K4:K48	K4	=IF(COUNTIF($B$4:D4,C4)=1,0,ROW()-MAX(ROW($B$3:D3)*(C4=$B$3:D3)))
L4:L48	L4	=IF(COUNTIF($B$4:D4,D4)=1,0,ROW()-MAX(ROW($B$3:D3)*(D4=$B$3:D3)))
Press CTRL+SHIFT+ENTER to enter array formulas.

No credits just worked upon earlier work from @Crystalyzer

jorismoerings, it worked as treat I want to appreciate for your understanding and modifying a @Crystalyzer formula and adapting it for my new request. ?

Good Luck! Have a great time

Kind Regards,
Moti