Need help in picking the values between 2 and gets highlighted.

[shankar12]

Hi ,

Having 2 files, file 1 contains count and Actual and file 2 contains from and to and estimated 
expecting result as below which should pick based on the count from table 2, if actual is less than estimated it should be highlighted.

File 1
count	Actual
38	13
38	18
54	14
52	18
34	9
30	17
4	3
20	14
16	6
77	24
101	22
48	18
112	43

File 2
From	To	Estimated
0	5	5
6	7	5
8	10	8
11	15	10
16	25	12
26	35	13
36	45	15
46	55	16
56	70	17
71	85	19
86	100	20
101	more	>23

Result

Result
count	Actual	Estimated
38	13	15
37	18	15
54	14	16
52	18	16
34	9	13
30	17	13
4	3	5
20	14	12
16	6	12
77	24	19
101	22	>23
48	18	16
112	43	>23

Ex:count:54(line item 3 in file 1 ) actual is 14 and 54 is falling in file 2 from 46-55 and estimated for that is 16. so actual is less then estimated that is 16. so 14 should be higlited

Ex2:52(line item 4 in file 1 ) actual is 18 and 52 is falling in file 2 from 46-55 and estimated for that is 16. so actual is greated then estimated i.e 16. so 16 should not be higlited

Thanks for the help in advance 

[ivoller]

There are a few options here. For example you can use the Generate Rows tool against the records in file 2 to create a row for each count and then process against that. Or you could use multi row logic to determine which row to check against.

[ponraj]

Attaching a sample workflow for your case.  Hope it helps. 

WorkflowResults

[ivoller]

See attached for a starting point.

[mandycgray · Accepted solution]

Hi,

I used an Append tool to do the matching between File 1 and File 2.  Depending on how many records you have in the 2 input files, this may not be ideal, as the Append would drastically increase the record count for a step.  The Table tool also contains a Column Rule formula that will highlight in yellow the records where Actual is less than Estimated.