Data Sort

[Penrhos]

Here is my data:
A B C D
0 [NONE] none 1
1 Teaspoon tsp 18
2 Tablespoon Tbs 3
3 Cup cup 0
4 Piece pce 5
5 Each ea 15
6 Ounce oz 7
7 Pound lb 8
8 Gram g 21
9 Kilogram Kg 10
10 Fluid ounce fl-oz 11
11 Milliliter ml 22
12 Liter ltr 13
13 Gallon gal 12
14 Pint pnt 9
15 Quart qt 16
16 Milligram mg 14
17 Microgram mcg 2
18 Intake intk 6
20 Bottle btl 17
21 Box box 20
22 Can can 4

A - is an ascending list of numbers that identify measurements
B - aligned with the numbers in A, this is the full name of each measurement
C- as in B, these are short forms of the measurement names
D- is a list in random order (actually previously sorted based on data in 32
columns to the right of column D not displayed here).

Objective:
To sort Columns A, B & C into the order of the random order in column D.

Issue:
I have posted this in several areas, but the responses all result in a sort
that does not achieve the necessary results - most yield a full re-sort of
all 4 columns - not what I require.

An urgent reply is needed, this project is huge, is overdue and I'm in a
panic.

Thank you in advance for the right answer.......

[Pete_UK]

Do you mean that you want the information from A2:C2 to appear on row
1 (as D1 = 1), and A19:C19 to appear on row 2 (as D2 = 18) ?

If so, put these formula in the cells stated:

E1: =INDEX(B:B,$A1+1)

F1: =INDEX(C:C,$A1+1)

Then copy down to row 23. You can then fix the values in columns E and
F (using copy/paste special etc), and then delete columns A to D.

Hope this helps.

Pete

[Pete_UK]

Sorry, you want to delete columns A to C afterwards, so D becomes the
new A.

Hope this helps.

Pete

> > Thank you in advance for the right answer.......- Hide quoted text -
>
> - Show quoted text -

[Penrhos]

Pete - would you mind if I contacted you by email - your suggestion didn't
work, and perhaps we could communicate directly on this issue. I would
really appreciate that.

[Pete_UK]

Ok, you can reach me at:

pashurst <at> auditel.net

(change the obvious).

However, please do not sent me a "huge" project file (your word).

Pete

[Pete_UK]

As a follow-up, and for the records, my earlier formulae assumed the
data started at row 1. Also, I didn't spot that no 19 was missing.
When the OP sent his sample data to me I saw that the data started on
row 4, and so had to amend the formulae as follows:

I4: =INDEX(B$4:B$25,MATCH($D4,$A$4:$A$25,0))

J4: =INDEX(C$4:C$25,MATCH($D4,$A$4:$A$25,0))

These were copied down to row 25 and gave him what he wanted (well,
for his sample - the real data covered several 1000 rows).

Pete

> > - Show quoted text -- Hide quoted text -