结合Time Excel VBA(Combining Time Excel VBA)

结合Time Excel VBA(Combining Time Excel VBA)

我有Shift ID，开始时间和结束时间列，但是移位被分成多行并重复相同的ID。 那么，如何将第一次开始时间和最后结束时间组合到一行中，从而将这种零碎的变化结合起来？ 我开始了，但因为我刚刚开始涉足代码，所以我不知道会发生什么。 我有两块面包，但不知道肉应该是什么。

Sub Combine() LR1 = Columns(1).Find("*", SearchDirection:=xlPrevious).Row LC1 = Rows(1).Find("*", SearchDirection:=xlPrevious).Column For x = 1 To LR1 If Cells(x,2).value End Sub

如何指示单元格值的更改，然后根据第一个开始时间和上次结束时间结合这些行？ 我知道我几乎没有写任何东西，但我没想到这可能会很长。

ShiftID: StartTime: EndTime: Employee: 123456 6:45pm 7:00pm Garcia Mendez 123456 7:00pm 9:30pm Garcia Mendez 123456 9:30pm 2:45am Garcia Mendez 654321 10:00am 12:00pm Garcia Mendez 654321 12:00pm 3:00pm Garcia Mendez

班次ID的变化代表了新的转变。 我想根据123456的第一个开始时间和最后一个结束时间，将123456的全部组合到一行中。第二个问题是我如何确保我的代码区分了Shift ID，因此它不会将所有的班次一起

I have Shift ID, start time and end time columns but the shift is broken up into multiple rows and repeats the same ID. So how would I combine the fragmented shift by taking the first start time and the last end time into one row? I started but I don't know what comes after since I'm just beginning to dabble in code. I have the two pieces of bread but have no idea what the meat should be.

Sub Combine() LR1 = Columns(1).Find("*", SearchDirection:=xlPrevious).Row LC1 = Rows(1).Find("*", SearchDirection:=xlPrevious).Column For x = 1 To LR1 If Cells(x,2).value End Sub

How do I indicate a change in cell value then combine the rows based on the first start time and end last time? I know I've barely written anything but I didn't envision this could to be long.

ShiftID: StartTime: EndTime: Employee: 123456 6:45pm 7:00pm Garcia Mendez 123456 7:00pm 9:30pm Garcia Mendez 123456 9:30pm 2:45am Garcia Mendez 654321 10:00am 12:00pm Garcia Mendez 654321 12:00pm 3:00pm Garcia Mendez

The change in shift ID represents a new shift. I want to combine all of 123456 into one row based on the first start time and the last end time of shift 123456. My second question is how could I make sure my code differentiates between Shift IDs so it doesn't combine all of the shifts together

最满意答案

所以你可以用公式做到这一点：

我这样设置：

然后我添加下面的公式

在F2中我放了以下数组公式：

=IFERROR(INDEX($A$2:$A$100,MATCH(0,COUNTIF($F$1:$F1,$A$2:$A$100),0)),"")

它是一个数组公式，在公式栏中粘贴公式之后，而不是按Enter或Tab键离开，我按Ctrl-Shift-Enter。 这告诉Excel它是一个数组公式。

然后在G2，H2，I2我把下面的公式：

=IF(F2<>"",INDEX($B$2:$B$100,AGGREGATE(15,6,1/($A$2:$A$100=F2)*(ROW($1:$99)),1)),"") =IF(F2<>"",INDEX($C$2:$C$100,AGGREGATE(14,6,1/($A$2:$A$100=F2)*(ROW($1:$99)),1)),"") =IF(F2<>"",INDEX($D$2:$D$100,AGGREGATE(15,6,1/($A$2:$A$100=F2)*(ROW($1:$99)),1)),"")

分别。 这些都是常规公式，可以用正常方式输入。

然后复制下来。 在下面的照片中，我复制到第20行。 这样，随着班次的增加，公式会自动选择它们。

我知道你没有要求公式解决方案，但如果你想要它，它就在这里。

So you could do this with formulas:

I set it up like this:

Then I add the following formulas

In F2 I put the following Array formula:

=IFERROR(INDEX($A$2:$A$100,MATCH(0,COUNTIF($F$1:$F1,$A$2:$A$100),0)),"")

It being an array formula, After pasting the formula in the formula bar, instead of hitting enter or tab to leave I hit Ctrl-Shift-Enter. This tells Excel it is an array formula.

Then in G2,H2,I2 I put the following formulas:

=IF(F2<>"",INDEX($B$2:$B$100,AGGREGATE(15,6,1/($A$2:$A$100=F2)*(ROW($1:$99)),1)),"") =IF(F2<>"",INDEX($C$2:$C$100,AGGREGATE(14,6,1/($A$2:$A$100=F2)*(ROW($1:$99)),1)),"") =IF(F2<>"",INDEX($D$2:$D$100,AGGREGATE(15,6,1/($A$2:$A$100=F2)*(ROW($1:$99)),1)),"")

Respectively. These are regular formula and can be entered with just the normal way.

Then copy down. In the following picture I copied down to the 20th row. This way as shifts are added the formula automatically picks them up.

I know you did not ask for a formula resolution, but it is here if you want it.