Adavanced filter?

  • Thread starter Thread starter karyoker
  • Start date Start date
K

karyoker

I have a spreadsheet with about 13,000 song entries... Col a artist col
b song title.. Iv'e read advanced filter tutorials till I'm blue in the
face.. All I want to is filter the artist column delete duplicates and
replace the dups with empty cells., Anything I try deletes the whole
row which deletes that song....

Artist .......... ............. Title

Neil Diamond .......... Brother Loves ....
Empty cel................ Holly Holy



Advanced filter or macro??????
 
One method if pre-sorted on Artist's name.........

I C1 enter =A1

In C2 enter =IF(A2=A1,"",A2)

Double-click on fill handle to copy down.

Copy>paste special>Values>OK>Esc

Cut that column and paste to column A


Gord Dibben Excel MVP
 
Filters only work on entire rows, so that doesn't seem like the path you want.
Are you just trying to clean it up visually, so it appears less cluttered?
If so, I'd suggest conditional formatting, setting it up so that a repeated
artist is IN the cell, but not shown. Then if the table gets shuffled
(sorted, whatever), the artist will be there. To set up the conditional
format, go to A2. Format > Conditional Formatting. Use the drop-downs to
set the condition to be 'cell value is' 'equal to' =A1. Click the format
button and set it up so that the font color is white. Click OK. Copy that
cell, highlight the rest of the column and Edit > Paste Special, select
Formats and click OK.
Now if an artist is repeated in consecutive rows, only the first will be
visible. Other rows contain the artist name, but it's masked by the white
font on a white background. And if the table gets sorted, the artist name
will appear as long as it doesn't repeat the prior cell.
HTH. --Bruce
 
.. All I want to is filter the artist column delete duplicates and
replace the dups with empty cells.,
Click to expand...

Focusing on the above line ..
perhaps it'll be ideal to use a pivot table (PT) here
(only a few clicks will get us there ..)

Sample construct available at:
http://cjoint.com/?mhegGPvYQp
PivotTable_Approach_karyoker_newusers.xls

Assume the source table is in Sheet1,
cols A & B, data from row2 down:

Artist Title
Neil Diamond Title1
Tom Jones Title2
Neil Diamond Title3
Tom Jones Title4
John Lennon Title5
Neil Diamond Title6
John Lennon Title7
Tom Jones Title8
etc

Select any cell within the table

Click Data > Pivot Table Report
Click Next > Next

In step3 of the wiz.:

Drag and drop "Artist" within the ROW area
Drag and drop "Title" within the ROW area, below "Artist"

Drag and drop "Title" within the DATA area
(It'll appear as "Count of Title")

Click Finish

The PT will be created in a new sheet just to the left of Sheet1,
giving the summary format that is desired, viz.:

Each Artist will appear once only in col A,
with the artists' titles listed in col B

And if we don't want the Artists' subtotal lines,
in the PT sheet, just:
Double-click on "Artist", then select "None" for subtotals > OK
(then just hide away col C and the "Grand Total" row for a neat look)

To quickly dress up a PT, just select any cell within the PT,
then click Format > Autoformat
(select one of the table formats, play around to taste)

--
 
Thanks guys you both have been very helpful. I'm doing what I want now.
I am not a frequent user of excel and it is like trying to do something
in a foregn language with all the buzz words and simple microsoft
definitions of terms. In the posts above I had to google certain
terms to see exactly what you were talking about.. But we got the job
done and I learned some more about a proggie that for some reason I
have a huge mental block on.....

Thanks again,
Ollie
 
Back
Top