Excel Data arranging

[Karan001]

Hi Experts,
I have huge data in excel column sample is given below.
<TABLE style="WIDTH: 48pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=64><COLGROUP><COL style="WIDTH: 48pt" width=64><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 48pt; HEIGHT: 15pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=20 width=64>DATA-1</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=20>1</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=20>2</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=20>3</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=20>4</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=20>5</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=20>6</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=20>7</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=20>8</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=20>9</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=20>10</TD></TR></TBODY></TABLE>

I want a formulae which can arrange the above column data as shown below in one cell of excel or in text file :
'1','2','3','4','5','6','7','8','9','10'

Please tell me how can i get the above result

 

[GTO]

Hi Experts,
I have huge data in excel column sample is given below...

I want a formulae which can arrange the above column data as shown below in one cell of excel or in text file :
'1','2','3','4','5','6','7','8','9','10'...

Hi there,

I'm not sure about concatenating huge data via formula. To write to a textfile, you would want vba.

Option Explicit
    
Sub exa6()
Dim TStream As Object
    
    Set TStream = CreateObject("Scripting.FileSystemObject").OpenTextFile(ThisWorkbook.Path & "\Temp.txt", 2, True, &HFFFFFFFE)
    TStream.Write Join(Application.Transpose(Range(Sheet1.Cells(2, 1), Sheet1.Cells(Sheet1.Rows.count, 1).End(xlUp)).Value), ",")
    TStream.Close
End Sub

Hope that helps,

Mark

 

[Karan001]

Hi Mark,
Thanks a lot for your valuable support.From your suggestion almost i got the solution of my query.Its really too helpful for me.

Currently i am getting out put in text file as shown below :
1,2,3,4,5,6,7,8,9,10

Can you please modified little bit your formula so that i can get output directly like this

1','2','3','4','5','6','7','8','9','10

Once again Thanks to you for your all effort.

 

[Rick Rothstein]

You are going to have to decide how you want to handle the leading apostrophe, but this macro should get you started (although I've never tested it against a huge amount of data)...

Sub ConcatenateColumnA()
  Range("B3").Value = "''" & Join(WorksheetFunction.Transpose(Range("A1:A" & Cells(Rows.Count, "A").End(xlUp).Row)), "','") & "'"
End Sub

Note the first part after the equal sign... that is two single apostrophes inside a set of double quotes. The first apostrophe is being used by Excel to indicate a text string, so it is not displayed... that is why I included the second apostrophe... so you can see a leading apostrophe on the concatenated text. If you were to assign the right side of my code to a variable for output to a text file, then you should remove one of the apostrophes so the text file ends up looking right.

 

[Rick Rothstein]

You are going to have to decide how you want to handle the leading apostrophe, but this macro should get you started (although I've never tested it against a huge amount of data)...

Sub ConcatenateColumnA()
  Range("B3").Value = "''" & Join(WorksheetFunction.Transpose(Range("A1:A" & Cells(Rows.Count, "A").End(xlUp).Row)), "','") & "'"
End Sub

Note the first part after the equal sign... that is two single apostrophes inside a set of double quotes. The first apostrophe is being used by Excel to indicate a text string, so it is not displayed... that is why I included the second apostrophe... so you can see a leading apostrophe on the concatenated text. If you were to assign the right side of my code to a variable for output to a text file, then you should remove one of the apostrophes so the text file ends up looking right.

I see in your follow up to Mark that you are not asking for a leading and trailing apostrophe any more. If that is correct, then you can use this variation of my macro...

Sub ConcatenateColumnA()
  Range("B3").Value = Join(WorksheetFunction.Transpose(Range("A1:A" & Cells(Rows.Count, "A").End(xlUp).Row)), "','")
End Sub

 

[Karan001]

Hi Rick,
Thank you very much for your powerful solution.I checked it and same is working form me.