VBA Lookup and Update
Budget: ₹1,500 – ₹12,500 INR
I need VBA code for Excel which would consult a Lookup Table, and on a match, duplicate records, update them, and then append. My experience has been search for VBA solutions and then "reverse engineer" the code to fit my needs. I stumble when it comes to For/Next looping and Lookup Table manipulation, particularly when there may be nested looping.
My Workbook has three worksheets – Master, NameLU and Processing. The Master worksheets has multiple columns, one of which is a person’s first name. A person's first name can have variations, e.g., RICHARD could alternatively be Rich, Rick, Ricky or Dick. The NameLU is a lookup table having those variations of a name and a count of how many variations there are. I want to have VBA which will do the lookup for a FirstName match on the Master and NameLU (lookup) tables, duplicate the record from the Master table to the Processing table up to 5 times depending on a count in the NameLU table (the number of name variations) and then replace the FirstName with a variation. Once the duplicated records are updated with the name variations, the rows would be appended back on the Master table, the data range on the Processing table is deleted, and each remaining record in the Master table is similarly checked for matches and processed accordingly.
I am attaching a more thorough explanation with visuals.
My Workbook has three worksheets – Master, NameLU and Processing. The Master worksheets has multiple columns, one of which is a person’s first name. A person's first name can have variations, e.g., RICHARD could alternatively be Rich, Rick, Ricky or Dick. The NameLU is a lookup table having those variations of a name and a count of how many variations there are. I want to have VBA which will do the lookup for a FirstName match on the Master and NameLU (lookup) tables, duplicate the record from the Master table to the Processing table up to 5 times depending on a count in the NameLU table (the number of name variations) and then replace the FirstName with a variation. Once the duplicated records are updated with the name variations, the rows would be appended back on the Master table, the data range on the Processing table is deleted, and each remaining record in the Master table is similarly checked for matches and processed accordingly.
I am attaching a more thorough explanation with visuals.