Custom Sort Problem, Please Help!!!!!!!

[Jack_58]

I have the following spreadseet I need to be able to sort Column A by the following,
R, G, P, O, F, Q, X, A

2004-01.xls
	A	B	C	D	E	F	G	H
7	ServiceLevelCalculationsforthePriorMonthbyInventoryClassCode
8	MonthEnding:January-04
9	Class	Description	Total Active Ordered	Total Lines Shipped	Warehouse Lines Out	Manuf Lines Out	Raw Service Line	Adjusted Service Line
10	A	AHSCREPACK	85608	82517	138	2953	96.39	99.84
11	F	FAMILYPHCYPRIVATELABEL	137626	131111	675	5840	95.27	99.51
12	G	GENERICITEMS	425264	383934	4225	37105	90.28	99.01
13	O	OTC/HBA	1674520	1484560	26890	163070	88.66	98.39
14	P	PROGENERICSITEM	1161920	1127184	2501	32235	97.01	99.78
15	Q	BRITELIFE	16880	13030	703	3147	77.19	95.84
16	R	PHARMACEUTICAL	2777490	2540946	22136	214408	91.48	99.20
17	X	GOODNEIGHBORPHARMACY(GNP)	4555	2928	115	1512	64.28	97.48
AAS

Any help in this matter would be helpful!



Thanks



Jack

 

[chef]

hi jack,

You could insert a column....column header class2...and against each class you could have 1 against each R..2 against each G....3 against each P etc etc..then Data Sort on Column class2..this will put them in the order you want..

If you have a large worksheet..set up a lookup table that will do the same thing.....hide the column...

Hope this helps
Chef

 

[Joe4]

Here is one way. Create a two column look-up table somewhere on your spreadsheet which indicates the sort order of these variables, i.e.:

R	1
G	2
P	3
O	4
F	5
Q	6
X	7
A	8

Now highlight the table range and name it, via Insert | Name | Define. For example sake, let's name it MyRange.

Now in your data, insert another column next to your codes and enter a VLOOKUP formula. For example, if you inserted a blank column B, then for your first entry in A10, enter this formula in B10:

=VLOOKUP(A10,MyRange,2,False)

This will return a value of 8. Copy this VLOOKUP formula down for all your entries.

You can now use this resulting column as your main sorting column. If you do not want to see this column, simply hide it when you are finished.