+ Reply to Thread
Results 1 to 9 of 9

Excel find which group of nunbers repeating in multiple columnes

  1. #1
    Registered User
    Join Date
    06-29-2013
    Location
    Philippines
    MS-Off Ver
    Excel 2019
    Posts
    3

    Excel find which group of nunbers repeating in multiple columnes

    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?


    data.PNG

    Crossposted here: https://www.mrexcel.com/board/thread...umnes.1212230/
    Last edited by AliGW; 08-02-2022 at 03:30 AM.

  2. #2
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    15,783

    Re: Excel find which group of nunbers repeating in multiple columnes

    Assuming the picture is representative of the real data (specifically, that a value is never repeated within a column), then a COUNTIFS() function might be useful. Something like =COUNTIFS($A$1:$D$23,31) will return 4. You could enter a column somewhere convenient where you enter the possible values (integers 1 to 100??), then put your COUNTIFS() function adjacent to this column and get a table with all of the values and how many times each value appears in the raw data. Then process this table/list for all of the values that occur 4 times.

    Would something like that work?
    Quote Originally Posted by shg
    Mathematics is the native language of the natural world. Just trying to become literate.

  3. #3
    Forum Expert dosydos's Avatar
    Join Date
    12-09-2015
    Location
    Massachusetts
    MS-Off Ver
    365(PC) V:2202
    Posts
    1,460

    Re: Excel find which group of nunbers repeating in multiple columnes

    what version of MS Excel do you have?Are you still using 2010??
    <----- If you are happy with your solution please click on the "* Add Reputation" as a way to say thank you.

  4. #4
    Forum Expert dosydos's Avatar
    Join Date
    12-09-2015
    Location
    Massachusetts
    MS-Off Ver
    365(PC) V:2202
    Posts
    1,460

    Re: Excel find which group of nunbers repeating in multiple columnes

    A simple way would be to use this formula in E1 or F1 and drag down.
    This will return the value in the corresponding row of column A if it is also in B, C, and D.

    =IFERROR(IF(MATCH($A1,$B$1:$B$23,0)+MATCH($A1,$C$1:$C$23,0)+MATCH($A1,$D$1:$D$23,0),$A1,""),"")

  5. #5
    Forum Expert dosydos's Avatar
    Join Date
    12-09-2015
    Location
    Massachusetts
    MS-Off Ver
    365(PC) V:2202
    Posts
    1,460

    Re: Excel find which group of nunbers repeating in multiple columnes

    Depending on your version of excel and if you want all of the nubers in one cell that repeat then try the below formula:

    Formula: copy to clipboard
    Please Login or Register  to view this content.

  6. #6
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,752

    Re: Excel find which group of nunbers repeating in multiple columnes

    Welcome (back) to the forum.

    Thanks for your question. However, we have just found out that you have posted the same question elsewhere.... and haven't told us. Feel free to cross-post on other sites...

    But. Every forum has its rules. Please see Forum Rule #3 about cross-posting. So.

    Do not keep it a secret. We all are willing to give you our time, freely. However, some of us get very annoyed if we waste our time developing a solution for you, when you already have a nice solution elsewhere. All we ask is that you show us some respect and tell us:

    1) if you have cross-posted, and

    2) the URL of the cross posts.

    That way, those of us who don't want to waste their time can quickly check to see if you're already happy with another solution.

    (Note: since you're new here, on THIS occasion I have done it for you.)
    Glenn




    None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.

    Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh

  7. #7
    Registered User
    Join Date
    06-29-2013
    Location
    Philippines
    MS-Off Ver
    Excel 2019
    Posts
    3

    Re: Excel find which group of nunbers repeating in multiple columnes

    now im using 2019

  8. #8
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    78,944

    Re: Excel find which group of nunbers repeating in multiple columnes

    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    Forum Rules (updated August 2023): please read them here.

  9. #9
    Registered User
    Join Date
    06-29-2013
    Location
    Philippines
    MS-Off Ver
    Excel 2019
    Posts
    3

    Re: Excel find which group of nunbers repeating in multiple columnes

    Both solution work fine . My problem was the general formating . Thank you very much

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] how to find duplicate in Nunbers
    By jimmiboy in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 05-12-2021, 02:37 AM
  2. Find repeating values across multiple datasets
    By tm.winkler in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 05-10-2019, 09:18 AM
  3. Macro to find group of 5 similar values repeating same in one column
    By H_123 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 02-09-2016, 02:51 PM
  4. [SOLVED] Averaging repeating group of rows
    By 4GONERS in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 09-04-2015, 03:39 PM
  5. Group responses from repeating samples
    By haradin in forum Excel General
    Replies: 18
    Last Post: 06-02-2015, 08:24 PM
  6. [SOLVED] Use multiple IF to find certain data and group them
    By Tino XXL in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 02-04-2013, 08:58 AM
  7. [SOLVED] Need to split a LONG list into separate sheets based on repeating, incremental nunbers
    By matrix_machine in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 07-31-2012, 04:37 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1