Multiple Columns into Single Column using Data, Text to Column

[kenny9002]

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>
[TABLE="class: MsoNormalTable, width: 2"]
<TBODY>[TR]
[TD="width: 59, bgcolor: transparent"]

Date<o></o>​

[/TD]
[TD="width: 47, bgcolor: transparent"]

Ball 1<o></o>​

[/TD]
[TD="width: 45, bgcolor: transparent"]

Ball 2<o></o>​

[/TD]
[TD="width: 43, bgcolor: transparent"]

Ball 3<o></o>​

[/TD]
[TD="width: 47, bgcolor: transparent"]

Bonus<o></o>​

[/TD]
[/TR]
[TR]
[TD="width: 59, bgcolor: transparent"]

01-Jun<o></o>​

[/TD]
[TD="width: 47, bgcolor: transparent"]

11<o></o>​

[/TD]
[TD="width: 45, bgcolor: transparent"]

13<o></o>​

[/TD]
[TD="width: 43, bgcolor: transparent"]

24<o></o>​

[/TD]
[TD="width: 47, bgcolor: transparent"]

9<o></o>​

[/TD]
[/TR]
[TR]
[TD="width: 59, bgcolor: transparent"]

08-Jun<o></o>​

[/TD]
[TD="width: 47, bgcolor: transparent"]

8<o></o>​

[/TD]
[TD="width: 45, bgcolor: transparent"]

9<o></o>​

[/TD]
[TD="width: 43, bgcolor: transparent"]

13<o></o>​

[/TD]
[TD="width: 47, bgcolor: transparent"]

14<o></o>​

[/TD]
[/TR]
[TR]
[TD="width: 59, bgcolor: transparent"]

15-Jun<o></o>​

[/TD]
[TD="width: 47, bgcolor: transparent"]

3<o></o>​

[/TD]
[TD="width: 45, bgcolor: transparent"]

4<o></o>​

[/TD]
[TD="width: 43, bgcolor: transparent"]

16<o></o>​

[/TD]
[TD="width: 47, bgcolor: transparent"]

27<o></o>​

[/TD]
[/TR]
[TR]
[TD="width: 59, bgcolor: transparent"]

22-Jun<o></o>​

[/TD]
[TD="width: 47, bgcolor: transparent"]

8<o></o>​

[/TD]
[TD="width: 45, bgcolor: transparent"]

24<o></o>​

[/TD]
[TD="width: 43, bgcolor: transparent"]

12<o></o>​

[/TD]
[TD="width: 47, bgcolor: transparent"]

34<o></o>​

[/TD]
[/TR]
[TR]
[TD="width: 59, bgcolor: transparent"]

29-Jun<o></o>​

[/TD]
[TD="width: 47, bgcolor: transparent"]

7<o></o>​

[/TD]
[TD="width: 45, bgcolor: transparent"]

13<o></o>​

[/TD]
[TD="width: 43, bgcolor: transparent"]

25<o></o>​

[/TD]
[TD="width: 47, bgcolor: transparent"]

4<o></o>​

[/TD]
[/TR]
[TR]
[TD="width: 59, bgcolor: transparent"]

06-Jul<o></o>​

[/TD]
[TD="width: 47, bgcolor: transparent"]

16<o></o>​

[/TD]
[TD="width: 45, bgcolor: transparent"]

24<o></o>​

[/TD]
[TD="width: 43, bgcolor: transparent"]

27<o></o>​

[/TD]
[TD="width: 47, bgcolor: transparent"]

44<o></o>​

[/TD]
[/TR]
[TR]
[TD="width: 59, bgcolor: transparent"]

13-Jul<o></o>​

[/TD]
[TD="width: 47, bgcolor: transparent"]

12<o></o>​

[/TD]
[TD="width: 45, bgcolor: transparent"]

7<o></o>​

[/TD]
[TD="width: 43, bgcolor: transparent"]

24<o></o>​

[/TD]
[TD="width: 47, bgcolor: transparent"]

32<o></o>​

[/TD]
[/TR]
[TR]
[TD="width: 59, bgcolor: transparent"]

20-Jul<o></o>​

[/TD]
[TD="width: 47, bgcolor: transparent"]

1<o></o>​

[/TD]
[TD="width: 45, bgcolor: transparent"]

3<o></o>​

[/TD]
[TD="width: 43, bgcolor: transparent"]

17<o></o>​

[/TD]
[TD="width: 47, bgcolor: transparent"]

31<o></o>​

[/TD]
[/TR]
[TR]
[TD="width: 59, bgcolor: transparent"]

27-Jul<o></o>​

[/TD]
[TD="width: 47, bgcolor: transparent"]

12<o></o>​

[/TD]
[TD="width: 45, bgcolor: transparent"]

13<o></o>​

[/TD]
[TD="width: 43, bgcolor: transparent"]

14<o></o>​

[/TD]
[TD="width: 47, bgcolor: transparent"]

37<o></o>​

[/TD]
[/TR]
[TR]
[TD="width: 59, bgcolor: transparent"]

03-Aug<o></o>​

[/TD]
[TD="width: 47, bgcolor: transparent"]

3<o></o>​

[/TD]
[TD="width: 45, bgcolor: transparent"]

5<o></o>​

[/TD]
[TD="width: 43, bgcolor: transparent"]

9<o></o>​

[/TD]
[TD="width: 47, bgcolor: transparent"]

15<o></o>​

[/TD]
[/TR]
[TR]
[TD="width: 59, bgcolor: transparent"]

10-Aug<o></o>​

[/TD]
[TD="width: 47, bgcolor: transparent"]

14<o></o>​

[/TD]
[TD="width: 45, bgcolor: transparent"]

15<o></o>​

[/TD]
[TD="width: 43, bgcolor: transparent"]

17<o></o>​

[/TD]
[TD="width: 47, bgcolor: transparent"]

18<o></o>​

[/TD]
[/TR]
</TBODY>[/TABLE]
<o></o>
<o>
Table B</o>
[TABLE="class: MsoNormalTable, width: 135"]
<TBODY>[TR]
[TD="width: 59, bgcolor: transparent"]

Date<o></o>​

[/TD]
[TD="width: 64, bgcolor: transparent"]

Number<o></o>​

[/TD]
[TD="width: 57, bgcolor: transparent"]

Bonus​

[/TD]
[/TR]
[TR]
[TD="width: 59, bgcolor: transparent"]<o></o>
[/TD]
[TD="width: 64, bgcolor: transparent"]<o></o>
[/TD]
[TD="width: 57, bgcolor: transparent"]<o></o>
[/TD]
[/TR]
[TR]
[TD="width: 59, bgcolor: transparent"]<o></o>
[/TD]
[TD="width: 64, bgcolor: transparent"]<o></o>
[/TD]
[TD="width: 57, bgcolor: transparent"]<o></o>
[/TD]
[/TR]
[TR]
[TD="width: 59, bgcolor: transparent"]<o></o>
[/TD]
[TD="width: 64, bgcolor: transparent"]<o></o>
[/TD]
[TD="width: 57, bgcolor: transparent"]<o></o>
[/TD]
[/TR]
[TR]
[TD="width: 59, bgcolor: transparent"]<o></o>
[/TD]
[TD="width: 64, bgcolor: transparent"]<o></o>
[/TD]
[TD="width: 57, bgcolor: transparent"]<o></o>
[/TD]
[/TR]
[TR]
[TD="width: 59, bgcolor: transparent"]<o></o>
[/TD]
[TD="width: 64, bgcolor: transparent"]<o></o>
[/TD]
[TD="width: 57, bgcolor: transparent"]<o></o>
[/TD]
[/TR]
[TR]
[TD="width: 59, bgcolor: transparent"]<o></o>
[/TD]
[TD="width: 64, bgcolor: transparent"]<o></o>
[/TD]
[TD="width: 57, bgcolor: transparent"]<o></o>
[/TD]
[/TR]
[TR]
[TD="width: 59, bgcolor: transparent"]<o></o>
[/TD]
[TD="width: 64, bgcolor: transparent"]<o></o>
[/TD]
[TD="width: 57, bgcolor: transparent"]<o></o>
[/TD]
[/TR]
[TR]
[TD="width: 59, bgcolor: transparent"]<o></o>
[/TD]
[TD="width: 64, bgcolor: transparent"]<o></o>
[/TD]
[TD="width: 57, bgcolor: transparent"]<o></o>
[/TD]
[/TR]
[TR]
[TD="width: 59, bgcolor: transparent"]<o></o>
[/TD]
[TD="width: 64, bgcolor: transparent"]<o></o>
[/TD]
[TD="width: 57, bgcolor: transparent"]<o></o>
[/TD]
[/TR]
</TBODY>[/TABLE]
<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]

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

He did something completely different than what you want to do.... you want to take data in separate cells and put them into one cell and put a delimiter between them... he took data delimited by commas that are in one cell and split them out into separate cells. So the video you provided a link for does not do what you are attempting to do.

Now, about what he did though... while he said he used Text To Columns to do it, I think he did more than that but did not mention what that was. The reason I say that is because he took this kind of data....

1,2,3,4

and placed it in separate cells down a common column like this..

1
2
3
4

and as far as I know, you cannot do that with Text To Columns all by itself. But I repeat, what he does in the video with his data is not what you are trying to do with your data... they are the complete opposite processing of data from each other.

 

[DocAElstein]

Hi DocAElstein,

Thanks for your kind suggestion. Please see my reply to Rick below. .........

..........In any case, thanks for trying to help.

Kenny

Kenny..
. You are welcome. The Practice did me good. And I have benefited greatly in my learning by going through Rick’s code. (As I mentioned initially I am sorry but I cannot help you in a solution that uses the Data, text to column feature in Excel to arrange the numbers ). Good luck anyway and have fun concatenating with your balls!

Alan
P.s. I had a quick look at the YouTube thing, but unfortunately I Know nothing about Pivot tables etc, sorry again.

 

[DocAElstein]

Give this macro a try...

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]"
  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.

Rick Rothstein..
. Although your code did not help the OP in concatenating with his Balls, I have benefited greatly in my learning of manipulating tables of data with VBA by working through it and understanding it. I have 95% of the code well understood now, but had one question on it. Could I ask that here as a reply to this thread?
Alan Elston

 

[Rick Rothstein]

Rick Rothstein..
. Although your code did not help the OP in concatenating with his Balls, I have benefited greatly in my learning of manipulating tables of data with VBA by working through it and understanding it. I have 95% of the code well understood now, but had one question on it. Could I ask that here as a reply to this thread?

I don't see why not... I would think a question about a proposed solution would be considered "fair game" as a follow up.

 

[DocAElstein]

I don't see why not... I would think a question about a proposed solution would be considered "fair game" as a follow up.

Rick..

. As you can see below I think I have the most of the well understood. I have modified it slightly to help me see what is going on as well as filling it with green comment Graffiti!


<font face=Calibri><SPAN style="color:#007F00">'______________________________________________________________</SPAN><br><SPAN style="color:#007F00">'</SPAN><br><SPAN style="color:#007F00">' Slightly modified with lots of Comments</SPAN><br><SPAN style="color:#00007F">Sub</SPAN> RickRothsteinsConcatenatingBalls()<br><SPAN style="color:#007F00">'NOTE: Change the ## A1 to the address of the top left cell of your</SPAN><br><SPAN style="color:#007F00">'existing data and change the '### G1 to the address of the top left cell where you want the converted table to go to.</SPAN><br>  <SPAN style="color:#00007F">Const</SPAN> ExistingTableAnyCellLocation <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN> = "A1" <SPAN style="color:#007F00">'##</SPAN><br>  <SPAN style="color:#00007F">Const</SPAN> NewTableLHCornerLocation <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN> = "G1" <SPAN style="color:#007F00">'###</SPAN><br>  <br>  <SPAN style="color:#00007F">Dim</SPAN> SourceTableRange <SPAN style="color:#00007F">As</SPAN> Range <SPAN style="color:#007F00">'Give a name and allow all methods and properties of range object to it</SPAN><br>  <SPAN style="color:#00007F">Set</SPAN> SourceTableRange = Range(ExistingTableAnyCellLocation).CurrentRegion <SPAN style="color:#007F00">' Give this a specific Range. CurrentRegiuon Property applied to a cell returns a complete range incorporating that cell and any ranges that it either is in or touches</SPAN><br>  <SPAN style="color:#00007F">Dim</SPAN> SourceTableRangeTableRowsCount <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Byte</SPAN> <SPAN style="color:#007F00">' For a small Table allow it to hve up to 255 Rows</SPAN><br>  <SPAN style="color:#00007F">Let</SPAN> SourceTableRangeTableRowsCount = SourceTableRange.Rows.Count <SPAN style="color:#007F00">' This returns the number of rows in the original table</SPAN><br><br>  <SPAN style="color:#00007F">Dim</SPAN> FinalTableFirstColumnRange <SPAN style="color:#00007F">As</SPAN> Range<br>  <SPAN style="color:#00007F">Set</SPAN> FinalTableFirstColumnRange = Range(NewTableLHCornerLocation).Resize(SourceTableRangeTableRowsCount) <SPAN style="color:#007F00">' Here the resize Property applied to the Range G1 (or Cell G1 here) returns a range increased by the row number, that is to say a range equal to the first column</SPAN><br>  <br>  SourceTableRange.Columns(1).Resize(, 2).Copy Destination:=FinalTableFirstColumnRange <SPAN style="color:#007F00">' This is one way of copying the first two columns of the original table to The final table</SPAN><br>  FinalTableFirstColumnRange.Columns(2).NumberFormat = "@" <SPAN style="color:#007F00">' This gives a format to the second column in the final Table</SPAN><br>  <br>  FinalTableFirstColumnRange.Offset(0, 1) = _<br>  Evaluate("IF(ROW()," & SourceTableRange.Columns(2).Address & "&"" - ""&" & SourceTableRange.Columns(3).Address & "&"" - ""&" & SourceTableRange.Columns(4).Address & ")")<br>  <br>  SourceTableRange.Columns(5).Copy Destination:=FinalTableFirstColumnRange.Offset(, 2) <SPAN style="color:#007F00">'Column 5 of Original table is copied to column 3 of the Final table by setting the destination to 2 colums offset from the first column</SPAN><br>  <br>  FinalTableFirstColumnRange.Cells(1, 0).Offset(0, 1).Value = "Numbers" <SPAN style="color:#007F00">' The current heading in the second column is finally overwriten with "Numbers".  This is done here by putting the value"Numbers" in the cell which is offset by 1 column to the first cell in the Final Table First Column</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN> <SPAN style="color:#007F00">'RickRothsteinsConcatenatingBalls()</SPAN><br><SPAN style="color:#007F00">'</SPAN><br><SPAN style="color:#007F00">''_______________________________________________________________________________</SPAN></FONT>


. One line I just could not understand despite pulling it apart over a few hours last night. Googling about Row() did not get me far either.

My code:

FinalTableFirstColumnRange.Offset(0, 1) = _
  Evaluate("IF(ROW()," & SourceTableRange.Columns(2).Address & "&"" - ""&" & SourceTableRange.Columns(3).Address & "&"" - ""&" & SourceTableRange.Columns(4).Address & ")")

Your Code:

Destination.Offset(, 1) = Evaluate("IF(ROW()," & Source.Columns(2).Address & "&"" - ""&" & _
                            Source.Columns(3).Address & "&"" - ""&" & Source.Columns(4).Address & ")")

. I think I did get close and in doing so mastered the Evaluate function: I was able to replace your one line code with the following loop and I achieve the same results as with your 1 line code


My 3 line Loop alternative:

<font face=Calibri>    <SPAN style="color:#00007F">For</SPAN> j = 2 <SPAN style="color:#00007F">To</SPAN> SourceTableRangeTableRowsCount <SPAN style="color:#00007F">Step</SPAN> 1 <SPAN style="color:#007F00">' For each column of data</SPAN><br>    FinalTableFirstColumnRange.Cells(1, 1).Offset(j - 1, 1) = Evaluate("" & SourceTableRange.Cells(j, 2).Address & "" & "&"" - ""&" & "" & SourceTableRange.Cells(j, 3).Address & "" & "&"" - ""&" & "" & SourceTableRange.Cells(j, 4).Address & "") <SPAN style="color:#007F00">' the evaluate fuction can be used in VBA to give the reults from a formula in a normal Excel Spreadsheet. Hier we use it simply to give the results of a formula something of the form  =B2 & " - " & C2 & " - " & D2. Just a convenient way of doung the concantenating. Further we here maks the addresses B2 C2 D2 variable in a loop. A very tricky syntax!!!</SPAN><br>    <SPAN style="color:#00007F">Next</SPAN> j</FONT>


. Exactly how your code line works is really baffling me. Somehow I cannot see wot the Row() bit is doing, but it seems to be essential to be there in any variations of the code line that I have tried.

. I would really appreciate a voll explanation of this code line. (As I mentioned, although it took a lot of trial and error I think that I mastered Evaluate Syntax)

Thanks.

P.s.1 Here is a file with all my codes and variations of yours that I have tried (Your codes and variations I have placed in the second sheet Module which I have renamed Rick)


FileSnack | Easy file sharing


P.s.2 I may not be able to reply again for a while.. Every time I reply to this forum it uses up one of my 5 allowed attempts to log-in, even though I never log-out in the meantime!?!. I am on my last one here!!. And contrarily to what is says about having to wait for 15 Minutes.. -by me that time seems to be about 2 hours!?.

 

[DocAElstein]

.........IWhat 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..............

Hi Kenny,
You are probably not “Concatenating with your Balls” these days, but I coincidently stumbled over a couple of things that might possibly be of short interest

. 1 On a lighter note First: I think we all assumed that a simple formula of the form = “B1” & “ - “ & “C1” & “ - “ & “D!” and our other suggestions of how to concatenate with your Balls was not of interest. However just in case the same or similar suggestion coming from a young Lady would be more attractive to you, then maybe a small glance here would be worthwhile while pondering how to Concatenate with your Balls
https://www.youtube.com/watch?v=XKxhJFXO9D4

. 2 On a more serious note: In the early replies to this Thread ( Multiple Columns Into Single Column Using Data Text To Column ) there are som interesting comments on that Bill Jelen Video .........

Alan