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

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

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

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

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

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

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

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