Multiple Columns into Single Column using Data, Text to Column

K

kenny9002

Board Regular
Joined
Aug 22, 2010
Messages
211
Please I need help with using Data, Text to column to convert numbers in multiple columns into a single column.
<o:p></o:p>
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:p></o:p>
<o:p></o:p>
<o:p></o:p>
<o:p>Table A
</o:p>
[TABLE="class: MsoNormalTable, width: 2"]
<TBODY>[TR]
[TD="width: 59, bgcolor: transparent"]
Date<o:p></o:p>​
[/TD]
[TD="width: 47, bgcolor: transparent"]
Ball 1<o:p></o:p>​
[/TD]
[TD="width: 45, bgcolor: transparent"]
Ball 2<o:p></o:p>​
[/TD]
[TD="width: 43, bgcolor: transparent"]
Ball 3<o:p></o:p>​
[/TD]
[TD="width: 47, bgcolor: transparent"]
Bonus<o:p></o:p>​
[/TD]
[/TR]
[TR]
[TD="width: 59, bgcolor: transparent"]
01-Jun<o:p></o:p>​
[/TD]
[TD="width: 47, bgcolor: transparent"]
11<o:p></o:p>​
[/TD]
[TD="width: 45, bgcolor: transparent"]
13<o:p></o:p>​
[/TD]
[TD="width: 43, bgcolor: transparent"]
24<o:p></o:p>​
[/TD]
[TD="width: 47, bgcolor: transparent"]
9<o:p></o:p>​
[/TD]
[/TR]
[TR]
[TD="width: 59, bgcolor: transparent"]
08-Jun<o:p></o:p>​
[/TD]
[TD="width: 47, bgcolor: transparent"]
8<o:p></o:p>​
[/TD]
[TD="width: 45, bgcolor: transparent"]
9<o:p></o:p>​
[/TD]
[TD="width: 43, bgcolor: transparent"]
13<o:p></o:p>​
[/TD]
[TD="width: 47, bgcolor: transparent"]
14<o:p></o:p>​
[/TD]
[/TR]
[TR]
[TD="width: 59, bgcolor: transparent"]
15-Jun<o:p></o:p>​
[/TD]
[TD="width: 47, bgcolor: transparent"]
3<o:p></o:p>​
[/TD]
[TD="width: 45, bgcolor: transparent"]
4<o:p></o:p>​
[/TD]
[TD="width: 43, bgcolor: transparent"]
16<o:p></o:p>​
[/TD]
[TD="width: 47, bgcolor: transparent"]
27<o:p></o:p>​
[/TD]
[/TR]
[TR]
[TD="width: 59, bgcolor: transparent"]
22-Jun<o:p></o:p>​
[/TD]
[TD="width: 47, bgcolor: transparent"]
8<o:p></o:p>​
[/TD]
[TD="width: 45, bgcolor: transparent"]
24<o:p></o:p>​
[/TD]
[TD="width: 43, bgcolor: transparent"]
12<o:p></o:p>​
[/TD]
[TD="width: 47, bgcolor: transparent"]
34<o:p></o:p>​
[/TD]
[/TR]
[TR]
[TD="width: 59, bgcolor: transparent"]
29-Jun<o:p></o:p>​
[/TD]
[TD="width: 47, bgcolor: transparent"]
7<o:p></o:p>​
[/TD]
[TD="width: 45, bgcolor: transparent"]
13<o:p></o:p>​
[/TD]
[TD="width: 43, bgcolor: transparent"]
25<o:p></o:p>​
[/TD]
[TD="width: 47, bgcolor: transparent"]
4<o:p></o:p>​
[/TD]
[/TR]
[TR]
[TD="width: 59, bgcolor: transparent"]
06-Jul<o:p></o:p>​
[/TD]
[TD="width: 47, bgcolor: transparent"]
16<o:p></o:p>​
[/TD]
[TD="width: 45, bgcolor: transparent"]
24<o:p></o:p>​
[/TD]
[TD="width: 43, bgcolor: transparent"]
27<o:p></o:p>​
[/TD]
[TD="width: 47, bgcolor: transparent"]
44<o:p></o:p>​
[/TD]
[/TR]
[TR]
[TD="width: 59, bgcolor: transparent"]
13-Jul<o:p></o:p>​
[/TD]
[TD="width: 47, bgcolor: transparent"]
12<o:p></o:p>​
[/TD]
[TD="width: 45, bgcolor: transparent"]
7<o:p></o:p>​
[/TD]
[TD="width: 43, bgcolor: transparent"]
24<o:p></o:p>​
[/TD]
[TD="width: 47, bgcolor: transparent"]
32<o:p></o:p>​
[/TD]
[/TR]
[TR]
[TD="width: 59, bgcolor: transparent"]
20-Jul<o:p></o:p>​
[/TD]
[TD="width: 47, bgcolor: transparent"]
1<o:p></o:p>​
[/TD]
[TD="width: 45, bgcolor: transparent"]
3<o:p></o:p>​
[/TD]
[TD="width: 43, bgcolor: transparent"]
17<o:p></o:p>​
[/TD]
[TD="width: 47, bgcolor: transparent"]
31<o:p></o:p>​
[/TD]
[/TR]
[TR]
[TD="width: 59, bgcolor: transparent"]
27-Jul<o:p></o:p>​
[/TD]
[TD="width: 47, bgcolor: transparent"]
12<o:p></o:p>​
[/TD]
[TD="width: 45, bgcolor: transparent"]
13<o:p></o:p>​
[/TD]
[TD="width: 43, bgcolor: transparent"]
14<o:p></o:p>​
[/TD]
[TD="width: 47, bgcolor: transparent"]
37<o:p></o:p>​
[/TD]
[/TR]
[TR]
[TD="width: 59, bgcolor: transparent"]
03-Aug<o:p></o:p>​
[/TD]
[TD="width: 47, bgcolor: transparent"]
3<o:p></o:p>​
[/TD]
[TD="width: 45, bgcolor: transparent"]
5<o:p></o:p>​
[/TD]
[TD="width: 43, bgcolor: transparent"]
9<o:p></o:p>​
[/TD]
[TD="width: 47, bgcolor: transparent"]
15<o:p></o:p>​
[/TD]
[/TR]
[TR]
[TD="width: 59, bgcolor: transparent"]
10-Aug<o:p></o:p>​
[/TD]
[TD="width: 47, bgcolor: transparent"]
14<o:p></o:p>​
[/TD]
[TD="width: 45, bgcolor: transparent"]
15<o:p></o:p>​
[/TD]
[TD="width: 43, bgcolor: transparent"]
17<o:p></o:p>​
[/TD]
[TD="width: 47, bgcolor: transparent"]
18<o:p></o:p>​
[/TD]
[/TR]
</TBODY>[/TABLE]
<o:p></o:p>
<o:p>
Table B</o:p>
[TABLE="class: MsoNormalTable, width: 135"]
<TBODY>[TR]
[TD="width: 59, bgcolor: transparent"]
Date<o:p></o:p>​
[/TD]
[TD="width: 64, bgcolor: transparent"]
Number<o:p></o:p>​
[/TD]
[TD="width: 57, bgcolor: transparent"]
Bonus​
[/TD]
[/TR]
[TR]
[TD="width: 59, bgcolor: transparent"]<o:p></o:p>
[/TD]
[TD="width: 64, bgcolor: transparent"]<o:p></o:p>
[/TD]
[TD="width: 57, bgcolor: transparent"]<o:p></o:p>
[/TD]
[/TR]
[TR]
[TD="width: 59, bgcolor: transparent"]<o:p></o:p>
[/TD]
[TD="width: 64, bgcolor: transparent"]<o:p></o:p>
[/TD]
[TD="width: 57, bgcolor: transparent"]<o:p></o:p>
[/TD]
[/TR]
[TR]
[TD="width: 59, bgcolor: transparent"]<o:p></o:p>
[/TD]
[TD="width: 64, bgcolor: transparent"]<o:p></o:p>
[/TD]
[TD="width: 57, bgcolor: transparent"]<o:p></o:p>
[/TD]
[/TR]
[TR]
[TD="width: 59, bgcolor: transparent"]<o:p></o:p>
[/TD]
[TD="width: 64, bgcolor: transparent"]<o:p></o:p>
[/TD]
[TD="width: 57, bgcolor: transparent"]<o:p></o:p>
[/TD]
[/TR]
[TR]
[TD="width: 59, bgcolor: transparent"]<o:p></o:p>
[/TD]
[TD="width: 64, bgcolor: transparent"]<o:p></o:p>
[/TD]
[TD="width: 57, bgcolor: transparent"]<o:p></o:p>
[/TD]
[/TR]
[TR]
[TD="width: 59, bgcolor: transparent"]<o:p></o:p>
[/TD]
[TD="width: 64, bgcolor: transparent"]<o:p></o:p>
[/TD]
[TD="width: 57, bgcolor: transparent"]<o:p></o:p>
[/TD]
[/TR]
[TR]
[TD="width: 59, bgcolor: transparent"]<o:p></o:p>
[/TD]
[TD="width: 64, bgcolor: transparent"]<o:p></o:p>
[/TD]
[TD="width: 57, bgcolor: transparent"]<o:p></o:p>
[/TD]
[/TR]
[TR]
[TD="width: 59, bgcolor: transparent"]<o:p></o:p>
[/TD]
[TD="width: 64, bgcolor: transparent"]<o:p></o:p>
[/TD]
[TD="width: 57, bgcolor: transparent"]<o:p></o:p>
[/TD]
[/TR]
[TR]
[TD="width: 59, bgcolor: transparent"]<o:p></o:p>
[/TD]
[TD="width: 64, bgcolor: transparent"]<o:p></o:p>
[/TD]
[TD="width: 57, bgcolor: transparent"]<o:p></o:p>
[/TD]
[/TR]
</TBODY>[/TABLE]
<o:p></o:p>
I'll appreciate help with the correct steps I should take to achieve the conversion.

Thank you all for your anticipated help.

<o:p></o:p>Kenny
 
Last edited:

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.
Sort by date Sort by votes
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.
 
Upvote 0
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.........

<font face=Calibri><SPAN style="color:#00007F">Option</SPAN> <SPAN style="color:#00007F">Explicit</SPAN><br><SPAN style="color:#00007F">Sub</SPAN> KennyConcatenatesHisBalls()<br> <SPAN style="color:#00007F">Dim</SPAN> j <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Byte</SPAN> <SPAN style="color:#007F00">' J is now an integer that can be from 0 to 255</SPAN><br>   <SPAN style="color:#00007F">For</SPAN> j = 2 <SPAN style="color:#00007F">To</SPAN> 12 <SPAN style="color:#00007F">Step</SPAN> 1 <SPAN style="color:#007F00">' go through each line and copy columns or copy and concatenate</SPAN><br>     Cells(j, 1).Copy Destination:=Cells(20 + j, 1)<br>     <SPAN style="color:#007F00">'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)</SPAN><br>     Cells(20 + j, 2).Value = Cells(j, 2).Value & " " & Cells(j, 3).Value & " " & Cells(j, 4)<br>     Cells(20 + j, 3).Value = Cells(j, 3).Value <SPAN style="color:#007F00">' Give the value of Bonus in the second Table to as that in the first table</SPAN><br>   <SPAN style="color:#00007F">Next</SPAN> j<br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN> <SPAN style="color:#007F00">'KennyConcatenatesHisBalls()</SPAN></FONT>


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


Book1
ABCDE
1DateBall 1Ball 2Ball 3Bonus
201. Jun1113249
308. Jun891314
415. Jun341627
522. Jun8241234
629. Jun713254
706. Jul16242744
813. Jul1272432
920. Jul131731
1027. Jul12131437
1103. Aug35915
1210. Aug14151718
13
14
15
16
17
18
19
20
21DateNumberBonus
22
23
24
25
26
27
28
29
30
31
32
Sheet1




.......to this


Book1
ABCDE
1DateBall 1Ball 2Ball 3Bonus
201. Jun1113249
308. Jun891314
415. Jun341627
522. Jun8241234
629. Jun713254
706. Jul16242744
813. Jul1272432
920. Jul131731
1027. Jul12131437
1103. Aug35915
1210. Aug14151718
13
14
15
16
17
18
19
20
21DateNumberBonus
2201. Jun11 13 2413
2308. Jun8 9 139
2415. Jun3 4 164
2522. Jun8 24 1224
2629. Jun7 13 2513
2706. Jul16 24 2724
2813. Jul12 7 247
2920. Jul1 3 173
3027. Jul12 13 1413
3103. Aug3 5 95
3210. Aug14 15 1715
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.
 
Upvote 0
DocAElstein said:
Sorry - as I wrote and sent my reply I did not see that you already had one!
Click to expand...
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.
 
Upvote 0
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.
Click to expand...



You mean "two methods: a beginners unflexible limited solution ; and an all encompasing Profi solution :pray:". I am destroying your code just now with 'green comments as I work through it to understand it:
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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.
Click to expand...
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.
 
Upvote 0
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
 
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Solved
Calculate Backdated dates
Replies
5
Views
277
Joe4
Joe4
S
  • Question Question
Problem with x-axis labels in excel chart
Replies
1
Views
422
sharshra
S
A
  • Question Question
Assign Month number 1 to n based on sequential week range
Replies
6
Views
467
amolvijay
A
K
  • Question Question
Date Range
Replies
1
Views
242
awoohaw
awoohaw
G
  • Question Question
VBA Userform Date Format
Replies
7
Views
182
gilly01625
G

Forum statistics

Threads
1,223,893
Messages
6,175,240
Members
452,621
Latest member
Laura_PinksBTHFT

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