same X,Y values with multiply Z values

jaystewie

New Member
Joined
Sep 7, 2007
Messages
1
I have the same X,Y values with multiply Z values. I wondering if there is a program or something that exists that will delete all of the cooresponding x,y,z values where the x and y are the same and just keep the lowest z value and at the same time keep the associated x,y value. Please get back to me on this ASAP.
 

Some videos you may like

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.

Oaktree

MrExcel MVP
Joined
Jun 20, 2002
Messages
7,994
Welcome to the board.

If your X values are in A1:A100, your Y values are in B1:B100, and your Z values are in C1:C100, try:

1) Selecting A1:B100, going to data --> filter --> advanced filter
2) Choose to copy to a new location, where the list range is A1:B100, the copy to range is the upper left cell where you'd like to paste (Say, F1), and you check the "unique records only" box. That will give you a list of the unique pairs of X and Y.
3) In cell H2 (or whatever cell is to the right of your first value from the unique list table) enter the formula =MIN(IF($A$2:$A$100=F2,IF($B$2:$B$100=G2,$C$2:$C$100))). Note that this is an array formula and must be confirmed with CTRL+SHIFT+ENTER instead of just ENTER (doing so correctly will result in Excel putting { }'s around your formula in the formula bar).

Then, copy H2's formula down through the end of your list.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,469
Messages
5,596,327
Members
414,053
Latest member
Dual Showman

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top