Slot sales value based on activity number

[jayant123]

Hello ,

i have three columns, first column has activity numbers A3: A43 , second column b3 : b43 is the
Sales Range From and third column c3 :c43 is
Sales Range To for respective activities.

i have sales achieved for a particular month against each activity in column E3 : E43 , need to slot the sales in the need to slot column G3 : G43 within the range of specific activity.
looking for an excel formula which will simplify the slotting.
attaching the excel file for better understanding.

Slotting challenge.xlsx
	A	B	C	D	E	F	G
1							solution expected
2
3	Activity no.	Sales Range From	Sales Range To		Sales		Need to slot
4	RBT400658	363,503	381,678		3,388		3,388
5	RBT400658	381,715	399,817		3,388
6	RBT400658	399,853	1,399,853		3,388
7	RBT400745	379,850	398,843		-
8	RBT400745	398,880	417,797		-
9	RBT400745	417,835	1,417,835		-
10	RBT400683	1,260,000	1,319,880		1,349,940
11	RBT400683	1,320,000	1,379,880		1,349,940		1,349,940
12	RBT400683	1,380,000	1,439,880		1,349,940
13	RBT400683	1,440,000	2,440,000		1,349,940
14	RBT400738	1,330,769	1,394,012		1,489,130
15	RBT400738	1,394,139	1,457,382		1,489,130
16	RBT400738	1,457,509	1,520,752		1,489,130		1,489,130
17	RBT400738	1,520,879	2,520,879		1,489,130
18	RBT400722	312,800	326,373		319,586		319,586
19	RBT400722	326,400	339,973		319,586
20	RBT400722	340,000	1,340,000		319,586
21	RBT400744	215,050	224,381		178,000		178,000
22	RBT400744	224,400	233,731		178,000
23	RBT400744	233,750	1,233,750		178,000
24	RBT400646	547,220	572,044		1,857,920
25	RBT400646	572,094	596,918		1,857,920
26	RBT400646	596,968	646,665		1,857,920
27	RBT400646	646,715	1,646,715		1,857,920		1,857,920
28	RBT400696	453,601	1,453,601		145,555		145,555
29	RBT400699	718,356	750,943		11,285		11,285
30	RBT400699	751,009	783,596		11,285
31	RBT400699	783,661	1,783,661		11,285
32	RBT400725	127,612	133,401		1,750		1,750
33	RBT400725	133,413	139,202		1,750
34	RBT400725	139,213	145,002		1,750
35	RBT400725	145,014	1,145,014		1,750
36	RBT400729	1,600,000	1,631,840		- 72,572		- 72,572
37	RBT400729	1,632,000	1,663,840		- 72,572
38	RBT400729	1,664,000	1,695,840		- 72,572
39	RBT400729	1,696,000	2,696,000		- 72,572
40	RBT400730	1,504,774	1,549,917		-
41	RBT400730	1,550,068	1,580,013		-
42	RBT400730	1,580,163	1,625,156		-
43	RBT400730	1,625,306	2,625,306		-
Sheet1

Thank you

 

[JamesCanale]

MrExcelPlayground20.xlsx
	A	B	C	D	E	F	G	H	I
1							solution expected
2
3	Activity no.	Sales Range From	Sales Range To		Sales		Need to slot	Plain Excel	Excel 365
4	RBT400658	363503	381678.15		$3,387.80		3387.8	$3,387.80	$3,387.80
5	RBT400658	381714.5003	399816.9497		$3,387.80
6	RBT400658	399853.3	1399853.3		$3,387.80
7	RBT400745	379850	398842.5		$0.00			$0.00	$0.00
8	RBT400745	398880.485	417797.015		$0.00
9	RBT400745	417835	1417835		$0.00
10	RBT400683	1260000	1319880		$1,349,940.00			$1,349,940.00	$1,349,940.00
11	RBT400683	1320000	1379880		$1,349,940.00		1349940
12	RBT400683	1380000	1439880		$1,349,940.00
13	RBT400683	1440000	2440000		$1,349,940.00
14	RBT400738	1330768.95	1394012.16		$1,489,130.46			$1,489,130.46	$1,489,130.46
15	RBT400738	1394138.9	1457382.11		$1,489,130.46
16	RBT400738	1457508.85	1520752.06		$1,489,130.46		1489130.5
17	RBT400738	1520878.8	2520878.8		$1,489,130.46
18	RBT400722	312800	326372.8		$319,586.40		319586.4	$319,586.40	$319,586.40
19	RBT400722	326400	339972.8		$319,586.40
20	RBT400722	340000	1340000		$319,586.40
21	RBT400744	215050	224381.3		$178,000.00		178000	$178,000.00	$178,000.00
22	RBT400744	224400	233731.3		$178,000.00
23	RBT400744	233750	1233750		$178,000.00
24	RBT400646	547220.3	572044.2027		$1,857,920.00			$1,857,920.00	$1,857,920.00
25	RBT400646	572093.95	596917.8527		$1,857,920.00
26	RBT400646	596967.6	646665.1527		$1,857,920.00
27	RBT400646	646714.9	1646714.9		$1,857,920.00		1857920
28	RBT400696	453601.196	1453601.196		$145,555.00		145555	$145,555.00	$145,555.00
29	RBT400699	718356.1	750943.3449		$11,284.80		11285	$11,284.80	$11,284.80
30	RBT400699	751008.65	783595.8949		$11,284.80
31	RBT400699	783661.2	1783661.2		$11,284.80
32	RBT400725	127612.111	133401.0604		$1,750.47		1750	$1,750.47	$1,750.47
33	RBT400725	133412.6615	139201.6109		$1,750.47
34	RBT400725	139213.212	145002.1614		$1,750.47
35	RBT400725	145013.7625	1145013.763		$1,750.47
36	RBT400729	1600000	1631840		-$72,572.02		-72572	-$72,572.02	-$72,572.02
37	RBT400729	1632000	1663840		-$72,572.02
38	RBT400729	1664000	1695840		-$72,572.02
39	RBT400729	1696000	2696000		-$72,572.02
40	RBT400730	1504774	1549917.22		$0.00			$0.00	$0.00
41	RBT400730	1550067.697	1580012.7		$0.00
42	RBT400730	1580163.177	1625155.92		$0.00
43	RBT400730	1625306.397	2625306.397		$0.00
Sheet20

