Split data in first row for every 24 records

  • Hi,


    I have created a spreadsheet with macro which concatenates the data which in first column with 'comma' character. This would be done for all the data which is there in first column and the concatenated result is given in cell E5.


    Now, the macro should identify first set of 24 records in first column, concatenate with 'comma' character and the results should display in cell E5. Next 24 records results should display in cell E6. This should happen till all the records are done which is in first column.


    I have attached the file for your reference. Kindly review and advise


    Thank you,
    gbpp

  • Re: Split data in first row for every 24 records


    http://www.thecodecage.com/forumz/showthread.php?t=214742



    I'm not going to be able to change things for you but would this be OK?

    Code
    Sub M_snb()
    Dim sn, j As Long
        sn = Cells(1).CurrentRegion.Columns(1).Resize(24 * (Cells(1).CurrentRegion.Rows.Count \ 24 + 1) + 1)
        For j = 2 To UBound(sn) Step 24
            sn(j \ 24 + 1, 1) = Join(Application.Transpose(Application.Index(sn, Evaluate("row(" & j & ":" & j + 23 & ")"), 1)), ", ")
        Next
        Cells(5, 5).Resize(UBound(sn) \ 24) = sn
    End Sub
  • Re: Split data in first row for every 24 records


    Hi Jolivanes,


    I am facing a problem. hope you could check and update me on this. The file really works fine for text. However, if I use numbers then it is not. I tried converting number to text but still it fails. I have attached the data file for your reference. Please have a look

  • Re: Split data in first row for every 24 records


    My English is not so good.
    I wanted to say that I tested the file posted by OP, and VBA code of SNB works (with string or numbers).

  • Re: Split data in first row for every 24 records


    Hi..


    Try formatting your E Column as TEXT.. and this slight variation to that code..


    Code
    Sub M_snb()
        Dim sn, j As Long
        sn = Cells(1).CurrentRegion.Columns(1).Resize(24 * (Cells(1).CurrentRegion.Rows.Count \ 24 + 1))
        For j = 1 To UBound(sn) Step 24
            sn(j \ 24 + 1, 1) = Replace(Trim(Join(Application.Transpose(Application.Index(sn, Evaluate("row(" & j & ":" & j + 23 & ")"), 1)), " ")), " ", ",")
        Next
        Cells(5, 5).Resize(UBound(sn) \ 24) = sn
    End Sub
  • Re: Split data in first row for every 24 records


    Hi,


    Please review and update me. I have attached the result file which I am getting when I use for number format.


    Thank you,
    -gbpp

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!