Multiple Columns into Single Column using Data, Text to Column

kenny9002 · Aug 10, 2014

Please I need help with using Data, Text to column to convert numbers in multiple columns into a single column.
<o

></o

>
In Table A below, I have 5 columns. However, I wish to convert the table into a 3-column table of Date, Numbers and Bonus (as shown in Table B below). I want to be able to use Data, Text to column to convert the numbers in Ball 1, Ball2 and Ball 3 columns into a single column named ‘Numbers’ - with each number shown against the relevant date in the date column.
<o

></o

>
<o

></o

>
<o

></o

>
<o

>Table A
</o

>

Date<o></o>	Ball 1<o></o>	Ball 2<o></o>	Ball 3<o></o>	Bonus<o></o>
01-Jun<o></o>	11<o></o>	13<o></o>	24<o></o>	9<o></o>
08-Jun<o></o>	8<o></o>	9<o></o>	13<o></o>	14<o></o>
15-Jun<o></o>	3<o></o>	4<o></o>	16<o></o>	27<o></o>
22-Jun<o></o>	8<o></o>	24<o></o>	12<o></o>	34<o></o>
29-Jun<o></o>	7<o></o>	13<o></o>	25<o></o>	4<o></o>
06-Jul<o></o>	16<o></o>	24<o></o>	27<o></o>	44<o></o>
13-Jul<o></o>	12<o></o>	7<o></o>	24<o></o>	32<o></o>
20-Jul<o></o>	1<o></o>	3<o></o>	17<o></o>	31<o></o>
27-Jul<o></o>	12<o></o>	13<o></o>	14<o></o>	37<o></o>
03-Aug<o></o>	3<o></o>	5<o></o>	9<o></o>	15<o></o>
10-Aug<o></o>	14<o></o>	15<o></o>	17<o></o>	18<o></o>

<o

></o

>
<o

>
Table B</o

>

Date<o></o>	Number<o></o>	Bonus
<o></o>	<o></o>	<o></o>
<o></o>	<o></o>	<o></o>
<o></o>	<o></o>	<o></o>
<o></o>	<o></o>	<o></o>
<o></o>	<o></o>	<o></o>
<o></o>	<o></o>	<o></o>
<o></o>	<o></o>	<o></o>
<o></o>	<o></o>	<o></o>
<o></o>	<o></o>	<o></o>

<o

></o

>
I'll appreciate help with the correct steps I should take to achieve the conversion.

Thank you all for your anticipated help.

<o

></o

>Kenny

Rick Rothstein · Aug 10, 2014

Give this macro a try...

Code:

