Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Limiting the range of a lookup function
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. |
#2
|
|||
|
|||
Limiting the range of a lookup function
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.x ls 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:D,0)) Select C2:D2, 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 -- "Dorn" wrote in message ... 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. |
#3
|
|||
|
|||
Limiting the range of a lookup function
Typo in line:
Select C2:D2, copy down till last row of data should read as: Select C2:E2, copy down till last row of data -- Rgds Max xl 97 --- Singapore, GMT+8 xdemechanik http://savefile.com/projects/236895 -- |
#4
|
|||
|
|||
Limiting the range of a lookup function
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="",R OW(Sheet1!A$1:A$14)),COLUMNS(Sheet1!$A:A))),2,0) Copy across then down as needed. Biff "Dorn" wrote in message ... 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. |
#5
|
|||
|
|||
Limiting the range of a lookup function
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" wrote: 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.x ls 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:D,0)) Select C2:D2, 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 -- "Dorn" wrote in message ... 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. |
#6
|
|||
|
|||
Limiting the range of a lookup function
You're welcome, Dorn !
Thanks for the feedback I'll take a shot at your new post -- Rgds Max xl 97 --- Singapore, GMT+8 xdemechanik http://savefile.com/projects/236895 -- "Dorn" wrote in message ... 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! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How can i use > in lookup function? | Excel Discussion (Misc queries) | |||
lookup and return range of cells | Excel Worksheet Functions | |||
Lookup Value is Between Range 1 and Range 2 | Excel Worksheet Functions | |||
Lookup Value is Between Range 1 and Range 2 | Excel Worksheet Functions | |||
Function to determine if any cell in a range is contained in a given cell | Excel Worksheet Functions |