How can I add duplicate rows of data based on info in a specific cell in excel?

Question

How can I add duplicate rows of data based on info in a specific cell in excel?

Maggie C 0

I am trying to create duplicate records in excel based on the data in a specific column. The data has one unique identifier per record but multiple models can apply to the identifier. I want to copy and duplicate the record if there are multiple models noted and show only one model in the new reference noted. I added a screenshot of an example of the data and the output I'd like to see. The actual data set is much larger and I'd ideally like to see the ouput on a separate tab. I believe this may be accomplished through VBA though I am not sure the code to do so.

User's image

Barry Schwarz 3,746

If you are not familiar with Excel VBA, google excel+vba+tutorial. There are several decent ones in the list. The ones from Learnit worked for me.

In VBA
   You can create a new worksheet by using ActiveWorkook.Worksheets.Add method.
   You can rename this worksheet by setting the ActiveSheet.Name property.
   After you identify the starting cells for your input and output data, loop through the input data
      Extract and save the identifier in a string
      Extract save the the models in a string
      Loop through the models string until it is empty
         Use the InStr function to find the next comma
         Use the Left function to extract this model
         Store the identifier and this model in the output
         Move the output range down a row
         Use the Mid or Right function to remove this model from the models string
         Repeat
      Move the input range down a row
      Repeat

You need to decide if you want the models to be numbers or text. If you want text, insert an apostrophe before the model when you store it in the output.

1 answer

Your answer

Barry Schwarz 3,746 Reputation points

2023-09-01T18:10:07.9466667+00:00

If you are not familiar with Excel VBA, google excel+vba+tutorial. There are several decent ones in the list. The ones from Learnit worked for me.

In VBA You can create a new worksheet by using ActiveWorkook.Worksheets.Add method. You can rename this worksheet by setting the ActiveSheet.Name property. After you identify the starting cells for your input and output data, loop through the input data Extract and save the identifier in a string Extract save the the models in a string Loop through the models string until it is empty Use the InStr function to find the next comma Use the Left function to extract this model Store the identifier and this model in the output Move the output range down a row Use the Mid or Right function to remove this model from the models string Repeat Move the input range down a row Repeat

You need to decide if you want the models to be numbers or text. If you want text, insert an apostrophe before the model when you store it in the output.

Answer 1

Herbert Seidenberg 1,191

Excel 365 Pro Plus with Power Query.
Duplicate record if adjacent cell has multiple values.
No formulas, no VBA macro.
https://www.mediafire.com/file_premium/xmqe7738isljwct/09_04_23.xlsx/file
https://www.mediafire.com/file_premium/5bfm6tjbud9ekk4/09_04_23.pdf/file

Share via

How can I add duplicate rows of data based on info in a specific cell in excel?

1 answer

Your answer