Sub ModifiedThreeBallTable()
  Dim DataRowsCount As Long, Source As Range, Destination As Range
  Const ExistingTableLocation As String = "[COLOR=#FF0000][B]A1[/B][/COLOR]"
  Const NewTableLocation As String = "[COLOR=#008000][B]G1[/B][/COLOR]"[COLOR=#FF0000][/COLOR]
  Set Source = Range(ExistingTableLocation).CurrentRegion
  Set Destination = Range(NewTableLocation).Resize(Source.Rows.Count)
  DataRowsCount = Source.Rows.Count
  Source.Columns(1).Resize(, 2).Copy Destination
  Destination.Columns(2).NumberFormat = "@"
  Destination.Offset(, 1) = Evaluate("IF(ROW()," & Source.Columns(2).Address & "&"" - ""&" & _
                            Source.Columns(3).Address & "&"" - ""&" & Source.Columns(4).Address & ")")
  Source.Columns(5).Copy Destination.Offset(, 2)
  Destination(1).Offset(, 1).Value = "Numbers"
End Sub

NOTE: Change the red highlighted text to the address of the top left cell of your existing data and change the green highlighted text to the address of the top left cell where you want the converted table to go to.

HOW TO INSTALL MACROs
------------------------------------
If you are new to macros, they are easy to install and use. To install it, simply press ALT+F11 to go into the VB editor and, once there, click Insert/Module on its menu bar, then copy/paste the above code into the code window that just opened up. That's it.... you are done. To use the macro, go back to the worksheet with your data on it and press ALT+F8, select the macro name (ModifiedThreeBallTable) from the list that appears and click the Run button. The macro will execute and perform the action(s) you asked for. If you will need to do this again in this same workbook, and if you are using XL2007 or above, make sure you save your file as an "Excel Macro-Enabled Workbook (*.xlsm) and answer the "do you want to enable macros" question as "yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.

DocAElstein · Aug 10, 2014

Hey Kenny,

. Why don't you concatenate with your balls!

. Hallo.
. I am not sure how to do what you want with the Data, Text to Column stuff, but with VBA it is fairly simple.
. It is called concatenating. -Big word, but just means joining or "sticking together" The symbol & is often known as the glue.

. Here is a macro.........

Option Explicit Sub KennyConcatenatesHisBalls() Dim j As Byte ' J is now an integer that can be from 0 to 255 For j = 2 To 12 Step 1 ' go through each line and copy columns or copy and concatenate Cells(j, 1).Copy Destination:=Cells(20 + j, 1) 'Cells(20 + j, 1).Value = Cells(j, 1).Value 'Copy date from first table and paste it in the second table. (Using this method the date format is preserved as a simple copy with all format included is made) Cells(20 + j, 2).Value = Cells(j, 2).Value & " " & Cells(j, 3).Value & " " & Cells(j, 4) Cells(20 + j, 3).Value = Cells(j, 3).Value ' Give the value of Bonus in the second Table to as that in the first table Next j End Sub 'KennyConcatenatesHisBalls()

.........which changes this:.......

Book1

A

B

C

D

E

1

Date

Ball 1

Ball 2

Ball 3

Bonus

2

01. Jun

11

13

24

9

3

08. Jun

8

9

13

14

4

15. Jun

3

4

16

27

5

22. Jun

8

24

12

34

6

29. Jun

7

13

25

4

7

06. Jul

16

24

27

44

8

13. Jul

12

7

24

32

9

20. Jul

1

3

17

31

10

27. Jul

12

13

14

37

11

03. Aug

3

5

9

15

12

10. Aug

14

15

17

18

13

14

15

16

17

18

19

20

21

Date

Number

Bonus

22

23

24

25

26

27

28

29

30

31

32

Sheet1

.......to this

Book1

A

B

C

D

E

1

Date

Ball 1

Ball 2

Ball 3

Bonus

2

01. Jun

11

13

24

9

3

08. Jun

8

9

13

14

4

15. Jun

3

4

16

27

5

22. Jun

8

24

12

34

6

29. Jun

7

13

25

4

7

06. Jul

16

24

27

44

8

13. Jul

12

7

24

32

9

20. Jul

1

3

17

31

10

27. Jul

12

13

14

37

11

03. Aug

3

5

9

15

12

10. Aug

14

15

17

18

13

14

15

16

17

18

19

20

21

Date

Number

Bonus

22

01. Jun

11 13 24

13

23

08. Jun

8 9 13

9

24

15. Jun

3 4 16

4

25

22. Jun

8 24 12

24

26

29. Jun

7 13 25

13

27

06. Jul

16 24 27

24

28

13. Jul

12 7 24

7

29

20. Jul

1 3 17

3

30

27. Jul

12 13 14

13

31

03. Aug

3 5 9

5

32

10. Aug

14 15 17

15

Sheet1

Here is the file in XL 2003 and 2007
FileSnack | Easy file sharing
and
FileSnack | Easy file sharing

P.s. Thanks for the question. I have always been looking forward to telling someone to concatenate with their balls, without the risk of being hit or punished.

DocAElstein · Aug 10, 2014

Sorry - as I wrote and sent my reply I did not see that you already had one!

Rick Rothstein · Aug 10, 2014

DocAElstein said:
Sorry - as I wrote and sent my reply I did not see that you already had one!

No problem... this way the OP gets to see two different methods of achieving his goal... your solution using a loop and my solution using no loops.

DocAElstein · Aug 10, 2014

Rick Rothstein said:
No problem... this way the OP gets to see two different methods of achieving his goal... your solution using a loop and my solution using no loops.

You mean "two methods: a beginners unflexible limited solution ; and an all encompasing Profi solution

ray:". I am destroying your code just now with 'green comments as I work through it to understand it:

kenny9002 · Aug 11, 2014

Thanks Rick for your kind help. I was actually looking for how to achieve that using the Data, Text to column feature in Excel, and not with a macro. Someone did that sort of thing in a youtube video. Unfortunately, the said video did not show how it was done even though it was clearly stated that the Data, Text to column feature was used - hence my request.

If there is help on how to achieve this with that feature, I'll be very grateful.

Meanwhile, thanks for all your help.

Kenny

kenny9002 · Aug 11, 2014

Hi DocAElstein,

Thanks for your kind suggestion. Please see my reply to Rick below. I am looking to use the Data, text to column feature to achieve that and to arrange the numbers in the new culumn singly, and the date for each number also showing on the left beside the number - with each number occupying a cell (not a group of numbers taking up one cell as your solution shows). That means that on the date column, there will be duplication of dates, e.g. row 1: 1 June - number = 13; row 2: 1 June - number = 13; row 3: 1 June - number = 24 etc.

In any case, thanks for trying to help.

Kenny

Rick Rothstein · Aug 11, 2014

kenny9002 said:
Thanks Rick for your kind help. I was actually looking for how to achieve that using the Data, Text to column feature in Excel, and not with a macro. Someone did that sort of thing in a youtube video. Unfortunately, the said video did not show how it was done even though it was clearly stated that the Data, Text to column feature was used - hence my request.

Text To Columns takes delimited or consistently spaced data in a single cell and splits it apart into separate, adjacent cells... your request was for the opposite (take data in separate cells and concatenate them together and place the result into a single cell)... as far as I know, there is no built-in Excel functionality that will do that. While there is a formula solution (involving three separate formulas) where the formulas can be copied down through all the rows of data, it appeared to me that you wanted a more direct solution that could be applied to all your data at once, hence the macro solution.

kenny9002 · Aug 11, 2014

Hi Rick,

Thanks again for your time and effort to help me with this. I'm sorry if my explanation was either incorrect or misunderstood. What I am asking for was done by Bill Jelen (MrExcel) in one of his earlier podcasts. He clearly stated in the video that he was using the Data, Text to column to do it. You could see in the video that he achieved that.

Unfortunately, because it was a short video, it did not show the steps he used to achieve that, using that feature. I am including the link to the said podcast/video. I'll be grateful if you would, please, have a look at it. I believe that you or indeed any other forum member also knowledgeable in this area will be able to assist further. Here is the link:

https://www.youtube.com/watch?v=PF8QpDhBQXQ

Thank you for your continued help.

Kenny

Multiple Columns into Single Column using Data, Text to Column

kenny9002

Board Regular

Excel Facts

Rick Rothstein

MrExcel MVP

DocAElstein

Banned user

DocAElstein

Banned user

Rick Rothstein

MrExcel MVP

DocAElstein

Banned user

kenny9002

Board Regular

kenny9002

Board Regular

Rick Rothstein

MrExcel MVP

kenny9002

Board Regular

Similar threads

Forum statistics

Share this page

Multiple Columns into Single Column using Data, Text to Column

Board Regular

Excel Facts

MrExcel MVP

Banned user

Banned user

MrExcel MVP

Banned user

Board Regular

Board Regular

MrExcel MVP

Board Regular

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock