Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Any help
I have the top 40 music charts in a text file
the file is like this 5 rows consisting of line 1 name of artist line 2 song line 3 date line 4 chart position line 5 is blank i need to be able to import the list into excel like this into 4 columns name of artist, song, date, chart Any ideas? Tia |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Any help
You could use a macro, but (editorial comment) why use a macro when a formula
will do? Here's one idea. Import your text file into column A. If you have 40 songs, 5 records of data per song, then you have 200 records of data. Artist 1 of song 1 is in cell A1. In cells B1..E1, write the following formulas: B1: =INDEX($A:$A,ROW()*5-4) C1: =INDEX($A:$A,ROW()*5-3) D1: =INDEX($A:$A,ROW()*5-2) E1: =INDEX($A:$A,ROW()*5-1) Copy B1..E1 from rows 1-40. ================================================ "Fred" wrote: I have the top 40 music charts in a text file the file is like this 5 rows consisting of line 1 name of artist line 2 song line 3 date line 4 chart position line 5 is blank i need to be able to import the list into excel like this into 4 columns name of artist, song, date, chart Any ideas? Tia |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Any help
Enter this in B1 and copy across to F1
=INDEX($A:$A,(ROWS($1:1)-1)*5+COLUMNS($A:B)-1) You will return zeros to column F because of the blank every 5th row. Delete column F Then select B1:E and copy down untill you get zeros When happy, Copy B:F then EditPasteSpecialValuesOKEsc. Delete column A Gord Dibben MS Excel MVP On Wed, 14 Nov 2007 21:21:38 -0000, "Fred" wrote: I have the top 40 music charts in a text file the file is like this 5 rows consisting of line 1 name of artist line 2 song line 3 date line 4 chart position line 5 is blank i need to be able to import the list into excel like this into 4 columns name of artist, song, date, chart Any ideas? Tia |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Any help
Thanks for the replies i will try both out.
Thanks |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|