Merge rows by removing duplicates in Excel 2007

microsoft-excel-2007

I have a database of more than 30,000 entries.

enter image description here

How do I get the duplicates and condense them down into one row with the last three columns filled with xs as appropriate? The above should become this:

Manmeet  abc   000   -   -   -      x   x    x

Best Answer

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.

Related Question