| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formual to Lookup and Transpose
Hi All
On Sheet1 I have data as follows: ID Account 1 500 1 510 1 550 2 500 2 505 2 600 2 650 3 400 etc Each ID will be repeated between 1 and 39 times. On Sheet2 I would like to be able to enter an ID number in A1 and have the relevant Account numbers displayed in the range A2:AM2. Many thanks in advance Sam |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Formual to Lookup and Transpose
Hi!
Enter this formula in Sheet2 A2 as an array using the key combo of CTRL,SHIFT,ENTER: =IF(COLUMNS($A:A)<=COUNTIF(Sheet1!$A2:$A9,$A1),IND EX(Sheet1!$B2:$B9,SMALL(IF(Sheet1!$A2:$A9=$A1,ROW( Sheet1!A2:A9)-ROW(A2)+1),COLUMNS($A:A))),"") Copy across to AM2. Biff "sam" wrote in message ... Hi All On Sheet1 I have data as follows: ID Account 1 500 1 510 1 550 2 500 2 505 2 600 2 650 3 400 etc Each ID will be repeated between 1 and 39 times. On Sheet2 I would like to be able to enter an ID number in A1 and have the relevant Account numbers displayed in the range A2:AM2. Many thanks in advance Sam |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Formual to Lookup and Transpose
Sam, I've put a sample file on the web for you. For simplicity everything is on one page, but no doubt you can take what you learn from this simple version and make it fit your needs. http://www.anywhereenterprises.com:8...OWOAApQZOAAdQX -- rsenn ------------------------------------------------------------------------ rsenn's Profile: http://www.excelforum.com/member.php...o&userid=29050 View this thread: http://www.excelforum.com/showthread...hreadid=491301 |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Formual to Lookup and Transpose
Fantastic, thanks Biff.
Biff wrote: Hi! Enter this formula in Sheet2 A2 as an array using the key combo of CTRL,SHIFT,ENTER: =IF(COLUMNS($A:A)<=COUNTIF(Sheet1!$A2:$A9,$A1),IND EX(Sheet1!$B2:$B9,SMALL(IF(Sheet1!$A2:$A9=$A1,ROW( Sheet1!A2:A9)-ROW(A2)+1),COLUMNS($A:A))),"") Copy across to AM2. Biff "sam" wrote in message ... Hi All On Sheet1 I have data as follows: ID Account 1 500 1 510 1 550 2 500 2 505 2 600 2 650 3 400 etc Each ID will be repeated between 1 and 39 times. On Sheet2 I would like to be able to enter an ID number in A1 and have the relevant Account numbers displayed in the range A2:AM2. Many thanks in advance Sam |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Formual to Lookup and Transpose
Thank you for your effort. Biff's solution has worked fine and while I
am sure your's will too the firewall in my company will not allow me to open files from the internet. Regards Sam rsenn wrote: Sam, I've put a sample file on the web for you. For simplicity everything is on one page, but no doubt you can take what you learn from this simple version and make it fit your needs. http://www.anywhereenterprises.com:8...OWOAApQZOAAdQX |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Formual to Lookup and Transpose
Here's something to try:
Assumptions: On Sheet1 contains your data in cells A1:B10 On Sheet2 is where you want the extracted data to be displayed So.... Using Sheet2: A1: ID B1: Account InsertNameDefine Names in workbook: Sheet2!rngDest Refers to: =Sheet2!$A$1:$B$1 I1: ID I2: 1 InsertNameDefine Names in workbook: Sheet2!rngCriteria Refers to: =Sheet2!$I$1:$I$2 Next...still on Sheet2: InsertNameDefine Names in workbook: Sheet1!rngSource Refers to: =Sheet1!$A$1:$B$10 (Notice: you are on Sheet2, but creating a Sheet2 level range name, but the referenced range is on Sheet1) The reason: An advanced filter cannot SEND data to another sheet, but it can PULL data from another sheet. Next: In a general vba module, enter this code: Option Explicit Sub PullMatchingData() Range("Sheet2!rngSource").AdvancedFilter _ Action:=xlFilterCopy, _ CriteriaRange:=Range("Sheet2!rngCriteria"), _ CopyToRange:=Range("Sheet2!rngDest"), _ Unique:=False End Sub To run the code: ToolsMacroMacros (or [Alt]+[F8]) Select and run: PullMatchingData Change the value of I2 to 3 and run it again. Does that help? *********** Regards, Ron "sam" wrote: Hi All On Sheet1 I have data as follows: ID Account 1 500 1 510 1 550 2 500 2 505 2 600 2 650 3 400 etc Each ID will be repeated between 1 and 39 times. On Sheet2 I would like to be able to enter an ID number in A1 and have the relevant Account numbers displayed in the range A2:AM2. Many thanks in advance Sam |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Formual to Lookup and Transpose
Hi Ron
That is an interesting slant. Maybe a typo in the setup "Names in workbook: Sheet1!rngSource" should be "Names in workbook: Sheet2!rngSource"?? I had a couple of VBA solutions of my own, one using ADO with a query string to open a recordset with the relevant Accounts and one using the ..find , .findnext methods to get the appropiate range. However, I need to distribute this without any macros hence the need for a formula solution. Thanks for your time anyway Regards Sam Ron Coderre wrote: Here's something to try: Assumptions: On Sheet1 contains your data in cells A1:B10 On Sheet2 is where you want the extracted data to be displayed So.... Using Sheet2: A1: ID B1: Account InsertNameDefine Names in workbook: Sheet2!rngDest Refers to: =Sheet2!$A$1:$B$1 I1: ID I2: 1 InsertNameDefine Names in workbook: Sheet2!rngCriteria Refers to: =Sheet2!$I$1:$I$2 Next...still on Sheet2: InsertNameDefine Names in workbook: Sheet1!rngSource Refers to: =Sheet1!$A$1:$B$10 (Notice: you are on Sheet2, but creating a Sheet2 level range name, but the referenced range is on Sheet1) The reason: An advanced filter cannot SEND data to another sheet, but it can PULL data from another sheet. Next: In a general vba module, enter this code: Option Explicit Sub PullMatchingData() Range("Sheet2!rngSource").AdvancedFilter _ Action:=xlFilterCopy, _ CriteriaRange:=Range("Sheet2!rngCriteria"), _ CopyToRange:=Range("Sheet2!rngDest"), _ Unique:=False End Sub To run the code: ToolsMacroMacros (or [Alt]+[F8]) Select and run: PullMatchingData Change the value of I2 to 3 and run it again. Does that help? *********** Regards, Ron "sam" wrote: Hi All On Sheet1 I have data as follows: ID Account 1 500 1 510 1 550 2 500 2 505 2 600 2 650 3 400 etc Each ID will be repeated between 1 and 39 times. On Sheet2 I would like to be able to enter an ID number in A1 and have the relevant Account numbers displayed in the range A2:AM2. Many thanks in advance Sam |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Formual to Lookup and Transpose
Yup...You caught a typo. Sorry about that....Good catch, though. Thanks for
letting me know. *********** Regards, Ron "sam" wrote: Hi Ron That is an interesting slant. Maybe a typo in the setup "Names in workbook: Sheet1!rngSource" should be "Names in workbook: Sheet2!rngSource"?? I had a couple of VBA solutions of my own, one using ADO with a query string to open a recordset with the relevant Accounts and one using the ..find , .findnext methods to get the appropiate range. However, I need to distribute this without any macros hence the need for a formula solution. Thanks for your time anyway Regards Sam Ron Coderre wrote: Here's something to try: Assumptions: On Sheet1 contains your data in cells A1:B10 On Sheet2 is where you want the extracted data to be displayed So.... Using Sheet2: A1: ID B1: Account InsertNameDefine Names in workbook: Sheet2!rngDest Refers to: =Sheet2!$A$1:$B$1 I1: ID I2: 1 InsertNameDefine Names in workbook: Sheet2!rngCriteria Refers to: =Sheet2!$I$1:$I$2 Next...still on Sheet2: InsertNameDefine Names in workbook: Sheet1!rngSource Refers to: =Sheet1!$A$1:$B$10 (Notice: you are on Sheet2, but creating a Sheet2 level range name, but the referenced range is on Sheet1) The reason: An advanced filter cannot SEND data to another sheet, but it can PULL data from another sheet. Next: In a general vba module, enter this code: Option Explicit Sub PullMatchingData() Range("Sheet2!rngSource").AdvancedFilter _ Action:=xlFilterCopy, _ CriteriaRange:=Range("Sheet2!rngCriteria"), _ CopyToRange:=Range("Sheet2!rngDest"), _ Unique:=False End Sub To run the code: ToolsMacroMacros (or [Alt]+[F8]) Select and run: PullMatchingData Change the value of I2 to 3 and run it again. Does that help? *********** Regards, Ron "sam" wrote: Hi All On Sheet1 I have data as follows: ID Account 1 500 1 510 1 550 2 500 2 505 2 600 2 650 3 400 etc Each ID will be repeated between 1 and 39 times. On Sheet2 I would like to be able to enter an ID number in A1 and have the relevant Account numbers displayed in the range A2:AM2. Many thanks in advance Sam |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Linear Mode
