Split data in cells and duplicate rows

ballpoint · #1 02-07-2018, 05:05 AM

I apologise if this question has been asked before, but I have not been able to find a satisfactory answer. So I thought I'd ask it here.

I have a set of data which is structured this way:

Code:

QuoteID | QuoteContent | Document | Cited References

Quote1  | lorem ipsum... | Doc1 | Reference1, Reference2, Reference3

What I need to do is to

Split the data in the last cell at the comma or other separator
Duplicate rows with rest of the data

In practice, the data should then look like this:

Code:

QuoteID | QuoteContent | Document | Cited References

Quote1  | lorem ipsum... | Doc1 | Reference1
Quote1  | lorem ipsum... | Doc1 | Reference2
Quote1  | lorem ipsum... | Doc1 | Reference3

Is there any way to do it?

Thank you ever so much!

Alsadius · #2 02-07-2018, 07:09 AM

First things first, pull the data in using commas as a delimiter, to break it up into cells.

The quick and dirty way from there is to figure out the largest number of references any of them have, and create that many four-column groups thereafter. The first three columns of a group will always be =$A1, =$B1, and =$C1, and the last will =D1 for the first four-column group, =E1 for the second, and so on. That creates the individual entries. To put them back together, you have a few options - formulas to pull from the four-column groups in proper order(which will result in blank lines, which may or may not be a problem), or simply copy the whole block, paste special values only, copy them all into a single four-column area, and then sort the data.

NoSparks · #3 02-07-2018, 08:39 AM

Any interest in a macro solution ?

ballpoint · #4 02-07-2018, 11:16 AM

Quote:

Originally Posted by NoSparks

Any interest in a macro solution ?

Yes, of course, please! That would be awesome. And thanks Alsadius for the quick and dirty solution. It sounds interesting, but a little problematic for over 2000 rows :/

NoSparks · #5 02-07-2018, 01:08 PM

Assuming data starts in A2, a comma is the separator and no event code for the sheet,
on a copy of your workbook, paste this into a standard module and give it a try, alter the sheet name as required

Code:

Sub Testing_1()

Dim lr As Long, i As Integer, j As Integer, ray

Application.ScreenUpdating = False

With Sheets("Sheet1")
    lr = .Range("A" & Rows.Count).End(xlUp).Row
    For i = lr To 2 Step -1
        ray = Split(.Cells(i, 4).Value, ",")
        If UBound(ray) > 0 Then
            .Range("A" & i).Resize(, 4).Copy
            .Range("A" & i + 1).Resize(UBound(ray), 4).Insert xlShiftDown
            For j = LBound(ray) To UBound(ray)
                .Cells(i, 4).Offset(j).Value = Trim(ray(j))
            Next j
        End If
    Next i
End With
    
Application.CutCopyMode = False
Application.ScreenUpdating = True

End Sub

ballpoint · #6 02-07-2018, 04:11 PM

Holy mother of sweet baby jesus, it seems like it is working like a charm—and on a mac too! You, sir, are a gem. Thank you ever so much!

NoSparks · #7 02-07-2018, 05:52 PM

You're welcome, glad I could help.

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
How to find duplicate rows in a huge excel file containing 500000 rows	Stc25234	Excel Programming	2	06-23-2017 10:51 AM
eliminating blan rows between cells in a column cantaining data	FUGMAN	Excel Programming	6	03-01-2017 07:35 AM
Merge duplicate rows but retain data from one column	Willem113	Excel	1	09-21-2016 05:42 PM
how to split merged data into vertical saperate cells	cheekugreat	Excel	5	10-11-2014 05:53 AM
Losing rows when pasting split cells	Gitley	Word Tables	1	01-15-2013 07:49 AM