Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Need big help with look up....is this possible
Hi,
I'm building a sheet that pulls data from another worksheet based on a customer number, so when I enter a customer number into cell it will pull specific information. The part I'm stuck on is this....There are varying numbers of rows below each customers' data depending on the number of years they have been a customer....one row per year, starting with the current year. I want to pull data for the pervious year, three years ago, five years ago and the first year they were a customer. The data looks like this: "Cust. #1" "Cust. Name" # Year Premium Losses Claims.....etc 1 2005 1000 50 1 2 2004 1500 10 2 3 2003 1500 0 0 4 2002 1500 15 1 5 2001 1500 15 1 6 2000 1500 10 1 7 1999 1500 0 0 "Cust. #2" "Cust. Name" # Year Premium Losses Claims.....etc 1 2005 500 50 1 2 2004 500 10 2 3 2003 500 0 0 4 2002 500 15 1 The column with the # and numbers is actually part of the data, not the Excel row number. So when I enter a customer number to look up, I need it to look at only the data for that customer and pull rows 1, 3, 5 and the last row, whatever it might be. Is this possible, or am I dreaming?? I hope that makes sense. Thanks, Dan |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Need big help with look up....is this possible
try this function. but it depend on that ur custumernames are in column A
and the cell just below is empty, then a cell with # just like it seems to be in ur example otherwise the function have to be modifyed a bit. put in a regular module then use like this: =Vlokup("Cust. #1",Sheet1!A1:A100,1,2) - return 2005 (column B) =Vlokup("Cust. #1",Sheet1!A1:A100,3,2) - return 2003 (column B) =Vlokup("Cust. #1",Sheet1!A1:A100,0,2) - return 1999 (0=first year) =Vlokup("Cust. #2",Sheet1!A1:A100,5,4) - return 15 (column D) Function Vlokup(Custumer As String, MyRange As Range, YearNumber, Col) Dim Mysheet Dim c As Range, Last Set Mysheet = MyRange.Parent Set c = MyRange Application.Volatile If YearNumber < 0 Then Vlokup = Mysheet.Cells(c.Find(Custumer, LookIn:=xlValues).Row + YearNumber + 2, Col) End If If YearNumber = 0 Then Last = c.Find(Custumer, LookIn:=xlValues).Offset(2, 0).End(xlDown).Row Vlokup = Mysheet.Cells(Last, Col) End If End Function |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Need big help with look up....is this possible
Assumptions:
A2:E19 contains the data Format is consistent G2 contains the customer number of interest Formulas: H2: 5 (This indicates that data from 5 years ago should be returned for the customer of interest. Change this accordingly.) I2: =MATCH(9.99999999999999E+307,A:A) J2: =MATCH(G2,A:A,0)+3 K4: =LOOKUP(9.99999999999999E+307,CHOOSE({1,2},I2-J2+1,MATCH(TRUE,INDEX(A:A,J 2):INDEX(A:A,I2)="",0)-1)) ....confirmed with CONTROL+SHIFT+ENTER, not just ENTER. L2, copied across: =VLOOKUP($H2,INDEX($A:$A,$J$2):INDEX($E:$E,$J$2+$K $2-1),COLUMNS($L2:L2)+1 ,0) Note that I2:K2 are helper cells, and can be hidden, if desired. Hope this helps! In article , "Dan B" wrote: Hi, I'm building a sheet that pulls data from another worksheet based on a customer number, so when I enter a customer number into cell it will pull specific information. The part I'm stuck on is this....There are varying numbers of rows below each customers' data depending on the number of years they have been a customer....one row per year, starting with the current year. I want to pull data for the pervious year, three years ago, five years ago and the first year they were a customer. The data looks like this: "Cust. #1" "Cust. Name" # Year Premium Losses Claims.....etc 1 2005 1000 50 1 2 2004 1500 10 2 3 2003 1500 0 0 4 2002 1500 15 1 5 2001 1500 15 1 6 2000 1500 10 1 7 1999 1500 0 0 "Cust. #2" "Cust. Name" # Year Premium Losses Claims.....etc 1 2005 500 50 1 2 2004 500 10 2 3 2003 500 0 0 4 2002 500 15 1 The column with the # and numbers is actually part of the data, not the Excel row number. So when I enter a customer number to look up, I need it to look at only the data for that customer and pull rows 1, 3, 5 and the last row, whatever it might be. Is this possible, or am I dreaming?? I hope that makes sense. Thanks, Dan |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Need big help with look up....is this possible
Actually...looking closer....this was a data dump and it put the customer
number and name in one cell on column A. So each customers data by this name and number merged into one cell. "paul" wrote in message ... so each customers data is headed by a row with customer # in col A with customer name in col b with a block of data down to a new customer # in varaible number of rows which depends on the number of years thay have been a customer? or is it just a big block of text but still basically as described above? -- paul remove nospam for email addy! "Dan B" wrote: Hi, I'm building a sheet that pulls data from another worksheet based on a customer number, so when I enter a customer number into cell it will pull specific information. The part I'm stuck on is this....There are varying numbers of rows below each customers' data depending on the number of years they have been a customer....one row per year, starting with the current year. I want to pull data for the pervious year, three years ago, five years ago and the first year they were a customer. The data looks like this: "Cust. #1" "Cust. Name" # Year Premium Losses Claims.....etc 1 2005 1000 50 1 2 2004 1500 10 2 3 2003 1500 0 0 4 2002 1500 15 1 5 2001 1500 15 1 6 2000 1500 10 1 7 1999 1500 0 0 "Cust. #2" "Cust. Name" # Year Premium Losses Claims.....etc 1 2005 500 50 1 2 2004 500 10 2 3 2003 500 0 0 4 2002 500 15 1 The column with the # and numbers is actually part of the data, not the Excel row number. So when I enter a customer number to look up, I need it to look at only the data for that customer and pull rows 1, 3, 5 and the last row, whatever it might be. Is this possible, or am I dreaming?? I hope that makes sense. Thanks, Dan |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Need big help with look up....is this possible
I will try these and see if I can make it work.
Thank you so much to all. I'll post back if needed. "Domenic" wrote in message ... Assumptions: A2:E19 contains the data Format is consistent G2 contains the customer number of interest Formulas: H2: 5 (This indicates that data from 5 years ago should be returned for the customer of interest. Change this accordingly.) I2: =MATCH(9.99999999999999E+307,A:A) J2: =MATCH(G2,A:A,0)+3 K4: =LOOKUP(9.99999999999999E+307,CHOOSE({1,2},I2-J2+1,MATCH(TRUE,INDEX(A:A,J 2):INDEX(A:A,I2)="",0)-1)) ...confirmed with CONTROL+SHIFT+ENTER, not just ENTER. L2, copied across: =VLOOKUP($H2,INDEX($A:$A,$J$2):INDEX($E:$E,$J$2+$K $2-1),COLUMNS($L2:L2)+1 ,0) Note that I2:K2 are helper cells, and can be hidden, if desired. Hope this helps! In article , "Dan B" wrote: Hi, I'm building a sheet that pulls data from another worksheet based on a customer number, so when I enter a customer number into cell it will pull specific information. The part I'm stuck on is this....There are varying numbers of rows below each customers' data depending on the number of years they have been a customer....one row per year, starting with the current year. I want to pull data for the pervious year, three years ago, five years ago and the first year they were a customer. The data looks like this: "Cust. #1" "Cust. Name" # Year Premium Losses Claims.....etc 1 2005 1000 50 1 2 2004 1500 10 2 3 2003 1500 0 0 4 2002 1500 15 1 5 2001 1500 15 1 6 2000 1500 10 1 7 1999 1500 0 0 "Cust. #2" "Cust. Name" # Year Premium Losses Claims.....etc 1 2005 500 50 1 2 2004 500 10 2 3 2003 500 0 0 4 2002 500 15 1 The column with the # and numbers is actually part of the data, not the Excel row number. So when I enter a customer number to look up, I need it to look at only the data for that customer and pull rows 1, 3, 5 and the last row, whatever it might be. Is this possible, or am I dreaming?? I hope that makes sense. Thanks, Dan |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|