MINIF /MAXIF Or something similar
 

I have a spreadsheet with a number of stores on it. The data is such
that each store is ranked based on their sales. However, I need to
break them out into their respective Districts and am running into some
issues there, as I need to keep the current layout of the data, and need
to have the ranking numbers be dynamic once the numbers are pulled in.
The catch is, that I need to do this (if possible) with only using
Excel Formulas (No VBA).

Basically this is a sample of what I have

---A---|---B---|---C---|---D---|
DMA2 |STO1 | $400 | 5 |
DMA1 |STO2 | $700 | 2 |
DMA2 |STO3 | $500 | 4 |
DMA1 |STO4 | $300 | 6 |
DMA2 |STO5 | $800 | 1 |
DMA1 |STO6 | $600 | 3 |
DMA1 |STO7 | $250 | 7 |


I need to pull out it such that it looks like this

DMA1 |STO2 | $700 | 2 |
DMA1 |STO6 | $600 | 3 |
DMA1 |STO4 | $300 | 6 |
DMA1 |STO4 | $250 | 7 |
DMA2 |STO5 | $800 | 1 |
DMA2 |STO3 | $500 | 4 |
DMA2 |STO1 | $400 | 5 |

I already have the DMA's to Pull in Fine, however I would like to use
the following information to get the store's correctly in there...

=MINIF(Range, Condition(i.e. "DMA2"), Min_Range)
Where if it looked at the the range and pulled in only the cells that
are DMA2 to find the minimum of.


--
kraljb
------------------------------------------------------------------------
kraljb's Profile: http://www.excelforum.com/member.php...fo&userid=9955
View this thread: http://www.excelforum.com/showthread...hreadid=484432

MINIF /MAXIF Or something similar
 

Hi

Can you not just mark he whole range of data, then
DataSortColumn CDescending

Regards

Roger Govier


kraljb wrote:
I have a spreadsheet with a number of stores on it. The data is such
that each store is ranked based on their sales. However, I need to
break them out into their respective Districts and am running into some
issues there, as I need to keep the current layout of the data, and need
to have the ranking numbers be dynamic once the numbers are pulled in.
The catch is, that I need to do this (if possible) with only using
Excel Formulas (No VBA).

Basically this is a sample of what I have

---A---|---B---|---C---|---D---|
DMA2 |STO1 | $400 | 5 |
DMA1 |STO2 | $700 | 2 |
DMA2 |STO3 | $500 | 4 |
DMA1 |STO4 | $300 | 6 |
DMA2 |STO5 | $800 | 1 |
DMA1 |STO6 | $600 | 3 |
DMA1 |STO7 | $250 | 7 |


I need to pull out it such that it looks like this

DMA1 |STO2 | $700 | 2 |
DMA1 |STO6 | $600 | 3 |
DMA1 |STO4 | $300 | 6 |
DMA1 |STO4 | $250 | 7 |
DMA2 |STO5 | $800 | 1 |
DMA2 |STO3 | $500 | 4 |
DMA2 |STO1 | $400 | 5 |

I already have the DMA's to Pull in Fine, however I would like to use
the following information to get the store's correctly in there...

=MINIF(Range, Condition(i.e. "DMA2"), Min_Range)
Where if it looked at the the range and pulled in only the cells that
are DMA2 to find the minimum of.

MINIF /MAXIF Or something similar
 

Hi,

Select the entire range (e.g., A2:D101) -- "Data" -- "Sort" -- Sort by
'Column A', check 'Ascending', Then By 'Column D', and check 'Ascending' --
"OK"

Regards,
B. R. Ramachandran

"kraljb" wrote:


I have a spreadsheet with a number of stores on it. The data is such
that each store is ranked based on their sales. However, I need to
break them out into their respective Districts and am running into some
issues there, as I need to keep the current layout of the data, and need
to have the ranking numbers be dynamic once the numbers are pulled in.
The catch is, that I need to do this (if possible) with only using
Excel Formulas (No VBA).

Basically this is a sample of what I have

---A---|---B---|---C---|---D---|
DMA2 |STO1 | $400 | 5 |
DMA1 |STO2 | $700 | 2 |
DMA2 |STO3 | $500 | 4 |
DMA1 |STO4 | $300 | 6 |
DMA2 |STO5 | $800 | 1 |
DMA1 |STO6 | $600 | 3 |
DMA1 |STO7 | $250 | 7 |


