However, is there a way to expand on it a little. Currently it will match all Ref#1 numbers against all Ref#1a numbers and copy the first one it finds. Is it possible for a macro to recognise whether there are multiple entires of ref1#a and copy them all over to the next coumns.

I've updated my sample table to make it a bit more clear.

Ref #1 Ref #1a Ref #2 Qty. REF#1a REF#2 Qty.
TEST0000001 TEST0000007 123 1 TEST0000004 127 1
TEST0000002 TEST0000009 123 1 TEST0000004 119 1
TEST0000003 TEST0000011 124 1
TEST0000004 TEST0000019 127 2
TEST0000005 TEST0000004 127 1
TEST0000005 TEST0000004 118 1

Any help you kind folks can offer will be very much appreciated.