我有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 SubHow 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 MendezThe 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.
更多推荐
发布评论