I need to pull out it such that it looks like this

DMA1 |STO2 | $700 | 2 |
DMA1 |STO6 | $600 | 3 |
DMA1 |STO4 | $300 | 6 |
DMA1 |STO4 | $250 | 7 |
DMA2 |STO5 | $800 | 1 |
DMA2 |STO3 | $500 | 4 |
DMA2 |STO1 | $400 | 5 |

I already have the DMA's to Pull in Fine, however I would like to use
the following information to get the store's correctly in there...

=MINIF(Range, Condition(i.e. "DMA2"), Min_Range)
Where if it looked at the the range and pulled in only the cells that
are DMA2 to find the minimum of.


--
kraljb
------------------------------------------------------------------------
kraljb's Profile: http://www.excelforum.com/member.php...fo&userid=9955
View this thread: http://www.excelforum.com/showthread...hreadid=484432

MINIF /MAXIF Or something similar
 

Sorting works, but I am not supposed to edit the original data. It is
used in that format for other purposes.


--
kraljb
------------------------------------------------------------------------
kraljb's Profile: http://www.excelforum.com/member.php...fo&userid=9955
View this thread: http://www.excelforum.com/showthread...hreadid=484432

MINIF /MAXIF Or something similar
 

Hi

Sorry posted too quickly, your sort needs to be by column A descending, then
column C descending.

Regards

Roger Govier


Roger Govier wrote:
Hi

Can you not just mark he whole range of data, then
DataSortColumn CDescending

Regards

Roger Govier


kraljb wrote:

I have a spreadsheet with a number of stores on it. The data is such
that each store is ranked based on their sales. However, I need to
break them out into their respective Districts and am running into some
issues there, as I need to keep the current layout of the data, and need
to have the ranking numbers be dynamic once the numbers are pulled in.
The catch is, that I need to do this (if possible) with only using
Excel Formulas (No VBA).
Basically this is a sample of what I have

---A---|---B---|---C---|---D---|
DMA2 |STO1 | $400 | 5 |
DMA1 |STO2 | $700 | 2 |
DMA2 |STO3 | $500 | 4 |
DMA1 |STO4 | $300 | 6 |
DMA2 |STO5 | $800 | 1 |
DMA1 |STO6 | $600 | 3 |
DMA1 |STO7 | $250 | 7 |


I need to pull out it such that it looks like this

DMA1 |STO2 | $700 | 2 |
DMA1 |STO6 | $600 | 3 |
DMA1 |STO4 | $300 | 6 |
DMA1 |STO4 | $250 | 7 |
DMA2 |STO5 | $800 | 1 |
DMA2 |STO3 | $500 | 4 |
DMA2 |STO1 | $400 | 5 |

I already have the DMA's to Pull in Fine, however I would like to use
the following information to get the store's correctly in there...

=MINIF(Range, Condition(i.e. "DMA2"), Min_Range)
Where if it looked at the the range and pulled in only the cells that
are DMA2 to find the minimum of.

MINIF /MAXIF Or something similar
 

Copy the data and sort on the copied data?

--

Regards,

Peo Sjoblom


"kraljb" wrote in
message ...

Sorting works, but I am not supposed to edit the original data. It is
used in that format for other purposes.


--
kraljb
------------------------------------------------------------------------
kraljb's Profile:
http://www.excelforum.com/member.php...fo&userid=9955
View this thread: http://www.excelforum.com/showthread...hreadid=484432

MINIF /MAXIF Or something similar
 

Hi,

Copy the data and do the sorting as I suggested in my previous response.

Or, create a helper column say (E2:E101) containing running numbers (1, 2,
3, ...)
Select the entire data including Column E, and sort as I suggested earlier.
To restore the data back to the original arrangement, select the entire data
and sort by Column E ascending.

Regards,
B. R. Ramachandran

"kraljb" wrote:


Sorting works, but I am not supposed to edit the original data. It is
used in that format for other purposes.


--
kraljb
------------------------------------------------------------------------
kraljb's Profile: http://www.excelforum.com/member.php...fo&userid=9955
View this thread: http://www.excelforum.com/showthread...hreadid=484432