VBA code - I just can't work it out

[Happytab]

Hi guys, a while ago someone on here kindly gave me the following code which solved a requirement I had to essentially un-pivot a data table.

What I now need the code to do is to keep the first five columns but un-pivot the remaining 12 columns so that each row of data has first five columns from the original data and then the column header of the remaining columns followed by the data in the corresponding cell.

The code below just gives me the first two columns and then the column header and the data and I have spent hours trying to work out what to change to gert the first five columns plus the data.

If anyone can help me out i'd be so grateful - here's the code

Please Login or Register  to view this content.

Thanks for your time and patience

[jindon]

Hi guys, a while ago someone on here kindly gave me the following code

No,it is not what I posted.

https://www.excelforum.com/excel-pro...ls-in-row.html

[Happytab]

No,it is not what I posted.

https://www.excelforum.com/excel-pro...ls-in-row.html

Sorry Jindon, I thought I made the changes you suggested in the original thread but I apologise if I offended you in any way.

I'm just really confused and frustrated right now.

Again, I apologise.

[jindon]

What you are asking here is not complicated.

If you upload a workbook showing before/after someone will give you a solution.

I'll go to sleep soon.

[Happytab]

Heres a sample of what I need with before and after. Thanks again

[Zer0Cool]

So after looking this over, its prudent to point out this has Nothing to do with pivot tables.

Essentially you are looking to transpose columns of a record into additional rows/records.

IE: record1 col1 col2 col3

becomes:

record1 col1
record1 col2
record1 col3

As mentioned not terribly hard, but there are many ways of doing it. Also, as I am sure you are aware, this will result in many more columns than you had in your original data (and less columns). How many rows is your actual data? Do we have to worry about ending up with more rows than is on a single sheet?

Also judging from your sample does every record have all 12 stages. In other words will each record become 12 records then or is it possible it will vary (one record may only have 3 stages, another 6, some 12, etc)?

[Happytab]

So after looking this over, its prudent to point out this has Nothing to do with pivot tables.

Essentially you are looking to transpose columns of a record into additional rows/records.

IE: record1 col1 col2 col3

becomes:

record1 col1
record1 col2
record1 col3

As mentioned not terribly hard, but there are many ways of doing it. Also, as I am sure you are aware, this will result in many more columns than you had in your original data (and less columns). How many rows is your actual data? Do we have to worry about ending up with more rows than is on a single sheet?

Also judging from your sample does every record have all 12 stages. In other words will each record become 12 records then or is it possible it will vary (one record may only have 3 stages, another 6, some 12, etc)?

Hi, thanks for your reply.

In this case I have about 3,000 records in the crosstab - and each record has twelve stages - so I will end up with 36,000 rows

The problem is that more customers will be added toi the list and I will need to keep doing this conversion as part of other processes which is why I need to work out the vba code to do it.

Like I said originally, the code above nearly works but iinstead of columns 1 to 5 plus the data I get column 1&2 plus the data.

I just can't work out what to change in the code to get the desired result.

thanks again for your help.

[Happytab]

OK finally worked it out - thank both for your help.

Code that finally worked is below - if anyone else happens accross the same issue.

Please Login or Register  to view this content.

Thanks again

[protonLeah]

Your post does not comply with Rule 1 of our Forum RULES. Your post title should accurately and concisely describe your problem, not your anticipated solution.

Use terms appropriate to a Google search. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will be addressed according to the OP's experience in the forum: If you have less than 10 posts, expect (and respond to) a request to change your thread title. If you have 10 or more posts, expect your post to be locked, so you can start a new thread with an appropriate title.

To change a Title go to your first post, click EDIT then Go Advanced and change your title, if 2 days have passed ask a moderator to do it for you.

(This thread should receive no further responses until this moderation request is fulfilled, as per Forum Rule 7)