none
arranging and grouping in excel RRS feed

  • Question

  • hello,

    I have two columns, the A column being a sequence of 1,2,3,4,5,6,7,8,9,10 and again 1,2,3..10 and subsequently.
    The C column, I have values corresponding to A starting from 0 with 4 numbers or 5 numbers and not completely till 10.
    I need to sort the second column such that for every 1 in the first column there should be 0 in the C column. See attachment. I need results like the one in the first link and right now it's the second link.

    https://gotitapp.zendesk.com/attachments/token/kMHNcFoKIcOpHKFLm8EnaIcvN/?name=15103665431942045526870.jpg

    https://gotitapp.zendesk.com/attachments/token/2FmgEzRB4Q31MXAwIGGG0B7Xo/?name=1510366582883346851707.jpg

    Thank You

    Sunday, November 12, 2017 6:42 PM

All replies

  • Hi Kunal Praveen Kumar,

    I would suggest you use VBA macro to do this job. You could get and put values in column C and then iterate through cells in Column A and then reset the value.

    Please refer to below code and gif demonstrate.

    Sub Test()
    Dim ws As Worksheet
    Set ws = ActiveSheet
    lastRowA = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row
    lastRowC = ws.Cells(ws.Rows.Count, 3).End(xlUp).Row
    
    arrCount = 0
    Dim arr()
    ReDim arr(0)
    For i = 1 To lastRowC
    If IsEmpty(ws.Cells(i, 3)) = False And IsNumeric(ws.Cells(i, 3)) = True Then
    arrCount = arrCount + 1
    ReDim Preserve arr(0 To arrCount)
    arr(arrCount) = ws.Cells(i, 3).Value
    End If
    Next i
    
    ws.Columns(3).ClearContents
    
    startIndex = 1
    For i = 1 To lastRowA Step 10
        If startIndex <= UBound(arr) Then
        ws.Cells(i, 3).Value = arr(startIndex)
        startIndex = startIndex + 1
        offCount = 0
        Do While Not arr(startIndex) = 0
        offCount = offCount + 1
        ws.Cells(i, 3).Offset(offCount, 0).Value = arr(startIndex)
        startIndex = startIndex + 1
        If startIndex > UBound(arr) Then Exit Do
        Loop
        End If
    Next i
    End Sub

    Best Regards,

    Terry


    MSDN Community Support Please remember to click &amp;quot;Mark as Answer&amp;quot; the responses that resolved your issue, and to click &amp;quot;Unmark as Answer&amp;quot; if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, November 13, 2017 3:50 AM