Finding max value in a mixed data set.

[chuggins143]

Good morning one and all! I'm working on a sorting problem that someone here might be able to shed some light on... I have two columns of data, the first column contains voltages. A mixture of 120, 208, and 240 values in no particular order, and in column two there are various amp draw values. I've been trying different ways to look down through the data set and pull the max amp draw for each voltage with little success. I've attempted a couple of variations with Lookup, Index, Match, and so forth, but am coming up dry. Anyone ever run into this and possibly have a solution they might be willing to share?
Thanks!
Chad

 

[JamesW]

Hi there,

Have you tried a pivot table?

Voltage = Row Area
Amps = Data Items Area (set to MAX instead of SUM).

 

[chuggins143]

Honestly, I've never used pivot tables... I'll take a look and see.
C

 

[bryonwoods30]

ok say your data is in column a and b. highlight both column and sort by a ascending and b ascending.

in c1,c2,c3 put 120,208,240.

then in c1 use the formula =VLOOKUP(C1,A:B,2,1) and it will return the max amps number for each

 

[chuggins143]

Ok, I'm trying to get this data into a pivot table, and it looks like I did it... got the amp values reset to Max, and it pulls the correct amp value when I creat the table, but it doesn't update if I go into the list and change anything. Is there a way to have it active?
C

 

[chuggins143]

ok say your data is in column a and b. highlight both column and sort by a ascending and b ascending.

in c1,c2,c3 put 120,208,240.

then in c1 use the formula =VLOOKUP(C1,A:B,2,1) and it will return the max amps number for each

There's the rub, I can't sort because my data is being looked up from somewhere else so I have to look for it where it's currently located. If it was only 5 or 10 points then it wouldn't be that big of a deal, but I have several thousand data points I'm sifting through.

 

[bryonwoods30]

if they are formulas and being looked up from somewhere else. just copy both columns and paste special then the values on a new sheet. then sort and do the vlookup

 

[chuggins143]

Ok, one of our guys here at work got it figured out... to do it he used an array formula and named ranges... Name the volts column "volts" and the amps column "amps" and use =MAX(IF(Volts=Target,Amps,0)) where Target is a named cell where I can put in 120, 208, or 240 and once you put in the formula do a <Ctrl> + <Shift> + <Enter> and then there should be {} around the formula so it looks like this on the formula bar... {=MAX(IF(Volts=Target,Amps,0))} ...it updates properly and works quite nicely. Just remember every time you go to that cell it resets to a regular formula so you have to hit <Ctrl> + <Shift> + <Enter> to make it an array formula! I'd never used array formulas before so I guess I learned something today!
Thanks for all the responses!
C

 

[chuggins143]

Ok, one of our guys here at work got it figured out... to do it he used an array formula and named ranges... Name the volts column "volts" and the amps column "amps" and use =MAX(IF(Volts=Target,Amps,0)) where Target is a named cell where I can put in 120, 208, or 240 and once you put in the formula do a <ctrl> + <shift> + <enter> and then there should be {} around the formula so it looks like this on the formula bar... {=MAX(IF(Volts=Target,Amps,0))} ...it updates properly and works quite nicely. Just remember every time you go to that cell it resets to a regular formula so you have to hit <ctrl> + <shift> + <enter> to make it an array formula! I'd never used array formulas before so I guess I learned something today!
Thanks for all the responses!
C

Apparently I used some bad symbols or something... to enter the formula as a array formula use Ctrl + Shift + Enter

</enter></shift></ctrl></enter></shift></ctrl>