I cant figure out how to retrieve the data from a different tab/sheet i need without ABS but I want to retrieve it if its negative or positive not just positive. Here is how I have it set up.

I have one tab/sheet with column A containing variables like 1L, 1R, 2L, 2R, etc. Then in column B-Z there are numbers like .024, -.50, 1.5, -3.5, ect.

In a second tab/sheet i'm trying to first reference which row I want by matching the 1L, 2L data in column A. There is a lot of data and so there are multiple cells that will have 1L or 2L or others. Once I find all the cells with 1L for example, I want to then look at column B and retrieve the cell with the max value whether its positive or negative. The data sheet would look like this: Trying to make it look like an excel sheet.

A | b | c, etc
____________________
1l | .25 |
2l | -.50 |
1l | 1.5 |


4R | -2.5 |
5r | -.4 |
1l | -.1.7 |
2l | 1.1 |

The sheet im pulling data to would look like this:

A | B
_____________
1L |?????
1R |
2L |
2R |
3L |
3R |


In the ??? cell i want to search the first sheet and find all rows with 1L then return the largest value in column B. In this example I want the end result to return -1.7 because its the largest absolute number in the 1L catagory.

So far I've only got this far. I can get the number but its absolute only. I want it to be positive or negative depending on which is further from 0. If it helps to know I'm working with data that fits in 2394 rows by 30 colums so the formula below is with that data range.

What I have so far:
=MAX(ABS(IF('sheet 1'!$A$1:$A$2394=A1,'sheet 1'!$B$1:$B$2394)))

Thanks and sorry if im missing a better explanation im new to excel

Kevin

Hi Kevin,

Based on your sample, put this in the 2nd sheet in B1 and copy down...

=IFERROR(INDEX(Sheet1!$B$1:$B$7,LARGE(IF(Sheet1!$A $1:$A$7=A1,ROW(Sheet1!$A$1:$A$7)-ROW(Sheet1!$A$1)+1),1)),"")

IMPORTANT

• This is an array formula
• Enter the formula >> press F2 then >> CTRL + SHIFT + ENTER
• If entered correctly, the formula will be enclosed in {brackets}
• Do not enter the {brackets} manually

Jeff thanks so much for the help. It's not working but I'm trying to transpose it to what i have and i may be messing it up. My actual formula is this:

=MAX(ABS(IF('Power data'!$A$1:$A$2394=A2,'Power data'!$B$1:$B$2394)))

I tried to make it simple on the post but these are the correct coordinates. Can you tell me what you had again with this info? I tried and got this:

=IFERROR(INDEX('Power data'!$B$1:$B$2394,LARGE(IF('Power data'!$A$1:$A$2394=A1,ROW('Power data'!$A$1:$A$2394)-ROW('Power data'!$A$1)+1),1)),"")

Is this right? it woked on one sell but when i drag to auto fill to the cell below it doesnt work.

Kevin

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, 9 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, 8 views)