Using Max function

D

Dhruva101

It seems Search fucntion is not working so I am posting this questio
here for which the answer must have been already available.

In one sheet I have couple of columns and one of the column has count.

From another sheet, I need the rows that has maximum count and minimu
count and average.

So basically I need to bring over the rows from one sheet to anothe
sheet based on max, min criteria. Can some one help me in getting thi
done
 
D

Dhruva101

Example:

Sheet 1:

JobName Job Value

AA25DX 3
XXK9MX 9
XXK9WX 2
AAL1WX 4
KUC0DX 5
JUC0MX 2
JUC2DX 4
JUC3MX 4

In Sheet 2:

I would like to see

XXK9MX 9 <== This comes bcoz 9 is the highest
XXK9WX 2 <== This comes bcoz 2 is the lowest

I would need a formula for sheet2 to bring the rows from sheet1 base
on the job value MAx and Min values
 
V

VBA Noob

Not sure I follow

Could it not be JUC0MX 2 then ?? as it also equal to 2.

VBA Noo
 
D

Dhruva101

VBA said:
Not sure I follow

Could it not be JUC0MX 2 then ?? as it also equal to 2.

VBA Noob
Click to expand...

Need to take the first occurance so that is XXK9WX.

Another Reqquirement is two know the number of jobs that have the
Job value as "2" OR "4"
 
V

VBA Noob

Put the below in Cell A1 to return XXK9MX

=INDIRECT("Sheet1!"&ADDRESS(ROW(Sheet1!$B:$B)+MATCH(MAX(Sheet1!$B:$B),Sheet1!$B:$B,0)-1,COLUMN(Sheet1!$B:$B)-1))

And in B1 enter the below to return Max

=INDIRECT("Sheet1!"&ADDRESS(ROW(Sheet1!$B:$B)+MATCH(MAX(Sheet1!$B:$B),Sheet1!$B:$B,0)-1,COLUMN(Sheet1!$B:$B)))

Use the same formula in A2 and B2 but change Max in formula to Min

Use this formula to show the number of jobs with 2. Just copy formul
into another cell and change =2 to =4 for that option

=SUMPRODUCT(--(Sheet1!B1:B100=2))

VBA Noo
 
Top