Transpose values in each row into the same column

I am so grateful for your response and the code works.

However, I am having a hard time trying to understand the bulk of the code as I am new to using arrays and scripting.

Would you mind walking me through the following code please?

Please Login or Register  to view this content.

So the application.index creates an array?

what does the "2" mean in this code?

Please Login or Register  to view this content.

The 2nd dimension? How is the 2nd dimension defined?

If you don't mind walking me through the above code line by line, it would be great. I need to understand this more.

Thank you

Hi smu,

It has occurred to me that there are two columns of strings that need to be split. Please see the attached file.

So now the difference is that values in col B and C in the "Original" sheet need to be displayed vertically in respective column.

The difficulty is that.....sometimes col C contains rows with more tests than there are in col B, and sometimes it's the other way around.So I think the number rows that are later displayed under each ID really depends on the largest number of tests done from either one of the two columns, right?

Can a similar macro get the job done easily?

I had thought that was the final solution, but it has just occurred to me that there is this extra bit that is needed.

Please, any help is much appreciated.

Thank you

Anybody able to assist?

Anybody able to help please?

hi Lifeseeker, if I undestood everything tight, please check attachment, press a button

Hi Watersev,

It works out like a charm. Thank you.....

I need to fully understand your code.So if you don't mind walking me through, starting on

Please Login or Register  to view this content.

that would be great..I'm really struggling to understand how it works, and yet it works....


Thank you

Please Login or Register  to view this content.

Hi thank you.

What does the

Please Login or Register  to view this content.

do?

In the

Please Login or Register  to view this content.

line, the "1 to 3" refers to column A B C in "output"? Also, if the result had not been initialized beforehand, can we just redim it?

what is

Please Login or Register  to view this content.

? And what is the 1 referring to? First column of which range? What does this statement really do?

In the split() function, I understand that we are splitting the strings by ",", but why do we have so many "," in the code?

why do we need

Please Login or Register  to view this content.

Why are we substracting on the counter?

Please Login or Register  to view this content.

Sorry as you can see, I'm really just learning from the basics of the basics...but really appreciate your time with me on this.

Hi Watersev,

I seem to be getting the logics of the code. Few follow-up questions:

When we use ubound() in this case, what does ubound() refer to? Is it the upper bound of data? If so, is it the last item in the array??

For the splitting string and replace part of the code, I was wondering why we had to have so many ", ", ", ", " in the code. Why can't we just write

Please Login or Register  to view this content.

Is this just how the syntax is?

And does it matter where we put

Please Login or Register  to view this content.

?

The code sounds like at the end of the loop, we are assigning whatever result equals to the range. Right?

Thank you

1. As per VB help file correct syntax for replace function is:

Replace(expression, find, replace[, start[, count[, compare]]])

underlined items are optional and can be omitted. So here we have:

Please Login or Register  to view this content.

expression - data(i,2)
find - ", "
replace - ","

2. Yes it matters where the "sheet output" line is as initially the result array is empty until you fill it with values.

3. Ubound - Returns a Long containing the largest available subscript for the indicated dimension of an array.
Run the following:

Please Login or Register  to view this content.

Thank you Watersev for your great help. You are my lifesaver.

See you on the forum!

Great day

Hi Watersev,

If I wanted to just display the finished result in the original sheet, do I just change the destination "Result" to "original" on the last line there?

Please Login or Register  to view this content.

Also, what if there are additional columns after C in the original worksheet that need to stay matching to the ID column after the breakup operation?

Do I need change much to the existing code to make what's in col D and E move as well? They don't need to be broken up, but they need to be matching to their original ID.

Are you able to shed some light?

Thank you

unfortunately when I try to open your attachment from post #15 my Excel crashes so I've made changes "blindly" as for result you need, please check

Yes, you can put result anywhere changing this line:

Please Login or Register  to view this content.

Hi Watersev,

While the code does work, it duplicates col D and E values for every ID that gets transposed down.

Is there any way to just reference one status and location for EACH ID?

I guess I can just do a vlookup on the result sheet against the original, but this will require me to perform the breakup operation and paste the final result in a separate worksheet.

Any ideas? Please see the attached.

Thank you

i've made it blindly as I did not see your expected result, please check attachment

Nice.....

That was a very easy step apparently.....So I think this:

Please Login or Register  to view this content.

basically does the value assignment for referencing status and location to each ID. Right?

Thank you very much Watersev. You are the best.

you are absolutely right, that's it

Hi Watersev,

A small twist perhaps, but I think this doesn't involve too much code change. Now we actually have 3 columns of strings that need to be broken up and transposed down.

I think where the code says

Please Login or Register  to view this content.

needs to be changed to pick the one array from the 3 with the largest number of subscripts. Right? I am not sure if the iif() will do the trick now now as I think about.

Maybe some kind of boolean test will do in this case? find the largest? or max()??

Thank you.

please check attachment

Thank you...

you are welcome