March 9, 2026•1 min read•from Microsoft Excel | Help & Support with your Formula, Macro, and VBA problems | A Reddit Community

Any way to see which rows in a data set have the most common values?

Our take

In the quest to analyze a dataset of individuals and their chosen items, one user seeks a solution to identify which rows share the most common values. With a dataset of approximately 300 individuals, each having selected five items from a predefined list, the goal is to determine commonalities without crafting individual formulas for each person. This challenge invites innovative approaches to streamline the comparison process, making it easier to discover which individuals have matching selections. Join the discussion to explore effective solutions!

SOLVED by u/Bradland, thank you!

Hello everyone!

I have a simple dataset that is a row per person (300ish in total) that has their name in column A, and then 5 things that they have chosen from a predefined list in columns B to F.

What I'd like to do (but can't quite figure out) is how to determine the number of things each person has in common with everyone else.

I've tried something with sum(index(countif(B$2:F$2, B3:F3))) which I can drag down to compare the 1st person to every other person. However, that leaves me needing to write a formula per person, which I would prefer to avoid.

Any ideas?

Edit: my title may be misleading. I want to know which rows have the most in common with other rows. Not whether the rows contain the most common values. For example, I'd like to know if person 1 has the same 5 things as someone else, etc.

submitted by /u/OpTOMetrist1
[link] [comments]

Read on the original site

Open the publisher's page for the full experience

View original article →

Is there a way to Sumifs up to a certain column with multiple criteria?Hi everyone, I’m looking for a formula to sum all of the columns starting from a particular column up to a particular column. How do I best do that? On the data tab, Row 1 I have the following columns: A1 = date B1 = serial number C1 = customer name D1 = date 1, with the data being $ E1 = date 2, with the data being $ F1 = date 3, with the data being $ In a separate tab I have the same thing mirroring the data tab, but without the $, for the calculation what I intend to do is on E2, I want to say if $A2 = E$ 1, then sum all of D up to E column on the data tab provided that the value on B column of the data tab match B2 of the calc tab, and C column of the data tab match C2 of the calc tab. For F2, it would be the same thing, but sum column D up to column F instead of column E. I hope it makes sense. Thanks for your help! submitted by /u/botng [link] [comments]

Tagged with

#rows.com#financial modeling with spreadsheets#Excel alternatives for data analysis#generative AI for data analysis#natural language processing for spreadsheets#AI formula generation techniques#Excel compatibility#large dataset processing#row zero#formula generator#Excel alternatives#big data management in spreadsheets#conversational data analysis#real-time data collaboration#intelligent data visualization#data visualization tools#enterprise data management#big data performance#data analysis tools#data cleaning solutions

Any way to see which rows in a data set have the most common values?

Related Articles

Tagged with