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
|