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

Formula to Extract most number of common occurrences from a list

BruceW

New Member
Hi all


I have a table as follows


Project No Name Task

A-100 MV Vanessa Task 1

A-200 MV Vanessa Task 2

A-300 MV Vanesa Task 3

B-100 MV Anneley Task 1

B-200 MV Annerley Task 2

B-300 MV Anneley Task 3

B-400 MV Anneley Task 4

B-500 MV Anneley Task 5


As can be seen sometime the name is spelt incorrectly ie A-300 and B-200


What is need is to extract the Correct name for project beginning with A. The assumption is that the correct name is the name that is most common. So for project beginning with A the count is 2 for MV Vanessa and 1 for MV Vanesa – so I need a formula to give me the name that is most common among projects starting with A. So I type in “A “and the formulae will produce MV Vanessa and I type in B and the formulae will produce MV Anneley


Regards

Bruce
 

Hui

Excel Ninja
Staff member
Bruce

Assuming your list is much longer than above, I would typically apply an Auto Filter to the list.

Then goto the pulldown of the field in question and look through the summarised names that don't match.

Select the Non Matching name and Autofilter will show you all the records with incorrect names.

Fix the values, select all the values, enter the correct value in one cell and Ctrl Enter to save it in all the cells.

Go back to your dropdown until complete
 

BruceW

New Member
Hui

Thanks for the reply. The data is from an external database that gets refreshed hourly. So above wont work. I dont necessarily want to correct the name just need to find the most common occurence. I am using Excel 2003. The countifa and countif dont seem to work as they need criteria. I will maybe try a filler column to do some work and then use the offset and max functions.
 

keymaster

New Member
Interesting question Bruce...


Here is one solution with helper columns. I am sure a non-helper column method exists too, but it seemed too much hassle to work that out.


http://chandoo.org/img/playground/most-common-name.xlsx


I use COUNTIFS to check for both project name and person name. The logic is pretty straight forward
 

Hui

Excel Ninja
Staff member
Bruce

As your using Excel 2003 Change E2 in Chandoo's example to =SUMPRODUCT((LEFT($A$2:A2,1)=$H$1)*($C$2:C2=C2))

and copy down
 
Top