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 4,786

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.

4 answers

Your answer

Barry Schwarz 4,786 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,201

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

Answer 2

Ashish Mathur 100.8K Volunteer Moderator

Hi,

Load the data to Power Query and right click on the second column. Select Split Data > By delimiter. Specify the delimiter as , and expand Options. Select Rows > OK. Click on Close and Apply.

Hope this helps.

Answer 3

peiye zhu 320

https://club.excelhome.net/forum.php?mod=viewthread&tid=1696179&fromguid=hot&extra=&mobile&_dsign=3edd7e1d

Answer 4

arr = [a2].CurrentRegion
ReDim brr(1 To 10000, 1 To UBound(arr, 2))
For i = 3 To UBound(arr)
    If arr(i, 2) <> "" Then
          skjs = Split(arr(i, 2), ",")
          For j = 0 To UBound(skjs)
              n = n + 1
              For k = 1 To UBound(arr, 2)
                   If k <> 2 Then
                      brr(n, k) = arr(i, k)
                    Else
                      brr(n, k) = skjs(j)
                    End If
              Next
            Next
     End If
Next
[H3].Resize(n, UBound(brr, 2)) = brr

Share via

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

4 answers

Your answer