Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Brian Ferris
 
Posts: n/a
Default 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



  #2   Report Post  
Bob Phillips
 
Posts: n/a
Default 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





  #3   Report Post  
Brian Ferris
 
Posts: n/a
Default 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






  #4   Report Post  
William Horton
 
Posts: n/a
Default 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






  #5   Report Post  
Ron Rosenfeld
 
Posts: n/a
Default 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


  #6   Report Post  
Bob Phillips
 
Posts: n/a
Default 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








  #7   Report Post  
Max
 
Posts: n/a
Default 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
--


  #9   Report Post  
Max
 
Posts: n/a
Default 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



  #10   Report Post  
Max
 
Posts: n/a
Default 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
--


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Offset And Vlookup Combo Help!! kollizion Excel Worksheet Functions 3 August 30th 05 05:07 AM
Vlookup then OFFSET over and down mendozalaura Excel Worksheet Functions 3 August 24th 05 12:32 AM
Which to use - if, vlookup, match, index, offset, vba? punsterr Excel Discussion (Misc queries) 3 June 7th 05 07:42 PM
VLOOKUP, OFFSET, MATCH PROBLEM, HELP? Steve Excel Worksheet Functions 0 January 30th 05 10:11 PM
offset and vlookup cutsygurl Excel Worksheet Functions 1 November 5th 04 10:47 PM


All times are GMT +1. The time now is 10:32 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"