按一列排序,然后在第二列中分组相同的值? [英] Sort by One Column, Then Group Same Values in Second Column?
问题描述
我的电子表格中有2列,序列号和日期.我已经将日期"列按升序排序,产生的结果如下:
I have 2 columns in my spreadsheet, Serial Number and Date. I have sorted the Date column into ascending order already which yields a result that looks like:
Serial Number Date
22222 02/09/2020
11111 02/10/2020
33333 02/11/2020
11111 02/12/2020
22222 02/13/2020
44444 02/14/2020
我想使用2016 VBA从这里开始,是将所有相同的序列号组合在一起,每个序列号的最早日期是第一个,就像这样:
What I'd like to do from here using 2016 VBA is group all the same serial numbers together, having the soonest date of each serial number as the first one, like so:
Serial Number Date
22222 02/09/2020
22222 02/13/2020
11111 02/10/2020
11111 02/12/2020
33333 02/11/2020
44444 02/14/2020
所以我最后想要的是首先是最快日期的电子表格,但是如果有多个序列号相同,则将它们放在列表中并按序列号进行分组,但要保持日期的升序以及其他序列号.
So what I want to end up with is a spreadsheet of the soonest dates first, but if there are multiple serial numbers the same, then bring those up the list and group them by serial number, but keeping the date ascending order in tact for them, and the other serial numbers.
我希望能得到比for循环更好的东西,但是我的伪代码想法是这样的:
I was hoping for something better than a for loop, but my pseudo-code idea was something like:
For Each cel in Range(Serial Number column)
If vLookup cel in Range(Serial Number column) = True Then
Get Row Number of the Match, Cut entire row, and Insert it below cels row (ie. cel.offset(1,0))
Else
Do Nothing
End If
Next cel
这将对新插入的序列号重复此过程,以寻找另一个匹配项,以防列表中还有另一个匹配项,它将被捕获.如果有人可以帮我编程这个东西,或者如果他们有一个更好的主意,我将不知所措.
This would repeat the process on the newly inserted Serial Number looking for another match, in case there's another one further down the list, it'll catch it. If someone could help me program this thing, or if they have a better idea I'm all ears.
谢谢!将于今晚晚些时候进行审核.
Thanks! Will review later tonight.
推荐答案
请使用下一个代码:
Sub SortTwoColumns()
Dim sh As Worksheet
Set sh = ActiveSheet 'use here your worksheet
sh.Columns("A:B").Sort key1:=sh.Range("A1"), _
order1:=xlAscending, Key2:=sh.Range("B1"), order2:=xlAscending, Header:=xlYes
End Sub
这篇关于按一列排序,然后在第二列中分组相同的值?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!