Cell Formulas
Range	Formula
I4:I43	I4	=LET(a,E4:E43,b,DROP(a,-1),c,VSTACK(0,b),IF(a<>c,a,""))
H4:H43	H4	=IF(E4=E3,"",E4)
Dynamic array formulas.

 

[Fluff]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[jayant123]

Thanks for the attempt , i am using excel 2109 version as such had to refer =IF(E4=E3,"",E4) this formula, which does not work .
if you look at activity RBT400683 has a sales amount of 1,349,940 which needs to be slotted between Range 1,320,000 and 1,379,880 as the sales falls within this range.

while the formula currently is slotting this amount between 1,260,00 and 1,319,880.

 

[JamesCanale]

Ah, I didn't understand your problem....

 

[jayant123]

what i am trying to do is the sales value reflecting in column E needs to be slotted against respective activity only once based on the range in which it is falling. S explained above
For Activity RBT400683 has a sales amount of 1,349,940 which needs to be slotted between Range 1,320,000 and 1,379,880 as the sales falls within this range.

Let me know if this is clear

 

[JamesCanale]

MrExcelPlayground20.xlsx
	A	B	C	D	E	F	G	H
1							solution expected
2
3	Activity no.	Sales Range From	Sales Range To		Sales		Need to slot	Plain Excel
4	RBT400658	$363,503.00	$381,678.15		$3,387.80		3387.8	$3,387.80
5	RBT400658	$381,714.50	$399,816.95		$3,387.80
6	RBT400658	$399,853.30	$1,399,853.30		$3,387.80
7	RBT400745	$379,850.00	$398,842.50		$0.00			$0.00
8	RBT400745	$398,880.49	$417,797.02		$0.00
9	RBT400745	$417,835.00	$1,417,835.00		$0.00
10	RBT400683	$1,260,000.00	$1,319,880.00		$1,349,940.00
11	RBT400683	$1,320,000.00	$1,379,880.00		$1,349,940.00		1349940	$1,349,940.00
12	RBT400683	$1,380,000.00	$1,439,880.00		$1,349,940.00
13	RBT400683	$1,440,000.00	$2,440,000.00		$1,349,940.00
14	RBT400738	$1,330,768.95	$1,394,012.16		$1,489,130.46
15	RBT400738	$1,394,138.90	$1,457,382.11		$1,489,130.46
16	RBT400738	$1,457,508.85	$1,520,752.06		$1,489,130.46		1489130.5	$1,489,130.46
17	RBT400738	$1,520,878.80	$2,520,878.80		$1,489,130.46
18	RBT400722	$312,800.00	$326,372.80		$319,586.40		319586.4	$319,586.40
19	RBT400722	$326,400.00	$339,972.80		$319,586.40
20	RBT400722	$340,000.00	$1,340,000.00		$319,586.40
21	RBT400744	$215,050.00	$224,381.30		$178,000.00		178000	$178,000.00
22	RBT400744	$224,400.00	$233,731.30		$178,000.00
23	RBT400744	$233,750.00	$1,233,750.00		$178,000.00
24	RBT400646	$547,220.30	$572,044.20		$1,857,920.00
25	RBT400646	$572,093.95	$596,917.85		$1,857,920.00
26	RBT400646	$596,967.60	$646,665.15		$1,857,920.00
27	RBT400646	$646,714.90	$1,646,714.90		$1,857,920.00		1857920	$1,857,920.00
28	RBT400696	$453,601.20	$1,453,601.20		$145,555.00		145555	$145,555.00
29	RBT400699	$718,356.10	$750,943.34		$11,284.80		11285	$11,284.80
30	RBT400699	$751,008.65	$783,595.89		$11,284.80
31	RBT400699	$783,661.20	$1,783,661.20		$11,284.80
32	RBT400725	$127,612.11	$133,401.06		$1,750.47		1750	$1,750.47
33	RBT400725	$133,412.66	$139,201.61		$1,750.47
34	RBT400725	$139,213.21	$145,002.16		$1,750.47
35	RBT400725	$145,013.76	$1,145,013.76		$1,750.47
36	RBT400729	$1,600,000.00	$1,631,840.00		-$72,572.02		-72572	-$72,572.02
37	RBT400729	$1,632,000.00	$1,663,840.00		-$72,572.02
38	RBT400729	$1,664,000.00	$1,695,840.00		-$72,572.02
39	RBT400729	$1,696,000.00	$2,696,000.00		-$72,572.02
40	RBT400730	$1,504,774.00	$1,549,917.22		$0.00			$0.00
41	RBT400730	$1,550,067.70	$1,580,012.70		$0.00
42	RBT400730	$1,580,163.18	$1,625,155.92		$0.00
43	RBT400730	$1,625,306.40	$2,625,306.40		$0.00
Sheet20

Cell Formulas
Range	Formula
H4:H43	H4	=IF(AND(E4>=B4,E4<=C4),E4,IF(AND(E4<>E3,E4<B4),E4,IF(AND(E4<>E5,E4>C4),E4,"")))