# Consolidation of Data

#### raymaster98

##### Board Regular
Using Excel 2003:

In column G, I have a list of names. Many of the names repeat on the list. I am looking for a formula that I can copy down in another column that will list each name only once.

Ex:

G1 Bill Smith
G2 John Adams
G3 Bill Smith
G4 Bill Smith
G5 Jessica Matthews
G6 John Adams
G7 Ralph Campbell

If correct, the formula should return:

A1 Bill Smith
A2 John Adams
A3 Jessica Matthews
A4 Ralph Campbell

Any help would be appreciated. Thanks, Kenny

### Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce
You could easily do this with the Advanced Filter feature...

• Select column G
• Select from the menu; Data\ Filter\ Advanced Filter
• Filter the list in place
• Check: Unique records only
• OK
• Select the filtered names in column G and copy
• Select cell A1 (or any cell you want) and paste.
• Select from the menu; Data\ Filter\ Show all

Last edited:
Alternately, you can use a pivot table and just use the names as a row field. If you do it this way, you can add entries to the original data, then refresh the table, and your unique entry list will be up to date.

I appreciate your help. I didn't mention it before, because I didn't think I needed to, but all of the names have associated data with them in surrounding columns. If I use a filter to keep only unique names, then much of the data is deleted. Therefore, I cannot use your solution. I appreciate your help. Thanks, Kenny

I don't quite follow. The Advanced Filter method I described doesn't delete anything.

TY Marcelo. Awesome formula. Works very nicely. Kenny

I found a litch in Schollar's formula that hopefully someone can resolve. I modified Schollar's formula to accomodate my data. It works great unless the cells at the end of column G are empty. Since the volume of data in column G will change from month to month, I extended the data range in the formula to comfortably exceed any data that may appear in column G.

The formula is this:

=INDEX('Scheduled Patients (Oct)'!\$G\$2:\$G\$10000,MATCH(0,COUNTIF('Scheduled Patients (Oct)'!\$G\$2:\$G\$10000,"<"&'Scheduled Patients (Oct)'!\$G\$2:\$G\$10000)-SUM(COUNTIF('Scheduled Patients (Oct)'!\$G\$2:\$G\$10000,"="&A\$43:A43)),0)) with {} on each end of the formula

In this case G1770 is the last row of data for column G. If you the range extends beyond 1800, the formula returns #NA in A44. Any idea why? Any help would be appreciated. Thanks, Kenny

Hi Kenny,

I made a test with the 7 names of your OP, extending the data-range to 20 rows, and Richard Schollar's formula worked perfectly.

This is the formula i used

=INDEX(\$G\$1:\$G\$20,MATCH(0,COUNTIF(\$G\$1:\$G\$20,"<"&\$G\$1:\$G\$20)-SUM(COUNTIF(\$G\$1:\$G\$20,\$A\$1:A1)),0))
Ctl+Shift+Enter

Result
A
<TABLE style="WIDTH: 88pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=117><COLGROUP><COL style="WIDTH: 88pt; mso-width-source: userset; mso-width-alt: 4278" width=117><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 88pt; HEIGHT: 15pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=20 width=117> </TD></TR>

<TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=20>Bill Smith</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=20>Jessica Matthews</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=20>John Adams</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=20>Ralph Campbell</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=20 align=middle>#N/D</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=20 align=middle>#N/D</TD></TR></TBODY></TABLE>

#N/D in portuguese-version = #N/A english-version

M.

Replies
3
Views
171
Replies
12
Views
484
Replies
25
Views
1K
Replies
5
Views
287
Replies
2
Views
227

Threads
1,217,750
Messages
6,138,406
Members
450,134
Latest member
TYoung24

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

### Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back