Data Sort
 

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

Data Sort
 

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

On Jan 29, 1:26*pm, Penrhos wrote:
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.......

Data Sort
 

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

Hope this helps.

Pete

On Jan 29, 1:53*pm, Pete_UK wrote:
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

On Jan 29, 1:26*pm, Penrhos wrote:



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.......- Hide quoted text -

- Show quoted text -

Data Sort
 

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" wrote:

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

On Jan 29, 1:26 pm, Penrhos wrote:
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.......

Data Sort
 

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

On Jan 29, 2:41*pm, Penrhos wrote:
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" wrote:
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

On Jan 29, 1:26 pm, Penrhos wrote:
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.......- Hide quoted text -

- Show quoted text -

Data Sort
 

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


On Jan 29, 3:09*pm, Pete_UK wrote:
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

On Jan 29, 2:41*pm, Penrhos wrote:



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" wrote:
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

On Jan 29, 1:26 pm, Penrhos wrote:
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.......- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -