Splitting Single Column Data Into Three

Z

zaska

Well-known Member
Joined
Oct 24, 2010
Messages
1,046
Hi All!

I am having data in a single column ..nearly 10,000 cells..


Code: 
column A

1 FLOWERS
ROSE
JASMIN
TULIP
2 DOGS
BULLDOG
GERMANSHEPHERED
I Would like to split this data AS

Code: 
Column A    Column B   Column C

    1          FLOWERS    ROSE
    1          FLOWERS    JASMIN
    1          FLOWERS    TULIP
    2          DOGS        BULLDOG
    2          DOGS        GERMANSHEPHERED
Kindly suggest of any possible solution..which could save me a lot of time.
 

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"
Sort by date Sort by votes
There will be a far better way to do this im sure...

But as I know nothing about VB....

1) Index the data, (Insert a column at the start and number 1-10,0000) This is so that when we sort the data it can be returned to its original order.

2) Sort the data by column B to get all 1 Flower, 2 Dog entries at the top of the sheet.

3) Text to columns on this range to seperate out the number and description elements.

4) All data below your categories move to column D.

5) Re sort by index value to get your order back how it should be.

Data should now be offsett..

A,B,C ,D
1,1,Flower ,
2, , ,ROSE
3, , ,JASMIN


In E1=1
In E2, =if(B2="",E1,B2)
In F1=Flower
In F2, =if(c2=""(F1,C2)

Copy down.

Copy and past special values into column B&C.

Delete Index once finished....


This is the mannual work arround I've used for this sort of problem... As I say there will be a cleaner quicker VB route..

Hope this helps!!

Regards


Roger
 
Upvote 0
zaska,


Sample data before the macro:


Excel Workbook
ABCDE
11 FLOWERS
2ROSE
3JASMIN
4TULIP
52 DOGS
6BULLDOG
7GERMANSHEPHERED
8
Sheet1





After the macro:


Excel Workbook
ABCDE
11 FLOWERS1FLOWERSROSE
2ROSE1FLOWERSJASMIN
3JASMIN1FLOWERSTULIP
4TULIP2DOGSBULLDOG
52 DOGS2DOGSGERMANSHEPHERED
6BULLDOG
7GERMANSHEPHERED
8
Sheet1





Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).


1. Copy the below code, by highlighting the code and pressing the keys CTRL + C
2. Open your workbook
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. Press the keys ALT + I to activate the Insert menu
5. Press M to insert a Standard Module
6. Where the cursor is flashing, paste the code by pressing the keys CTRL + V
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel, open the workbook, and press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.


Code: 
Option Explicit
Sub SplitA()
' hiker95, 04/08/2011
' http://www.mrexcel.com/forum/showthread.php?t=542144
Dim c As Range, NR As Long, N As String, H As String
Application.ScreenUpdating = False
Columns("C:E").ClearContents
NR = 0
For Each c In Range("A1", Range("A" & Rows.Count).End(xlUp))
  If IsNumeric(Left(c, 1)) Then
    N = Left(c, Application.Find(" ", c) - 1)
    H = Right(c, Len(c) - Application.Find(" ", c, 1))
  Else
    NR = NR + 1
    Range("C" & NR) = N
    Range("D" & NR) = H
    Range("E" & NR) = c
  End If
Next c
Columns("C:E").AutoFit
Application.ScreenUpdating = True
End Sub


Then run the SplitA macro.
 
Upvote 0
Thank you Very much for the Quick Solution.

I would like to learn VBA too.. But i have no fair idea of where to start with.

Can u please tell me about a good book to start learning VBA programming..

Have a nice day too Mr. Hiker.
 
Upvote 0
zaska,


Training / Books / Sites:

