Formating data

  • Thread starter Thread starter KH Puah
  • Start date Start date
K

KH Puah

I have a set of data which is not formated in columns.
Want to know how to apply a formula to format it into
columns form.

e.g.

In column A, first row, the fist data is e.g. A1100. Then
the next data is 10 rows down, follow by the third data in
the next 10 rows down.
Similarly in column B, the data starts at second row is
e.g. Brandon.

I want to copy the data to a new column format such that
the data run continuosly.
Column N Column O
A1100 Brandon
A1101 Steve

KH Puah
 
Hi
try the following on a separate sheet:
A1:
=OFFSET('sheet1'!$A$1100,(ROW()-1)*10,0)

B1:
=OFFSET('sheet1'!$B$1100,(ROW()-1)*10,0)
copy both formulas down. After this copy the resulting range and insert
it again as valuse ('Edit - Paste Special - Values')
 
Did not got it to work. I tried that. Only got the first
data using this.
=OFFSET(Sheet1!A1:A100, (ROW()-1)*5,0)

where did I go wrong?
sheet 1
A B
1D10
2 D11
3 D12
4
5
6D20
7 D21
8 D22
9

I want the result to be like this
D10 D11 D12
D20 D21 D22

KH
 
Back
Top