Transpose Array to Single Column

Hi Folks,

I'm trying to transpose an array of data into a single column. The array will eventually be around 30 columns wide by thousands of rows long, unlike the example that is currently set at 10x15. The formula I am looking for needs to list the row data in a columnar format as shown in column S and not olny that, it's needs to reference the respective ID number from Column A (see column T). The colour coding I have set up is pretty self explanatory.

Thanks in advance.

transpose2.xlsx

With the code below you can transpose the data, like you showed.

There where no data in row 1 from column H and further, so I added an formula.

It does not split the values of (e.g.) cell c6 into column (first).

Edit:

To split the data in column C, you could probably use text to column in excel 2007 and use ; (semicolon) as seperator.
Since I don't have excel 2007 available on the moment I can test that for you.

See the attached file.

Please Login or Register  to view this content.

Thanks for that oeldere, it seems to work very well. I did use the text to column function to extract the data in columns I:P.

Is there a way to do this without using VBA though, the people who will eventually use this document are better with formulas than coding?

Thanks once again.

You can use this formula. In I4 and pull it to the right until you see blanks.

=TRIM(MID(SUBSTITUTE($B4,";",REPT(" ",255)),(COLUMNS($A:A)-1)*255+1,255))

Nice formula Alkey! I'll definitely use that one as an alternative to "Text to Columns". Unfortulately my problem lies in transposing the data into a single column (see column S and T). I'm hoping there is a formula solution that can do exactly the same as what oeldere provided in his VBA suggestion.

Oh, boy! Sorry, I misread your requirements. Unfortunately, the only way to get it down would be with VBA. The solution offered by oeldere is exactly what you need.

Seems like I have to study up on VBA coding now . Thank you both for your assitance in this regard.

If you wouldn't mind, could you break down the VBA code for me so that I can get an idea of what it's actually doing. As mentioned in my first post the data I will be dealing with will be considerably larger and I would like to be able to customise the VBA code accordingly (i.e. change search range). I just tried adding a few extra pieces of data to the sample and the OutPut tab remained the same. Is there something I need to do to 'initiate/activate' the code so that it continually updates the OutPut tab?

Please Login or Register  to view this content.

general setting to the code.

Please Login or Register  to view this content.

code for making (and checking if the sheet Output exist) a new worksheet output

Please Login or Register  to view this content.

Please Login or Register  to view this content.

Please Login or Register  to view this content.

Hope I explained well enough.

Please reply.

Since you posted this in the Formula & Functions forum, I presume you were originally hoping for a purely formula-based solution?

That can be done, and is not overly complex, though with the size of your dataset it would certainly be best achieved using VBA, as you have been given.

However, if you absolutely insist on a formula-based solution (and I would't necessarily recommend it for the size of the dataset that you have) then let me know and I will take you through the steps.

Regards

I would be interested XOR LX, thank you. As you say I will probably have to use the VBA for this exercise, but I would definitely like to know how to do it for smaller data sets.

Thank you oeldere for the step by step guide, it does make a lot more sense now and I will have to play around with it some more to really understand it's working. A few questions though:

1. Should the Output tab update automatically every time data is changed in the source tab?
2. If I were to assign a macro to a text box, would this run the vba code that you set up and generate the Output tab?

I guess what I'm trying to figure out is, if the Output tab doesn't update automatically, how would I generate a new list every time the source data changes.

Thanks to all for your help so far.

1) only if you run the macro again.

2) ?

Originally Posted by jeversf

I would be interested XOR LX, thank you. As you say I will probably have to use the VBA for this exercise, but I would definitely like to know how to do it for smaller data sets.

Perhaps have a look at the technique outlined here and see if you can adapt it to your needs, should you wish to:

http://excelxor.com/2014/08/24/singl...-1-rows-first/

If you need any help with it let me know.

Regards