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

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.

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

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

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.

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

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