Assign matching codes to same items in a list

Quite a straightforward requirement, but none-the-less if your dataset is quite large, repetitive pasting of values into multiple cells in a column can become monotonous, very labour intensive, and the more you have the more prone to errors and inaccuracies or missing data. In the example below we only use 44 rows of data, but this project which was recently managed for Zest Promotions who had in excess of 50,000 items of which some had the same SKU code (but different colour) and required their own unique ‘ZP Code’ which could also apply to other products.

It would have taken hours to run down each product and assign the same ZP Code to the same SKU so this code was created as the 2nd part of a process. The first part could not be automated as it required the company to assign their incremental ZP Codes to multiple products but the 2nd part made the task that much more manageable.

Essentially this code goes through all the data in column B and with each cell it finds that has a code, it grabs that code and matches it with the item code in column D. It then runs down column D from top to bottom and wherever it finds the same item code it will populate the corresponding ZP Code on the same row then continue on down column D until the end where it will then go back to where it left off in column B and move down, repeating this process. This is quite long winded and there are additional functions such as counting and using exit for, but as this is a 1-off it is just as easy to do it this way.

If you have an idea for data manipulation and need any help or guidance, we’d be more than happy to help. Leave a message below or contact us through the contact form.


For zp = 1 To 44

If ActiveSheet.Range("B" & zp) <> "" Then
zpcode = ActiveSheet.Range("B" & zp)
itmcode = ActiveSheet.Range("D" & zp)

For itm = 1 To 44
If ActiveSheet.Range("D" & itm) = itmcode Then
ActiveSheet.Range("B" & itm) = zpcode
End If

Next itm

End If
Next zp






Contact us for some advice and guidance on how your Excel development could be created and start helping your business straight away. Contact Us

Datanology

Leave a Reply

Your email address will not be published. Required fields are marked *