# Find time lap between 3 stores sold part code.

#### motilulla

##### Well-known Member
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

 * 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

#### Attachments

• Time Lap 3 Stores.jpg
246.1 KB · Views: 1

### 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.

#### jorismoerings

##### Well-known Member
Book1
ABCDEFGHIJKL
1Time LapTime LapTime Lap
2Store1Store2Store3Among 3 StoresAmong 3 StoresAmong 3 Stores
3Parts Code SoldParts Code SoldParts Code SoldStore1Store2Store3
46389110000000
566110165010010
6108120136000000
7105130162000000
8113140159000000
963130151520520
1085108114040040
11110124132600600
1263136152360360
13110130170240240
1466128161900900
15105121150800800
16106134150001001
1787146175000000
18668910541434143
1986123145000000
201081201741014010140
2161143170008008
2211114516503170317
2311212015203110311
24121131150908908
2511314617217801780
2673108116060060
271061241691116011160
288901401102010020
2971112179???060
30130137150???1706
31126128131???0177
3266139144???1400
33129145165???01111
3472104133???000
357986149???0160
367095100???000
37101135162???0030
38109112146???0913
3968100127???030
40111125130???18010
41104106120???71418
4280149156???070
437592116???0017
44117122164???000
45104131175???41428
46124125154???1960
47109115152???9024
48101113167???11230
Sheet1
Cell Formulas
RangeFormula
J4:J48J4=IF(COUNTIF(\$B\$4:D4,B4)=1,0,ROW()-MAX(ROW(\$B\$3:D3)*(B4=\$B\$3:D3)))
K4:K48K4=IF(COUNTIF(\$B\$4:D4,C4)=1,0,ROW()-MAX(ROW(\$B\$3:D3)*(C4=\$B\$3:D3)))
L4:L48L4=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

##### Well-known Member
Book1
ABCDEFGHIJKL
1Time LapTime LapTime Lap
2Store1Store2Store3Among 3 StoresAmong 3 StoresAmong 3 Stores
3Parts Code SoldParts Code SoldParts Code SoldStore1Store2Store3
46389110000000
566110165010010
6108120136000000
7105130162000000
8113140159000000
963130151520520
1085108114040040
11110124132600600
1263136152360360
13110130170240240
1466128161900900
15105121150800800
16106134150001001
1787146175000000
18668910541434143
1986123145000000
201081201741014010140
2161143170008008
2211114516503170317
2311212015203110311
24121131150908908
2511314617217801780
2673108116060060
271061241691116011160
288901401102010020
2971112179???060
30130137150???1706
31126128131???0177
3266139144???1400
33129145165???01111
3472104133???000
357986149???0160
367095100???000
37101135162???0030
38109112146???0913
3968100127???030
40111125130???18010
41104106120???71418
4280149156???070
437592116???0017
44117122164???000
45104131175???41428
46124125154???1960
47109115152???9024
48101113167???11230
Sheet1
Cell Formulas
RangeFormula
J4:J48J4=IF(COUNTIF(\$B\$4:D4,B4)=1,0,ROW()-MAX(ROW(\$B\$3:D3)*(B4=\$B\$3:D3)))
K4:K48K4=IF(COUNTIF(\$B\$4:D4,C4)=1,0,ROW()-MAX(ROW(\$B\$3:D3)*(C4=\$B\$3:D3)))
L4:L48L4=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

Replies
3
Views
130
Replies
1
Views
120
Replies
4
Views
85
Replies
31
Views
1K
Replies
10
Views
204

1,147,816
Messages
5,743,376
Members
423,790
Latest member
kevinlee_5

### 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.

### Which adblocker are you using?

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

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