Forum Discussion
Duplicating Rows based on Value from another table
Cambosity100 Sure. See attached.
On the sheet called "Data to Load" - This is a sheet which will be used to prepare data for loading into SQL.
On the sheet called "Reference Data", this is data which will be used for referencing, in otherwords, we will perform a vlookups to this sheet to return the level ID associated with the Level Code.
In the example on the attached, there are two level codes with the value "SO" in the column called Level Code on the sheet called reference data. Both have two unique IDs and therefore, both are valid.
On the tab called "Data to Load", there is one record which contains the Level Code of SO in the column called Level Code. However, as there are actually two distinct records for SO (as shown on the reference data sheet), we would need to duplicate the line in yellow, one showing level ID 6 and the other showing level id 834.
I hope this explains
michaeldee As a variant, PowerQuery can do this swiftly without having to bother about VLOOKUP. Attached file contains a working example based on the data set you provided. PQ connects to the blue tables and spits out the green one (in the Upload sheet) upon a refresh. No VBA needed and much easier to maintain and adapt.
- Thank you Riny_van_Eekelen. I was not very familiar with Power Query before today. However, I think I now have a solution as a result. Thank you very much
