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]

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

Alpha-numeric values are in fact text values. So you can try the solution I offered. If you re-read my previous post, you'll see that I stated "either all text values as in your sample data..."

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

Based on the sample data in your original post, try...

C1, copied down:

=IF(A1="x",SUBSTITUTE(RIGHT(B1,7),"-","")+0,"")

Note that this extracts the last 7 characters, removes the hyphen, and the resulting text value is coerced into a numerical value via "+0".

D1, copied down:

=IFERROR(INDEX($B$1:$B$52,MATCH(SMALL($C$1:$C$52,ROWS(D$1:D1)),$C$1:$C$52,0)),"")

 

[collegeitdept]

Alpha-numeric values are in fact text values. So you can try the solution I offered. If you re-read my previous post, you'll see that I stated "either all text values as in your sample data..."



Based on the sample data in your original post, try...

C1, copied down:

=IF(A1="x",SUBSTITUTE(RIGHT(B1,7),"-","")+0,"")

Note that this extracts the last 7 characters, removes the hyphen, and the resulting text value is coerced into a numerical value via "+0".

D1, copied down:

=IFERROR(INDEX($B$1:$B$52,MATCH(SMALL($C$1:$C$52,ROWS(D$1:D1)),$C$1:$C$52,0)),"")

Thanks.... what I'm to do/see if its possible... maybe to remove the text values... then sort ... then return the text values after it's sorted.


Is that possible?