If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Adavanced filter?
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?????? -- karyoker ------------------------------------------------------------------------ karyoker's Profile: http://www.excelforum.com/member.php...o&userid=29417 View this thread: http://www.excelforum.com/showthread...hreadid=491284 |
#2
|
|||
|
|||
Adavanced filter?
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. Copypaste specialValuesOKEsc Cut that column and paste to column A Gord Dibben Excel MVP On Tue, 6 Dec 2005 17:34:09 -0600, karyoker wrote: 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?????? |
#3
|
|||
|
|||
Adavanced filter?
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 "karyoker" wrote: 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?????? -- karyoker ------------------------------------------------------------------------ karyoker's Profile: http://www.excelforum.com/member.php...o&userid=29417 View this thread: http://www.excelforum.com/showthread...hreadid=491284 |
#4
|
|||
|
|||
Adavanced filter?
.. All I want to is filter the artist column delete duplicates and
replace the dups with empty cells., 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) -- Rgds Max xl 97 --- Singapore, GMT+8 xdemechanik http://savefile.com/projects/236895 -- "karyoker" wrote in message ... 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?????? -- karyoker ------------------------------------------------------------------------ karyoker's Profile: http://www.excelforum.com/member.php...o&userid=29417 View this thread: http://www.excelforum.com/showthread...hreadid=491284 |
#5
|
|||
|
|||
Adavanced filter?
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 -- karyoker ------------------------------------------------------------------------ karyoker's Profile: http://www.excelforum.com/member.php...o&userid=29417 View this thread: http://www.excelforum.com/showthread...hreadid=491284 |
#6
|
|||
|
|||
Adavanced filter?
You're welcome, Ollie !
(from us ..) -- Rgds Max xl 97 --- Singapore, GMT+8 xdemechanik http://savefile.com/projects/236895 -- "karyoker" wrote in message ... 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 |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Toolbars, Drop-Down Menus | Rick | New Users | 1 | September 21st, 2005 11:17 AM |
Filters won't turn off. | Al Camp | Using Forms | 7 | May 11th, 2005 07:28 PM |
Need to clear controls of Filter form | Jan Il | Using Forms | 2 | November 28th, 2004 02:04 PM |
Filter - Advanced Filter? | carrera | New Users | 4 | August 13th, 2004 09:33 PM |
Filter Button | Antonio | General Discussion | 4 | June 16th, 2004 05:21 AM |