更新时间:2022-10-18 11:25:17
如果可以使用公式,那么可以执行以下操作:
我正在做的几个假设:
A2
(在第1行中有标题)现在,对于以下步骤:
= if(and(A2 = A3,B2 = B3),C2&/& C3,)
这是看它的名称单元格A2和A3是否等于(名称相同),以及单元格B2和B3是否相等(姓氏是相同的)。
如果两者都是真的,那就是同一个人我们连接(使用&
是另外一种连接方式,除了使用 concatenate()
)两个种族。 >
否则,如果名称或用户名或两者都不同,请留空。
删除冗余一行,在列D上复制/粘贴值,对列D中的空白单元进行过滤并进行删除。
编辑:根据编辑问题:
新的步骤:
= IF(AND(A1 = A2,B1 = B2),E1&/& C2,C2)
(我改变了公式以适应新的方法) = if和(A1 = A2,B1 = B2),F1 + 1,1)
= if (F3< F2,1,0)
= if(and(D2 = A1 = A2,B1 = B2),H1,D2)
(这需要身份证号码)。将公式放在第2行。第3步是为同名的人添加增量号码。
什么第4步是检查列F何时返回到1.这将标识您的要保留的最后一行。
这是这些公式的输出:
绿色行是你保留的(注意在列 G
,您可以快速找到它们),列A,B,C,E和H是最后一张表中保留的列。完成公式并删除行之前,不要忘记复制/粘贴值!
I'm trying to make a macro that will go through a spreadsheet, and based on the first and last name being the same for 2 rows, add the contents of an ethnicity column to the first row.
eg.
FirstN|LastN |Ethnicity |ID |
Sally |Smith |Caucasian |55555 |
Sally |Smith |Native American | |
Sally |Smith |Black/African American | |
(after the macro runs)
Sally |Smith |Caucasian/Native American/Black/African American|55555 |
Any suggestions on how to do this? I read several different methods for VBA but have gotten confused as to what way would work to create this macro.
EDIT There may be more than 2 rows that need to be combined, and the lower row(s) need to be deleted or removed some how.
If you can use a formula, then you can do those:
Couple of assumptions I'm making:
A2
(there are headers in row 1).Now, for the steps:
=if(and(A2=A3,B2=B3),C2&"/"&C3,"")
That is does is it sees whether the names cells A2 and A3 are equal (names are the same), and whether the cells B2 and B3 are equal (surnames are the same).
If both are true, it's the same person, so we concatenate (using &
is another way to concatenate besides using concatenate()
) the two ethnicities.
Otherwise, if either the name, or username, or both are different, leave as blank.
To delete the redundant rows altogether, copy/paste values on column D, filter on the blank cells in column D and delete. Sort afterwards.
EDIT: As per edit of question:
The new steps:
=IF(AND(A1=A2,B1=B2),E1&"/"&C2,C2)
(I changed the formula to adapt to the new method)=if(and(A1=A2,B1=B2),F1+1,1)
=if(F3<F2,1,0)
=if(and(D2="",A1=A2,B1=B2),H1,D2)
(this takes the ID wherever it goes).Put the formulae as from row 2. What step 3 does is putting an incremental number for the people with same name.
What step 4 does is checking for when the column F goes back to 1. This will identify your 'final rows to be kept'.
Here's my output from those formulae:
The green rows are what you keep (notice that there is 1
in column G
that allows you to quickly spot them), and the columns A, B, C, E and H are the columns you keep in the final sheet. Don't forget to copy/paste values once you are done with the formulae and before deleting rows!