用于清理数据的宏 [英] Macro to Clean Up Data
问题描述
我需要一些帮助来创建一个宏来清理数据。 我想表明,对于每个实体,标准1,2或两者都说"是" 表示为是。如果两者都没有,我想要一个"不"。在输出中。 我需要宏来循环
直到所有实体和货币都用尽(因为文件会定期更改)。
请注意下表中的x是空白的,我只是把x放在这个例子中清楚地粘贴。
实体名称 | Criteria | Currency1 | Currency2 | Currency3 |
实体1 | Criterion 1 | 是 | x | x |
实体1 | Criterion 2 | x | x | 是 |
实体2 | Criterion 1 | 是 | 是 | x |
实体2 | Criterion 2 | 是 | x | 是 |
实体3 | Criterion 1 | x | x | 是 |
实体3 | Criterion 2 | x | 是 | 是 |
如果标准1,2或两者都是:是
如果两者都是空白:否
输出如下所示:
实体名称 | | Currency1 | Currency2 | Currency3 |
实体1 | | 是 | 否 | 是 |
实体2 | | 是 | 是 | 是 |
实体3 | | 否 | 是 | 是 |
非常感谢提前!
Melissa
将您的矩阵粘贴到工作表的范围("a1")中并使用此代码:
Sub cleandataa()
number_of_lines =范围("A1")。结束(xlDown)。行为
对于j = 3到5
x = 1
对于i = 1到number_of_lines步骤2
criterion1 = Cells(i,j)
criterion2 = Cells(i + 1) ,j)
entity = Cells(i,1)
Cells(x,7)=实体
如果标准1 ="是"或标准2 ="是"然后是&b $ b 单元格(x,j + 5)="是"
  ;
$
否则
Cells(x,j + 5)=" no"
End If
x = x + 1
下一页i
下一页j
结束Sub
Hi, I need some help to create a macro to clean up data. I want to show that, for each Entity, Criterion 1, 2 or both that say "Yes" are denoted as Yes. If neither, I want a "No" in the output. I need the macro to loop until all entities and currencies are exhausted (as the file will change periodically).
Please note that the x's in the below table are blank, i just put x's to paste clearly for this example.
Entity Name | Criteria | Currency1 | Currency2 | Currency3 |
Entity 1 | Criterion 1 | Yes | x | x |
Entity 1 | Criterion 2 | x | x | Yes |
Entity 2 | Criterion 1 | Yes | Yes | x |
Entity 2 | Criterion 2 | Yes | x | Yes |
Entity 3 | Criterion 1 | x | x | Yes |
Entity 3 | Criterion 2 | x | Yes | Yes |
if Criterion 1, 2 or both are Yes: YES
If both are blank: NO
The Output would look like this:
Entity Name | Currency1 | Currency2 | Currency3 | |
Entity 1 |
|
Yes | No | Yes |
Entity 2 | Yes | Yes | Yes | |
Entity 3 | No | Yes | Yes |
Many thanks in advance!
Melissa
paste your matrix in range("a1") of a sheet and use this code:
Sub cleandataa()
number_of_lines = Range("A1").End(xlDown).Row
For j = 3 To 5
x = 1
For i = 1 To number_of_lines Step 2
criterion1 = Cells(i, j)
criterion2 = Cells(i + 1, j)
entity = Cells(i, 1)
Cells(x, 7) = entity
If criterion1 = "Yes" Or criterion2 = "Yes" Then
Cells(x, j + 5) = "Yes"
Else
Cells(x, j + 5) = "no"
End If
x = x + 1
Next i
Next j
End Sub
这篇关于用于清理数据的宏的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!