Why are only some of the numbers in the example populated?

As a quick fix

• Under the AB in cell A3 type in =IF($G3="","",A2)
• Then drag to the right so you get AB and APPLE repeated
• Copy A3 by pressing ctrl C
• High light column A & B then press ctrl G
• Click special & then blanks and OK
• Then ctrl V
• All the code & products will be populated
• Select the whole grid and sort by Code A-Z
• Delete blank rows
• Then just reference the correct column (so sheet2 A3 will be =SHEET1!C2 etc etc)

Takes about 30 seconds once you have done it a few times