Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

In my case i have a database of more than 30,000

enter image description here

How do i get the duplicates in one row and the last three criteria of puting x in one row of one person Like Manmeet abc 000 - - - x x x

share|improve this question
do you know about VBA? a VBA macro can do the exact thing... – tumchaaditya Jun 13 '12 at 8:28

You can use Excel's built in Table and Pivot Table functionality for this.

  1. Select your existing data and Insert Table.
  2. Highlight a cell in your table and use it as the datasource to Insert Pivot Table (choose the appropriate location).
  3. Change the Pivot Table Report Layout to Tabular.
  4. Choose Name, Address and Phone for your Pivot Table Row Labels.
  5. Turn off subtotals for Name, Address and Phone and Grand Totals for Rows & Columns.
  6. Choose Count of Web, ISCI and Lenders for your Pivot Table Values.

enter image description here

The resulting Pivot Table will have the Count (i.e. 1) instead of "X" for the values in your Web, ISCI and Lenders columns. If you really want it to be "X" (or someother letter/symbol) you can use a custom number format for that column.

One extra benefit of this solution is that as you add values or update your table, the Pivot Table will update, so your summary will always be current.

share|improve this answer

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.