How to create vertical list based on horizontal instances

[escobf]

Hello, I have been trying to solve an issue related to creating vertical lists for which I am unsure if it is possible to do using formulas or simply macros. Basically my data is laid out as follows:

__ A___ B__ C__ D__ E__ F__ G_
1 Hotel_ .1
2 Office_ .1_ .2__ .3
3 Metro_ ___ .2_ _ _ _ .4
4 Bus___ .1_ _ _ _ _ _ _ _ _ _ _ .6

And I need to create a list as follows:
_ _ A__ _ B
1 Hotel_ .1
2 Office_ .1
3 Office_ .2
4 Office_ .3
5 Metro_ .2
6 Metro_ .4
7 Bus___ .1
8 Bus___ .6

[Alf]

Since I'm more of a "marco" man than a "formula" guy I've put together a macro.

Please Login or Register  to view this content.

Alf

[hamjam]

Hello escobf
Just require some clarification > I presume your layout refers to columns (A B C D E F) and data (.1 .2 .3 )

So you are trying to condense your original data into a 2 column layout.

I would try something like (excuse the messy code, I am tired):

Please Login or Register  to view this content.

You can change the n < 10 to be something different, to suit the number of columns that you have.

Regards

[escobf]

Thanks for your reply Alf, the code you provide looks very cool in resorting the data into the correct position (with the exception of K=1, which results in an error because it is undefined I think). However in doing some more research about my problem I stumbled across some code that may be a better fit:

Please Login or Register  to view this content.

Book3.xlsm
I am attaching a more detailed sample workbook of my problem, where the data in the CityA table of "DB2" sheet should be sorted into City1 table of "DB" sheet. Can the above code sample work to do this? I am working on modifying it for table ranges, but could use some input as to how it might look.

Thanks
-Felipe

[escobf]

Hello hamjam,
Your code works great! If you see my workbook example in my other reply message to Alf, you can get a better idea of how my actual data looks (as it is in table format). Given the other code I had found, your code is great because it skips entirely the need to create an additional column that counts how many "Scenarios" there are (which would only input the asset name X times and still require additional code to input the scenario numbers into the second column).

I will work on modifying your sample code to fit my tables, however any input is greatly appreciated!

-Felipe

[Alf]

exception of K=1, which results in an error because it is undefined

Yes that is right. Was playing around a bit with the code before I decided how to do it but forgot to delete the "k = 1" line.

On the other hand you got a working code from hamjam so your problem seems to be solved which is the main objective.

Alf

[AB33]

This code does not insert the ID nos. I think you can fill in them by dragging and no need for a code.

Please Login or Register  to view this content.

[escobf]

Thanks all, these are great solutions. I will have to modify these examples to my original file (AB33's code seems to perform the process the fastest which is good considering I need it for multiple tables), however my issue is solved.

[AB33]

You are welcome!
Could you please now close (Mark) this thread as solved? Go in to the top right-hand side of this page, choose "Thread Tools" from the menu, then select "solved" from the drop down menu.

[escobf]

Thanks all, these are great solutions. I will have to modify these examples to my original file (AB33's code seems to perform the process the fastest which is good considering I need it for multiple tables), however my issue is solved.