Reference every nth cell?

Question

I have a single column of data which is a repeating 3 cell pattern of name, address and city.  I'd like to reorganize this data so it's in 3 columns, one for each field of name address and city.  So it'd go from:

Name1

Address1

City1

to:

Name1     Address1      City1

Answer 1

With A1:A15 containing the pattern you described (Name, Address, City)

I'll use a helper column to make the example a bit more understandable

This regular formula begins the list of row references that begin sections

D1: =(ROW()-1)*3+1

copy that formula down as far as you need

...the list will be: 1, 4, 7, 10, 13

This regular formula begins the list of section items

E1: =INDEX($A:$A,$D1+COLUMNS($E:E)-1)

Copy that formula across through G1 and down as far as you need

Is that something you can work with?

Answer 2

I have a single column of data which is a repeating 3 cell pattern of name, address and city.  I'd like to reorganize this data so it's in 3 columns, one for each field of name address and city.  So it'd go from:

Name1

Address1

City1

to:

Name1     Address1      City1

Suppose your original data are in A1:A999.  Also suppose you want the reorganized data in B:D starting with row 1.

Then, in B1 enter the formula =INDEX($A$1:$A$999,(ROW()-ROW($B$1))*3+COLUMN()-COLUMN($B$1)+1)

Copy B1 to C1:D1.

Copy B1:D1 to 2:333. Note that 333 is 999/3.

Answer 3

I have a single column of data which is a repeating 3 cell pattern of name, address and city.  I'd like to reorganize this data so it's in 3 columns, one for each field of name address and city.  So it'd go from:

Name1

Address1

City1

to:

Name1     Address1      City1

Try this...

Let's assume your data is in the range A2:A19.

Let's assume you want to transpose that data into 3 columns by 6 rows starting in cell C2.

Enter this formula in C2:

=INDEX($A$2:$A$19,ROWS(C$2:C2)*3-3+COLUMNS($C2:C2))

Copy across to E2 then down to C7:E7.

Inspect the data and makes sure everything is Ok.

Select the entire range of formulas.

Right click>Copy

Right click>Paste Special>Values>OK

Delete the original data in column A if desired.

Answer 4

Hi,

Try this

1. Assume your data is in range F3:F8
2. In cell K3, enter the following formula =INDEX($F$3:$F$8,COUNTA($J3:J3)+COUNTA($K$2:$M2)+1,1)
3. Copy down and right

Ensure that K2:M2 are blank and cell column J is blank

Hope this helps.

Answer 5

I'm going to assume that your data starts in A1. The formula will have to be modified if this is not the case.

In an adjacent cell (eg. B1 perhaps?) put in this formula,

=INDIRECT("A"&((ROW()-1)*3)+COLUMNS($A:A))

... then Fill or Copy Right two cells to D1. Fill or Copy Down as desired.

If you have trouble modifiying the formula to suit your own needs, post back with the originating cell of your data and I'll try to assist.