Can Excel do this?

P

pdan

I have exported +7000 Tracks and Artists from a program.
I want to print a book which would read/look better if I could hide all
after the first instance of the Artist.
i.e.:
Abba Dancing Queen
Take A Chance on Me
Waterloo
Aerosmith Billy's Got a Gun
Love in an Elevator
Sweet Emotion
etc.
Could Excel hide any instance after the first of the Artist or delete it.
(Obviously if deleted I would need to maintain the original file for sorting
purposes)
I would import the sorted Excel file(s) into a page layout program which
would respect whatever Excel sorts to.
I am nearly a novice at Excel, so a lengthy answer would help.
 
S

Stephen Bye

pdan said:
I have exported +7000 Tracks and Artists from a program.
I want to print a book which would read/look better if I could hide all
after the first instance of the Artist.
i.e.:
Abba Dancing Queen
Take A Chance on Me
Waterloo
Aerosmith Billy's Got a Gun
Love in an Elevator
Sweet Emotion
etc.
Could Excel hide any instance after the first of the Artist or delete it.
(Obviously if deleted I would need to maintain the original file for
sorting purposes)
Click to expand...

You could use Conditional Formatting (for the whole of the first column) to
set the text color to white if the cell contents are the same as the cell
above.
That way, the text is still there but not visible.
 
T

T. Valko

Do you mean, this is what you have:

Abba...............Dancing Queen
Abba...............Take A Chance on Me
Abba...............Waterloo
Aerosmith........Billy's Got a Gun
Aerosmith........Love in an Elevator
Aerosmith........Sweet Emotion

And this is what you want:
Abba Dancing Queen
Take A Chance on Me
Waterloo
Aerosmith Billy's Got a Gun
Love in an Elevator
Sweet Emotion
Click to expand...

Assume the artist name is in column A.

Enter this formula in C1:

=A1

Enter this formula in C2:

=IF(A2=A1,"",A2)

Copy down to the end of the list.

Now, select the entire range of formulas in column C.
Goto the menu Edit>Copy
Then, Edit>Paste Special>Values>OK

Now, drag column C over and replace column A.

Biff
 
G

Guest

Hi,

Here is how to do what Stephen suggested:

Assume that the Artist's are in the range A2:A500. Select this range and
choose Format, Conditional Formatting, and from the first drop down choose
"Formula is" and in the second box enter the formula:

=A1=A2

Click the Format button, choose the Font tab, open the Color drop down and
pick White. Then click OK twice.

Cheers,
Shane Devenshire
 
C

Carrie

pdan said:
I have exported +7000 Tracks and Artists from a program.
I want to print a book which would read/look better if I could hide all
after the first instance of the Artist.
i.e.:
Abba Dancing Queen
Take A Chance on Me
Waterloo
Aerosmith Billy's Got a Gun
Love in an Elevator
Sweet Emotion
etc.
Could Excel hide any instance after the first of the Artist or delete it.
(Obviously if deleted I would need to maintain the original file for
sorting purposes)
I would import the sorted Excel file(s) into a page layout program which
would respect whatever Excel sorts to.
I am nearly a novice at Excel, so a lengthy answer would help.
Click to expand...

Off topic here, and I know not the point, but unless it's a typo, if you
imported it this way, the title of the Aeorsmith song is "Janie's Got a Gun"

If your list has it listed the way you do, you should check the list for
other mistakes.

~ Carrie
 
P

pdan

Right on the mark T. Valko, thank you.

T. Valko said:
Do you mean, this is what you have:

Abba...............Dancing Queen
Abba...............Take A Chance on Me
Abba...............Waterloo
Aerosmith........Billy's Got a Gun
Aerosmith........Love in an Elevator
Aerosmith........Sweet Emotion

And this is what you want:


Assume the artist name is in column A.

Enter this formula in C1:

=A1

Enter this formula in C2:

=IF(A2=A1,"",A2)

Copy down to the end of the list.

Now, select the entire range of formulas in column C.
Goto the menu Edit>Copy
Then, Edit>Paste Special>Values>OK

Now, drag column C over and replace column A.

Biff
Click to expand...
 
P

pdan

For the record, that worked well and was able to maintain the text, but I
have imported the text into Adobe InDesign and the white font color is not
carried thru.
For printing from Excel this would be preferable to T. Valko's solution, but
being a commercial printer I feel the need to manipulate text in our day to
day program.
(I'm sure many of you could manipulate the headers, footers, page numbering,
strokes, fills etc. as well as required right in Excel).
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Deleting duplicates and printing issue 2
Can Excel do this? 2
Can Excel do this 5
Can Excel do this with functions? 5
data export: can I do this? 1
Excel 97 hyperlinks 1
Do I need DLL or COM Addin, Excel Addin 1
Can Excel do this? 6

Top