Any help

F

Fred

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
 
M

MrAcquire

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.

================================================
 
G

Gord Dibben

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 Edit>Paste>Special>Values>OK>Esc.

Delete column A


Gord Dibben MS Excel MVP
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top