Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
LOU LOU is offline
external usenet poster
 
Posts: 40
Default Find the next occurance

I want to find each occurance of a certain value in a worksheet. The value
can occur once or several times. Each time the value occurs, I need to list
related data.

Hlookup works great to find the first occurance, but how do I get it to find
the next occurance and then the next, etc?
  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 15,768
Default Find the next occurance

Hlookup works great to find the first occurance, but how do
I get it to find the next occurance and then the next, etc?


You have to use a different formula.

Where are the lookup_values? Are they sorted so that they might be grouped
together? Where is the related data that you want returned?

--
Biff
Microsoft Excel MVP


"Lou" wrote in message
...
I want to find each occurance of a certain value in a worksheet. The value
can occur once or several times. Each time the value occurs, I need to
list
related data.

Hlookup works great to find the first occurance, but how do I get it to
find
the next occurance and then the next, etc?



  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
LOU LOU is offline
external usenet poster
 
Posts: 40
Default Find the next occurance

The look up values are in row 1. The related data that I need is in rows 3, 4
and 5. However, there is related data in up to 45 rows. I am trying to get
the look up values and related data listed in a seperate worksheet as a
summary.
Thanks

"T. Valko" wrote:

Hlookup works great to find the first occurance, but how do
I get it to find the next occurance and then the next, etc?


You have to use a different formula.

Where are the lookup_values? Are they sorted so that they might be grouped
together? Where is the related data that you want returned?

--
Biff
Microsoft Excel MVP


"Lou" wrote in message
...
I want to find each occurance of a certain value in a worksheet. The value
can occur once or several times. Each time the value occurs, I need to
list
related data.

Hlookup works great to find the first occurance, but how do I get it to
find
the next occurance and then the next, etc?




  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 15,768
Default Find the next occurance

However, there is related data in up to 45 rows.

Ugh! A formula method probably isn't the best way to go. Maybe a pivot table
?????

--
Biff
Microsoft Excel MVP


"Lou" wrote in message
...
The look up values are in row 1. The related data that I need is in rows
3, 4
and 5. However, there is related data in up to 45 rows. I am trying to
get
the look up values and related data listed in a seperate worksheet as a
summary.
Thanks

"T. Valko" wrote:

Hlookup works great to find the first occurance, but how do
I get it to find the next occurance and then the next, etc?


You have to use a different formula.

Where are the lookup_values? Are they sorted so that they might be
grouped
together? Where is the related data that you want returned?

--
Biff
Microsoft Excel MVP


"Lou" wrote in message
...
I want to find each occurance of a certain value in a worksheet. The
value
can occur once or several times. Each time the value occurs, I need to
list
related data.

Hlookup works great to find the first occurance, but how do I get it to
find
the next occurance and then the next, etc?






  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions
LOU LOU is offline
external usenet poster
 
Posts: 40
Default Find the next occurance

I'll give that a try. Thanks.

"T. Valko" wrote:

However, there is related data in up to 45 rows.


Ugh! A formula method probably isn't the best way to go. Maybe a pivot table
?????

--
Biff
Microsoft Excel MVP


"Lou" wrote in message
...
The look up values are in row 1. The related data that I need is in rows
3, 4
and 5. However, there is related data in up to 45 rows. I am trying to
get
the look up values and related data listed in a seperate worksheet as a
summary.
Thanks

"T. Valko" wrote:

Hlookup works great to find the first occurance, but how do
I get it to find the next occurance and then the next, etc?

You have to use a different formula.

Where are the lookup_values? Are they sorted so that they might be
grouped
together? Where is the related data that you want returned?

--
Biff
Microsoft Excel MVP


"Lou" wrote in message
...
I want to find each occurance of a certain value in a worksheet. The
value
can occur once or several times. Each time the value occurs, I need to
list
related data.

Hlookup works great to find the first occurance, but how do I get it to
find
the next occurance and then the next, etc?








  #6   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1,231
