LinkBack URL
About LinkBacks
I am in need of a macro that takes my data as depicted in the spreadsheet and A-D constant whiles transposing the months horizontally to vertically.. as on desired output tab. any help will be greatly appreciated.
Excel VBA to copy and paste from horizontal to vertical format
I am in need of a macro that takes my data as depicted in the spreadsheet and A-D constant whiles transposing the months horizontally to vertically.. as on desired output tab. any help will be greatly appreciated.
Hi ennin12
Originally Posted by ennin12
. This looks very easy. But could you please reduce the test data in your Raw data sheet so that there are about only 20 rows in the Desired output sheeet.
. Then I will do it for you later if no one else does it for you
Alan
'_- Google first, like this _ site:ExcelForum.com Gamut
Use Code Tags: Highlight code; click on the # icon above,
Post screenshots COPYABLE to a Spredsheet; NOT IMAGES PLEASE
http://www.excelforum.com/the-water-...ml#post4109080
https://app.box.com/s/gjpa8mk8ko4vkwcke3ig2w8z2wkfvrtv
http://excelmatters.com/excel-forums/ ( Scrolll down to bottom )
Try the attached.
Wow!!!!
. you make it look so easy, Respect!
Alan,
Thank you!
deleted .
Surround your VBA code with CODE tags e.g.;
[CODE]your VBA code here[/CODE]
The # button in the forum editor will apply CODE tags around your selected text.
Hi ennin12
. ... here is my attempt.. 5 Hours later..
. 1) I wanted to be clever and do a splitting , slicing, chopping of Array thing to reduce Looping.. But then had to interact with the sheet to make some help columns so did away probably with any advantages then anyway.. But it is another version..
. 2) As always with my codes there are some unnecessary or and / or repeated steps, and dimensioning to help me see what is going on and make the Code more adaptable for other requirements ( for my benefit if no-one else’s!!)
. 3) My reasoning was to produce a stack of Arrays, one for each date then paste ‘em out one after the other.. ( The method for getting those Array using the .Index Function I am still struggling to understand..
http://www.excelforum.com/excel-new-...ba-arrays.html
.....)
. 4) I struggled a bit with the date format ( as often ! ), so in the end copied AB33’s as am sure he knows what he is doing.
. Here is the code. It appears to give the required results
'
Let us know please how you get on.
Please Login or Register to view this content.
Alan
...........................................
P.s. 1 I am sure a formula could do this. ( But I cannot do it! )
.................
P.s. 2
@ AB33 if you happen to pop back here:
. Can you explain the & bits in your line
. Dim X, Y(), i&, j&, k&
. – has something to do with Dimensioning but it is a bit subtle I think. I did follow a Thread where that was discussed, but then JBeaucaire came in the Thread , got a bit upset with the OP’s ideas about that way of dimensioning and so deleted some of the posts, as he does....!!..( No criticism of him, - his contributions I find amazing like most of you regulars..)
Last edited by Doc.AElstein; 08-28-2015 at 05:57 PM. Reason: Just Wnking Awrays
@Doc.AElstein
The ampersand (&) type-declaration character represents a Long.
The percent sign (%) type-declaration character represents an Integer.
The at sign (@) type-declaration character represents Currency.
The exclamation point (!) type-declaration character represents a Single.
The number sign (#) type-declaration character represents the Double.
The dollar sign ($) type-declaration character represents a String.
Hi joly vanes
. Thanks very much for that extra info,
. I seem to remember some discussion in the Thread I mentioned in the last Post, Post #7, that there were some additional subtle differences associated with using this “short hand” over the full
Dim str as String … etc.
Method. Something to do with how Optional Explicit works ( or doesn’t ) I seem to remember. Unfortunately I have “Lost the Thread on That”.
. But that extra info from you is helpful should I stumble across a discussion again in that Area.
. ( And I shall, as ever, experiment a bit and see if that reveals anything..)
. Thanks again, appreciate the response there
. Alan
Interesting challenge here! Though I can't say for sure, it looks like you're trying to whip this data into shape for use with a pivot table, which makes a lot of sense.
I wrote an in-depth post on how the code below works, with tons of images and illustrations, here:
How to Transpose Horizontal Data to Vertical Data for Easy Pivot Tables
Before you drop the following code in, you will need to enable the
object, which is available via the "Microsoft Scripting Runtime" in Tools > References:Please Login or Register to view this content.
enable_microsoft_scripting_dictionary.png
Once you have enabled the "Microsoft Scripting Runtime", the following should do the trick!
Please Login or Register to view this content.
VBA demystified:HTML Code:
. Hi Dan Vwangoer@co..!?
. I am must say I am totally astounded to your in depth, (presumably spontaneous and quick done ? ) follow up to a seemingly unobtrusive almost “dead” Thread which I “dug out” in a search of Unanswered Threads a couple of days ago…
.
. I confess as a Beginner learning both Excel and VBA Basics simultaneously, I have avoided the mystery of Pivot Tables like the Pest.
.
. But as you have done such an amazing follow up to a Thread already benefiting from Multiple answers, I am inspired now to go throw all your supplied material ( here and elsewhere) as I find it the best way to learn when I see a different solution to a Thread which I have struggled to answer, as I did here.
. I note you do not achieve the original requested output of the OP, which was taking data of this form
Using Excel 2007
Row\Col Area Dist Parent Retailer Product Retailer 1 XYZ Retailer 2 ABC Retailer 3 DEF Retailer 4 CEF Retailer 5 XYZ
Raw Data
.. and returning 3 main sections, one for each date of this form
Using Excel 2007
Row\Col Area Dist Parent Retailer Product Date Volume Retailer 1 XYZ Retailer 2 ABC Retailer 3 DEF Retailer 4 CEF Retailer 5 XYZ Retailer BBC Retailer 10 CNN Retailer 1 XYZ
Ops After
Resulting in three main tables which he then wanted stacked one on top of the other.
…………………
.. you, on the other hand have returned this
Using Excel 2007
Row\Col Area Dist Parent Retailer Product Date Volume Retailer 1 XYZ Retailer 1 XYZ Retailer 1 XYZ Retailer 2 ABC Retailer 2 ABC Retailer 2 ABC Retailer 3 DEF Retailer 3 DEF Retailer 3 DEF Retailer 4 CEF
Tabelle4
..
. I expect the point you are suggesting is that your form lends itself nicely to achieving the desired results then through a Pivot table ( And presumably my and AB33 output, which matches fully the wishes of the OP does not ?? ). I guess that would be obvious to me if I had the slightest idea about Pivot Tables!!
. Interesting unexpected input of yours!!
Alan
P.s. 1 ) just out of interest. Did you Copy the OPs Test Data, or did he Copy yours???
P.s.2 ) I am a great fan of Line numbers and as you demonstrated by you, it is very helpful when explain code. Do you know how I could copy your code into the VB Development Window whilst preserving your line numbers, that is to say copying them over with the code. Or could you re-post the code with the line Numbers? I am keen to examine your code in more detail, and understanding it to compare with our Methods and possibly learn something new.. I do understand the various uses of the Microsoft Scripting Runtime Dictionary. But at first glance I do not see the advantage over an Array method such as that from AB33 and myself. ( But it is late now here! )
. Thanks again for this surprise Follow – Up.
Hey @Doc.AElstein --
1. Thanks for the kind words. This post posed an interesting question, that's all!
2. You are correct in that my solution (as posted) does not achieve the originally requested output. It appears that the original poster wants the data sorted by the "Date" field, which could be achieved using the two techniques in this 1-minute video:
Whipping the Scripting.Dictionary results into shape
First, sort the "Date" column by date (from oldest to newest). Second, apply a custom format ("mmm-yy") to the column. Nice!
To complete this within the macro, you could adjust my response like so:
This is the new code (on its own):Please Login or Register to view this content.
PS1: I copied the OP's test data.Please Login or Register to view this content.
PS2: Unfortunately, the VB Development window does NOT support line numbers. When you want to see the line numbers, I suggest using a programmer's text editor (like Notepad++ or Sublime Text 3) or a Github's amazing paste-service, Gist:
The script from above with line numbers courtesy of Github's Gist
Hi Dan, “you Mega Blogger”
.1 ).. Amazing feedback with follow up alternative solution again Dan ( used in conjunction with your first code ) , Respect!
. I guessed there should have been a simple Excel Spreadsheet thing to do that. You saved me looking. And as always, great to see alternative solutions in a Thread. I learn the most in such Threads.
.
. 2) Re You First Code Solution in Post # 10 ( and the Blog thereof ).....
http://danwagner.co/how-to-transpose...-pivot-tables/
... I have worked through that now and understand it, thanks to your very clear Blogging of it. ( I still prefer having a lined code version in my VB Development window as I step through the code with F8 while reading your Blog. – It occurred to me a very simple way to achieve that. I Copied the Code from your given “Notepad++ Type” version into excel in the next column to a numbered column. -Then made another column made from joining those two and copied that final Columninto the VB Editor.
Using Excel 2007
15 With wksOutput 15 With wksOutput 16 .Cells(1, 1) = "Area" 16 .Cells(1, 1) = "Area"
Using Excel 2007
15 With wksOutput =H21& " " &I21 16 .Cells(1, 1) = "Area" =H22& " " &I22
.. a few quick tweaks were needed where the format did not quite come out right, but VBA kindly highlighted those bits in Red. It all Saved a bit of time over manually typing the numbers in. I posted the code here, in case anyone like me likes numbered Lines
http://www.excelforum.com/developmen...ml#post4175683
...................)
.. Anyways it made following with your explanations very easy. The code is very inefficient compared with the other codes given as it loops so much and keeps interacting with the spreadsheet. , BUT THAT IS NOT THE POINT: It is a very nice demonstration of the Microsoft Scripting Runtime Dictionary. MSRD, , as it makes use of both keys and the items in a meaningful way. ( And very refreshing to see it used in some other way than as often in VBA, just using it’s by- product, the unique thing, to get unique lists etc.. )
. ..so if I got the point:
. a) . an outer "Rows Loop 1)" Loops as many times as input data rows and a further inner "Rows Loop 2)" Loops as many times as there are dates. This gives the total number of output Rows required. So far mirrors AB33’s simple code ( just his inner is your outer , his outer your inner..)
. b ) Then instead of just selecting appropriate parts of the Input sheet as AB33 did ( or rather he selected from an Array made from that sheet ), you go off in an extravagant unnecessary but very interesting diversion to make a nice demonstration of the use of the MSRD:
. For each “row Loop” you make 2 fresh directories, the keys being the headings and the items the row values. One of these is for the first 4 columns, the other for the last N columns, where N is the number of dates, ( 3 in the example ).
. The “structured use of the MSRD idea” then is to mostly use the Items for the required output columns except for the case of the Date where you select the key which in the “real world” is the heading - that sort of ties up, both with a “Dictionary” and a MSRD
A Dictionary is made of ..... Key: = “heading” or “part number” or “identifier or “Name” , Item: = “the thing” or “value” or “Wot it is”
.
. So basically the code does a nice structured utilising of the keys and the items. Albeit extravagant and very long winded. But I like it: . ( I tried to do the same with a code to reorder a range item list using the MSRD , post #9
http://www.excelforum.com/excel-new-...t-reorder.html
.. but i did not get to really get to demonstrate it as nicely as you. And i was using it again partly for it’s “unique” by - product thing.
.............................................................
. 3 )..... Thanks for adding that as well, nice further contribution.
Thanks Dan
Alan
p.s 1. Just for fun and to confuse the issue even more, I did a modified version of your code using Arrays rather than interacting with the spreadsheet for all input data, and even managed to slip in my “pet thing at the moment” (......Application.Index with Look Up Rows and Columns Arguments as VBA Arrays
http://www.excelforum.com/excel-new-...ba-arrays.html
.....) - which is what i used (almost exclusively) for my code solution ( Post # 7 .. Sub ChopStack() )
The code for that modified version of your code is here
http://www.excelforum.com/developmen...ml#post4176080
( Note I had to do a modified Function as well . This is because retrieving “row” information from an Array gives a 1 Dimensional Array compared to what you get when accessing a Spreadsheet row, which is a 1 Row 2 Dimensional Array )
P.s.2 A formula solution would be nice to see, I kinda guess it must be possible . I can’t do it ( yet! ).
wow.. this is so awesome.. if there were a Nobel peace award in excel.. you'd be a prime candidate. Thank you
. Gald you liked all our efforts
. Thanks for the Feedback
. Alan
@ jolivanes ( or AB33 )
_ Re Posts #7 and posts #8
_ Just in case you are still subscribed.. or for anyone else lurking through this popular Thread..
_ Just noticed in passing in experiments here
http://www.mrexcel.com/forum/excel-q...rdinates.html?
that the $ does not seem to work at all and an attempt at putting % on a small whole number fails with VBA knocking the % off and dimensioning it as an Integer anyway, and similarly that sort of behaviour continues if VBA “thinks it knows” what the Type should be. Sometimes it takes the qualifier off, sometimes it puts the qualifier on !! All very confusing!
Please Login or Register to view this content.
"$ does not seem to work at all"
is a variable type, nothing else. It is a lazy way of saying that the variable type is a string. You can use the full type name "String" instead of $. A variable is a storage box in the memory. The box has different sizes.
Hi,
?- Did not quite follow..
_ my point about the $ was that it did not appear to work at all.
_ Or I was applying it incorrectly?.
_ ................
_ I agree ="______" seems to work to tell VBA that what is in between the "___" is to be taken as / dimensioned as, a String.....
_ That ties up with my code line
Let v = "3" ' v becomes Variant Type with String Type value 3 in it.
_ This last point was elaborated a bit more in the Link I gave
http://www.mrexcel.com/forum/excel-q...ordinates.html
and here I didthat point a bit to death just recently as well..
http://www.excelfox.com/forum/f2/spe....html#post9517
Thanks for the Reply
Alan
Unless you tell VBA to store a box for a string, it will treat the data type as variant. Variant behaves as it names implies-varies.
Where in the code you have declared the variable type as a string?
Hi.
_ Sorry AB, I probably explained myself badly ( was trying my best to shorten a bit my rambling sentances!!!!)
_ I think we are talking about slightly different things.
_
_ I appreciate that you are trying to tell me the very basics of Dimensioning , Variants, Strings and such. Thanks for taking the time.
_
_ But I do just about understand all that basic stuff.
_
_ In the code in Post # 16 and in the other Threads I referenced i was taking it a bit further ( or maybe backwards!! ) , and in particular looking at the ( “lazy” as you called it ) way of dimensioning as an alternative which jolivanes gave in this thread ( Post #8 ) and which was at the time new to me....
_
_ I had just noticed in passing that the lazy $ way did not seem to work at all, I mean syntaxly VBA errored it. I also noted a few other interesting characteristics about this lazy method
which I also demonstrated in that demonstration code in Post # 16
_
_ So the main question again was simply: I can get most of the Lazy things to work, ( albeit with the interesting occurrences i showed in that code in post # 16 ), but not the one using the $
_ These lines I cannot get to work ( yet ) no matter how I Dimension anything or whether i do Option Explicit or not, etc. Etc....
v = Text$
v = 3$
_ Not a big deal, I was just curious. It would appear that
Does not work in VBA. Maybe it is a VB thing or just ( as i remember it ) an old Basic thing
_ .... Let A$ = "fgfg" is something i did a lot the last time i did any real programming - which was about 25 years ago before i got my first pc a couple of years ago and started learning VBA ( I had nothing at all to do with computers in the meantime, that is why i miss the point sometimes, - sorry about that )
Thanks again
Alan
Alan,
This is my intake. You can not use a number as a variable name, so the second line (v1) is wrong. If you have used
Option Explicit, VBA will also ask you to declare variables V and V1. The lazy way is NOT v = text$ type, but Dim text$,. I have seen people use this type of expression, but personally I do not think it is a good coding practice and prone to errors.
While I was learning VBA, veterans of VBA reminded me to declare all variables by forcing using Option Explicit. At first I ignored this plea but at a greater cost, hence I have the developed the habit of declaring ALL variables. I do not know and use this type of expression-v = text$, I declare all variables using a dim at the top. I hope this helps.
Please Login or Register to view this content.
I now see what the issue is. You want VBA to accept this
v = Text$
as a string.
This is not declaring a variable rather it is an expression. If you have used Option Explicit, VBA will not let run this expression.
Hi AB33.
_ No not really. ( I probably confused the issue a bit again, v = Text$ was a final long shot idea - ) - I really wanted to know why when v=3 v= 3# v=”3” v = 3@ v = 3! are all acceptable (My demo code post #16 ) , then why is v=3$ not... that is still a ( minor ) mystery..
BUT
_ I think you have got me more or less there, or at least a lot further......
_ there seem to be 3 things going on here.
_ 1) The “normal” or “sensible” or “usual” way of Dimensioning things ( necessary with Option Explicit )
Dim SS As String
Dim L As Long etc. etc.. Etc...
_ .....................................
_ 2) The “ Lazy “ way.
_ I demonstrate this in the Code lines below.
_ I would not call this the lazy way. (It seems to do exactly the same as the “normal” way. I cannot see any difference. ( yet ).). Stands to reason that it is not “Lazy”. - You are not lazy. It was you who originally sparked off my curiosity ( - my original question at the end of Post # 7 ) by your use of it in Post #3.Please Login or Register to view this content.
_......................
_ As for the Third thing ..... - that just seems to be some obscure thing I stumbled across or invented by ignorance. It is a method to define the type of Variable that is held within a variant variable. It works using all the “qualifiers” & % @ ! and # ( with the restrictions discussed in my demonstration code in post # 16 ) . For some reason it does not work using the $ , but you can use “____” instead for that. I shall Patent this idea as the “Alan Method to determine the type of variable placed in a variable of variant Type.”I shall become very rich, and amongst other things will start a new Excel Forum where Threads of my rambling nature shall be encouraged
-( sorry i could not find a falling over backwards laughing Smiley!! )
Thanks again for the inspiration!
Alan
Last edited by Doc.AElstein; 09-20-2015 at 07:08 PM.
Alan,
VBA uses a short hand for data type-FULL STOP, but you seem to dig deep down to find what?
Yes, it is true, some data which appear to be numbers not to be treated as such by VBA and it might be the same for strings and other data types. That could be the reason for VBA to include functions which can convert one data type to another. It is similar concepts like date in excel. Excel might recognise 13-12 or 13/12 as dates or it might not- it depends on the context of the data type. As far I know the short hand (Lazy) only work with dim statement.
Dim L& 'This is short hand data type dim, but below this line as I said on my previous post, you are trying to extend this lazy way in to initiating a variable and expression. But this is not I am not sure works. I personally do not use, the lazy way out side the dim line. That is where the confusion lies.
Let L& = 3 ' L& ( and L ) becomes a variable of Type Long with 3 in it
Let v = L& + L ' v becomes a Variable of Variant Type with Long Type in it of value 6
Let v = L& & L '
Last edited by AB33; 09-21-2015 at 10:58 AM.
Thanks for coming back AB,
_ So are we are a bit in agreement, maybe, with this...
The _ 2 ) ( The “Lazy” Method ) is probably better ( or just as well ), described as you say, a “Shorthand” for the “normal / usual “ method _ 1 ) .It actually achieves the same At the Dim Stage and is perfectly OK to use as an alternative to the more usual way At the Dim Stage...?? ( as indeed you do )
( This shorthand appears, as you say , just available for data Type, including $ for String )
_3) In My initial attempts, around Post # 16 I was probably simply doing the shorthand incorrectly...
And as you say....
..
_ Agreed, that is where I went off track..
and so in Post # 23 I sort of corrected myself saying that what i was actually doing in Post #16 (by accident / ignorance ) was my obscure idea of( _ The spanner in the works that throw me off a bit there was that in my “method” for some strange reason the $ does not work, the = "___” being the alternative, for my Method _3 ) ).
For method 2) the shorthand $ is indeed acceptable as my code in Post #23 demonstrates.. )
_ Does that all make sense.... ?
Thanks again
Alan
Alan,
I do not know if what you are trying to do is documented in VBA help. I have seen people use this kind of coding, but I have to switch off Option Explicit to test this sort of code as every single data was not dimensioned. Assuming declaring all variables is a good coding practice, using a lazy method other than on the dim line will be a non starter.
Sub test()
v = text$
v1 = 3$
End Sub
The code might or might not do what is supposed to do, but V,V1,text$ and 3$, need declaration and if you dim them what is the point of using text$ if text is dimmed as a string. The only time I use the use this kind of statement is to convert a data from one type to another.
Since you are digging deep down and trying to get the bottom of VBA
Last edited by AB33; 09-21-2015 at 01:29 PM.
. I doubt it !!!!!
_ ..................................
Hi AB,
_ I think it sounds as though we are in agreement, and thanks to your help I think i have done this one far enough even for my "obsession" with "digging deep down and trying to get the bottom of VBA"
_ ( I have strained my Brain a bit too far.. - If any Pro out there can add , then great..But I fear no one knows further, and time and time again very experienced people still say to me it is down to experimenting ! ( scares me sometimes that ...
http://www.mrexcel.com/forum/lounge-...ml#post4080745
_ ...)
_ ..............................
_ I think your code above will not work even without Option Explicit.
_ This bit
v1 = 3$
_ is attempting my Method _3) and as mentioned $ is strangely not available to that method, - ( my alternative ="__" must be used
v1 =3%
is OK but defaults to the default integer so
VBA changes to
v1 = 3
_ All the others, v1 = 3& etc.. etc.. are OK ... That all explained again in my Code in Post # 16..
_...........
It would apperar that as $ is not available to "my method" then the line
v = text$
just behaves the same as
v3=text
_...............................
For this ( which only works without Option Explicit )
Sub test()
v = Text$
v3 = Text
End Sub
v and v3 become as we expect Variables of Variant types with something in them.....
_ ... and that something is a variable of Type string, as Text$ and Text as separate Variables become Type String.
_ VBA is , I find, often “kind” with converting “strings” to what you want, so I often Dimension so, even when I have numbers.......
http://www.excelforum.com/excel-gene...ml#post4195694
_ .. but i am getting deep down again, and my brain is hurting, so i will stop !!
_..................................
_ effectively, I think , once VBA sees what is obviously Text then it does not attempt "my Method" , knowing there is no textAlan Bit
_ For a number on the other hand, it thinks there could be one of the Alan Bits, so looks... and then VBA gets upset to see the $, rather than a valid "Alan Method Bit"
Thanks again
Alan
Last edited by Doc.AElstein; 09-21-2015 at 02:34 PM. Reason: Helping Kyle's constipation http://www.excelforum.com/the-water-cooler/1103850-extra-space-or-spaces-in-text-in-forum-post-bb
Hi Alan.....
...... you forgot maybe the Posts starting from about here....
http://www.excelforum.com/excel-new-...ml#post4083690
.. Although as you noted it is badly defined and behaved the $ is a way of telling things that belong to the Strings Object collection to do less, and work quicker. This is because you effectively tell them they should be expecting certain arguments as strings, or similar. ( As you noted that would increase the likely hood of errors occurring should your arguments be something that the “quicker” process can not handle. ) What you “discovered” may be just some old “Throw-back” thing to old basic codes or wot ever, and the people who introduced using the $ for use as I just described here and in those earlier referenced Posts may have forgotten about it. Hence they used it and unwittingly “removed” it from its previous use.
_.. hence your “anomaly” that it was not available to your “discovered new” thing. – In fact what you “discovered” were probably just the relicts form some obscure old thing that everyone sane forgot about. But never the less it could be useful. Or Not
_. Hope that clears that one up a bit
Alan
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks