I have a Spreadsheet that looks like the following,

Name Order Date Order
John Smith 06/05/14 A,B,C,D
Mike Doe 06/02/26 B,C,E

and so on... with several thousand entries.

I need the database to be structured in the following way.

Name Order Date Order
John Smith 06/05/14 A
John Smith 06/05/14 B
John Smith 06/05/14 C
John Smith 06/05/14 D
Mike Doe 06/02/26 B
Mike Doe 06/02/26 C
Mike Doe 06/02/26 E

Is there any way that I can make this happen using a macro or applet of
some sort? Does anyone have one that will do this already?
Things to note are that all orders are currently separated by a comma
followed by a single space, these must be removed.

If anyone can help, thanks in advance.

It isn't pretty, but it works.

Sub SplitSeparate()
Application.ScreenUpdating = False
Dim r As Integer, r2 As Integer
Dim c As Integer, c2 As Integer
r = 0
r2 = 0
c = 3
c2 = 0
Columns("C:C").Select
Selection.TextToColumns Destination:=Range("C1")
DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=True
Tab:=True, _
Semicolon:=False, Comma:=True, Space:=True, Other:=False
FieldInfo:= _
Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1))
TrailingMinusNumbers:= _
True

Range("A2").Select
Do
Do
If IsEmpty(ActiveCell.Offset(r, c)) = False Then
r2 = r2 + 1
ActiveCell.Offset(r2, 0).EntireRow.Insert
Range(ActiveCell.Offset(r, 0), ActiveCell.Offset(r, c)).Copy
Range(ActiveCell.Offset(r2, 0).Address).PasteSpecial
Range(ActiveCell.Offset(0, c).Address).Copy
Range(ActiveCell.Offset(0, 2).Address).PasteSpecial
Application.CutCopyMode = False
c = c + 1
Else
c = c + 1
r2 = r2 + 1
End If
ActiveCell.Offset(-r2, -2).Activate
Loop Until IsEmpty(ActiveCell.Offset(r, c)) = True
r = r2
ActiveCell.Offset(r + 1, 0).Activate
r = 0
r2 = 0
If c c2 Then c2 = c
c = 3
Loop Until IsEmpty(ActiveCell) = True
Range("D1", ActiveCell.Offset(0, c2
1).Address).EntireColumn.ClearContents
Application.ScreenUpdating = True
End Sub

JokerFrowns Wrote:
I have a Spreadsheet that looks like the following,
.
.
.
Is there any way that I can make this happen using a macro or apple
of
some sort? Does anyone have one that will do this already?
Things to note are that all orders are currently separated by a comma
followed by a single space, these must be removed.

If anyone can help, thanks in advance

--
Ikaabo
-----------------------------------------------------------------------
Ikaabod's Profile: http://www.excelforum.com/member.php...fo&userid=3337
View this thread: http://www.excelforum.com/showthread.php?threadid=54252

Many thanks,

I am no excel buff, do I just insert this into the code console and
away I go?

In excel you can hit alt-F11, then click INSERT-MODULE and paste the
code in there. You can run the code a number of ways (i.e. - create a
command button that runs the macro, or manually run it in Microsoft VB
Editor, etc.) Let me know if you have problems.
JokerFrowns Wrote:
Many thanks,

I am no excel buff, do I just insert this into the code console and
away I go?


--
Ikaabod
------------------------------------------------------------------------
Ikaabod's Profile: http://www.excelforum.com/member.php...o&userid=33371
View this thread: http://www.excelforum.com/showthread...hreadid=542520

There is one thing that needs changed actually. Change:
ActiveCell.Offset(-r2, -2).Activate
to
If ActiveCell.Column < 1 Then ActiveCell.Offset(-r2, -2).Activate


--
Ikaabod
------------------------------------------------------------------------
Ikaabod's Profile: http://www.excelforum.com/member.php...o&userid=33371
View this thread: http://www.excelforum.com/showthread...hreadid=542520

I am getting an error compiling syntax in the following line:

Selection.TextToColumns Destination:=Range("C1"),

Am I doing something wrong or forgetting something?