Default Find the next occurance

"T. Valko" wrote...
....
Ugh! A formula method probably isn't the best way to go. Maybe a pivot table
?????

....

Or maybe not. If the OP is fetching data with HLOOKUP, that would
imply identifying values are in row 1 rather than field names. OP
would need to transpose the data and add field names to use a pivot
table. Then there's the question whether any of the data the OP needs
to pull would be text. Pivot tables can't do much with text in the
Data area of a pivot table.

If the data were in a range named Data with possibly duplicate
identifiers in row 1 and fields in different rows rather than
different columns, one way of fetching all data for identifier X would
involve formulas like

A1:
=MATCH(X,INDEX(Data,1,0),0)

A2:
=INDEX(Data,3,A1)

A3:
=INDEX(Data,4,A1)

A4:
=INDEX(Data,5,A1)

which will return the column index of the first match in A1 and the
data in rows 3, 4 and 5 in that column in A2, A3 and A4, respectively.
Then fetch the next possible match in column B using

B1:
=IF(COUNTIF(INDEX(Data,1,0),X)COLUMNS($A1:A1),
MATCH(X,INDEX(Data,1,A1+1):INDEX(Data,1,COLUMNS(Da ta)),0)+A1,"")

B2:
=IF(B$1<"",INDEX(Data,3,B1),"")

B3:
=IF(B$1<"",INDEX(Data,4,B1),"")

B4:
=IF(B$1<"",INDEX(Data,5,B1),"")

Then copy B1:B4 and fill right as far as needed.
  #7   Report Post  
Posted to microsoft.public.excel.worksheet.functions
LOU LOU is offline
external usenet poster
 
Posts: 40
Default Find the next occurance

Thanks Harlan, it works great! Sorry for the delayed reponse, it took me a
while to to get my head around what you suggested.

"Harlan Grove" wrote:

"T. Valko" wrote...
....
Ugh! A formula method probably isn't the best way to go. Maybe a pivot table
?????

....

Or maybe not. If the OP is fetching data with HLOOKUP, that would
imply identifying values are in row 1 rather than field names. OP
would need to transpose the data and add field names to use a pivot
table. Then there's the question whether any of the data the OP needs
to pull would be text. Pivot tables can't do much with text in the
Data area of a pivot table.

If the data were in a range named Data with possibly duplicate
identifiers in row 1 and fields in different rows rather than
different columns, one way of fetching all data for identifier X would
involve formulas like

A1:
=MATCH(X,INDEX(Data,1,0),0)

A2:
=INDEX(Data,3,A1)

A3:
=INDEX(Data,4,A1)

A4:
=INDEX(Data,5,A1)

which will return the column index of the first match in A1 and the
data in rows 3, 4 and 5 in that column in A2, A3 and A4, respectively.
Then fetch the next possible match in column B using

B1:
=IF(COUNTIF(INDEX(Data,1,0),X)COLUMNS($A1:A1),
MATCH(X,INDEX(Data,1,A1+1):INDEX(Data,1,COLUMNS(Da ta)),0)+A1,"")

B2:
=IF(B$1<"",INDEX(Data,3,B1),"")

B3:
=IF(B$1<"",INDEX(Data,4,B1),"")

B4:
=IF(B$1<"",INDEX(Data,5,B1),"")

Then copy B1:B4 and fill right as far as needed.

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
Find nth occurance of data yshridhar Excel Worksheet Functions 4 February 16th 08 06:19 PM
help to find a string for 4th occurance Eddy Stan Excel Worksheet Functions 1 September 22nd 07 11:13 PM
Macro to find last occurance hnyb1 Excel Discussion (Misc queries) 2 June 8th 07 04:40 PM
Find next occurance Jambruins Excel Discussion (Misc queries) 5 August 10th 06 04:48 PM
Find last occurance of text in range farutherford Excel Worksheet Functions 5 August 30th 05 02:00 AM


All times are GMT +1. The time now is 11:38 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"