|
Vlookup is not enough ... can OFFSET be used ?
Hi there,
Please help me out with the folliwing query. This is my data which I wish to lookup: Group name 12 Brian 33 Norman 12 Victor 12 Sonia 25 Mary 33 Horris 25 Peter 44 james 66 Holden I wish to select say groups 12 & 33 and wish to obtain the following answer (i.e. list all under each other who belong to that group) Thereof 12 Brian 12 Victor 12 Sonia 33 Norman 33 Horris Is this possible ... I hope I made myself clear enough. If not, please reply and I will elaborate further. Thanks, Brian |
Vlookup is not enough ... can OFFSET be used ?
Brian,
Assuming that the data is in columns A & B, use these formulae =IF(ISERROR(SMALL(IF($A1:$A20={12,33},ROW($A1:$A20 ),""),ROW($A1:$A20))),"", INDEX(A1:A20,SMALL(IF($A1:$A20={12,33},ROW($A1:$A2 0),""),ROW($A1:$A20)))) =IF(ISERROR(SMALL(IF($A1:$A20={12,33},ROW($A1:$A20 ),""),ROW($A1:$A20))),"", INDEX(B1:B20,SMALL(IF($A1:$A20={12,33},ROW($A1:$A2 0),""),ROW($A1:$A20)))) These are both array formulae, but you don't enter in just one cell at a time, but a block. So, assuming your source data has 20 rows, select 20 rows elsehwere on the spreadsheet, add the first formula to the formula bar, and commit with Ctrl-Shift-Enter, Then repeat in an adjacent column with the second formula. -- HTH RP (remove nothere from the email address if mailing direct) "Brian Ferris" wrote in message ... Hi there, Please help me out with the folliwing query. This is my data which I wish to lookup: Group name 12 Brian 33 Norman 12 Victor 12 Sonia 25 Mary 33 Horris 25 Peter 44 james 66 Holden I wish to select say groups 12 & 33 and wish to obtain the following answer (i.e. list all under each other who belong to that group) Thereof 12 Brian 12 Victor 12 Sonia 33 Norman 33 Horris Is this possible ... I hope I made myself clear enough. If not, please reply and I will elaborate further. Thanks, Brian |
Vlookup is not enough ... can OFFSET be used ?
Hi Bob ...
Thanks for your reply, However the 12 & 33 was just an example, these group numbers will always change and there will be more than 100 groups making it impossible to enter in the formula !!! Is there some other way ??? Thanks in advance once again, Brian "Bob Phillips" wrote: Brian, Assuming that the data is in columns A & B, use these formulae =IF(ISERROR(SMALL(IF($A1:$A20={12,33},ROW($A1:$A20 ),""),ROW($A1:$A20))),"", INDEX(A1:A20,SMALL(IF($A1:$A20={12,33},ROW($A1:$A2 0),""),ROW($A1:$A20)))) =IF(ISERROR(SMALL(IF($A1:$A20={12,33},ROW($A1:$A20 ),""),ROW($A1:$A20))),"", INDEX(B1:B20,SMALL(IF($A1:$A20={12,33},ROW($A1:$A2 0),""),ROW($A1:$A20)))) These are both array formulae, but you don't enter in just one cell at a time, but a block. So, assuming your source data has 20 rows, select 20 rows elsehwere on the spreadsheet, add the first formula to the formula bar, and commit with Ctrl-Shift-Enter, Then repeat in an adjacent column with the second formula. -- HTH RP (remove nothere from the email address if mailing direct) "Brian Ferris" wrote in message ... Hi there, Please help me out with the folliwing query. This is my data which I wish to lookup: Group name 12 Brian 33 Norman 12 Victor 12 Sonia 25 Mary 33 Horris 25 Peter 44 james 66 Holden I wish to select say groups 12 & 33 and wish to obtain the following answer (i.e. list all under each other who belong to that group) Thereof 12 Brian 12 Victor 12 Sonia 33 Norman 33 Horris Is this possible ... I hope I made myself clear enough. If not, please reply and I will elaborate further. Thanks, Brian |
Vlookup is not enough ... can OFFSET be used ?
You could try using Excel's autofilter, or advanced filter, or pivot table
functionality to tackle this issue. It sounds like maybe advanced filter may work the best (Menu path Data / Filter / Advanced Filter). Excel help is pretty good at explaining it. Hope this gives you a start. Bill Horton "Brian Ferris" wrote: Hi Bob ... Thanks for your reply, However the 12 & 33 was just an example, these group numbers will always change and there will be more than 100 groups making it impossible to enter in the formula !!! Is there some other way ??? Thanks in advance once again, Brian "Bob Phillips" wrote: Brian, Assuming that the data is in columns A & B, use these formulae =IF(ISERROR(SMALL(IF($A1:$A20={12,33},ROW($A1:$A20 ),""),ROW($A1:$A20))),"", INDEX(A1:A20,SMALL(IF($A1:$A20={12,33},ROW($A1:$A2 0),""),ROW($A1:$A20)))) =IF(ISERROR(SMALL(IF($A1:$A20={12,33},ROW($A1:$A20 ),""),ROW($A1:$A20))),"", INDEX(B1:B20,SMALL(IF($A1:$A20={12,33},ROW($A1:$A2 0),""),ROW($A1:$A20)))) These are both array formulae, but you don't enter in just one cell at a time, but a block. So, assuming your source data has 20 rows, select 20 rows elsehwere on the spreadsheet, add the first formula to the formula bar, and commit with Ctrl-Shift-Enter, Then repeat in an adjacent column with the second formula. -- HTH RP (remove nothere from the email address if mailing direct) "Brian Ferris" wrote in message ... Hi there, Please help me out with the folliwing query. This is my data which I wish to lookup: Group name 12 Brian 33 Norman 12 Victor 12 Sonia 25 Mary 33 Horris 25 Peter 44 james 66 Holden I wish to select say groups 12 & 33 and wish to obtain the following answer (i.e. list all under each other who belong to that group) Thereof 12 Brian 12 Victor 12 Sonia 33 Norman 33 Horris Is this possible ... I hope I made myself clear enough. If not, please reply and I will elaborate further. Thanks, Brian |
Vlookup is not enough ... can OFFSET be used ?
On Thu, 27 Oct 2005 04:40:02 -0700, Brian Ferris
wrote: Hi there, Please help me out with the folliwing query. This is my data which I wish to lookup: Group name 12 Brian 33 Norman 12 Victor 12 Sonia 25 Mary 33 Horris 25 Peter 44 james 66 Holden I wish to select say groups 12 & 33 and wish to obtain the following answer (i.e. list all under each other who belong to that group) Thereof 12 Brian 12 Victor 12 Sonia 33 Norman 33 Horris Is this possible ... I hope I made myself clear enough. If not, please reply and I will elaborate further. Thanks, Brian Without using VBA, the simplest method would be to first sort the data by Group; and then use the AutoFilter to select the groups. You can select two individual groups or you could probably combine some of the other operators to come up with other combinations. If that is not enough, you could use the Advanced Filter with criteria ranges. All of the above are options under the Data menu at the top menu bar. --ron |
Vlookup is not enough ... can OFFSET be used ?
Put the numbers in a range, and then just replace {12,33} with that range
reference. -- HTH RP (remove nothere from the email address if mailing direct) "Brian Ferris" wrote in message ... Hi Bob ... Thanks for your reply, However the 12 & 33 was just an example, these group numbers will always change and there will be more than 100 groups making it impossible to enter in the formula !!! Is there some other way ??? Thanks in advance once again, Brian "Bob Phillips" wrote: Brian, Assuming that the data is in columns A & B, use these formulae =IF(ISERROR(SMALL(IF($A1:$A20={12,33},ROW($A1:$A20 ),""),ROW($A1:$A20))),"", INDEX(A1:A20,SMALL(IF($A1:$A20={12,33},ROW($A1:$A2 0),""),ROW($A1:$A20)))) =IF(ISERROR(SMALL(IF($A1:$A20={12,33},ROW($A1:$A20 ),""),ROW($A1:$A20))),"", INDEX(B1:B20,SMALL(IF($A1:$A20={12,33},ROW($A1:$A2 0),""),ROW($A1:$A20)))) These are both array formulae, but you don't enter in just one cell at a time, but a block. So, assuming your source data has 20 rows, select 20 rows elsehwere on the spreadsheet, add the first formula to the formula bar, and commit with Ctrl-Shift-Enter, Then repeat in an adjacent column with the second formula. -- HTH RP (remove nothere from the email address if mailing direct) "Brian Ferris" wrote in message ... Hi there, Please help me out with the folliwing query. This is my data which I wish to lookup: Group name 12 Brian 33 Norman 12 Victor 12 Sonia 25 Mary 33 Horris 25 Peter 44 james 66 Holden I wish to select say groups 12 & 33 and wish to obtain the following answer (i.e. list all under each other who belong to that group) Thereof 12 Brian 12 Victor 12 Sonia 33 Norman 33 Horris Is this possible ... I hope I made myself clear enough. If not, please reply and I will elaborate further. Thanks, Brian |
Vlookup is not enough ... can OFFSET be used ?
"Brian Ferris" wrote:
... Is there some other way ??? Another play to try using non-array formulas .. Assume source data is in Sheet1, cols A & B, data from row2 down Using 2 empty cols to the right, cols C and D List the numbers in D1 down: 12, 33 (numbers can be listed in any order) Put in C2: =IF(ISNUMBER(MATCH(A2,D:D,0)),A2+ROW()/10^10,"") Copy C2 down to say, C100, to cover the max expected data in cols A & B (Leave C1 empty) In Sheet2 ----- Put in A2: =IF(ISERROR(SMALL(Sheet1!$C:$C,ROWS($A$1:A1))),"", INDEX(Sheet1!A:A,MATCH( SMALL(Sheet1!$C:$C,ROWS($A$1:A1)),Sheet1!$C:$C,0)) ) Copy A2 across to B2, fill down to B100 (cover the same extent as was done in col C in Sheet1) Sheet2 will return the desired results from Sheet1 in cols A & B, all neatly bunched at the top -- Rgds Max xl 97 --- Singapore, GMT+8 xdemechanik http://savefile.com/projects/236895 -- |
Vlookup is not enough ... can OFFSET be used ?
No prob, Brian. Sample sent.
Here's a quick link to the sample: http://cjoint.com/?kFoDnHknxZ Vlookup_is_not_enough_can_OFFSET_be_used_BrianFerr is_wksht.xls -- Rgds Max xl 97 --- Singapore, GMT+8 xdemechanik http://savefile.com/projects/236895 -- "Brian Ferris" wrote in message ... Hi Max ... Thanks for your suggestion ... I am not sure whether I am asking too much ;) ... is it possible to send me a sample file with the below ...I would very much appreciate it. My e-mail address is < ... Thanks in advance for your support, Brian |
Vlookup is not enough ... can OFFSET be used ?
... a note received from the OP:
Subject: Vlookup is not enough ... can OFFSET be used ? Date: Mon, 31 Oct 2005 15:22:06 +0100 Thanks Max, your help is very much appreciated. Thanks once again you solved my problem ;o) Best regards, Brian Ferris Malta -- Rgds Max xl 97 --- Singapore, GMT+8 xdemechanik http://savefile.com/projects/236895 -- |
| All times are GMT +1. The time now is 09:17 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com