help required identifying the value in between the numbers

[shankar12]

I have 2 tables with the actual and estimated
table 1 contains the actual and 2 contains the estimated.If actual-a is where ever falling between actual-a of table 2 needs to pick the estimated and pasted in the result as shown.

Table A
product in kgs	Actual-a	Actual-B	Actual-C
43566	15.60%	0.60%	0.00%
42432	14.30%	0.80%	0.00%
73576	12.00%	2.10%	0.00%
63340	15.20%	1.70%	0.00%
42600	17.80%	0.60%	8.00%
59980	11.00%	0.90%	4.00%
46330	16.20%	7.80%	0.00%
45300	19.20%	3.20%	0.00%
98380	15.70%	11.70%	15.00%
19120	24.00%	0.60%	0.00%

Table B
	from	to	Estimated
Actual-a	0	14	Ok
Actual-a	14.01	16	1.5
Actual-a	16.01	18	1.6
Actual-a	18.01	22	1.7
Actual-a	22.01	99999999999	1.8
Actual-B	0	1	ok
Actual-B	1.01	5	1
Actual-B	5.01	99999999999	2
Actual-C	0	3	ok
Actual-C	3.01	99999999999	1

result
Count	Actual-a	Actual-B	Actual-C	Est-a	Est-B	Est-C
43566	15.60%	0.60%	0.00%	1.50%	ok	ok
42432	14.30%	0.80%	0.00%	1.50%	ok	ok
73576	12.00%	2.10%	0.00%	ok	1.00%	ok
63340	15.20%	1.70%	0.00%	1.50%	1.00%	ok
42600	17.80%	0.60%	8.00%	1.60%	ok	1.00%
59980	11.00%	0.90%	4.00%	ok	ok	1.00%
46330	16.20%	7.80%	0.00%	1.60%	2.00%	ok
45300	19.20%	3.20%	0.00%	1.70%	1.00%	ok
98380	15.70%	11.70%	15.00%	1.50%	2.00%	1.00%
19120	24.00%	0.60%	0.00%	1.80%	ok	ok

Example :product in KG-line item 98380,Actual-a in table a is 15.70 which falls between 14.01 and 16 and estimated for that is 1.5 which shows in th result. Same way Actual-b contains for the same 98380 is 11.70 and actual-b form table b falls between 5.01 to infinity so its estimated is 2 reflecting result .same way for Actual c  result from table b estimated will be 1

tried when concatenated and applied filter instead of showing same count as A i.e 10 result showing less records.

Thanks for the help in advance.

[cmcclellan · Accepted solution]

There's a lot of ways to do this, I prefer this way.

Please note that I've changed the percentage values into decimals.  I'm assuming that you will use something to display the data and that will format it to a proper percentage.  Because of that, using OK is not an option, so I've used 0% instead.

[ponraj]

I created a macro to solve your case.  Hope this helps. 

WorkflowMacro workflowResults

[shankar12]

Hi Ponraj,

can you please re-upload the macro, unable to view So please post only macro once again.

Thanks a lot for the support.

regards

Shankar

[ponraj]

Here is the macro.