Ok, this is a tricky one:

I have a workbook with multiple worksheets. In each worksheet, the rows are labeled with gene names, and the columns with case #s. The data can be one of five different values: -2, -1, 0, 1, or 2.

Case 1 Case 2 Case 3
Gene 1 -2 0 1
Gene 2 -1 -1 2
Gene 3 0 1 -1


I need to match the Case and Gene from across the worksheets, and place them into a new sheet labeled with the above rows and column headers. However, there are duplicate Case-Gene matches across the sheets (e.g. Case 1 and Gene 1 may appear in multiple worksheets).

I would like to pull the data with the following priorities: -2 > -1 > 0 > 1 > 2

For example:

If Case 1 and Gene 1 from a worksheet has the value 0, but Case 1 and Gene 1 from a different worksheet has the value -2, I would like for it to pull the value -2 (and so on, according to the priorities just listed).

Here's an example:

Worksheet A
Case 1 Case 2 Case 3
Gene 1 -2 0 1
Gene 2 -1 -1 2
Gene 3 0 1 -1


Worksheet B
Case 1 Case 2 Case 3
Gene 1 0 1 2
Gene 2 2 0 0
Gene 3 -1 0 -1



Final worksheet
Case 1 Case 2 Case 3
Gene 1 -2 -1 -1
Gene 2 0 -1 0
Gene 3 1 0 -1