如果2个单元格相同，则将单元格字符串添加到另一个单元格 [英] Add cell string to another cell if 2 cells are the same for 2 rows
问题描述
我正在尝试制作一个通过电子表格的宏，并且根据第一个和最后一个名称相同的2行，将种族列的内容添加到第一行。
例如。
FirstN | LastN |种族| ID |
Sally |史密斯|白种人| 55555 |
Sally |史密斯|美洲原住民| |
Sally |史密斯|黑人/非裔美国人| |
（宏运行后）
Sally |史密斯|白种人/美洲原住民/黑人/非裔美国人| 55555 |
有关如何做的任何建议？我为VBA读了几种不同的方法，但是对于创建这个宏的方式会有什么困惑。
EDIT
可能有两行以上需要组合，下行需要删除或删除一些如何。 / p>
如果可以使用公式，那么可以执行以下操作：
我正在做的几个假设：
- Sally在单元格
A2（在第1行中有标题） - 没有人拥有超过2个种族。
现在，对于以下步骤：
- 放一个过滤器并按姓名排序。这规定任何人姓名分开。 （即如果顶部有萨利史密斯，在不同的人之后，表格中没有更多的萨利·史密斯）。
- 在列D中，将公式
= if（and（A2 = A3，B2 = B3），C2&/& C3，）
<将过滤器扩展到D列，并过滤出所有的空白。
这是看它的名称单元格A2和A3是否等于（名称相同），以及单元格B2和B3是否相等（姓氏是相同的）。
如果两者都是真的，那就是同一个人我们连接（使用& 是另外一种连接方式，除了使用 concatenate（））两个种族。 >
否则，如果名称或用户名或两者都不同，请留空。
删除冗余一行，在列D上复制/粘贴值，对列D中的空白单元进行过滤并进行删除。
编辑：根据编辑问题：
新的步骤：
- 放一个过滤器，按名称和姓氏排序。 （上面已经解释）
- 在列E中，将公式
= IF（AND（A1 = A2，B1 = B2），E1&/& C2，C2）（我改变了公式以适应新的方法） - 在F列中，将公式
= if和（A1 = A2，B1 = B2），F1 + 1,1） - 在列G中，将公式
= if （F3< F2,1,0） - 在列H中，将公式
= 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:
- Sally is in cell
A2(there are headers in row 1). - No person has more than 2 ethnicities.
Now, for the steps:
- Put a filter and sort by name and surname. This provides for any person having their names separated. (i.e. if there is a 'Sally Smith' at the top, there are no more 'Sally Smith' somewhere down in the sheet after different people).
- In column D, put the formula
=if(and(A2=A3,B2=B3),C2&"/"&C3,"") - Extend the filter to column D and filter out all the blanks.
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:
- Put a filter and sort by name and surname. (already explained above)
- In column E, put the formula
=IF(AND(A1=A2,B1=B2),E1&"/"&C2,C2)(I changed the formula to adapt to the new method) - In column F, put the formula
=if(and(A1=A2,B1=B2),F1+1,1) - In column G, put the formula
=if(F3<F2,1,0) - In column H, put the formula
=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!
这篇关于如果2个单元格相同，则将单元格字符串添加到另一个单元格的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
