re-arranging data

[asrivera]

hi all..

please help in re-arranging data. i post picture below for my work. i also attached the excel file.

i tried using pivot, but im not getting what i need..

thank you.

Attachment 651506

[AliGW]

Please update your forum profile with the version of Excel that you are using - what is pictured is definitely not from 1997!

[LouisTh]

I don't understand what you're trying to accomplish. can you clarify and be specific in your expected results please?

[asrivera]

hi LouisTh.. i am given the table on the right, where the data is arranged horizontally. I need to re-arrange the data in vertical format, such as the table on the left. I am working with 20,000 sites and i need to re-arrange data for upload.

Thanks!

Attachment 652273

[Glenn Kennedy]

Try this:

A3:
=IFERROR(INDEX($F$3:$F$5,MATCH(0,INDEX(--(COUNTIF($A$2:A2,$F$3:$F$5)=$G$3:$G$5),0),0)),"")

B3:
IF($A3="","",OFFSET(INDEX($F$3:$F$6,MATCH($A3,$F$3:$F$6,0)),,2+2*(IF($A2<>$A3,1,B2+1)-1),,))

C3:
=IF($A3="","",OFFSET(INDEX($F$3:$F$6,MATCH($A3,$F$3:$F$6,0)),,3+2*($D3-1),,))

D3 (helper - which can be hidden, moved, or even be on another sheet):
IF(A3="","",IF($A2<>$A3,1,D2+1))

[asrivera]

Try this:

A3:
=IFERROR(INDEX($F$3:$F$5,MATCH(0,INDEX(--(COUNTIF($A$2:A2,$F$3:$F$5)=$G$3:$G$5),0),0)),"")

B3:
IF($A3="","",OFFSET(INDEX($F$3:$F$6,MATCH($A3,$F$3:$F$6,0)),,2+2*(IF($A2<>$A3,1,B2+1)-1),,))

C3:
=IF($A3="","",OFFSET(INDEX($F$3:$F$6,MATCH($A3,$F$3:$F$6,0)),,3+2*($D3-1),,))

D3 (helper - which can be hidden, moved, or even be on another sheet):
IF(A3="","",IF($A2<>$A3,1,D2+1))

hi glenn.. thanks for the reply. i downloaded your file and pasted my data.. but the output table on the left is not exactly what im looking for. it seems only the first row is correct (green). the following data in red are wrong. the data in yellow did not appear on the output table.

Capture.PNG

[Glenn Kennedy]

I cannot tell anything from a picture. Please attach the file.

I have checked my file and it seems fine to me.

Did you adjust the ranges to the formula to include all of the data?