Hi
I have a spreadsheet that looks as below
Zone W1 Value Z1 Value ......
a 100-200 .12 150-300 .13
b 150-170 .14 170-190 .15
c 200-300 .17 500-600 .19
I need to format it so it look like this
W1 a 100-200 .12
W1 b 150-170 .14
W1 c 200-300 .17
Z1 a 150-300 .13
Z1 b 170-190 .15
Z1 c 500-600 .19
Is there any easy way to make that change.
Thanks
Please upload a sample....your column are difficult to read as text above. (but you should be able to make formula using absolute and relative cell references.)
Hello AF_Z. Welcome to the forum.
If you do not know how to upload a file directly to this forum:
To attach a file to your post: (Please no pics or screenshots ... saves retyping data.),
be sure to desensitize the data
- click “Go Advanced” (next to Post Quick Reply – bottom right),
- scroll down until you see “Manage Attachments”, click that,
- click “Browse”.
- select your file(s)
- click “Upload”
- click “Close window”
- click “Submit Reply”
The file name will appear at the bottom of your reply.
Dave
Thanks I managed to attach a sample file with 3 sheets original_data_1, original_data_2 and Required_Result.
I am trying to organize the data in the original_data sheets to be the same format as in the Required_result sheet and it
very hard to do manually as there is a very large amount of data.
Thanks for your help
In A2: ="W"&1+INT((ROWS($1:1)-1)/9)
In B2: ="Zone"&1+MOD((ROWS($1:1)-1),9)
In C2, drasgged across to D2: =VLOOKUP($B2,Original_Data1!$A:$C,COLUMNS($A:B),FALSE)
In e2: =VLOOKUP(B2,Original_data_2!$A:$C,3,FALSE)
Drag down as far as needed.
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
Amazing! Thank you very much. I guess the only problem and this is my mistake is that the names and zones are more complicated they are not w1,w2 and zone1,zone2. I am attaching a sample with names and zones that change and do not have a pattern.
Hope this can be done.
Assuming the zone names are the same on both sheets (you'd changed only on sheet 1):
=INDEX(Original_Data1!$A$2:$A$10,1+MOD((ROWS($1:1)-1),9))
You're welcome.
If that takes care of your original question, please select "Thread Tools" from the menu link above and mark this thread as SOLVED.
It'd also be appreciated if you were to click the Add Reputation button at the foot of any of the posts of all members who helped you reach a solution.
Yes they are the same Really Thank you very much!
Great! glad to help.
If that takes care of your original question, please select "Thread Tools" from the menu link above and mark this thread as SOLVED.
It'd also be appreciated if you were to click the Add Reputation button at the foot of any of the posts of all members who helped you reach a solution.
I am so sorry but there is still one piece that is not working for me which is the name it is not w1,w2,w3.. it does not have a pattern it goes like e.g w1,x2,z4 as in the attached file.
I wonder if we can reference it as well.
Thanks again.
In A2:
=INDEX(Original_Data1!$B$1:$F$1,,1+2*INT((ROWS($1:1)-1)/9))
Great! Thank you again very much
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks