Moving down a specific number of rows - Offset

[tapsmiled]

Good day.

I copied information from a website to an Excel sheet, and it did not format in the way that I require. Everything came up in column A as different sets of information (name, address, city, contact person, phone number). It appears as such:
A5:A9 (name, address, city, contact person, phone number)
A10 is blank
A11:A15 is the next set of information (name, address, city, contact person, phone number)
A16 is blank
A17:A21 is the next set and so on.

I want the information to appear in columns.
A5= Name B5=address C5=city D5=contact person e5=phone number

When I try to copy it down, it is not copying the cell reference properly. I believe I need to use an OFFSET and ROW formula but I do not know how to do it. Can anyone assist?

Thanks in advance

[Pete_UK]

It would help if you attached a sample Excel workbook, so that we can see exactly how your data is laid out and how you want it to appear (you might want it in a new sheet).

To do this, click on Go Advanced (below the Edit Window) while you are composing a reply, then scroll down to and click on Manage Attachments and the Upload window will open. Click on Browse and navigate to (and double-click) the file icon that you want to attach, then click on Upload and then on Close this Window to return to the Edit window. When you have finished composing your post, click on Submit Post.

I am going out soon, but I'll take a look later on (unless someone else chips in).

Hope this helps.

Pete

[dflak]

It sounds like since you have a variable amount of data that a VB solution might be appropriate. I've seen cases similar to this where if say the address is missing, you either have a blank row or the next row contains whatever the next piece of available data is. In other words, the number of rows in the record isn't fixed. We'd have to see the actual data to see what exceptions (if any) have to be handled.

Please attach a sample workbook. Thanks.

[tapsmiled]

Hi guys,

Sorry, but I am at work and unable to attach documents.

THIS IS HOW IT CURRENTLY APPEARS:

A
5 NAME
6 ADDRESS
7 CITY
8 CONTACT
9 PHONE
10
11 NAME
12 ADDRESS
13 CITY
14 CONTACT
15 PHONE
16
17 NAME
18 ADDRESS
19 CITY
20 CONTACT
21 PHONE

THIS IS HOW I WANT IT TO APPEAR:
A B C D E
5 NAME ADDRESS CITY CONTACT PHONE
6 NAME ADDRESS CITY CONTACT PHONE
7 NAME ADDRESS CITY CONTACT PHONE

Hope this makes it clearer.

[Pete_UK]

With your data in that format, you can put this formula in A5 of Sheet2:

=INDEX(Sheet1!$A:$A,(ROWS($1:1)-1)*6 + COLUMNS($A:A) + 4)

Copy across into B5:E5, then copy A5:E5 down as far as you need to.

Hope this helps.

Pete