Solved

excel question special need

Posted on 2013-01-08
11
218 Views
Last Modified: 2013-03-08
I have an excel spreadsheet with about 6 columns and 350 rows

one of the columns "C" has values separated by a ; within it

now we are told that we cannot user the multi entries separated by a ; so each one needs its own line.

and example of "C" would be A;B;C;D which now need to be three separate lines

before

joe     Blow    A;B;C;D    golf

now

joe     Blow    A    golf
joe     Blow    B    golf
joe     Blow    C    golf
joe     Blow    D    golf


is there a way for me to automatically do this?
0
Comment
Question by:Matt Pinkston
  • 7
  • 4
11 Comments
 
LVL 26

Expert Comment

by:redmondb
ID: 38756551
Hi, pinkstonmp.

Please see attached. The code is...
Option Explicit

Sub Split_Semi_Colon()
Dim i As Long
Dim j As Long
Dim xLast_Row As Long
Dim xCell As Range
Dim xArray  As Variant
        
ThisWorkbook.Sheets("Sheet1").Activate

Application.ScreenUpdating = False
    
    xLast_Row = [A1].SpecialCells(xlLastCell).Row
    If xLast_Row < 2 Then
        MsgBox ("No data found - run cancelled.")
        Exit Sub
    End If
    
    For i = xLast_Row To 2 Step -1
        xArray = Split(Cells(i, 3), ";")
        If UBound(xArray) > -1 Then
            For j = UBound(xArray) To 0 Step -1
                If j = 0 Then
                    Cells(i, 3) = xArray(j)
                Else
                    Rows(i).Copy
                    Rows(i + 1).Insert Shift:=xlDown
                    Cells(i + 1, 3) = xArray(j)
                End If
            Next
        End If
    Next

Application.CutCopyMode = False
Application.ScreenUpdating = True

End Sub

Open in new window

Regards,
Brian.
Split-Semi-Colon.xlsm
0
 

Author Comment

by:Matt Pinkston
ID: 38756603
not familiar with code, but how do you run?
0
 
LVL 26

Expert Comment

by:redmondb
ID: 38756625
pinkstonmp,

Press the blue button! Once everything is OK, I'll talk you through how you do it on your own file.

Regards,
Brian.
0
 

Author Comment

by:Matt Pinkston
ID: 38756695
it worked perfect, so how do I implement on my own file?
0
 
LVL 26

Expert Comment

by:redmondb
ID: 38756836
pinkstonmp,

A few questions, please...
(1) Is this a one-off or will you be doing it repeatedly?
(2) Do you need to run this against one file or many?

Thanks,
Brian
0
Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

 

Author Comment

by:Matt Pinkston
ID: 38756973
will do it a lot
one file
0
 
LVL 26

Expert Comment

by:redmondb
ID: 38757088
pinkstonmp,

Apologies, one last question - which version of Excel?

Thanks,
Brian.
0
 
LVL 26

Accepted Solution

by:
redmondb earned 500 total points
ID: 38757244
pinkstonmp,

These notes are for 2010.

(1) The Developer tab needs to be displayed on your Ribbon's menu bar. If it's not then please do the following (per Excel's Help)...
 - Click the File tab, click Options, and then click the Customize Ribbon category.
 - In the Main Tabs list, select the Developer check box, and then click OK.
(2) Click on "Developer" to display its tab. Click on "Visual Basic". This opens a whole new window - the Visual Basic editor (VBE). Your exact layout may be different, but you'll see something like this...(3) On the VBE's menu bar, click on "Insert" and then "Module". You'll now see something like the following....(4) You may or may not have the "Option Explicit" text. If it's there then delete it - it's already in my code above.
(5) Copy my code from above and paste it into the module (i.e. where the "Option Explicit" is shown in my diagram).
(6) The macro explicitly looks for a sheet called "Sheet1". If your sheet has a different name then change line 10 accordingly.
(7) Close the VBE window.
(8) If your file was already an xlsm, xlsb or xls then simply save it, otherwise do a "Save As" and select the "Excel Macro-Enabled Workbook" type.

When you want to run the macro...
(A) Click on "Developer" to display its tab. Click on "Macros". This displays a dialogue such as the following....(B) Select the macro (if its not already selected) and click on "Run".

Regards,
Brian.
0
 
LVL 26

Expert Comment

by:redmondb
ID: 38774304
Thanks, pinkstonmp.

I keep an eye on "my" closed questions for a few weeks, so if you've issues with any of this, please feel free to post here.

Regards,
Brian.
0
 

Author Comment

by:Matt Pinkston
ID: 38965510
Brian,

is there a way to reverse this?  I opened a new question on this.  So basically going the opposite way.
0
 
LVL 26

Expert Comment

by:redmondb
ID: 38967664
Pinkstonmp,

Sorry, I was too slow, but als315 seems to have you well sorted!

Regards,
Brian.
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

A2 = A1 That kind of cell reference is relative.  If you copy it from A2 to B2, then B2 will get this: B2 = B1 That's all fine and good, but if you then insert a new row above row 2, you'll find: A3 = A1 B3 = B1 This is intentional. …
Introduction This Article is a follow-up to my Mappit! Addin Article (http://www.experts-exchange.com/A_2613.html), it was inspired by an email posting I made to EUSPRIG (http://www.eusprig.org/index.htm), I will briefly cover: 1) An overvie…
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now