根据另一列中的值，在多列中填充和关闭单元格 [英] Fill up and down cells in multiple columns based on values in another column
问题描述
我有以下列和值：
开始时间其他值名字姓氏其他信息
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
1006 05 value30 info30
100954 value31 info31
我希望这样：
开始时间其他值名字姓氏其他信息pre>
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 val ue12 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 inf o27
98725 value28 firstname6 lastname6 info28
100605 value29 firstname6 lastname6 info29
100605 value30 firstname6 lastname6 info30
100954 value31 firstname6 lastname6 info31
我使用以下代码感谢@Jeeped's answer
Sub FillColBlanksSpecial2（）
Dim wks As Worksheet
Dim rng As Range
Dim rng2 As范围
Dim blnk As Range
Dim LastRow As Long
Dim col As Long
Dim lRows As Long
Dim lLimit As Long
Dim lCount As Long
错误恢复下一步
lRows = 2
lLimit = 1000
设置wks = ActiveSheet
对于工作表中的每个wks
如果Ri ght（wks.Name，2）=-A或右（wks.Name，2）=-B然后
用wks
带.Cells（1，1）.CurrentRegion
与.Columns（C：D）
如果CBool（Application.CountBlank（.Cells））然后
对于每个blnk在.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， - ] c：r9999c，match（rc1，r [1] c1：r9999c1,0）），r [-1] c））
blnk.Value = blnk.Value
下一个blnk
结束如果
结束
结束
结束
结束如果
下一个wks
结束Sub
使用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 ] 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 ，$ A $ 1：$ A1，0）），IF（COUNTIFS（$ A3：$ A $ 9999，$ A2，C3：C $ 9999，），INDEX（C3：C $ 9999，MATCH ，$ A3：$ A $ 9999，0）），C1））
目标是根据条件 C：D 填充现有值：
1。以 C：D 中的值填充，直到相同列中的下一个非空单元格（这些非空单元格包含唯一值firstname1，firstname2等）
< BR> 2。取 C：D 中的值，并填写向上，如果上面的空行在列A 中具有相同的值，其行在下面，我们正在处理的价值。
@ Jeeped的答案正常工作：
它需要列 C：D ，并填写空行，直到包含新值的下一行;如果该空行在列 A 中共享相同的值，那么它还会填满一个空行，下面的行包含要复制的值。
，但只有填写一行。我的示例数据（以下链接）显示可能有 多个空行 在包含需要填写的列 C：D 中的值的行之上。
如何修改此vba代码以适应此条件？
这是具有较长示例数据的excel工作表。该工作簿包含两个表格：我有什么&
PS 除了第一列中的代码之外的星号除外视觉可访问性的目的是识别在列 A 中共享相同值的空行与下面的行具有列 C：D 中的值;否则它们不会出现在原始数据表中。
通过空行，我的意思是那些没有列 C：D 中的值。解决方案我已经对公式进行了更多的复杂化，以实现垂直查找到多个列。需要修改的单行是所有嵌套代码的中心。
blnk.FormulaR1C1 == if（countifs r1c1：r [-1] c1，rc1，r1c：r [-1] c， - ），索引（r1c：r [-1] c，match（rc1，r1c1： 1] c1，0）），if（countifs（r [1] c1：r9999c1，rc1，r [1] c：r9999c， - ），index（r [1] c：r9999c，分钟（指数（行（R：r9998あ）行（R [-1]）+（（R [1] C1：r9999c1<> RC1）+未（LEN（R [1] C：r9999c）））* 1e + 99，，））），r [-1] c））
一个xlA1风格的公式，如下所示（从C2看到）。
= IF（COUNTIFS（$ A $ 1：$ A1， （A2 $，A $ 1：$ A1,0）），IF（COUNTIFS（$ A3：$ A $ 9999，$ A $，C $ 1：C1，& $ 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））
请注意，编码公式需要将带引号的字符串中的引号加倍。
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
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:
=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.@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.
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.How to modify this vba code to accommodate this condition?
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.
By empty row, I mean those rows which don't have values in columns C:D.解决方案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).
=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.
这篇关于根据另一列中的值，在多列中填充和关闭单元格的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