MrExcel's Products: Books, CDs, Podcasts Discuss topics related to Holy Macro! Products: Learn Excel from MrExcel, VBA and Macros for Microsoft Excel,Holy Macro! It's 2500 VBA Examples CD, Guerilla Data Analysis Using Microsoft Excel and Excel Knowledge Base CD and the MrExcel Podcasts.
http://www.mrexcel.com/forum/forumdisplay.php?f=19

How to Learn to Write Macros
http://articles.excelyogi.com/playin...ba/2008/10/27/

How to use the macro recorder
http://articles.excelyogi.com/

Click here and scroll down to Getting Started with VBA.
http://www.datapigtechnologies.com/ExcelMain.htm

If you are serious about learning VBA try
http://www.add-ins.com/vbhelp.htm

Excel Tutorials and Tips - VBA - macros - training
http://www.mrexcel.com/articles.shtml

See David McRitchie's site if you just started with VBA
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Debra Dalgleish has some notes how to implement macros here:
Excel VBA -- Adding Code to a Workbook
http://www.contextures.com/xlvba01.html

David McRitchie has an intro to macros:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Ron de Bruin's intro to macros:
http://www.rondebruin.nl/code.htm

Creating custom functions
http://office.microsoft.com/en-us/excel/HA011117011033.aspx

Writing Your First VBA Function in Excel
http://www.exceltip.com/st/Writing_Your_First_VBA_Function_in_Excel/631.html

http://www.excel-vba.com/
http://www.mvps.org/dmcritchie/excel/getstarted.htm
http://www.exceltip.com/excel_links.html

(livelessons video)
Excel VBA and Macros with MrExcel
ISBN: 0-7897-3938-0
http://www.amazon.com/Excel-Macros-M...7936479&sr=1-1

http://www.youtube.com/user/ExcelIsFun#g/search

http://www.xl-central.com/index.html

http://www.datapigtechnologies.com/ExcelMain.htm

Dependent validation lists. Debra has a neat little tutorial here.
http://www.contextures.com/xlDataVal02.html

Data Validation > Drop-Down Lists - Dependent
http://www.bettersolutions.com/excel/EGH188/QE229212022.htm

Cascading queries
http://www.tushar-mehta.com/excel/ne...ing_dropdowns/

http://www.contextures.com/xlDataVal05.html

Excel Data Validation - Add New Items
http://www.contextures.com/excel-data-validation-add.html

Programming The VBA Editor - Created by Chip Pearson at Pearson Software Consulting LLC
This page describes how to write code that modifies or reads other VBA code.
http://www.cpearson.com/Excel/vbe.aspx

Locating files containing VBA
Searching Files in Subfolders for VBA code string:
http://www.dailydoseofexcel.com/arch...a-code-string/

http://www.pcreview.co.uk/forums/thread-978054.php

Excel 2003 Power Programming with VBA, by John Walkenbach

VBA and Macros for Microsoft Excel, by Bill Jelen "Mr.Excel" and Tracy Syrstad

Excel Hacks 100 Industrial-Strength Tips & Tools, by David & Traina Hawley

VBA and Macros for Microsoft Excel 2007, by Bill Jelen "Mr.Excel" and Tracy Syrstad

Excel 2007 Book: you can try this...there is a try before you buy ebook available at this link…
http://www.mrexcel.com/learnexcel2.shtml

DonkeyOte: My Recommended Reading:
Volatility
http://www.decisionmodels.com/calcsecretsi.htm

Sumproduct
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

Introduction to Array Formulas
http://www.xtremevbtalk.com/showthread.php?t=296012

Using Pivot Tables and Pivot Charts in Microsoft Excel
http://peltiertech.com/Excel/Pivots/pivotstart.htm

A List of Pivot Table links at Jon Peltier's site (contributed by Debra Dalgleish) is here:
http://peltiertech.com/Excel/Pivots/pivotlinks.htm

Email from XL - VBA & Outlook VBA
http://www.rondebruin.nl/sendmail.htm
http://www.outlookcode.com/article.aspx?ID=40

