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
|