Excel gurus - help please :)

[BCNZ]

Ok, I have a database that I keep of music - song title and artist in that order.

I use a standard Excel sheet and it goes like this:

Column A Column B

A hard days night The Beatles
Bright side of the road Van Morrison
Changes David Bowie
Don't lose my number Phil Collins


and so on. Titles in column A are arranged by alphabetical order.

Now, what I need is another list that is the same data as this, but with column B's contents in column A.

That is, I want the list to start with the artist, and then the song, sorted alphabetically in column A.
The data for this second list needs to come from the data I enter in the first list.
Is there an automated way that I can make Excel do this?

[Philquad]

cant you just copy column B to A on a new sheet?
and A to column B

[BCNZ]

cant you just copy column B to A on a new sheet?
and A to column B

Errr.. no. If I do that, then I still have the titles in alphabetical order, and the artists all out of order.
What I need is the second list to have the artists in alphabetical order.

See, I could do it manually, and then sort both columns by alphabetic of column A, but I add data all the time and it would be quite cumbersome. I am hoping someone can supply me with a macro or such that will do it automatically.

[Rocket]

Cant you sort by column B then column A

Have a look under the data/sort menu

[Globe]

Select column B, so that all of column B is highlighted. Then right click "cut"

Then

Select column A, so that all of column A is highlighted, right click then "insert cut cells".

This will swap column A and B.

easy peasy

Edit: just saw you need to sort by artist alphabetically, highlight all columns by selecting the box directly to the left of column A (or directly above row 1 if you like), then data>sort>by column A.

[ocd_csv]

Yes you can have both lists if you want but the simple way is to sort the list when needed.

To do it the other way is to have a macro which activates when pressing the button on the screen.
You can record it yourself with the macro recorder inside excel

Just do the following when the recorder is recording.
Click on the first cell
While Holding shift press the end key then down arrow then press the right arrow
This should jump to the bottom of your list and highlight both columns

then copy and paste to new location and sort on column B
Then stop macro recording

This then can be associated with a command button to do this same task each time.

[crypto7]

Im sure you are using excel because thats what you know. A database would be better for your application eg access.
If you use a database you can create any report you like on the data and just keep adding records.

[BCNZ]

Thanks people. I will have a play with it. I'm a total novice with Excel. I don't actually use it for anything but my music library

Oh, and I tried to set that list out in my OP, but I mucked up the formatting. I'm sure you get the idea anyway.

[best4less]

This has nothing to do wit Excel

But for sorting all my files on the PC i can't go pass "Copernic 2.2"

You can get it from here


and with just a click you can type the 1st couple of letters of the song/file and it will list them it soughts your music (or what ever files)
by artist, alphabetical, size, blah blah you get the idea
click on the song once it plays inside of copenic or you double click the song it will open in your default media player
It's so good even the boss uses it with out asking me for help
and don't get me started on how good it is in searching your emails LOL

here is a screen shot

[BCNZ]

B4L .. do you use mp3s as your music on your PC ?

I am thinking that the program you are showing relies on the tags in the files
to generate the data on screen.
Where it says (in your example) "Artist - AC-DC", where is it getting this from?

[best4less]

Yes converted most to MP3's
yes it does rely on tags but where did they get them tags from they where already there LOL

I just right clicked on a AC/DC song and in the properties under details its all there. I didn't put it there someone else must have