Fairly Extensive Help Needed with Array/Table Calculations VBA

[goodysgotacuda]

I have the following table of data [actually much more extensive, this is simplified] that I need to sort and analyize.

Ml/Sec	RPM	Torque
200	500	0
400	575	4
600	600	1
800	625	1
1000	650	3
1200	550	5

<tbody>
</tbody>

I need to look at this table and use the data to fulfill another table, if the criteria is met for that RPM/Torque, I need to average the values if there are more than one, insert the single value if only one exists or put "N" if no values exist.

Example

The VBA code would perform the following search of every x,y within the table below and fulfill it accordingly.

Search	Values	Average
500,0	200,800	500
500,1	N	N
600,1	600	600
650,3	1000	1000

<tbody>
</tbody>

etc.

​

The finished product after the code runs would look like this

	550	575	600	625	650
0	500	N	N	N	N
1	N	N	600	800	N
2	N	N	N	N	N
3	N	N	N	N	1000
4	N	400	N	N	N
5	550	N	N	N	N

<tbody>
</tbody>

I haven't had this issue in a long time, but I am at a loss on where to start doing this. I expect to be able to handle most of everything within a stored array and VBA...but where do I start?!

Thanks for any help!

 

[RickXL]

Hi,

To get from the first table to the last one you need to:

1. Extract a list of Torque figures, place it in column A, sort and remove the duplicates.
2. Repeat that for the row headings.
3. Perform a lookup on the first table using the row and column headings in the last table as keys.