Transpose data from one sheet to other in particular format - AUD $20

  • Hi guys,
    I have got bites of the code working fine already. However cannot figure how to transpose the data in columns in output 1. Hence I need a VBA code which can the following:


    • Copy BD in column A in “output 1” for all rows
    • Column B in "output 1" is counter which will have 1 to 48 for each date
    • Copy BC in column C in “output 1” for all rows
    • Column D in “Output 1” is row heading from “Raw Data” which needs to be replicated
    • Transpose data as below:

      • If column BE starts with E, copy C to AX from raw data into column E in “Output 1”
      • If column BE starts with Q, copy C to AX from raw data into column F in “output 1”
      • If column BE starts with B, copy C to AX from raw data into column G in “output 1”
      • If column BE starts with K, copy C to AX from raw data into column H in “output 1”


    • Copy formula from I2 to P2 to the last row in “Output 1”
    • Refresh the pivot table in column T in “Output 1”


    have paid 2$ to OZgrid and will pay $20 to the person who completes the task. ETA Friday 26/05/2017 if possible.

  • Re: Transpose data from one sheet to other in particular format - AUD $20


    I can look at this for you.

    We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.

  • Re: Transpose data from one sheet to other in particular format - AUD $20


    Your code is done, I will PM you with my PayPal details.

    We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.

  • Re: Transpose data from one sheet to other in particular format - AUD $20


    Payment received, many thanks.


    I assumed that row 2 of the Raw Data was to be ignored as it does not contain data that is consistent with the rest of the dataset. The Pivot Table has to move to its own sheet otherwise the Output data cannot update.


    I am attaching a file with the code working.


    Your sample file only has "B" as the first letter in column BE of Raw Data so pivot table is empty and a full test could not be done for other letters ( E, Q and K), let me know if you have any issues with a complete set of data in Raw Data.


    The code is in Module 3

  • Re: Transpose data from one sheet to other in particular format - AUD $20


    Hi Charles I am getting an error at line as type miss match


    Case "E": y(5, iii) = CDbl(x(i, ii + 2))

  • Re: Transpose data from one sheet to other in particular format - AUD $20


    That is probably due to the fact that the value of x(i, ii + 2) is not numerical.


    Can you attach the file with the raw data that you are testing with, or e-mail it to me (same e-mail as PayPal ID) if you prefer.

    We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.

  • Re: Transpose data from one sheet to other in particular format - AUD $20


    Hi Charles, just one note which forgot to mention, that if BC is blank it need to ignore that line.

  • Re: Transpose data from one sheet to other in particular format - AUD $20


    Try changing the code to this, is it works then no need to attach or send the file.

    We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.

  • Re: Transpose data from one sheet to other in particular format - AUD $20


    it now comes with error on below line:


    .UsedRange.Offset(2).Clear


    the error is 1004 : cant make this change for the selected cells because it will affect a PivotTable.


    Also will the above line delete formula in cell I2:P2? as this should not be delete.

  • Re: Transpose data from one sheet to other in particular format - AUD $20


    Note my previous post where I said pivot has to go on a separate sheet.


    No the code will clear the used range offset by 2 rows, hence, the header row and the first row of data plus formulas do not get cleared.


    As it happens I got your actual file and full dataset by e-mail in the end, I did not realise the data would be so big. Output 1 sheet ends up with almost 300,000 rows. I am having to find an efficient method of manipulating the data in stages because doing all in one shot deletes system resources!


    I will post amended code shortly.

    We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.

  • Re: Transpose data from one sheet to other in particular format - AUD $20


    Thanks for this, just a note the actual file can go out to 800K rows. the file I sent was a short version

  • Re: Transpose data from one sheet to other in particular format - AUD $20


    In that case you have a real problem!


    800,000 rows of raw data would mean just short of 800,000 dates in the raw data.


    Each date needs 48 rows in Output 1, that would mean over 38,000,000 rows on a sheet that has a maximum of 1,048,575 rows available for data below a header row!


    The maximum rows of raw data that Excel alone could handle would therefore be 21,845.


    I think you are going to have to look at using Access.

    We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.

  • Re: Transpose data from one sheet to other in particular format - AUD $20


    Hmmm you cannot mean 800,000 rows of raw data, that would be data collected for over 2,000 years, I am pretty certain you have not being doing that!


    Raw Data that results in 800,000 rows on Output 1 would be OK, but note you are approaching the maximum.

    We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.

  • Re: Transpose data from one sheet to other in particular format - AUD $20


    Here is the amended code.


    The raw data is dealt with 1,000 rows at a time and Output 1 is updated after each block of 1,000 has been processed.


    I will e-mail the file to you.


    We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.

  • Re: Transpose data from one sheet to other in particular format - AUD $20


    Thanks r this, it working fine now. However the date is coming up as text and hence my pivot is not showing this report properly. Can you please update this?

  • Re: Transpose data from one sheet to other in particular format - AUD $20


    Strange, it works fine for me.


    Try changing this line in the code

    Code
    y(3, iii) = x(i, UBound(x, 2) - 2)


    to

    Code
    y(3, iii) = CDate(x(i, UBound(x, 2) - 2))

    We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.

  • Re: Transpose data from one sheet to other in particular format - AUD $20


    this is not working as well. it copies the data but the pivot does not group them as its not in date format

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!