Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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....... |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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....... |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 - |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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....... |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 - |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 - |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
data, sort option is grayed. how to sort on a column? | Excel Discussion (Misc queries) | |||
Help to sort out filtered data from the data contained in another sheet of the same workbook | Excel Worksheet Functions | |||
I want to convert word column data to excel row data to sort addre | Excel Discussion (Misc queries) | |||
data sort is not including all columns in sort | Excel Discussion (Misc queries) | |||
How do I sort a column of data and have each data row sort accordi | Excel Discussion (Misc queries) |