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.

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

On Feb 9, 10:47*pm, "Cal" <cal> wrote:
> 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" <(E-Mail Removed)> wrote in message
news:59a0f4bb-acaf-42b6-a272-(E-Mail Removed)...

Hope this helps.

Pete



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

On Sat, 9 Feb 2008 at 22:47:14, Cal <cal@?.?.invalid> wrote in
microsoft.public.excel :

>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...
--
Paul Hyett, Cheltenham (change 'invalid83261' to 'blueyonder' to email me)

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

Pete

On Feb 10, 6:09*am, "Cal" <cal> wrote:
> "Pete_UK" <pashu...@auditel.net> wrote in message
>
> news:59a0f4bb-acaf-42b6-a272-(E-Mail Removed)...
>
> Hope this helps.
>
> Pete
>
> Thanks Pete that works a treat , and it is really appreciated!.