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

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

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

 

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

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

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]

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

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

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

 

[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

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

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
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'min a
panic.
Thank you in advance for the right answer.......- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -