• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Splitting Values in Excel

karthick87

karthick87

Member
Hi Excel Geeks!

Here is my problem...

We have a data that contains 4830 values. All these datas were placed in "Column A". Now I would like to split that 4830 values into 1000's as (=1000+1000+1000+1000+830) and paste that with Transpose (only 1000 values per line). Is there any easy way that I can achieve this?

I have attached an sample excel file, in sheet-1 there are 4830 values and it in sheet-2 I've placed the output like how it could look like for your better understanding.

Thanks in advance.
 

Attachments

  • Test_File.xlsx
    113.2 KB · Views: 11
Hi karthick..
Try this Code..

Code: 
Sub test()
  Set myrange = Sheet1.Range("A1").CurrentRegion
  For k = 1 To Int(myrange.Rows.Count / 1000) + 1
  Sheet2.Range("A" & k).Resize(, 1000) = _
  Application.Transpose(myrange.Cells(((k - 1) * 1000) + 1, 1).Resize(1000))
  Next k
End Sub
 
Thank you Debraj. It is exactly the same what i need but can the result be modified slightly, I would also need the result in Sheet-3 like the one in the attachment. Is that possible?
 
Hi, karthick87!
If you go for a formula solution, try this in A1 and copy across thru ALL (column 1000), and down as required:
A1: =SI((FILA()-1)*1000+COLUMNA()<=CONTARA(Sheet1!$A:$A);INDICE(Sheet1!$A:$A;(FILA()-1)*1000+COLUMNA());"") -----> in english: =IF((ROW()-1)*1000+COLUMN()<=COUNTA(Sheet1!$A:$A),INDEX(Sheet1!$A:$A,(ROW()-1)*1000+COLUMN()),"")
Regards!
 
Hi SirJB7, that is not giving the expected results. Can u just apply it in the sample file which i 've shared to make sure that I'm not doing anything wrong.
 
Hi, karthick87!
That's what I did and it works. Check the uploaded file.
Regards!
 

Attachments

  • Splitting Values in Excel - Test_File (for karthick87 at chandoo.org).xlsx
    105 KB · Views: 6
Thankyou SirJB7, i thought that you were answering for the following..

Thank you Debraj. It is exactly the same what i need but can the result be modified slightly, I would also need the result in Sheet-3 like the one in the attachment. Is that possible?
Click to expand...
 
Hi Debraj,

Can I have the above VBA code as a button in any of the sheet, so that these things should happen on clicking that button. Also I would like to have the same data in Sheet-3 but with different format as shown in the attached Excel file. Is that possible?

Thanks in advance!
 

Attachments

  • Test_File.xlsx
    107.6 KB · Views: 4
Hi Karthick..

Three Manual step.. (I used daily.. whenever I need to search in SQL, and need where clause..
Step 1:
Open winWord > Copy A1:A1000 > and paste Special as unformated Text.
Step 2:
In Word > Find : ^t > Replace with : ','

Step 2.5 : add a single quote in start word & last Word..
Step 0.5 : Your Word should have setted as Word > option > AutoCorrect > uncheck Staright Quote to Smart Quote.

Yes, there was no Step 3, and I am learning Automation in Word..
 
Thanks again.. But I already know the manual method of doing this.. Is there anyway that we could automate this in excel?

Also I would like to have the above VBA code assigned to a button on Sheet-4. Can you please teach me on how to do that...
 
Check this one..

Code: 
Sub test()
  Set SourceSheet = Sheets("Sheet1")
  Set DestSheet = Sheets("Sheet3")
  Set myrange = SourceSheet.Range("A1").CurrentRegion
  For k = 1 To Int(myrange.Rows.Count / 1000) + 1
  DestSheet.Range("A" & k) = _
  "'" & Join(Application.Transpose(myrange.Cells(((k - 1) * 1000) + 1, 1).Resize(1000).Value), "','") & "'"
  Next k
End Sub

Sorry.. its approx 3:00 AM.. i / Someone will update.. if you need.. to remove "',',',',',',',',',',','" at the end.. session..
 
Here is the updated one..
Code: 
Sub test()
  Set SourceSheet = Sheets("Sheet1")
  Set DestSheet = Sheets("Sheet3")
  Set myrange = SourceSheet.Range("A1").CurrentRegion
  For k = 1 To Int(myrange.Rows.Count / 1000) + 1
  DestSheet.Range("A" & k) = _
  "'" & Replace(Join(Filter(Split("~" & Join(Application.Transpose(myrange.Cells(((k - 1) * 1000) + 1, 1).Resize(1000)), "~','~") & "~", "','"), "~~", False), "','"), "~", "") & "'"
  Next k
End Sub
 
Thank you again. It works as expected...

Can you also help me in adding a button and assign the above VBA code, so that I can just click the button to achieve the result.
 
Last edited:
@Debraj(ex-Roy)
Hi, my friend!
On Tuesday. I guess, o from then on, I'll try to play a bit with Word and post here a sample of table automation, that I've done many times, time ago (I actually don't know in which computer should it be, if it still is :(). I had in mind doing it before this weekend but things get a little messy since Wednesday.
Regards!
PS: If I fail remembering it, please tell me.
 
You must log in or register to reply here.
Back
Top