Format unique data into row (Excel 2003)

Topic

New Reply

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?

Thanks in advance for your help

regards, francis

Reply | Quote

Replies

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

Reply | Quote