MINIF /MAXIF Or something similar

  • Thread starter Thread starter kraljb
  • Start date Start date
K

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

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

Regards

Roger Govier
 
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
 
Sorting works, but I am not supposed to edit the original data. It i
used in that format for other purposes
 
Hi

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

Regards

Roger Govier
 
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
 
Back
Top