Excel Function Dictionary
http://www.xlfdic.com/

Excel 2007 function name translations
http://www.piuha.fi/excel-function-name-translation/

Dynamic Named Ranges
http://www.contextures.com/xlNames01.html

Where to paste code in VBE VBA
Introducing the Excel VBA Editor
http://www.ask.com/web?qsrc=2417&o=101881&l=dis&q=Where+to+paste+code+in+the+Excel+VBA+Editor

VBA for Excel (Macros)
http://www.excel-vba.com/excel-vba-contents.htm

VBA Lesson 11: VBA Code General Tips and General Vocabulary
http://www.excel-vba.com/vba-code-2-1-tips.htm

Excel VBA -- Adding Code to a Workbook
http://www.contextures.com/xlvba01.html

Basics of array formulas
http://www.youtube.com/view_play_lis...7E7E9CA63304D3

Array formula data extract formulas
http://www.youtube.com/watch?v=Tp7I5u1MqiM
http://www.youtube.com/watch?v=R5ZWAiNJLNo
http://www.youtube.com/watch?v=132ZdpxBm1U

How to create Excel Dashboards
http://www.contextures.com/excel-dashboards.html
http://chandoo.org/wp/excel-dashboards/

Unique Record Counting and Data Extract formulas
http://www.youtube.com/watch?v=uUrI8hoj8BA
 
Upvote 0
Good Morning

There are Blank cells in my Data after every Category....Can you plz modify the code to exclude the Blank cells



column A

1 FLOWERS
ROSE
JASMIN
TULIP

2 DOGS
BULLDOG
GERMANSHEPHERED

3 BOOKS
SCIENTIFIC
FICTION
STORIES



Thank you
 
Last edited:
Upvote 0
zaska,

What version of Excel are you using?

You will generally get much more help (and faster) in this forum if you can post your small samples (what you have and what you expect to achieve) directly in the forum.

To attach screenshots, see below in my Signature block: Post a screen shot with one of these:

If you are not able to give us screenshots, see below in my Signature block: You can upload your workbook to Box Net
 
Upvote 0
Code: 
ACTUAL DATA                   DESIRED OUTPUT
 <table width="347" border="0" cellpadding="0" cellspacing="0" height="180"><col style="mso-width-source:userset;mso-width-alt:3035;width:62pt" width="83">  <col style="width:48pt" span="2" width="64">  <col style="mso-width-source:userset;mso-width-alt:2633;width:54pt" width="72">  <col style="width:48pt" width="64">  <tbody><tr style="height:12.75pt" height="17">   <td class="xl22" style="height:12.75pt;width:62pt" width="83" height="17">1 FLOWERS</td>   <td class="xl23" style="width:48pt" width="64"> </td>   <td class="xl23" style="width:48pt" width="64" align="right">1</td>   <td class="xl23" style="width:54pt" width="72">FLOWERS</td>   <td class="xl24" style="width:48pt" width="64">ROSE</td>  </tr>  <tr style="height:12.75pt" height="17">   <td class="xl25" style="height:12.75pt" height="17">ROSE</td>   <td>
