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.

 

[Kaper]

One of solutions could be using Power Query - go into Data->Get and transform data->get data _>from other sources->blank query
PQ editor will open Go Home->advanced editor and replace empty query there with:

let
    t1_t = Excel.CurrentWorkbook(){[Name="tabell1"]}[Content],
    t2_t = Excel.CurrentWorkbook(){[Name="tabell2"]}[Content],
    t3_t = Excel.CurrentWorkbook(){[Name="tabell3"]}[Content],
    t4_t = Excel.CurrentWorkbook(){[Name="tabell4"]}[Content],
    t5_t = Excel.CurrentWorkbook(){[Name="tabell5"]}[Content],
    t6_t = Excel.CurrentWorkbook(){[Name="tabell6"]}[Content],
    t7_t = Excel.CurrentWorkbook(){[Name="tabell7"]}[Content],
    t8_t = Excel.CurrentWorkbook(){[Name="tabell8"]}[Content],
    t9_t = Excel.CurrentWorkbook(){[Name="tabell9"]}[Content],
    Appended = Table.Combine({t1_t, t2_t, t3_t, t4_t, t5_t, t6_t, t7_t, t8_t, t9_t}),
    Changed = Table.TransformColumnTypes(Appended,{{"Motherboards", type text}}),
    Grouped = Table.Group(Changed, {"Motherboards"}, {{"Count", each Table.RowCount(_), Int64.Type}}),
    Filtered = Table.SelectRows(Grouped, each ([Count] = 9)),
    Removed = Table.RemoveColumns(Filtered,{"Count"})
in
    Removed

If in real life there is more tables and worksheets* , then the solution could be to prepare the PQ in separate workbook and "import" all tables from the original file.

*) (if possible please don't call them tabs)

 

[rexcel3]

One of solutions could be using Power Query - go into Data->Get and transform data->get data _>from other sources->blank query
PQ editor will open Go Home->advanced editor and replace empty query there with:

let
    t1_t = Excel.CurrentWorkbook(){[Name="tabell1"]}[Content],
    t2_t = Excel.CurrentWorkbook(){[Name="tabell2"]}[Content],
    t3_t = Excel.CurrentWorkbook(){[Name="tabell3"]}[Content],
    t4_t = Excel.CurrentWorkbook(){[Name="tabell4"]}[Content],
    t5_t = Excel.CurrentWorkbook(){[Name="tabell5"]}[Content],
    t6_t = Excel.CurrentWorkbook(){[Name="tabell6"]}[Content],
    t7_t = Excel.CurrentWorkbook(){[Name="tabell7"]}[Content],
    t8_t = Excel.CurrentWorkbook(){[Name="tabell8"]}[Content],
    t9_t = Excel.CurrentWorkbook(){[Name="tabell9"]}[Content],
    Appended = Table.Combine({t1_t, t2_t, t3_t, t4_t, t5_t, t6_t, t7_t, t8_t, t9_t}),
    Changed = Table.TransformColumnTypes(Appended,{{"Motherboards", type text}}),
    Grouped = Table.Group(Changed, {"Motherboards"}, {{"Count", each Table.RowCount(_), Int64.Type}}),
    Filtered = Table.SelectRows(Grouped, each ([Count] = 9)),
    Removed = Table.RemoveColumns(Filtered,{"Count"})
in
    Removed

If in real life there is more tables and worksheets* , then the solution could be to prepare the PQ in separate workbook and "import" all tables from the original file.

*) (if possible please don't call them tabs)

this is overcomplicated for me.

i managed to copy all values to a list -> remove duplicates -> use count if formula.

 

[Eric W]

Sounds like you found a method to accomplish your goal. Here's a formula that should work too:

Book1
	A	B	C	D	E	F	G	H
1
2		Motherboards		Motherboards		Motherboards		motherboards
3		a		a		z		z
4		b		b		y		a
5		c		c		x		y
6		d		f		c		b
7		e		g		b		x
8		f		h		a		c
9
10		Common
11		a
12		b
13		c
14
Sheet1

Cell Formulas
Range	Formula
B11:B13	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))
Dynamic array formulas.

The 4 tables are all named tabell1, tabell2, tabell3, and tabell4. For your workbook, just change the 4 to a 9 in the formula (2 places).

 

[Kaper]

What method did you use to:

i managed to copy all values to a list

If the task was "do it just once" then even manual copy is perfect and quite quick solution.
It could be also done with new excel functions, but I'm not sure if they are available in 2021 or only in 365 version, that's why I showed PQ approach (simple not optimized, basically - clicked-in PQ interface .

 

[rexcel3]

Sounds like you found a method to accomplish your goal. Here's a formula that should work too:

Book1
	A	B	C	D	E	F	G	H
1
2		Motherboards		Motherboards		Motherboards		motherboards
3		a		a		z		z
4		b		b		y		a
5		c		c		x		y
6		d		f		c		b
7		e		g		b		x
8		f		h		a		c
9
10		Common
11		a
12		b
13		c
14
Sheet1

Cell Formulas
Range	Formula
B11:B13	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))
Dynamic array formulas.

The 4 tables are all named tabell1, tabell2, tabell3, and tabell4. For your workbook, just change the 4 to a 9 in the formula (2 places).

is doesnt work for me. im getting #referens!
my excel 2021 is in 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))

 

[rexcel3]

im getting #KALK! because Tabell4 was named Tabell5.

whats #KALK! ?

 

[HighAndWilder]

im getting #KALK! because Tabell4 was named Tabell5.

whats #KALK! ?

I assume that it is #CALC!

#CALC! errors occur when Excel's calculation engine encounters a scenario it does not currently support.

How to correct a #CALC! error - Microsoft Support

 

[Eric W]

I think you have an extra delimiter. My formula had:

SEQUENCE(,4)

and yours has

SEKVENS(;;4)

Using the formula translator at Translator I get this:

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

 

[rexcel3]

I think you have an extra delimiter. My formula had:

SEQUENCE(,4)

and yours has

SEKVENS(;;4)

Using the formula translator at Translator I get this:

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

im still getting calc error. im so sad.