Alphanumeric Sorting Formula with 1 condition (if)

[collegeitdept]

I have alphanumeric item codes listed in column b... with an "x" activator in column A for their respective item code.

I need to combine my 2 formulas that will sort alphanumerically (Column B) if it is activated with an X in column A.


My 2 formulas are:

(if it has an "x")

{ =IFERROR(INDEX($B$1:$B$1992,SMALL(IF($A$1:$A$1992="x",ROW($B$1:$B$1992)-ROW($B$1)+1),ROWS(F$1:F1))),0) }


(sort alphanumerically)

{ =INDEX($B$1:$B$99, MATCH(SMALL(COUNTIF($B$1:$B$99, "<"&$B$1:$B$99), ROWS(I$1:I1)), COUNTIF($B$1:$B$99, "<"&$B$1:$B$99), 0)) }




Data:


Col A Col B
x C119-060
x C121-060
C125-050
x C199-032
x C210-102
C211-240
x C213-024
x C154-024
C160-036
x C162-024
x C165-030
x C167-024
x C170-030
x C172-024
x C175-036
x C177-030
C180-036
x C181-036
x C182-036
x C183-036
x C191-024
C193-024
x C195-024
x C197-024
x C127-060
x C131-050
x C135-084
x C244-024
C215-030
x C217-030
x C223-030
x C231-024
x C232-048
x C237-028
x C238-030
x C260-032
x C274-048
x C281-030
x C291-030
C292-048
x C294-024
x C299-024
x C300-040
x C307-024
x C310-030
x C314-028
x C317-036
C326-040
C378-030
C385-028
C388-028
C323-032

 

[Domenic]

For simplicity, let's assume that A2:A10 contains the "activator", and B2:B10 contains the corresponding "item code". Try...

D2, confirmed with CONTROL+SHIFT+ENTER, and copied down:

=IFERROR(INDEX($B$2:$B$10,MATCH(SMALL(IF($A$2:$A$10="x",MMULT(IF($B$2:$B$10>TRANSPOSE($B$2:$B$10),1,0),ROW($B$2:$B$10)^0)),ROWS(D$2:D2)),IF($A$2:$A$10="x",MMULT(IF($B$2:$B$10>TRANSPOSE($B$2:$B$10),1,0),ROW($B$2:$B$10)^0)),0)),"")

Note that both text and/or numerical values are allowed within Column B.

 

[collegeitdept]

That formula works.

I don't mean to be a pain... as it turns out, this formula extremely taxing on the system. It runs dog slow whenever you add a new item code to the data reference list with the fans going at full speed.

Do you know of a "more efficient formula?"


The two formulas I used before to do single functions worked really fast.

Thanks.

 

[collegeitdept]

or maybe just another sort function. I noticed that it doesn't work too well if there are blank entries (or zeros).


{ =INDEX($B$1:$B$99, MATCH(SMALL(COUNTIF($B$1:$B$99, "<"&$B$1:$B$99), ROWS(I$1:I1)), COUNTIF($B$1:$B$99, "<"&$B$1:$B$99), 0)) }

I tried editing this to make this 0< but it doesn't work:

{ =INDEX($B$1:$B$99, MATCH(SMALL(COUNTIF($B$1:$B$99, "1<"&$B$1:$B$99), ROWS(I$1:I1)), COUNTIF($B$1:$B$99, "1<"&$B$1:$B$99), 0)) }

 

[Domenic]

I noticed that it doesn't work too well if there are blank entries (or zeros).

That's why I avoided offering you a similar solution that involved the COUNTIF function. Unfortunately, the solution I offered is resource intensive, as you've already discovered. Have you considered using VBA so that a macro could be used to automatically transfer and sort the desired data?

 

[collegeitdept]

No... Can't use macros... against security policy.

Can you think of any other solution that's not as resource intensive??

Thanks for all your help!

 

[collegeitdept]

What about the rank formula?

or any other formula.

 

[Domenic]

As long as Column B contains only one data type (ie. either all text values as in your sample data or all numerical values), maybe...

D2, confirmed with CONTROL+SHIFT+ENTER, and copied down:

=IFERROR(INDEX($B$2:$B$10,MATCH(SMALL(IF($A$2:$A$10="x",IF($B$2:$B$10<>"",COUNTIF($B$2:$B$10,"<"&$B$2:$B$10))),ROWS(D$2:D2)),IF($A$2:$A$10="x",IF($B$2:$B$10<>"",COUNTIF($B$2:$B$10,"<"&$B$2:$B$10))),0)),"")

Note that Column B can contain empty/blank cells.

 

[collegeitdept]

unfortunately item codes are alphanumeric... one letter before the numbers.

 

[collegeitdept]

is there away we can use the "right" function...because only the first character is a letter? the other 7 characters are numbers (with a hyphen in between).