Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Retrieve whole row of data
I want to sift for one cell in a row, then have the other cells retrieved
also. Say the data table is this: A B C 1 110 8 7 2 130 11 8 3 90 5 6 In cell A20 I enter =small(A1:A3,1) In cell A21 I enter =small(A1:A3,2) In cell A22 I enter =small(A1:A3,3) Here is the result I want. A B C 20 90 5 6 21 110 8 7 22 130 11 8 So I know how to get column A, but how about the rest of the row? Thanks in advance for any help. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Retrieve whole row of data
In B20:
=VLOOKUP($A20,$A$1:$C$3,2,0) in C20: =VLOOKUP($A20,$A$1:$C$3,3,0) fill down. Notice that the only difference between the two formulas is ,2, in one ,3, in the other. Check Help for VLOOKUP() for more information, but that parameter says which column from the lookup matrix to return values from when a match is found in the first column. "manxman" wrote: I want to sift for one cell in a row, then have the other cells retrieved also. Say the data table is this: A B C 1 110 8 7 2 130 11 8 3 90 5 6 In cell A20 I enter =small(A1:A3,1) In cell A21 I enter =small(A1:A3,2) In cell A22 I enter =small(A1:A3,3) Here is the result I want. A B C 20 90 5 6 21 110 8 7 22 130 11 8 So I know how to get column A, but how about the rest of the row? Thanks in advance for any help. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Retrieve whole row of data
Thanks for the help JLatham. I have used Vlookup extensively, but didn't
realize it could be used in that fashion. There is a problem in that the real data table has duplicate values in Col A with unique values in Col B & C. I have modified the illustration accordingly. Using Vlookup returns a duplicate all the way across for Col A=110. Modified data Desired result Result with Vlookup A B C A B C A B C 1 110 8 7 20 90 5 6 20 90 5 6 2 130 11 8 21 110 8 7 21 110 8 7 3 90 5 6 22 110 4 4 22 110 8 7 4 110 4 4 23 130 11 8 23 130 11 8 So somehow we need to retrieve values for Col B & C that are unique to that row. "JLatham" wrote: In B20: =VLOOKUP($A20,$A$1:$C$3,2,0) in C20: =VLOOKUP($A20,$A$1:$C$3,3,0) fill down. Notice that the only difference between the two formulas is ,2, in one ,3, in the other. Check Help for VLOOKUP() for more information, but that parameter says which column from the lookup matrix to return values from when a match is found in the first column. "manxman" wrote: I want to sift for one cell in a row, then have the other cells retrieved also. Say the data table is this: A B C 1 110 8 7 2 130 11 8 3 90 5 6 In cell A20 I enter =small(A1:A3,1) In cell A21 I enter =small(A1:A3,2) In cell A22 I enter =small(A1:A3,3) Here is the result I want. A B C 20 90 5 6 21 110 8 7 22 130 11 8 So I know how to get column A, but how about the rest of the row? Thanks in advance for any help. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Retrieve whole row of data
"manxman" wrote: Thanks for the help JLatham. I have used Vlookup extensively, but didn't realize it could be used in that fashion. There is a problem in that the real data table has duplicate values in Col A with unique values in Col B & C. I have modified the illustration accordingly. Using Vlookup returns a duplicate all the way across for Col A=110. Modified data Desired result Result with Vlookup A B C A B C A B C 1 110 8 7 20 90 5 6 20 90 5 6 2 130 11 8 21 110 8 7 21 110 8 7 3 90 5 6 22 110 4 4 22 110 8 7 4 110 4 4 23 130 11 8 23 130 11 8 So somehow we need to retrieve values for Col B & C that are unique to that row. "JLatham" wrote: In B20: =VLOOKUP($A20,$A$1:$C$3,2,0) in C20: =VLOOKUP($A20,$A$1:$C$3,3,0) fill down. Notice that the only difference between the two formulas is ,2, in one ,3, in the other. Check Help for VLOOKUP() for more information, but that parameter says which column from the lookup matrix to return values from when a match is found in the first column. "manxman" wrote: I want to sift for one cell in a row, then have the other cells retrieved also. Say the data table is this: A B C 1 110 8 7 2 130 11 8 3 90 5 6 In cell A20 I enter =small(A1:A3,1) In cell A21 I enter =small(A1:A3,2) In cell A22 I enter =small(A1:A3,3) Here is the result I want. A B C 20 90 5 6 21 110 8 7 22 130 11 8 So I know how to get column A, but how about the rest of the row? Thanks in advance for any help. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Retrieve whole row of data
Yes, VLOOKUP() has that drawback - stops looking at the first match.
Probably going to have to switch to some creative use of match and index. Let me think on it, meanwhile some really sharp worksheet function person may step in with the solution while I'm still scratching my head. "manxman" wrote: "manxman" wrote: Thanks for the help JLatham. I have used Vlookup extensively, but didn't realize it could be used in that fashion. There is a problem in that the real data table has duplicate values in Col A with unique values in Col B & C. I have modified the illustration accordingly. Using Vlookup returns a duplicate all the way across for Col A=110. Modified data Desired result Result with Vlookup A B C A B C A B C 1 110 8 7 20 90 5 6 20 90 5 6 2 130 11 8 21 110 8 7 21 110 8 7 3 90 5 6 22 110 4 4 22 110 8 7 4 110 4 4 23 130 11 8 23 130 11 8 So somehow we need to retrieve values for Col B & C that are unique to that row. "JLatham" wrote: In B20: =VLOOKUP($A20,$A$1:$C$3,2,0) in C20: =VLOOKUP($A20,$A$1:$C$3,3,0) fill down. Notice that the only difference between the two formulas is ,2, in one ,3, in the other. Check Help for VLOOKUP() for more information, but that parameter says which column from the lookup matrix to return values from when a match is found in the first column. "manxman" wrote: I want to sift for one cell in a row, then have the other cells retrieved also. Say the data table is this: A B C 1 110 8 7 2 130 11 8 3 90 5 6 In cell A20 I enter =small(A1:A3,1) In cell A21 I enter =small(A1:A3,2) In cell A22 I enter =small(A1:A3,3) Here is the result I want. A B C 20 90 5 6 21 110 8 7 22 130 11 8 So I know how to get column A, but how about the rest of the row? Thanks in advance for any help. |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Retrieve whole row of data
I am certain someone will have a better solution for you at this site.
Perhaps temporarily if you can sort your data by column A ascending, then you can change the range reference slightly before filling down thus deleting one of the repeats each time............example for the B20..=VLOOKUP($A20,A1:$C$3,2,0) and for C20...=VLOOKUP($A20,A1:$C$3,3,0)..........Will "JLatham" wrote: Yes, VLOOKUP() has that drawback - stops looking at the first match. Probably going to have to switch to some creative use of match and index. Let me think on it, meanwhile some really sharp worksheet function person may step in with the solution while I'm still scratching my head. "manxman" wrote: "manxman" wrote: Thanks for the help JLatham. I have used Vlookup extensively, but didn't realize it could be used in that fashion. There is a problem in that the real data table has duplicate values in Col A with unique values in Col B & C. I have modified the illustration accordingly. Using Vlookup returns a duplicate all the way across for Col A=110. Modified data Desired result Result with Vlookup A B C A B C A B C 1 110 8 7 20 90 5 6 20 90 5 6 2 130 11 8 21 110 8 7 21 110 8 7 3 90 5 6 22 110 4 4 22 110 8 7 4 110 4 4 23 130 11 8 23 130 11 8 So somehow we need to retrieve values for Col B & C that are unique to that row. "JLatham" wrote: In B20: =VLOOKUP($A20,$A$1:$C$3,2,0) in C20: =VLOOKUP($A20,$A$1:$C$3,3,0) fill down. Notice that the only difference between the two formulas is ,2, in one ,3, in the other. Check Help for VLOOKUP() for more information, but that parameter says which column from the lookup matrix to return values from when a match is found in the first column. "manxman" wrote: I want to sift for one cell in a row, then have the other cells retrieved also. Say the data table is this: A B C 1 110 8 7 2 130 11 8 3 90 5 6 In cell A20 I enter =small(A1:A3,1) In cell A21 I enter =small(A1:A3,2) In cell A22 I enter =small(A1:A3,3) Here is the result I want. A B C 20 90 5 6 21 110 8 7 22 130 11 8 So I know how to get column A, but how about the rest of the row? Thanks in advance for any help. |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Retrieve whole row of data
One play to try ..
Assume source data in cols A to D (eg below), from row2 down, where the key col = col B (110,90,130,..) whose values are to be autosorted in ascending order with corresponding data in cols C and D following intact 1 110 8 7 2 130 11 8 3 90 5 6 4 110 4 4 etc (I ignored col A which is just a serial numbering?) In F2: =IF(B2="","",B2+ROW()/10^10) Leave F1 blank In G2: =IF(ROWS($1:1)COUNT($F:$F),"",INDEX(B:B,MATCH(SMA LL($F:$F,ROWS($1:1)),$F:$F,0))) Copy G2 to I2. Select F2:I2, copy down to cover the max expected extent of source data in cols B to D. Hide away col F. Cols G to I will return the required results, all neatly bunched at the top. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "manxman" wrote: "manxman" wrote: Thanks for the help JLatham. I have used Vlookup extensively, but didn't realize it could be used in that fashion. There is a problem in that the real data table has duplicate values in Col A with unique values in Col B & C. I have modified the illustration accordingly. Using Vlookup returns a duplicate all the way across for Col A=110. Modified data Desired result Result with Vlookup A B C A B C A B C 1 110 8 7 20 90 5 6 20 90 5 6 2 130 11 8 21 110 8 7 21 110 8 7 3 90 5 6 22 110 4 4 22 110 8 7 4 110 4 4 23 130 11 8 23 130 11 8 So somehow we need to retrieve values for Col B & C that are unique to that row. |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Retrieve whole row of data
Thanks Max, that does the trick. Now I'll study for awhile to try and
understand your solution, and then see if I can get it to work on the real data. "Max" wrote: One play to try .. Assume source data in cols A to D (eg below), from row2 down, where the key col = col B (110,90,130,..) whose values are to be autosorted in ascending order with corresponding data in cols C and D following intact 1 110 8 7 2 130 11 8 3 90 5 6 4 110 4 4 etc (I ignored col A which is just a serial numbering?) In F2: =IF(B2="","",B2+ROW()/10^10) Leave F1 blank In G2: =IF(ROWS($1:1)COUNT($F:$F),"",INDEX(B:B,MATCH(SMA LL($F:$F,ROWS($1:1)),$F:$F,0))) Copy G2 to I2. Select F2:I2, copy down to cover the max expected extent of source data in cols B to D. Hide away col F. Cols G to I will return the required results, all neatly bunched at the top. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "manxman" wrote: "manxman" wrote: Thanks for the help JLatham. I have used Vlookup extensively, but didn't realize it could be used in that fashion. There is a problem in that the real data table has duplicate values in Col A with unique values in Col B & C. I have modified the illustration accordingly. Using Vlookup returns a duplicate all the way across for Col A=110. Modified data Desired result Result with Vlookup A B C A B C A B C 1 110 8 7 20 90 5 6 20 90 5 6 2 130 11 8 21 110 8 7 21 110 8 7 3 90 5 6 22 110 4 4 22 110 8 7 4 110 4 4 23 130 11 8 23 130 11 8 So somehow we need to retrieve values for Col B & C that are unique to that row. |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Retrieve whole row of data
welcome, manxman.
-- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "manxman" wrote in message ... Thanks Max, that does the trick. Now I'll study for awhile to try and understand your solution, and then see if I can get it to work on the real data. |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Retrieve whole row of data
Hi Max
I'm trying to look-up my source data and only return the multiple unique values. However the formulas i'm using are returning the multiple values (including dupes) INDEX(Closed YTD_1'!$A$1:$M$9908,SMALL(IF('Closed YTD_1'!$A$1:$A$9908=$C$4,ROW('Closed YTD_1'!$A$1:$A$9908)),ROW(1:1)),11)) any idea how this can be fixed? -- nikko "Max" wrote: welcome, manxman. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "manxman" wrote in message ... Thanks Max, that does the trick. Now I'll study for awhile to try and understand your solution, and then see if I can get it to work on the real data. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Retrieve multiple data rows data from a very long list and copy t | Excel Discussion (Misc queries) | |||
Retrieve xml data from SharePoint | Excel Worksheet Functions | |||
Retrieve data | Excel Discussion (Misc queries) | |||
How can I retrieve deleted data? | Excel Discussion (Misc queries) | |||
Retrieve last cell with data | Excel Worksheet Functions |