• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Excel find which group of nunbers repeating in multiple columnes

E

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

V

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.
 
V

vletm

Excel Ninja
exoterikos
Please, reread Forum Rules:

Site Rules - New Users - Please Read

Hi all, Welcome to the Chandoo.org Forums. Posting Rules & Etiquette The Chandoo.org Forums is a collaborative and happy place to learn and expand your Excel knowledge. The Chandoo.org Forums consist of several Sub-Forums based on the type of question/area of Excel you are interested in...
chandoo.org
  • 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.
  • If you do cross-post, please put that in your post.
  • 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.
 
B

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)),"")

80326
 
P

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

You must log in or register to reply here.
Top