Consolidation of Data

raymaster98

Board Regular
Joined
Oct 28, 2009
Messages
212
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
 

Some videos you may like

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number

AlphaFrog

MrExcel MVP
Joined
Sep 2, 2009
Messages
16,377
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:

Asator

Board Regular
Joined
Apr 5, 2010
Messages
186
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.
 

raymaster98

Board Regular
Joined
Oct 28, 2009
Messages
212
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
 

AlphaFrog

MrExcel MVP
Joined
Sep 2, 2009
Messages
16,377

ADVERTISEMENT

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

raymaster98

Board Regular
Joined
Oct 28, 2009
Messages
212

ADVERTISEMENT

TY Marcelo. Awesome formula. Works very nicely. Kenny
 

raymaster98

Board Regular
Joined
Oct 28, 2009
Messages
212
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
 

Marcelo Branco

MrExcel MVP
Joined
Aug 23, 2010
Messages
16,407
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,461
Messages
5,596,274
Members
414,050
Latest member
Rick Royer

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

Which adblocker are you using?

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
Top