MINIF /MAXIF Or something similar

[kraljb]

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.

[B. R.Ramachandran]

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
>
>

[Roger Govier]

Hi

Can you not just mark he whole range of data, then
Data>Sort>Column C>Descending

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.
>
>

[kraljb]

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

[Roger Govier]

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
> Data>Sort>Column C>Descending
>
> 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.
>>
>>

[Peo Sjoblom]

Copy the data and sort on the copied data?

--

Regards,

Peo Sjoblom


"kraljb" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

[B. R.Ramachandran]

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
>
>