# Excel find which group of nunbers repeating in multiple columnes

#### exoterikos

##### New Member
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?

#### Attachments

• 19.8 KB Views: 15

#### vletm

##### Excel Ninja
exoterikos
eg cells E1 `=IF(COUNTIF(B\$1:E\$100,ROW())=4,ROW(),"")` and copy down as long as needs.
set 100 as Your data needs
Ps It's a challenge to test with Your picture - it's much better to test with an Excel-file.

#### vletm

##### Excel Ninja
exoterikos
• Cross-Posting. Generally, it is considered poor practice to cross post. That is to post the same question on several forums in the hope of getting a response quicker.
• Also if you have cross-posted and get an Solution elsewhere, have the courtesy of posting the Solution here so other readers can learn from the answer also, as well as stopping people wasting their time on your answered question.

#### shili12

##### Member
You have alot of cross-postings @exoterikos and no xlsx file uploaded only a pic, and i believe a solution has been found for you regardless, but on office 365.
=FILTER(A1:A23,ISNUMBER(MATCH(A1:A23,B1:B23,0))*ISNUMBER(MATCH(A1:A23,C1:C23,0))*ISNUMBER(MATCH(A1:A23,D1:D23,0)),"")

#### Attachments

• 10.1 KB Views: 12

#### bosco_yip

##### Excel Ninja
As mentioned by the other post, the OP used Excel 2019,

Here is a formula solution,

In F1, array ("Ctrl+Shift+Enter") formula copied down :

=IFERROR(AGGREGATE(15,6,IF(MATCH(A\$1:A\$23,B\$1:B\$23,0)+MATCH(A\$1:A\$23,C\$1:C\$23,0)+MATCH(A\$1:A\$23,D\$1:D\$23,0),A\$1:A\$23,""),ROW(A1)),"")

#### Peter Bartholomew

##### Well-Known Member
The purpose of this post is to play with Excel 365 techniques.
The first step was to develop a Lambda function that would test whether a specific number is present in every column. The formula
Code:
``= BYCOL(data, LAMBDA(dataCol, COUNTIFS(dataCol, v)))``
shows the number of times a value 'v' occurs in each column of the array 'data'. Wrapping the formula in AND returns true only if 'v' is present in each column. A Lambda function allows the formula to be applied to multiple values 'v'
Code:
``````TestValueλ
= LAMBDA(value,
AND(BYCOL(data, LAMBDA(dataCol, COUNTIFS(dataCol, value))))
);``````
Any column of the original data table would provide a list of possible values to test, but I chose to start with every value that appeared within the 'data' table.
Code:
``= SORT(UNIQUE(TOCOL(data)))``
Putting it all together gives
Code:
``````= LET(
distinct, SORT(UNIQUE(TOCOL(data))),
common?,  MAP(distinct, LAMBDA(v, TestValueλ(v))),
FILTER(distinct, common?)
)``````

#### Attachments

• 11.9 KB Views: 5

#### exoterikos

##### New Member
I'm sorry for cross posting !

#### vletm

##### Excel Ninja
exoterikos
Moderator Note:
You'll be busy to post solution to all Your used Forums.
All Forums has basically same rules with Cross-Posting.

#### exoterikos

##### New Member
exoterikos
eg cells E1 `=IF(COUNTIF(B\$1:E\$100,ROW())=4,ROW(),"")` and copy down as long as needs.
set 100 as Your data needs
Ps It's a challenge to test with Your picture - it's much better to test with an Excel-file.
It works perfectly. Thank you very much

#### exoterikos

##### New Member
As mentioned by the other post, the OP used Excel 2019,

Here is a formula solution,

In F1, array ("Ctrl+Shift+Enter") formula copied down :

=IFERROR(AGGREGATE(15,6,IF(MATCH(A\$1:A\$23,B\$1:B\$23,0)+MATCH(A\$1:A\$23,C\$1:C\$23,0)+MATCH(A\$1:A\$23,D\$1:D\$23,0),A\$1:A\$23,""),ROW(A1)),"")

View attachment 80326
It works fine. Thank you very much

#### bosco_yip

##### Excel Ninja
It works fine. Thank you very much
I give you a copy of file to show how does my formula work

bosco_yip

#### Attachments

• 9.7 KB Views: 7

#### exoterikos

##### New Member
I give you a copy of file to show how does my formula work