Transpose large amount of Rows by multiple Columns

[noboffinme]

Hi Forum,
I have a worksheet of data comprised of 3 fields of Values plus a 4th Column containing the Figure Headings and a 5th with the actual Figures for those fields.
I've attached a sample worksheet to show the before and after.
There are about a full worksheet (1,000,000+) of rows to transpose.
I have a couple of examples created by jindon which are great, but now I need to handle more data as I am getting errors when running the code.

I've attached the 2 Macros to show where I'm at:

The first errors due to the number of rows to process and I am getting an error not recognising the source data for the second.

Please Login or Register  to view this content.

Second solution:

Please Login or Register  to view this content.

[Sintek]

data comprised of 3 fields of Values plus a 4th Column containing the Figure Headings and a 5th with the actual Figures for those fields.

This is based on sample supplied...
If not working then try and upload a sample with a much larger dataset...

Please Login or Register  to view this content.

[noboffinme]

Thanks sintek,
I tried it but I get a 'Subscript out of Range' error at this line:

Temp(x, xx) = Data(i, 5)

Also, would you be able to add comments to show your logic?
Thanks Peter

[romperstomper]

That's exactly what a pivot table would produce - is there a reason for not using one? Or PQ would do the same job but produce a table.

[Sintek]

I tried it but I get a 'Subscript out of Range' error at this line:

Works for your sample file...Not so?
Like I said, if not working for actual add a sample file representing your actual data...

[noboffinme]

Hi rorya,
Yes, this is to replace a Pivot Table to improve the integrity of a report.
Having hardcoded results avoids any mistakes if someone inadvertently makes changes to the pivot.

[noboffinme]

I didn't want to post a full worksheet of data due to the size.
Why wouldn't your code work on my sample but work on a larger dataset?

[Sintek]

Why wouldn't your code work on my sample

It does work on your sample...have you tested the file I attached in post 5

[noboffinme]

Yes, and as I stated, I get the error.

I tried it but I get a 'Subscript out of Range' error at this line:

Temp(x, xx) = Data(i, 5)

[Sintek]

This is what i get when i press the button...

Untitled.png

Cannot see why you would get an error if we are both pushing the same button in the same file...

[alansidman]

Please Login or Register  to view this content.

Power Query

[romperstomper]

Hi rorya,
Yes, this is to replace a Pivot Table to improve the integrity of a report.
Having hardcoded results avoids any mistakes if someone inadvertently makes changes to the pivot.

You could just make a pivot, then copy and paste values.

[noboffinme]

Thanks, however the report also uses a function using some of the fields.
If anyone changes the pivot, what's copied will be incorrect.

[noboffinme]

Thanks alansidman, I'll take a look.

[noboffinme]

Hi sintek,
I tried it using the button and then using the VB editor, it works great!
Thank you.
Would you be able to add comments to explain your logic?
Thank you
Peter

[Sintek]

Glad we got it sorted...

Would you be able to add comments to explain your logic?

'! Comments Added Above...

[noboffinme]

Hi sintek, I wonder if you help me follow the purpose of the below line?

Please Login or Register  to view this content.

Thanks

[Sintek]

I have no desire to learn power query so cannot assist...alan will explain this in more detail...
Tx for rep by the way...Glad I could assist...

[noboffinme]

Hi sintek,
This line is from your code, it's not from Power Query.
I wanted to know what effect the cn 3,3,1 has?
Thanks

[jindon]

That's my code
https://www.excelforum.com/excel-pro...nce-field.html

This is a duplicate thread, so against the forum rule, and you said

I follow the 2nd one

Means you understand SQL... what a...

[noboffinme]

Since you only have time for experts why don't you just go away ...

[jindon]

Step debug is the one of the best tools for both BEGINERS/experts to understand the code.

Adding comments in each line of code doesn't help, especially for BEGINNERs most of the case.

[AliGW]

Administrative Note:

Unfortunately, this is a duplicate thread, and you are allowed only ONE thread per issue here.

Please see Forum Rule #2 about thread duplication.

I am closing this thread, but you may continue here in the original thread: https://www.excelforum.com/excel-pro...nce-field.html

Thread closed.