Limiting the range of a lookup function

[Dorn]

I have data exported into excel from another program. The way the data is
presented is as follows:

Employee Name
Date data
Date data
Date data
Date data
Date data
Date data

Another Employee Name
Date data
Date data
Date data
Date data

and on and on,

What I'm trying to do is make a new tab that lists all of the employee names
in a row and dates down a column and then use a look up function (I was
trying index, match) to compare the dates from both tabs enter the data for
each employee. The problem I'm facing is that under each employees name
their are a different number of rows (some people didn't work some days), so
I want to make the range for the look up to compare dates going down the
spreadsheet only until it hits a new employees name (or hits a row with the
word "agent" in it) then I want it to continue comparing the dates and
entering the data, but I want the next employees data to show up under their
name on the new spreadsheet. How do I make it so I don't have to choose a
set range? But make it so it looks between two values? Aaaagh sooo confused.

 

[Max]

One play which might work for you ..

Sample construct at:
http://www.savefile.com/files/2840182
Limiting_the_range_of_a_lookup_function_Dorn_wks.xls

Assume source data is in Sheet1 cols A & B, from row2 down

Using 3 empty cols to the right of the data
Put in C2: =IF(OR(A2="",ISNUMBER(A2)),"","x")
Put in D2: =COUNTIF($C$2:C2,"x")
Put in E2: =INDEX(A:A,MATCH(D2,D,0))
Select C22, copy down till last row of data

In Sheet2,
employee names are listed in B2 across
dates are listed in A2 down

-------------- Emp1 Emp2 Emp3
01-Nov-05
02-Nov-05
03-Nov-05
etc

Put in B2, and array-enter (press CTRL+SHIFT+ENTER):

=IF(ISNA(MATCH($A2&"_"&B$1,Sheet1!$A$2:$A$100&"_"&Sheet1!$E$2:$E$100,0)),"",
INDEX(Sheet1!$B$2:$B$100,MATCH($A2&"_"&B$1,Sheet1!$A$2:$A$100&"_"&Sheet1!$E$
2:$E$100,0)))

Copy B2 across and fill down to populate the table

Adapt the ranges to suit ..
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--

I have data exported into excel from another program. The way the data is
presented is as follows:

Employee Name
Date data
Date data
Date data
Date data
Date data
Date data

Another Employee Name
Date data
Date data
Date data
Date data

and on and on,

What I'm trying to do is make a new tab that lists all of the employee names
in a row and dates down a column and then use a look up function (I was
trying index, match) to compare the dates from both tabs enter the data for
each employee. The problem I'm facing is that under each employees name
their are a different number of rows (some people didn't work some days), so
I want to make the range for the look up to compare dates going down the
spreadsheet only until it hits a new employees name (or hits a row with the
word "agent" in it) then I want it to continue comparing the dates and
entering the data, but I want the next employees data to show up under their
name on the new spreadsheet. How do I make it so I don't have to choose a
set range? But make it so it looks between two values? Aaaagh sooo

confused.

 

[Max]

Typo in line:

Select C22, copy down till last row of data

should read as:
Select C2:E2, copy down till last row of data

 

[Biff]

Hi!

Assuming that there is an empty row between each employee's data set AND the
next row at the end of the entire range is empty:

Based on your sample data being in the range Sheet1A1:B13. Sheet2 is where
you want the data extracted to with the names starting in B1 and the dates
starting in A2.

Enter this formula in Sheet2 B2 as an array using the key combo of
CTRL,SHIFT,ENTER:

=VLOOKUP($A2,INDIRECT("Sheet1!A"&MATCH(B$1,Sheet1!$A$1:$A$14,0)&":B"&SMALL(IF(Sheet1!$A$1:$A$14="",ROW(Sheet1!A$1:A$14)),COLUMNS(Sheet1!$A:A))),2,0)

Copy across then down as needed.

Biff

 

[Dorn]

Thank you very much for your help, unfortunately I described what I was
trying to do very poorly and I can't seem to get it to work, I issued another
message board question. Your suggestion would have definitely worked for the
situation I described!

 

[Max]

You're welcome, Dorn !
Thanks for the feedback
I'll take a shot at your new post