英文:
Generate a table with condition
问题
我正在使用Excel制作表格。这是Sheet1中我的表格的示例：
| A | B | C | D |
|---|---|---|---|
| al | id | id | id |
| df | id | desc | desc |
| df | id | id | desc |
| df | id | id | id |
| ff | desc | id | desc |
| ff | desc | id | desc |
| al | id | id | id |
| al | id | id | desc |
| mn | desc | desc | desc |
| mn | desc | desc | desc |
| ff | desc | id | desc |
在这个表格中，目标是比较列A中的重复值，并得到包含列A、B、C和D的表格。然后，我需要同时比较列B、C和D，并创建一个新列，如果它们全部匹配，则将1放入该列，否则放入0。对于前面的表格，这是我得到的内容：
| A | B |
|---|---|
| al | 0 |
| df | 0 |
| ff | 1 |
| mn | 1 |
这保存在名为Sheet2的另一个工作表中。
现在，我想将Sheet1中在Sheet2中为0的所有信息保存在另一个工作表Sheet3中。例如，在Sheet3中应该有：
| A | B | C | D |
|---|---|---|---|
| al | id | id | id |
| al | id | id | id |
| al | id | id | desc |
| df | id | desc | desc |
| df | id | id | desc |
| df | id | id | id |
看起来，我想要在Sheet3中按行将Sheet1中列A的所有值都放在一起。
如何做到这一点？
最好的问候。
英文:
I'm working on a table with Excel. Here is an example of my table in Sheet1:
| A | B | C | D |
|---|---|---|---|
| al | id | id | id |
| df | id | desc | desc |
| df | id | id | desc |
| df | id | id | id |
| ff | desc | id | desc |
| ff | desc | id | desc |
| al | id | id | id |
| al | id | id | desc |
| mn | desc | desc | desc |
| mn | desc | desc | desc |
| ff | desc | id | desc |
In this table, the goal was to compare column A with duplicate values and you will get a table of columns A B C, and D. With that table, I have to compare de columns B C and D at once. Later, I have to create a new column where I have to put 1 if they all match, and 0 otherwise. For the previous table, this is what I get:
| A | B |
|---|---|
| al | 0 |
| df | 0 |
| ff | 1 |
| mn | 1 |
This is saved in another sheet called Sheet2.
Now, I want to save in another Sheet, called Sheet3, all the information of Sheet1 where in Sheet2 there is a 0. For example, in Sheet3 there should be:
| A | B | C | D |
|---|---|---|---|
| al | id | id | id |
| al | id | id | id |
| al | id | id | desc |
| df | id | desc | desc |
| df | id | id | desc |
| df | id | id | id |
How it looks, I want all of the values for column A in Sheet1, together by rows in Sheet3.
How can I do that?
Best regard.
答案1
得分: 1
Here is the translation of the provided content:
根据下面的截图，我使用了以下公式。
<kbd>G1</kbd> 单元格公式-
=HSTACK(UNIQUE(A1:A11),
BYROW(UNIQUE(A1:A11),
LAMBDA(x,IF(ROWS(UNIQUE(FILTER($B$1:$D$11,$A$1:$A$11=x)))>1,0,1)))
<kbd>J1</KBD> 单元格公式。
=LET(x,FILTER(UNIQUE(A1:A11),BYROW(UNIQUE(A1:A11),LAMBDA(x,IF(ROWS(UNIQUE(FILTER($B$1:$D$11,$A$1:$A$11=x)))>1,0,1)))=0),
y,REDUCE("",x,LAMBDA(a,b,VSTACK(a,FILTER(A1:D11,A1:A11=b)))),DROP(y,1))
英文:
As per below screenshot I have used the following formulas.
<kbd>G1</kbd> cell formula-
=HSTACK(UNIQUE(A1:A11),
BYROW(UNIQUE(A1:A11),
LAMBDA(x,IF(ROWS(UNIQUE(FILTER($B$1:$D$11,$A$1:$A$11=x)))>1,0,1))))
<kbd>J1</KBD> cell formula.
=LET(x,FILTER(UNIQUE(A1:A11),BYROW(UNIQUE(A1:A11),LAMBDA(x,IF(ROWS(UNIQUE(FILTER($B$1:$D$11,$A$1:$A$11=x)))>1,0,1)))=0),
y,REDUCE("",x,LAMBDA(a,b,VSTACK(a,FILTER(A1:D11,A1:A11=b)))),DROP(y,1))
答案2
得分: 1
I used the following formula to generate your initial table:
=LET(x,UNIQUE(A1:A11),HSTACK(x,--MAP(x,LAMBDA(y,ROWS(UNIQUE(FILTER(B1:D11,A1:A11=y)))=1)))
To generate your desired output in a 2nd table, simply use:
Formula in F6:
=SORT(FILTER(A1:D11,NOT(VLOOKUP(A1:A11,F1#,2,0)))
英文:
Again, as per previous answers to your questions here and here, I used the following formula to generate your initial table:
=LET(x,UNIQUE(A1:A11),HSTACK(x,--MAP(x,LAMBDA(y,ROWS(UNIQUE(FILTER(B1:D11,A1:A11=y)))=1))))
To generate your desired output in a 2nd table just simple use:
Formula in F6:
=SORT(FILTER(A1:D11,NOT(VLOOKUP(A1:A11,F1#,2,0))))
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论