Any Help?

[Cal]

I like to keep the top 20 music charts on a simple excel file

ie

Artist - Song - Date - Chart Position


Every year i update it with the previous years chart.


I manage to get the data like this


Hard-Fi
Cash Machine
01 Jan
14

Ordinary Boys
Boys Will Be Boys
01 Jan
3

Editors
Munich
08 Jan
10


Ideally i would love it in seperate columns and rows like this..commas are
seperate columns etc


Hard-Fi , Cash Machine , 01 Jan ,14
Ordinary Boys , Boys Will Be Boys , 01 Jan , 3
Editors , Munich 08 Jan 10

If i copy n paste using transpose i get the info but all on one row which
isnt what i want etc


Any help appreciated.

 

[Pete_UK]

Assume your data starts in A1 and is always 4 rows separated by a
blank row. Put these formulae in the cells shown:

C1: =INDIRECT("A"&(5*ROW(A1)-4))
D1: =INDIRECT("A"&(5*ROW(A1)-3))
E1: =INDIRECT("A"&(5*ROW(A1)-2))
F1: =INDIRECT("A"&(5*ROW(A1)-1))

Notice that the formulae are identical, except for the number being
subtracted from the end. Then copy C1:F1 down into C2 downwards, for
as many rows as you need (20?). If you fix the values in columns C to
F, then you can delete columns A and B.

Hope this helps.

Pete

 

[Cal]

Hope this helps.

Pete



Thanks Pete that works a treat , and it is really appreciated!.

 

[Paul Hyett]

I like to keep the top 20 music charts on a simple excel file

ie

Artist - Song - Date - Chart Position


Every year i update it with the previous years chart.


I manage to get the data like this


Hard-Fi
Cash Machine
01 Jan
14

Ordinary Boys
Boys Will Be Boys
01 Jan
3

Editors
Munich
08 Jan
10


Ideally i would love it in seperate columns and rows like this..commas are
seperate columns etc


Hard-Fi , Cash Machine , 01 Jan ,14
Ordinary Boys , Boys Will Be Boys , 01 Jan , 3
Editors , Munich 08 Jan 10

If i copy n paste using transpose i get the info but all on one row which
isnt what i want etc

FYI, I also keep a record of the charts, though I use a quite different
format :

Artist - Song - Entry Date - Hst Pos - Weeks

I then have one column for each week to the right of the above, allowing
me to see a record's progression throughout an extended period.

e.g.

Artist Song Date Hst Wks 05/1,12/1,19/1,26/1,02/2 ...
Nickelback Rockstar 27/10/07 #3 16w 15 8 4 6 3

And in the case of the above record, hopefully adding a #1 to my list in
the next week or so...

 

[Pete_UK]

You're welcome, Cal - thanks for feeding back.

Pete