在excel中安排和分组 [英] arranging and grouping in excel

问题描述

你好，

我有两列，A列是1,2,3,4,5,6,7,8,9,10和1,2,3..10以及随后的序列。

C列，我的值对应于A，从0开始，4数字或5个数字，但不完全是10。

我需要对第二列进行排序，使得第一列中的每1列在C列中应该为0。详见附件。我需要结果
，就像第一个链接中的结果一样，现在它是第二个链接。

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

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

谢谢

解决方案

您好Kunal Praveen Kumar，

我建议您使用VBA宏来完成这项工作。您可以在C列中获取并放置值，然后遍历A列中的单元格，然后重置该值。

请参阅以下代码和gif 演示。

 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）。
 
 arrCount = 0 
 Dim arr（）
 ReDim arr（0）
 For i = 1 to lastRowC 
 If IsEmpty（ws.Cells（i，3））= False和IsNumeric（ws.Cells（i，3））= True然后
 arrCount = arrCount + 1 
 ReDim Preserve arr（0 To arrCount）
 arr（arrCount）= ws.Cells（i，3）.Value 
 End if 
接下来i 
 
 ws.Columns（3）.ClearContents 
 
 startIndex = 1 
对于i = 1到lastRowA步骤10 
如果startIndex< = UBound（arr）然后
 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）然后退出Do 
 Loop 
 End if 
 Next i 
 End Sub 

最好的问候，

Terry

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

解决方案

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

这篇关于在excel中安排和分组的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！