Can Excel do this?

[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.

 

[Stephen Bye]

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)

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. 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

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

 

[Dave Peterson]

Just to add to Stephen's response...

Debra Dalgleish explains what Stephen suggested (but with pictures!):
http://contextures.com/xlCondFormat03.html#Duplicate

And I would keep those names there, it'll make sorting/filtering much easier
later.

========
And if you actually have these files as .mp3 files, you may want to look at John
Walkenbach's MP3SongLister program:

http://www.j-walk.com/ss/excel/tips/tip103.htm

And Abba is a guilty pleasure, huh???????

 

[ShaneDevenshire]

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

 

[JoAnn Paules]

Why post this in the Publisher newsgroup? Just curious.

--

JoAnn Paules
Microsoft MVP - Publisher

How to ask a question
http://support.microsoft.com/kb/555375

 

[Carrie]

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.

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

 

[pdan]

JoAnn, your replies are always very to the point and timely here.
I cross posted manually, and meant to post to microsoft.public.excel. Cross
posting is frowned upon in some circles, my bad.

 

[JoAnn Paules]

Oh, that's why. I thought maybe you were merging into Publisher or something
but didn't see any mention. Thought maybe I'd missed something, which
wouldn't be the first.

And I do try getting straight to the point. Some see it as being harsh but
when *that's* my intention, there's no mistaking it. I'm just trying to
answer a question and hopefully educate at the same time.

I hope you were able to get the help you needed. My Excel skills also leave
a lot to be desired but I am learning.

--

JoAnn Paules
Microsoft MVP - Publisher

How to ask a question
http://support.microsoft.com/kb/555375

 

[Mary Sauer]

Def Leppard did Billy's Got a Gun. We have a Def Leppard poster hanging on the
back stairs wall, along with the Chicago Bears Super Bowl poster and many other
rock bands. Even a psychedelic peace poster.

 

[Mary Sauer]

I grew up in Des Moines, their minor league team was the Bruins which was a Cubs
franchise. My dad took me to a lot of Bruin games.

As for the Bears, we lived in Illinois for a number of years, never Chicago
however. We are in Ohio... Go Cav's.

 

[Mary Sauer]

We lived in St. Louis for a few years. We got to ride up to the top of the arch
before it was completed. I remember the newspaper coverage of the keystone!

We have been all over. My son was in 11 schools before he graduated.

My husband lived in Chicago before we met, he is from Maryland. Went to the
University of Miami.