LinkBack URL
About LinkBacks
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
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
Please update your forum profile with the version of Excel that you are using - what is pictured is definitely not from 1997!
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
Forum Rules (updated August 2023): please read them here.
I don't understand what you're trying to accomplish. can you clarify and be specific in your expected results please?
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
Last edited by AliGW; 12-02-2019 at 01:49 AM.
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))
Glenn
None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.
Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh
Originally Posted by Glenn Kennedy
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
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?
Last edited by Glenn Kennedy; 12-10-2019 at 03:57 AM.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks