Excel find which group of nunbers repeating in multiple columnes

[exoterikos]

I have 4 columnes with simple values. 31,42,44 appears in every columne. But this is a small sample of data . I need to work with much bigger data.
Is there any formula to find which numbers appears simultaneously on all columnes?

 

[Fluff]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

You could use conditional formatting to highlight col A

Fluff.xlsm
	A	B	C	D
1	35	50	9	43
2	46	2	46	2
3	47	11	14	35
4	34	43	40	36
5	17	23	18	11
6	45	45	25	23
7	37	2	43	40
8	9	40	24	41
9	40	12	8	7
10	10	16	14	2
11	3	11	34	6
12	48	8	50	34
13	43	20	7	3
14	47	21	19	41
15	3	9	5	38
16	34	19	24	46
17	1	24	6	50
18	30	41	48	22
19	29	40	15	49
20	38	40	35	8
Main

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A1:A20	Expression	=AND(COUNTIFS($B$1:$B$20,A1),COUNTIFS($C$1:$C$20,A1),COUNTIFS($D$1:$D$20,A1))	text	NO

 

[Fluff]

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: Excel find which group of nunbers repeating in multiple columnes
If you have posted the question at more places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.

 

[Fluff]

If you want to return the actual numbers, try

Fluff.xlsm
	A	B	C	D	E	F
1	35	50	9	43
2	46	2	46	2		40
3	47	11	14	35		43
4	34	43	40	36
5	17	23	18	11
6	45	45	25	23
7	37	2	43	40
8	9	40	24	41
9	40	12	8	7
10	10	16	14	2
11	3	11	34	6
12	48	8	50	34
13	43	20	7	3
14	47	21	19	41
15	3	9	5	38
16	34	19	24	46
17	1	24	6	50
18	30	41	48	22
19	29	40	15	49
20	38	40	35	8
Main

Cell Formulas
Range	Formula
F2:F6	F2	=IFERROR(INDEX($A$1:$A$20,AGGREGATE(15,6,ROW($A$1:$A$20)/(COUNTIFS($B$1:$B$20,$A$1:$A$20)>0)/(COUNTIFS($C$1:$C$20,$A$1:$A$20)>0)/(COUNTIFS($D$1:$D$20,$A$1:$A$20)>0),ROWS(F$2:F2))),"")

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A1:A20	Expression	=AND(COUNTIFS($B$1:$B$20,A1),COUNTIFS($C$1:$C$20,A1),COUNTIFS($D$1:$D$20,A1))	text	NO

 

[Fluff]

Also posted here Excel find which group of nunbers repeating in multiple columnes
here Excel find which group of nunbers repeating in multiple columnes
and Excel find which group of nunbers repeating in multiple columnes

 

[exoterikos]

If you want to return the actual numbers, try

Fluff.xlsm
	A	B	C	D	E	F
1	35	50	9	43
2	46	2	46	2		40
3	47	11	14	35		43
4	34	43	40	36
5	17	23	18	11
6	45	45	25	23
7	37	2	43	40
8	9	40	24	41
9	40	12	8	7
10	10	16	14	2
11	3	11	34	6
12	48	8	50	34
13	43	20	7	3
14	47	21	19	41
15	3	9	5	38
16	34	19	24	46
17	1	24	6	50
18	30	41	48	22
19	29	40	15	49
20	38	40	35	8
Main

Cell Formulas
Range	Formula
F2:F6	F2	=IFERROR(INDEX($A$1:$A$20,AGGREGATE(15,6,ROW($A$1:$A$20)/(COUNTIFS($B$1:$B$20,$A$1:$A$20)>0)/(COUNTIFS($C$1:$C$20,$A$1:$A$20)>0)/(COUNTIFS($D$1:$D$20,$A$1:$A$20)>0),ROWS(F$2:F2))),"")

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A1:A20	Expression	=AND(COUNTIFS($B$1:$B$20,A1),COUNTIFS($C$1:$C$20,A1),COUNTIFS($D$1:$D$20,A1))	text	NO

it looks exactly what i need but don't work

 

[exoterikos]

I'm sorry for cross posting !

 

[Fluff]

it looks exactly what i need but don't work

In what way?