Splitting Single Column Data Into Three

[zaska]

Hi All!

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

column A

1 FLOWERS
ROSE
JASMIN
TULIP
2 DOGS
BULLDOG
GERMANSHEPHERED

I Would like to split this data AS

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.

 

[mancemonster]

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

 

[hiker95]

zaska,


Sample data before the macro:

Excel Workbook
	A	B	C	D	E
1	1 FLOWERS
2	ROSE
3	JASMIN
4	TULIP
5	2 DOGS
6	BULLDOG
7	GERMANSHEPHERED
8
Sheet1

After the macro:

Excel Workbook
	A	B	C	D	E
1	1 FLOWERS		1	FLOWERS	ROSE
2	ROSE		1	FLOWERS	JASMIN
3	JASMIN		1	FLOWERS	TULIP
4	TULIP		2	DOGS	BULLDOG
5	2 DOGS		2	DOGS	GERMANSHEPHERED
6	BULLDOG
7	GERMANSHEPHERED
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.

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.

 

[zaska]

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.

 

[hiker95]

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

 

[zaska]

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

 

[hiker95]

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

 

[zaska]

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>

 

[hiker95]

zaska,


Sample data before the macro:

Excel Workbook
	A	B	C	D	E
1	1 FLOWERS
2	ROSE
3	JASMIN
4	TULIP
5
6	2 DOGS
7	BULLDOG
8	GERMANSHEPHERED
9
10	3 BOOKS
11	SCIENTIFIC
12	FICTION
13	STORIES
14
Sheet1

After the new macro:

Excel Workbook
	A	B	C	D	E
1	1 FLOWERS		1	FLOWERS	ROSE
2	ROSE		1	FLOWERS	JASMIN
3	JASMIN		1	FLOWERS	TULIP
4	TULIP		2	DOGS	BULLDOG
5			2	DOGS	GERMANSHEPHERED
6	2 DOGS		3	BOOKS	SCIENTIFIC
7	BULLDOG		3	BOOKS	FICTION
8	GERMANSHEPHERED		3	BOOKS	STORIES
9
10	3 BOOKS
11	SCIENTIFIC
12	FICTION
13	STORIES
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).

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.

 

[zaska]

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