About MrExcel
Consulting Services
Learn Excel Resources
Challenge of the Month
MrExcel Seminars

Message Board

MrExcel Store


Paul asks "I have a number of columns of text data in my Excel database which contain
boys and girls names some of which are repeated many times, I have to return
a summary of the columns to a different spreadsheet. The information that
should appear on the summary sheet is the the name and the amount of times
it appears in the data base columns. the name should only appear once in the
summary sheet and along side it in another column is a count of the number
of times it appears in the data base column. Can you help with the
solution. "

This is the PERFECT job for a VERY FAST AND EASY Pivot Table. Let's assume that your names are in Column A, with the heading in A1. Select this column (A1:A100 if you have 99 names), go to Data, Pivot Tables, make sure that the origin range is set up correctly (Should appear Sheet1!A1:A100 or similar), and in the table definition, drag the heading to the ROW fields, AND to the DATA fields. You should see something like

[ROW]       [DATA]
Names       Count of Names

The Pivot Table should only show unique names, and the count of the times each name appeared on the list.

By Juan Pablo Gonzalez on 09-Feb-2002 Consulting can be hired to implement this concept, or many other cool applications, with your data. provides examples of Visual Basic procedures for illustration only, without warranty either expressed or implied, including but not limited to the implied warranties of merchantability and/or fitness for a particular purpose. The Visual Basic procedures on this web site are provided "as is" and we do not guarantee that they can be used in all situations.


Excel is a registered trademark of the Microsoft® Corporation.

All contents Copyright 1998-2008 by MrExcel Consulting.