# Thread: Format unique data into row (Excel 2003)

1. ## Format unique data into row (Excel 2003)

Hi all

I have a workbook which was downloaded into Excel, there are 5 columns and the data in Column A, B, D and E were repeated in 4 rows while
Column C is having different infomation in each row. The next sub set can be identify by the differences in the Ref .
I want to add a new sheet and insert 2 columns after Column B with header " SYMBOL " and " CODE "
and move the data in the 2nd row of column C in the sub set above it. the info from row 3 and 4 of column C be move to " SYMBOL " and " CODE " respectively.

I have attached a sample to show the original data in Sheet 1 and the result in Sheet 2

Can this be accomplish by formula?

regards, francis

2. ## Re: Format unique data into row (Excel 2003)

A2:
=OFFSET(Sheet1!\$A\$1,(ROW()-2)*4+1,COLUMN()-1)

B2:
=OFFSET(Sheet1!\$A\$1,(ROW()-2)*4+1,COLUMN()-1)

C2:
=OFFSET(Sheet1!\$A\$1,(ROW()-2)*4+4,COLUMN()-1)

D2:
=OFFSET(Sheet1!\$A\$1,(ROW()-2)*4+3,COLUMN()-2)

E2:
=OFFSET(Sheet1!\$A\$1,(ROW()-2)*4+2,COLUMN()-3)

F2:
=OFFSET(Sheet1!\$A\$1,(ROW()-2)*4+1,COLUMN()-3)

G2:
=OFFSET(Sheet1!\$A\$1,(ROW()-2)*4+1,COLUMN()-3)

Autofill/Copy A2:G2 down the columns.

[Note: This does not give your results since the pattern in the 2 groups are not the same. In one row you put the 4th item in col c and the 3rd in col D. In the other you had themn reversed....]

Steve

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•