Forum Discussion
Find Common Values In ALL 5 Columns With Array Formulas
Amlesh7400 Perhaps easiest with Power Query. In the attached workbook you'll find a sheet called "Count". I trust you can do your analysis from there.
Regards,
Amlesh7400 Well, the query that generates the table is in the file that is attached to my previous post. Depending on your Excel version you may have a separate Power Query tab/ribbon or you'll find the tools needed on the Data ribbon under "Get & Transform Data".
The steps applied to achieve the output aren't very complicated, that is, if you are familiar with Power Query. Otherwise you may get lost in all the options and icons.
I have got the latest version of Excel for 365 subscription but don't know much about Power Query so thanks for the tip to help explore to refine my skills.
btw I stumbled upon a simpler formula using conditional formatting > highlight cell rules > use a formula to determine which cells to format
=COUNTIF($A$2:$E$101,A2)>4
Output is highlighted in chosen cell colour.
Amlesh7400 To begin with PQ, the link below could be a good starting point.
https://exceloffthegrid.com/power-query-introduction/
As to your other question, I'm not sure what the problem is. The rule you mentioned, colours a cell if the value in A2 occurs more than 4 times in the range specified A2:E101. If that is not what you want, what is it?
- it is exactly what i wanted and i solved it using a far simpler formula for me that is, without having to use power query.
