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
 

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:
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0

Forum statistics

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

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
Back
Top