</td>   <td align="right">1</td>   <td>FLOWERS</td>   <td class="xl26">LILY</td>  </tr>  <tr style="height:12.75pt" height="17">   <td class="xl25" style="height:12.75pt" height="17">LILY</td>   <td>
</td>   <td align="right">1</td>   <td>FLOWERS</td>   <td class="xl26">JASMIN</td>  </tr>  <tr style="height:12.75pt" height="17">   <td class="xl25" style="height:12.75pt" height="17">JASMIN</td>   <td>
</td>   <td align="right">2</td>   <td>DOGS</td>   <td class="xl26">GERMAN</td>  </tr>  <tr style="height:12.75pt" height="17">   <td class="xl25" style="height:12.75pt" height="17"> </td>   <td>
</td>   <td align="right">2</td>   <td>DOGS</td>   <td class="xl26">BULLDOG</td>  </tr>  <tr style="height:12.75pt" height="17">   <td class="xl25" style="height:12.75pt" height="17">2 DOGS</td>   <td>
</td>   <td align="right">2</td>   <td>DOGS</td>   <td class="xl26">PUG</td>  </tr>  <tr style="height:12.75pt" height="17">   <td class="xl25" style="height:12.75pt" height="17">GERMAN</td>   <td>
</td>   <td>
</td>   <td>
</td>   <td class="xl26"> </td>  </tr>  <tr style="height:12.75pt" height="17">   <td class="xl25" style="height:12.75pt" height="17">BULLDOG</td>   <td>
</td>   <td>
</td>   <td>
</td>   <td class="xl26"> </td>  </tr>  <tr style="height:13.5pt" height="18">   <td class="xl27" style="height:13.5pt" height="18">PUG</td>   <td class="xl28"> </td>   <td class="xl28"> </td>   <td class="xl28"> </td>   <td class="xl29"> </td>  </tr> </tbody></table>
 
Upvote 0
zaska,


Sample data before the macro:


Excel Workbook
ABCDE
11 FLOWERS
2ROSE
3JASMIN
4TULIP
5
62 DOGS
7BULLDOG
8GERMANSHEPHERED
9
103 BOOKS
11SCIENTIFIC
12FICTION
13STORIES
14
Sheet1





After the new macro:


Excel Workbook
ABCDE
11 FLOWERS1FLOWERSROSE
2ROSE1FLOWERSJASMIN
3JASMIN1FLOWERSTULIP
4TULIP2DOGSBULLDOG
52DOGSGERMANSHEPHERED
62 DOGS3BOOKSSCIENTIFIC
7BULLDOG3BOOKSFICTION
8GERMANSHEPHERED3BOOKSSTORIES
9
103 BOOKS
11SCIENTIFIC
12FICTION
13STORIES
14
Sheet1





Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).


Code: 
Option Explicit
Sub SplitAV2()
' hiker95, 04/08/2011
' http://www.mrexcel.com/forum/showthread.php?t=542144
Dim c As Range, NR As Long, N As String, H As String
Application.ScreenUpdating = False
Columns("C:E").ClearContents
NR = 0
For Each c In Range("A1", Range("A" & Rows.Count).End(xlUp))
  If IsNumeric(Left(c, 1)) Then
    N = Left(c, Application.Find(" ", c) - 1)
    H = Right(c, Len(c) - Application.Find(" ", c, 1))
  ElseIf c = "" Then
    'do nothing
  Else
    NR = NR + 1
    Range("C" & NR) = N
    Range("D" & NR) = H
    Range("E" & NR) = c
  End If
Next c
Columns("C:E").AutoFit
Application.ScreenUpdating = True
End Sub


Then run the SplitAV2 macro.
 
Upvote 0
Thank you Mr. Hiker..It worked..

But i have few errors when the Actual Data started with a number as shown below

Column A

6 CPM
3ICER
7SEAS
10GUTE

7 RPM
10
20
30
40
 
Upvote 0
You must log in or register to reply here.

Similar threads

H
  • Solved
Splitting Data into Workbooks - EDIT HELP
Replies
1
Views
265
horrellbt01
H
G
  • Question Question
Rearrange data into two columns
Replies
3
Views
66
Fluff
Fluff
K
  • Question Question
Rolled up color coding
Replies
3
Views
242
felixstraube
F
airforceone
  • Solved
Populate Rows & Columns by Comma Split
2
Replies
12
Views
357
airforceone
airforceone
A
  • Question Question
VBA to Split Data From an Existing Table into Another Single Worksheet with Header for Each Section
Replies
1
Views
550
John_w
J

Forum statistics

Threads
1,223,927
Messages
6,175,438
Members
452,641
Latest member
Arcaila

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top