• 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

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

  • data.PNG
    data.PNG
    19.8 KB · Views: 22
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.
 
exoterikos
Please, reread Forum Rules:
  • 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.
 
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
 
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

  • AllCol(PB).xlsx
    11.9 KB · Views: 7
Hi,

In F1, formula copied down.

IFERROR(AGGREGATE(15,6,(1/(INDEX(COUNTIF($A$1:$D$23,$A$1:$D$23),,1)=4))*$A$1:$A$23,ROW(A1)),"")

David
Try to put a number 4 times in just one column and test. Your solution does not appear to work (checking presence in all 4 columns part).
 
Hi shrivallabha,

Also check this version,
=IFERROR(AGGREGATE(15,6,(1/((COUNTIF(B1:D23,A1:A23))=3))*A1:A23,ROW(A1)),"")

David
I just posted my response from academic interest. You probably don't need a sample. This solution is correct. Thanks.
Edit: The range references shall be absolute, nothing wrong in the logic.
=IFERROR(AGGREGATE(15,6,(1/((COUNTIF($B$1:$D$23,$A$1:$A$23))=3))*$A$1:$A$23,ROW(A1)),"")
 

Attachments

  • exoterikos test.xlsx
    10.8 KB · Views: 5
Hi shrivallabha,

I would like to make it clear that the data in the columns are unique data, and are not repeated more than once. If it were otherwise, then of course a different formula is needed.

David
 
Hi to all!

Just to put a slight variation for bines53 solution (Non CSE too):
=IFERROR(AGGREGATE(15,6,A$1:A$23/(COUNTIF(B$1:D$23,A$1:A$23)=3),ROWS(F$1:F1)),"")

And another way to do it (CSE version):
=IFERROR(INDEX(MODE.MULT(IF(COUNTIF(A$1:D$23,A$1:D$23)=4,A$1:D$23)),ROWS(F$1:F1)),"")

Blessings!
 
Back
Top