i want to relocate cells in two columns convert to lay as two rows like i showed in the sample file.
its to easy for all of you, i know
i wait your responses my friends
i want to relocate cells in two columns convert to lay as two rows like i showed in the sample file.
its to easy for all of you, i know
i wait your responses my friends
1. Highlight your cells in column A. Click on Copy. In D1. Click on Paste Special-->Transpose
2. Highlight your cells in column B. Click on Copy In D2. Click on Paste Special-->Transpose
3. Highlight D1 to V2. Pres the F5 key and select Blank Cells. And Ok
4. The Blank cells will be highlighted. Right Click and select delete. Select Move Left.
Alan עַם יִשְׂרָאֵל חַי
Change an Ugly Report with Power Query
Database Normalization
Complete Guide to Power Query
Man's Mind Stretched to New Dimensions Never Returns to Its Original Form
or....
For the numbers:
=OFFSET($A$1,2*COLUMNS($A:A)-2,,,)
For the names:
=OFFSET($B$2,2*COLUMNS($A:A)-2,,,)
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
thanks a lot for ur responses guys
but i couldnt apply ur methods to my original sample file
can u have a look at it pls ?
All you had to do was alter the starting cell reference to the one used in your real sheet.
column B
=OFFSET($B$2,2*COLUMNS($A:A)-2,,,)
column M
=OFFSET($M$3,2*COLUMNS($A:A)-2,,,)
oke super formula, Glenn
i edited the formula next sheet also
thanks a lot Glenn
U RE %100 ORIGINAL GURU, not from china
Last edited by aaaaa34; 08-08-2016 at 12:04 PM.
You were nearly there. the ' and the $ were mixed up. there's no need to refer to the other sheet for the columns bit. That's just a counter that increments the copied cell by 1 column as you drag it across (2 columns after multiplication by two)
=OFFSET('2016-05-09'!$M$3,2*COLUMNS($A:A)-2,,,)
daaaa, da Glenn!!!!! i found it!!! but i could edit my post ater u sent
U RE KING OF EXCELFORUM.COM!!!
Hardly that - but thanks for the Rep. Appreciated!!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks