Macro to find cell value in column and retrieve duplicates

Here

Please Login or Register  to view this content.

you shall take into account that

Please Login or Register  to view this content.

could mean that in data can be one OR MORE entries of search_str in coulmn A of your data.

As a matter of fact I do not think it is a good idea to use dictionary object here. Note that in your data there is only 31 unique values in column A so in dictionary you will have only 31 items.

also - in post text you missed one row of output
Column A Column B Column C Column D
Car Saw Screen Sock
Car Saw Screen Mouse
Dew Jacket Window
Pen Key
Saw Screen Sock
Saw Screen Mouse
Hand Shoe
Jacket Window
Screen Sock
Screen Mouse

The idea colld be illustrated with:

Please Login or Register  to view this content.

Check how it works with all 3 sheets of sample data.

key points are:
working until there is something in recently completed column

Please Login or Register  to view this content.

going down-up in desults sheet to easily add rows (rows below move down, but in next step we are anyway a row up, so dont't care abou what happened below

Please Login or Register  to view this content.

finding not one, but all :

Please Login or Register  to view this content.

instances of predcessor in column A of source

Please Login or Register  to view this content.

(match range is from one below found in previous step till the end of data.

Of course you can now finetune it, add columns widths etc. but the algorithm shall work fine. May be look for more efficient data structures, because here all processing occurs in the spreadsheet.

Final comment: I added a security check

Please Login or Register  to view this content.

to let user intervene in case of looped data like

HTML Code: 

Long post, isn't it?