Consolidation of Data

[raymaster98]

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

 

[AlphaFrog]

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

 

[Asator]

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]

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]

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

 

[Marcelo Branco]

Hi,

There is a formula created by Richard Schollar to achieve the results you need.
http://www.mrexcel.com/forum/showthread.php?t=518138


HTH

M.

 

[raymaster98]

TY Marcelo. Awesome formula. Works very nicely. Kenny

 

[raymaster98]

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]

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.

 

[Marcelo Branco]

Kenny,

Complementing my previous post

See #9 and #10 in the same thread i have suggested
http://www.mrexcel.com/forum/showthread.php?t=518138

M.