Find time lap between 3 stores sold part code.

motilulla

Well-known Member
Joined
Feb 13, 2008
Messages
2,049
Office Version
  1. 2003 or older
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

*ABCDEFGH
1Time LapTime LapTime Lap
2Store1Store2Store3Among 3 StoresAmong 3 StoresAmong 3 Stores
3Parts Code SoldParts Code SoldParts Code SoldStore1Store2Store3
46389110000
566110165010
6108120136000
7105130162000
8113140159000
963130151520
1085108114040
11110124132600
1263136152360
13110130170240
1466128161900
15105121150800
16106134150001
1787146175000
1866891054143
1986123145000
2010812017410140
2161143170008
221111451650317
231121201520311
24121131150908
251131461721780
2673108116060
2710612416911160
2889014011020
2971112179???
30130137150???
31126128131???
3266139144???
33129145165???
3472104133???
357986149???
367095100???
37101135162???
38109112146???
3968100127???
40111125130???
41104106120???
4280149156???
437592116???
44117122164???
45104131175???
46124125154???
47109115152???
48101113167???
49
50

Thank you all.

I am using Excel 2000

Regards,
Moti
 

Attachments

  • Time Lap 3 Stores.jpg
    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
Joined
Jul 4, 2014
Messages
1,482
Office Version
  1. 365
  2. 2016
  3. 2013
Platform
  1. Windows
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
 
Solution

motilulla

Well-known Member
Joined
Feb 13, 2008
Messages
2,049
Office Version
  1. 2003 or older
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 :)
 

Forum statistics

Threads
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.
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
Top