Can you attach a sample file and what results you expect on the 2nd sheet?

ok here it is. The first tab is what it should look like. The second is the data, i had to cut 2/3 out to make it small enough so it should be 2394 rows by 30 columns, but you can still use it to find 1L, 1R through 10L, 10R to test it). The third tab is where i need the formula to put the results.

In case it helps to visualize this is an EEG map of the brain so im looking at different frequencies from 1-30 in 44 different parts of the brain both left and right. So in this case I'm trying to find the max score, or furthest from 0 whether its positive or negative at 1hz in area 1 left of the brain. Some areas have 3 different points and some like 20 have almost 40 different points i have to compare.

again thanks so much.

Kevin

Attached Files

comparison.xlsx (490.3 KB, 11 views)

Found the answer on a different forum, Thanks for the suggestion Jeff , here are the 2 codes that ended up working in case anyone was interested.

=IFERROR(LOOKUP(2,1/(MAX(IFERROR(ABS(IF('Power data'!$A$1:$A$2394=A2,'Power data'!$B$1:$B$2394,"")),""))=ABS(IF('Power data'!$A$1:$A$2394=A2,'Power data'!$B$1:$B$2394,""))),'Power data'!$B$1:$B$2394),"")

Attached is the code in action.


The other code that worked is this, it was able to be copied down and left which was nice.

=IF(MAX(ABS(INDEX(IF($A2='Power Data'!$A$2:$A$1397,'Power Data'!$B$2:$AE$1397),,MATCH(B$1,'Power Data'!$B$1:$AE$1,0))))=-MIN(INDEX(IF($A2='Power Data'!$A$2:$A$1397,'Power Data'!$B$2:$AE$1397),,MATCH(B$1,'Power Data'!$B$1:$AE$1,0))),MIN(INDEX(IF($A2='Power Data'!$A$2:$A$1397,'Power Data'!$B$2:$AE$1397),,MATCH(B$1,'Power Data'!$B$1:$AE$1,0))),MAX(INDEX(IF($A2='Power Data'!$A$2:$A$1397,'Power Data'!$B$2:$AE$1397),,MATCH(B$1,'Power Data'!$B$1:$AE$1,0))))

wish i could explain it but its way above my head but here it is none the less

Kevin

Attached Files

Book1.xlsx (10.9 KB, 10 views)