VBA or Excel to Auto Filter

[JMC57]

I have a list of numbers referencing an item in column B of sheet 2 that can be found in col A sheet1. I want to auto filter column A in Sheet 1 for rows that contain this number in col A Sheet 1. The number can appear multiple times in column A Sheet 1. <o></o>
I tried VLook up. However, it does not return each occurrence of the number. There are 6000 rows in sheet 1. An item can appear up to 200 times or not at all. <o></o>
Hopefully someone can help with a VBA code or excel formula to solve this. <o></o>
Thanks!<o></o>

 

[JMC57]

Sheet 2 Col B

=IF(COUNTIF(Sheet2!$A$1:$A$6000,$A2)>=COLUMNS($B2:B2),INDEX(Sheet2!$B$1:$B$6000,SMALL(IF(Sheet2!$A$1:$A$6000=$A2,ROW(Sheet2!$A$1:$A$6000)-ROW(Sheet2!$A$1)+1),COLUMNS($B2:B2))),"")

Sheet 2 Col C
=IF(COUNTIF(Sheet2!$A$1:$A$6000,$A2)>=COLUMNS($C2:C2),INDEX(Sheet2!$C$1:$C$6000,SMALL(IF(Sheet2!$A$1:$A$6000=$A2,ROW(Sheet2!$A$1:$A$6000)-ROW(Sheet2!$A$1)+1),COLUMNS($C2:C2))),"")

 

[Andrew Poulsom]

Did you remember to confirm those formulas with Ctrl+Shift+Enter, not just Enter? If entered correctly Excel will surround them with curly braces {}.