|
Return a unique value
I use "=VLOOKUP($B$4,Sheet1!$G$2:$W$137,17,FALSE)" to find cells in column
"G" in "Sheet1" that contains the same value as in "B4" and return the value in column "W" in "Sheet1". My problem is that the numbers in column "W" are unique but not the numbers in column "G". In the next cell I want to do the same and also look for another cell in Column "G" in "Sheet1" with the same value as in "B4", but I will always get the first found result. I want to look for the next cell in column "G" in "Sheet1" that contains the same value as in "B4" but with another value in Column "W" than the first found. Is my question clear? Probably not, but I would very much appreciate if some one could help me. /Confused |
Here is a formula that I think works, courtesy of Domenic. Put it in F4 and
copy across until you get a blank (no more) =IF(COLUMN()-COLUMN($F4)+1<=COUNTIF(Sheet1!$G$2:$G$6,$B4),INDEX (Sheet1!$W$2: $W$6,SMALL(IF(Sheet1!$G$2:$G$6=$B4,ROW(Sheet1!$G$2 :$G$6)-CELL("row",Sheet1!$ G$2)+1),COLUMN()-COLUMN($F4)+1)),"") It is an array formula, so commit with Ctrl-Shift-Enter. -- HTH Bob Phillips "Confused" wrote in message ... I use "=VLOOKUP($B$4,Sheet1!$G$2:$W$137,17,FALSE)" to find cells in column "G" in "Sheet1" that contains the same value as in "B4" and return the value in column "W" in "Sheet1". My problem is that the numbers in column "W" are unique but not the numbers in column "G". In the next cell I want to do the same and also look for another cell in Column "G" in "Sheet1" with the same value as in "B4", but I will always get the first found result. I want to look for the next cell in column "G" in "Sheet1" that contains the same value as in "B4" but with another value in Column "W" than the first found. Is my question clear? Probably not, but I would very much appreciate if some one could help me. /Confused |
Thank you very much for your help, but I am sorry this is not working.
"Bob Phillips" wrote: Here is a formula that I think works, courtesy of Domenic. Put it in F4 and copy across until you get a blank (no more) =IF(COLUMN()-COLUMN($F4)+1<=COUNTIF(Sheet1!$G$2:$G$6,$B4),INDEX (Sheet1!$W$2: $W$6,SMALL(IF(Sheet1!$G$2:$G$6=$B4,ROW(Sheet1!$G$2 :$G$6)-CELL("row",Sheet1!$ G$2)+1),COLUMN()-COLUMN($F4)+1)),"") It is an array formula, so commit with Ctrl-Shift-Enter. -- HTH Bob Phillips "Confused" wrote in message ... I use "=VLOOKUP($B$4,Sheet1!$G$2:$W$137,17,FALSE)" to find cells in column "G" in "Sheet1" that contains the same value as in "B4" and return the value in column "W" in "Sheet1". My problem is that the numbers in column "W" are unique but not the numbers in column "G". In the next cell I want to do the same and also look for another cell in Column "G" in "Sheet1" with the same value as in "B4", but I will always get the first found result. I want to look for the next cell in column "G" in "Sheet1" that contains the same value as in "B4" but with another value in Column "W" than the first found. Is my question clear? Probably not, but I would very much appreciate if some one could help me. /Confused |
In what way does it not work?
-- HTH Bob Phillips "Confused" wrote in message ... Thank you very much for your help, but I am sorry this is not working. "Bob Phillips" wrote: Here is a formula that I think works, courtesy of Domenic. Put it in F4 and copy across until you get a blank (no more) =IF(COLUMN()-COLUMN($F4)+1<=COUNTIF(Sheet1!$G$2:$G$6,$B4),INDEX (Sheet1!$W$2: $W$6,SMALL(IF(Sheet1!$G$2:$G$6=$B4,ROW(Sheet1!$G$2 :$G$6)-CELL("row",Sheet1!$ G$2)+1),COLUMN()-COLUMN($F4)+1)),"") It is an array formula, so commit with Ctrl-Shift-Enter. -- HTH Bob Phillips "Confused" wrote in message ... I use "=VLOOKUP($B$4,Sheet1!$G$2:$W$137,17,FALSE)" to find cells in column "G" in "Sheet1" that contains the same value as in "B4" and return the value in column "W" in "Sheet1". My problem is that the numbers in column "W" are unique but not the numbers in column "G". In the next cell I want to do the same and also look for another cell in Column "G" in "Sheet1" with the same value as in "B4", but I will always get the first found result. I want to look for the next cell in column "G" in "Sheet1" that contains the same value as in "B4" but with another value in Column "W" than the first found. Is my question clear? Probably not, but I would very much appreciate if some one could help me. /Confused |
In article ,
"Confused" wrote: Thank you very much for your help, but I am sorry this is not working. Make sure that you confirm the formula with CONTROL+SHIFT+ENTER, not just ENTER. Also, have you adjusted the range for your source data? Try... F4, copied across: =IF(COLUMNS($F4:F4)<=COUNTIF(Sheet1!$G$2:$G$137,$B 4),INDEX(Sheet1!$W$2:$W $137,SMALL(IF(Sheet1!$G$2:$G$137=$B4,ROW(Sheet1!$G $2:$G$137)-ROW(Sheet1!$ G$2)+1),COLUMNS($F4:F4))),"") And, of course, if you want your results to be returned in C4, C5, etc., change COLUMNS($F4:F4) to COLUMNS($C4:C4). Hope this helps! |
Still not working. I get #VALUE!. I will try to be more specific.
Sheet1: G H I ...... W 1 123 3 456 1 789 8 111 In Sheet 2 I want to look in "Sheet1" for all cells in Column G that contains "1" and then return the value in the same row but column W, as below: 123 789 Thanks in advance "Domenic" wrote: In article , "Confused" wrote: Thank you very much for your help, but I am sorry this is not working. Make sure that you confirm the formula with CONTROL+SHIFT+ENTER, not just ENTER. Also, have you adjusted the range for your source data? Try... F4, copied across: =IF(COLUMNS($F4:F4)<=COUNTIF(Sheet1!$G$2:$G$137,$B 4),INDEX(Sheet1!$W$2:$W $137,SMALL(IF(Sheet1!$G$2:$G$137=$B4,ROW(Sheet1!$G $2:$G$137)-ROW(Sheet1!$ G$2)+1),COLUMNS($F4:F4))),"") And, of course, if you want your results to be returned in C4, C5, etc., change COLUMNS($F4:F4) to COLUMNS($C4:C4). Hope this helps! |
try autofilter custom <equalls =1* in the first column only
then the range is filtered and you can copy these visible cells only to sheet2 then in sheet1 you can remove the autofilter Confused wrote in message ... Still not working. I get #VALUE!. I will try to be more specific. Sheet1: G H I ...... W 1 123 3 456 1 789 8 111 In Sheet 2 I want to look in "Sheet1" for all cells in Column G that contains "1" and then return the value in the same row but column W, as below: 123 789 Thanks in advance "Domenic" wrote: In article , "Confused" wrote: Thank you very much for your help, but I am sorry this is not working. Make sure that you confirm the formula with CONTROL+SHIFT+ENTER, not just ENTER. Also, have you adjusted the range for your source data? Try... F4, copied across: =IF(COLUMNS($F4:F4)<=COUNTIF(Sheet1!$G$2:$G$137,$B 4),INDEX(Sheet1!$W$2:$W $137,SMALL(IF(Sheet1!$G$2:$G$137=$B4,ROW(Sheet1!$G $2:$G$137)-ROW(Sheet1!$ G$2)+1),COLUMNS($F4:F4))),"") And, of course, if you want your results to be returned in C4, C5, etc., change COLUMNS($F4:F4) to COLUMNS($C4:C4). Hope this helps! |
It could work, but I have alot of data so this would actually take a long
time, and the data is quite dynamic so I would need to this often. Thank you but I would appreciate if someone could help me to solve this more automatically. "R.VENKATARAMAN" wrote: try autofilter custom <equalls =1* in the first column only then the range is filtered and you can copy these visible cells only to sheet2 then in sheet1 you can remove the autofilter Confused wrote in message ... Still not working. I get #VALUE!. I will try to be more specific. Sheet1: G H I ...... W 1 123 3 456 1 789 8 111 In Sheet 2 I want to look in "Sheet1" for all cells in Column G that contains "1" and then return the value in the same row but column W, as below: 123 789 Thanks in advance "Domenic" wrote: In article , "Confused" wrote: Thank you very much for your help, but I am sorry this is not working. Make sure that you confirm the formula with CONTROL+SHIFT+ENTER, not just ENTER. Also, have you adjusted the range for your source data? Try... F4, copied across: =IF(COLUMNS($F4:F4)<=COUNTIF(Sheet1!$G$2:$G$137,$B 4),INDEX(Sheet1!$W$2:$W $137,SMALL(IF(Sheet1!$G$2:$G$137=$B4,ROW(Sheet1!$G $2:$G$137)-ROW(Sheet1!$ G$2)+1),COLUMNS($F4:F4))),"") And, of course, if you want your results to be returned in C4, C5, etc., change COLUMNS($F4:F4) to COLUMNS($C4:C4). Hope this helps! |
If the functions in the freely downloadable file at
http:/home.pacbell.net/beban are available to your workbook =INDEX(VLookups($B$4,Sheet1!$G$2:$W$137,17),ROW(A1 )) filled down as far as required. Alan Beban Confused wrote: It could work, but I have alot of data so this would actually take a long time, and the data is quite dynamic so I would need to this often. Thank you but I would appreciate if someone could help me to solve this more automatically. "R.VENKATARAMAN" wrote: try autofilter custom <equalls =1* in the first column only then the range is filtered and you can copy these visible cells only to sheet2 then in sheet1 you can remove the autofilter Confused wrote in message ... Still not working. I get #VALUE!. I will try to be more specific. Sheet1: G H I ...... W 1 123 3 456 1 789 8 111 In Sheet 2 I want to look in "Sheet1" for all cells in Column G that contains "1" and then return the value in the same row but column W, as below: 123 789 Thanks in advance "Domenic" wrote: In article , "Confused" wrote: Thank you very much for your help, but I am sorry this is not working. Make sure that you confirm the formula with CONTROL+SHIFT+ENTER, not just ENTER. Also, have you adjusted the range for your source data? Try... F4, copied across: =IF(COLUMNS($F4:F4)<=COUNTIF(Sheet1!$G$2:$G$137, $B4),INDEX(Sheet1!$W$2:$W $137,SMALL(IF(Sheet1!$G$2:$G$137=$B4,ROW(Sheet1! $G$2:$G$137)-ROW(Sheet1!$ G$2)+1),COLUMNS($F4:F4))),"") And, of course, if you want your results to be returned in C4, C5, etc., change COLUMNS($F4:F4) to COLUMNS($C4:C4). Hope this helps! |
Thanks, though this excel-file will be used from many different computers and
I can't make sure that your file will be downloaded to all these computers. Any other suggestions? "Alan Beban" wrote: If the functions in the freely downloadable file at http:/home.pacbell.net/beban are available to your workbook =INDEX(VLookups($B$4,Sheet1!$G$2:$W$137,17),ROW(A1 )) filled down as far as required. Alan Beban Confused wrote: It could work, but I have alot of data so this would actually take a long time, and the data is quite dynamic so I would need to this often. Thank you but I would appreciate if someone could help me to solve this more automatically. "R.VENKATARAMAN" wrote: try autofilter custom <equalls =1* in the first column only then the range is filtered and you can copy these visible cells only to sheet2 then in sheet1 you can remove the autofilter Confused wrote in message ... Still not working. I get #VALUE!. I will try to be more specific. Sheet1: G H I ...... W 1 123 3 456 1 789 8 111 In Sheet 2 I want to look in "Sheet1" for all cells in Column G that contains "1" and then return the value in the same row but column W, as below: 123 789 Thanks in advance "Domenic" wrote: In article , "Confused" wrote: Thank you very much for your help, but I am sorry this is not working. Make sure that you confirm the formula with CONTROL+SHIFT+ENTER, not just ENTER. Also, have you adjusted the range for your source data? Try... F4, copied across: =IF(COLUMNS($F4:F4)<=COUNTIF(Sheet1!$G$2:$G$137, $B4),INDEX(Sheet1!$W$2:$W $137,SMALL(IF(Sheet1!$G$2:$G$137=$B4,ROW(Sheet1! $G$2:$G$137)-ROW(Sheet1!$ G$2)+1),COLUMNS($F4:F4))),"") And, of course, if you want your results to be returned in C4, C5, etc., change COLUMNS($F4:F4) to COLUMNS($C4:C4). Hope this helps! |
On Sheet2, enter the following formula in A1 and copy down:
=IF(ROWS(A$1:A1)<=COUNTIF(Sheet1!$G$1:$G$4,1),INDE X(Sheet1!W$1:W$4,SMALL( IF(Sheet1!$G$1:$G$4=1,ROW(Sheet1!$G$1:$G$4)-ROW(Sheet1!$G$1)+1),ROWS(A$1: A1))),"") The formula needs to be confirmed with CONTROL+SHIFT+ENTER, not just ENTER. (Press the CONTROL and SHIFT keys down, then while those keys are pressed down hit ENTER. Excel will place braces {} around the formula which will tell you that you've entered the formula correctly.) Adjust the range accordingly. Hope this helps! In article , "Confused" wrote: Still not working. I get #VALUE!. I will try to be more specific. Sheet1: G H I ...... W 1 123 3 456 1 789 8 111 In Sheet 2 I want to look in "Sheet1" for all cells in Column G that contains "1" and then return the value in the same row but column W, as below: 123 789 Thanks in advance |
Domenic's array formula can be generalized, with
luVal referring to the cell containing the lookup value Tbl referring to the range of the lookup table luCol referring to the cell containing the lookup column from which the value is to be returned =IF(ROWS(A$1:A1)<=COUNTIF(INDEX(Tbl,0,1),luVal),IN DEX(INDEX(Tbl,0,luCol),SMALL(IF(INDEX(Tbl,0,1)=luV al,ROW(INDIRECT("A1:A"&ROWS(Tbl)))),ROWS(A$1:A1))) ,"") A portion of it: =INDEX(INDEX(Tbl,0,luCol),SMALL(IF(INDEX(Tbl,0,1)= luVal,ROW(INDIRECT("A1:A"&ROWS(Tbl)))),ROWS(A$1:A1 ))) is a built-in equivalent of the non-array VLookups formula =INDEX(VLookups(luVal,Tbl,luCol),ROW(A1)) With the built-in formulas one can substitute an array of column numbers for luCol. Alan Beban Domenic wrote: On Sheet2, enter the following formula in A1 and copy down: =IF(ROWS(A$1:A1)<=COUNTIF(Sheet1!$G$1:$G$4,1),INDE X(Sheet1!W$1:W$4,SMALL( IF(Sheet1!$G$1:$G$4=1,ROW(Sheet1!$G$1:$G$4)-ROW(Sheet1!$G$1)+1),ROWS(A$1: A1))),"") The formula needs to be confirmed with CONTROL+SHIFT+ENTER, not just ENTER. (Press the CONTROL and SHIFT keys down, then while those keys are pressed down hit ENTER. Excel will place braces {} around the formula which will tell you that you've entered the formula correctly.) Adjust the range accordingly. Hope this helps! In article , "Confused" wrote: Still not working. I get #VALUE!. I will try to be more specific. Sheet1: G H I ...... W 1 123 3 456 1 789 8 111 In Sheet 2 I want to look in "Sheet1" for all cells in Column G that contains "1" and then return the value in the same row but column W, as below: 123 789 Thanks in advance |
Thanks a lot! It works perfect.
"Domenic" wrote: On Sheet2, enter the following formula in A1 and copy down: =IF(ROWS(A$1:A1)<=COUNTIF(Sheet1!$G$1:$G$4,1),INDE X(Sheet1!W$1:W$4,SMALL( IF(Sheet1!$G$1:$G$4=1,ROW(Sheet1!$G$1:$G$4)-ROW(Sheet1!$G$1)+1),ROWS(A$1: A1))),"") The formula needs to be confirmed with CONTROL+SHIFT+ENTER, not just ENTER. (Press the CONTROL and SHIFT keys down, then while those keys are pressed down hit ENTER. Excel will place braces {} around the formula which will tell you that you've entered the formula correctly.) Adjust the range accordingly. Hope this helps! In article , "Confused" wrote: Still not working. I get #VALUE!. I will try to be more specific. Sheet1: G H I ...... W 1 123 3 456 1 789 8 111 In Sheet 2 I want to look in "Sheet1" for all cells in Column G that contains "1" and then return the value in the same row but column W, as below: 123 789 Thanks in advance |
| All times are GMT +1. The time now is 04:52 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com