Difficult Sorting Equation

A

Andrew Gaskell

Hi all,
I have some data which I need to sort. Cells in column A contain a
location identifier. Cells in column B contain a string of the form
"rk_1" or "rk_2" ... etc or it may contain "ne_001", or "ne_002" ...
etc. Cells in column M contain a string of the form "RACKS" or a
different string which is a product type. Cells in column N contains a
string of the form "rk_1" or "rk_2" ... etc

I do the following:

1. Sort on Column B
2. Sort on Column A
3. Sort using a sort flag in a new column. The flag is a number,
generated by the following equation that I autofill in the new column:

=IF(COUNTIF(B2,"*rk_*")>0,1,IF((COUNTIF(B2,"*ne_*")*COUNTIF(N2,"*rk_*"))>0,2,IF(COUNTBLANK(B2)*COUNTBLANK(N2)*COUNTIF(M2,"RACKS")>0,3,4)))

4. Finally I sort again on Column A


The above sorting method works to a certain degree, but what I also
need is for rows which have the same location (column A) and the same
"rk_*" in column N to be grouped together, *but* with rows with
"RACKS" in column M coming after rows with something else in column M
*IF* column B contains a "rk_*".

I tried the following new equation to sort in, replacing that in 3:

=IF(AND(COUNTIF(B2,"*rk_*")*COUNTIF(N2,"*rk_*"),COUNTIF(M2,
"RACKS")=0),1,IF((COUNTIF(B2,"*rk_*")*COUNTIF(M2,"RACKS"))>0,2,IF((COUNTIF(B2,"*ne_*")*COUNTIF(N2,"*rk_*"))>0,3,IF(COUNTBLANK(B2)*COUNTBLANK(N2)*COUNTIF(M2,"RACKS")>0,4,5))))

but whilst this results in rows with "RACKS" coming after rows with
something else in column M, rows with the same "rk_*" in column N are
no longer grouped together.

This is a bit difficult to explain without sending data, but I would
be really grateful for any help. I can mail sample data if necessary.

Thanks
Andrew
 
Top