0

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.

Sujith Kumar
  • 872
  • 6
  • 19
DatBigD
  • 129
  • 7

2 Answers2

1

As per below screenshot I have used the following formulas.

G1 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))))

J1 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))

enter image description here

Harun24hr
  • 30,391
  • 4
  • 21
  • 36
1

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:

enter image description here

Formula in F6:

=SORT(FILTER(A1:D11,NOT(VLOOKUP(A1:A11,F1#,2,0))))
JvdV
  • 70,606
  • 8
  • 39
  • 70
  • your code is not working for me. The problem is the function ````--MAP````; I do no know why is not working. – DatBigD Apr 11 '23 at 11:59
  • 1
    Instead of 'MAP()' try 'BYROW()'. It would have the same effect. Some locales have a problem with the MAP function (the Dutch version of Excel for example.). I for example switched to the English version and since have no problem with this function. @DatBigD – JvdV Apr 11 '23 at 13:34