I have the following columns and values:
我有以下列和值:
Begin Time Other values First Name Last Name other info
5041* value1 info1
5041* value2 firstname1 lastname1 info2
13089 value3 info3
16130 value4 info4
26391* value5 info5
26391* value6 info6
26391* value7 firstname2 lastname2 info7
27878 value8 info8
27878 value9 info9
28234 value10 info10
63189* value11 info11
63189* value12 info12
63189* value13 info13
63189* value14 firstname3 lastname3 info14
64335 value15 info15
65423 value16 info16
72089* value17 info17
72089* value18 firstname4 lastname4 info18
73495 value19 info19
73495 value20 info20
74330 value21 info21
74877 value22 info22
76710 value23 info23
82599* value24 info24
82599* value25 firstname5 lastname5 info25
98712* value26 info26
98712* value27 firstname6 lastname6 info27
98725 value28 info28
100605 value29 info29
100605 value30 info30
100954 value31 info31
I expect this:
我期待这个:
Begin Time Other values First Name Last Name other info
5041 value1 firstname1 lastname1 info1
5041 value2 firstname1 lastname1 info2
13089 value3 firstname1 lastname1 info3
16130 value4 firstname1 lastname1 info4
26391 value5 firstname2 lastname2 info5
26391 value6 firstname2 lastname2 info6
26391 value7 firstname2 lastname2 info7
27878 value8 firstname2 lastname2 info8
27878 value9 firstname2 lastname2 info9
28234 value10 firstname2 lastname2 info10
63189 value11 firstname3 lastname3 info11
63189 value12 firstname3 lastname3 info12
63189 value13 firstname3 lastname3 info13
63189 value14 firstname3 lastname3 info14
64335 value15 firstname3 lastname3 info15
65423 value16 firstname3 lastname3 info16
72089 value17 firstname4 lastname4 info17
72089 value18 firstname4 lastname4 info18
73495 value19 firstname4 lastname4 info19
73495 value20 firstname4 lastname4 info20
74330 value21 firstname4 lastname4 info21
74877 value22 firstname4 lastname4 info22
76710 value23 firstname4 lastname4 info23
82599 value24 firstname5 lastname5 info24
82599 value25 firstname5 lastname5 info25
98712 value26 firstname6 lastname6 info26
98712 value27 firstname6 lastname6 info27
98725 value28 firstname6 lastname6 info28
100605 value29 firstname6 lastname6 info29
100605 value30 firstname6 lastname6 info30
100954 value31 firstname6 lastname6 info31
I am using the following code thanks to @Jeeped's answer
我正在使用以下代码,感谢@ Jeeped的回答
Sub FillColBlanksSpecial2()
Dim wks As Worksheet
Dim rng As Range
Dim rng2 As Range
Dim blnk As Range
Dim LastRow As Long
Dim col As Long
Dim lRows As Long
Dim lLimit As Long
Dim lCount As Long
On Error Resume Next
lRows = 2
lLimit = 1000
Set wks = ActiveSheet
For Each wks In Worksheets
If Right(wks.Name, 2) = "-A" Or Right(wks.Name, 2) = "-B" Then
With wks
With .Cells(1, 1).CurrentRegion
With .Columns("C:D")
If CBool(Application.CountBlank(.Cells)) Then
For Each blnk In .SpecialCells(xlCellTypeBlanks)
blnk.FormulaR1C1 = "=if(countifs(r1c1:r[-1]c1, rc1, r1c:r[-1]c, ""<>""), index(r1c:r[-1]c, match(rc1, r1c1:r[-1]c1, 0)), if(countifs(r[1]c1:r9999c1, rc1, r[1]c:r9999c, ""<>""), index(r[1]c:r9999c, match(rc1, r[1]c1:r9999c1, 0)), r[-1]c))"
blnk.Value = blnk.Value
Next blnk
End If
End With
End With
End With
End If
Next wks
End Sub
with the formula in xlR1C1 style and its xlA1 equivalent:
使用xlR1C1样式的公式及其xlA1等效:
=IF(COUNTIFS(R1C1:R[-1]C1, RC1, R1C:R[-1]C, "<>"), INDEX(R1C:R[-1]C, MATCH(RC1, R1C1:R[-1]C1, 0)), IF(COUNTIFS(R[1]C1:R9999C1, RC1, R[1]C:R9999C, "<>"), INDEX(R[1]C:R9999C, MATCH(RC1, R[1]C1:R9999C1, 0)), R[-1]C))
=IF(COUNTIFS($A$1:$A1, $A2, C$1:C1, "<>"), INDEX(C$1:C1, MATCH($A2, $A$1:$A1, 0)), IF(COUNTIFS($A3:$A$9999, $A2, C3:C$9999, "<>"), INDEX(C3:C$9999, MATCH($A2, $A3:$A$9999, 0)), C1))
The goal is to fill up and down the columns C:D with the existing values based on the conditions:
1. Take the values in C:D and fill down until the next non-empty cell in the same columns.(these non-empty cells contain unique values, firstname1, firstname2, etc)
2. Take the values in C:D and fill up if the empty row(s) above share the same value in Column A with the row which is below and whose values we are coping up.
目标是使用基于条件的现有值填充C:D列:1。获取C:D中的值并填充,直到相同列中的下一个非空单元格。(这些非空单元格包含唯一值,firstname1,firstname2等)2。取C:D中的值并填充如果上面的空行在列A中与下面的行共享相同的值,并且我们的值是应对。
@Jeeped's answer is working correctly:
it takes the values from columns C:D and fills down the empty rows until the next row that contains new values; it also fills up one empty row if the that empty row shares the same value in column A with the row below that contains the to-be-copied values.
@ Jeeped的答案是正确的:它从列C:D中获取值并填充空行直到包含新值的下一行;如果该空行在A列*享相同的值,并且下面的行包含要复制的值,它也会填充一个空行。
but it only fills up one row. My example data (link below) shows that there might be more than one empty row above the rows that contain values in column C:D that need to be filled up.
但它只填满了一排。我的示例数据(下面的链接)显示行上方可能有多个空行,其中包含C:D列中需要填充的值。
How to modify this vba code to accommodate this condition?
如何修改此vba代码以适应这种情况?
这是带有更长示例数据的excel工作表。该工作簿包含两个表:我拥有什么和我期望什么。
P.S. the asterisk mark besides the codes in first column are solely for the purpose of the visual accessibility to identify the empty rows that share the same value in column A with the row below that has values in columns C:D; otherwise they don't appear in the original data sheet.
附:除了第一列中的代码之外的星号标记仅用于视觉可访问性的目的,以识别在列A中具有相同值的空行,下面的行具有在列C:D中的值;否则它们不会出现在原始数据表中。
By empty row, I mean those rows which don't have values in columns C:D.
在空行中,我的意思是那些在C列中没有值的行:D。
1 个解决方案
#1
I've complicated the formula a bit more to achieve the vertical lookup to more than a single column. The single line that needs modification is in the center of all the nested code.
我已经使公式更复杂,以实现对多个列的垂直查找。需要修改的单行位于所有嵌套代码的中心。
blnk.FormulaR1C1 = "=if(countifs(r1c1:r[-1]c1, rc1, r1c:r[-1]c, ""<>""), index(r1c:r[-1]c, match(rc1, r1c1:r[-1]c1, 0)), if(countifs(r[1]c1:r9999c1, rc1, r[1]c:r9999c, ""<>""), index(r[1]c:r9999c, min(index(row(r:r9998)-row(r[-1])+((r[1]c1:r9999c1<>rc1)+not(len(r[1]c:r9999c)))*1e+99, , ))), r[-1]c))"
That translates to an xlA1 style formula like the following (as seen from C2).
这转换为xlA1样式公式,如下所示(从C2中可以看出)。
=IF(COUNTIFS($A$1:$A1, $A2, C$1:C1, "<>"), INDEX(C$1:C1, MATCH($A2, $A$1:$A1, 0)), IF(COUNTIFS($A3:$A$9999, $A2, C3:C$9999, "<>"), INDEX(C3:C$9999, MIN(INDEX(ROW(2:$9998)-ROW(1:1)+(($A3:$A$9999<>$A2)+NOT(LEN(C3:C$9999)))*1E+99, , ))), C1))
Note that the coded formula requires doubling up the quotes within the quoted string.
请注意,编码公式需要将引用字符串中的引号加倍。
#1
I've complicated the formula a bit more to achieve the vertical lookup to more than a single column. The single line that needs modification is in the center of all the nested code.
我已经使公式更复杂,以实现对多个列的垂直查找。需要修改的单行位于所有嵌套代码的中心。
blnk.FormulaR1C1 = "=if(countifs(r1c1:r[-1]c1, rc1, r1c:r[-1]c, ""<>""), index(r1c:r[-1]c, match(rc1, r1c1:r[-1]c1, 0)), if(countifs(r[1]c1:r9999c1, rc1, r[1]c:r9999c, ""<>""), index(r[1]c:r9999c, min(index(row(r:r9998)-row(r[-1])+((r[1]c1:r9999c1<>rc1)+not(len(r[1]c:r9999c)))*1e+99, , ))), r[-1]c))"
That translates to an xlA1 style formula like the following (as seen from C2).
这转换为xlA1样式公式,如下所示(从C2中可以看出)。
=IF(COUNTIFS($A$1:$A1, $A2, C$1:C1, "<>"), INDEX(C$1:C1, MATCH($A2, $A$1:$A1, 0)), IF(COUNTIFS($A3:$A$9999, $A2, C3:C$9999, "<>"), INDEX(C3:C$9999, MIN(INDEX(ROW(2:$9998)-ROW(1:1)+(($A3:$A$9999<>$A2)+NOT(LEN(C3:C$9999)))*1E+99, , ))), C1))
Note that the coded formula requires doubling up the quotes within the quoted string.
请注意,编码公式需要将引用字符串中的引号加倍。