9 tables, list values that r in every list?

[rexcel3]

is it possible to list values that appear in every table?

tab 1 name: "List 1", contains "tabell1"
tab 2 name: "List 2", contains "tabell2"
.......
tab 9 name: "List 9", contains "tabell9"

table headers r called Motherboards

i tried consulting with chatgpt without success.

 

[Eric W]

I'm not sure what to suggest at this point. Did you try the full formula from the translator, just in case there's another misplaced delimiter? Can you use xl2bb to display your sheet, working from a picture doesn't give enough details. Although I'm not sure how it would handle Swedish formulas.

 

[rexcel3]

I'm not sure what to suggest at this point. Did you try the full formula from the translator, just in case there's another misplaced delimiter? Can you use xl2bb to display your sheet, working from a picture doesn't give enough details. Although I'm not sure how it would handle Swedish formulas.

Bok1.xlsx
	B	C	D	E	F	G	H
2	Motherboards		Motherboards		Motherboards		Motherboards
3	a		h		s		r
4	b		k		a		t
5	c		r		o		y
6	d		u		i		u
7	e		o		q		d
8	g		e		o		x
9
10	Common
11	#KALK!
Blad2

Cell Formulas
Range	Formula
B11	B11	=LET(a,Tabell1[Motherboards],sh,SEQUENCE(,4),sv,TRANSPOSE(sh)^0,b,MMULT(SIGN(COUNTIF(INDIRECT("Tabell"&sh&"[Motherboards]"),a)),sv),FILTER(a,b=4))

my formula is in swedish even though this add-in autotranslates it here.

 

[Eric W]

Ah, an oversight on my part. You're getting the #CALC error because there are no common values in those tables. It's easy enough to add a default message to the FILTER function to identify that:

English:

=LET(a,tabell1[Motherboards],sh,SEQUENCE(,4),sv,TRANSPOSE(sh)^0,b,MMULT(SIGN(COUNTIF(INDIRECT("Tabell"&sh&"[Motherboards]"),a)),sv),FILTER(a,b=4,"No Common values"))

Swedish:

=LET(a;tabell1[Motherboards];sh;SEKVENS(;4);sv;TRANSPONERA(sh)^0;b;MMULT(TECKEN(ANTAL.OM(INDIREKT("Tabell"&sh&"[Motherboards]");a));sv);FILTER(a;b=4;"No Common values"))