($200) Create a business report with rows of data that have multiple columns of data

Important Notice


Please note that on 14th December 2023 users will experience an expected outage whilst we make upgrades to our network. We anticipate this process may take a couple of hours and so we apologise in advance for any inconvenience.

    I'm working on a project that has a large data set - too large for excel to handle. Would love to know if you could help.

    I have this file here and it’s a list of hundreds of thousands of zip codes with specific personas (there are 67). Each column has a value to annotate how many households roll into each persona.

    I'm trying to run some numbers to determine:

    1. How many zip codes are in each persona - with the ability to drill into which zip codes those are.
    2. How many households roll into each zip code (with the ability to drill into which zip codes those are.)
    3. How many households roll into each persona (with the ability to drill into which zip codes those are.)


    The 67 personas are titled "PZM" in the column headers. Each row of zip codes has a numerical value in the respective columns where they have a presence (it could be any value from >0, based on the volumes of households in each persona).

    The household volumes are annotated by the numerical values in each column >0.

    At the end of the day I’d like to be able to have some sort of view of the data to actually understand what it is showing. Obviously looking at thousands of rows of data is not efficient and I would need something that could repeat this process multiple times by reading the columns - sometimes would be PZM, other times could be CXN, etc)


    I'm also working on this on my own, please let me know if you can assist before working on it!

    Re: ($200) Create a business report with rows of data that have multiple columns of d


    I will have a look at it now.


    Wigi

    Regards,


    Wigi


    Excel MVP 2011-2014


    For more Excel memes: visit http://www.wimgielis.com ==> English articles ==> Excel memes


    -- Topics without [CODE] tags or a meaningful topic title, will be neglected by me (and probably many others as well) --

    Re: ($200) Create a business report with rows of data that have multiple columns of d


    Hello there


    What version of Excel are you using?

    Regards,


    Wigi


    Excel MVP 2011-2014


    For more Excel memes: visit http://www.wimgielis.com ==> English articles ==> Excel memes


    -- Topics without [CODE] tags or a meaningful topic title, will be neglected by me (and probably many others as well) --

    Re: ($200) Create a business report with rows of data that have multiple columns of d


    Hoowzer


    Can you please respond ? ;)


    You can do this in Excel, if you have Excel 2007 or later. I found out that you have almost 520,000 non-zero values in the grid.
    Which is a lot, but not impossible with recent versions of Excel.


    Wigi

    Regards,


    Wigi


    Excel MVP 2011-2014


    For more Excel memes: visit http://www.wimgielis.com ==> English articles ==> Excel memes


    -- Topics without [CODE] tags or a meaningful topic title, will be neglected by me (and probably many others as well) --

    Re: ($200) Create a business report with rows of data that have multiple columns of d


    Hi Wigi -


    I have excel 2007 and 2010.


    Re: ($200) Create a business report with rows of data that have multiple columns of d


    Thanks.


    Would it be a solution to grab the FULL table, create a different layout with a macro (a layout suited for a pivot table),
    and then define 1 or more pivot tables based on the changed layout?

    Regards,


    Wigi


    Excel MVP 2011-2014


    For more Excel memes: visit http://www.wimgielis.com ==> English articles ==> Excel memes


    -- Topics without [CODE] tags or a meaningful topic title, will be neglected by me (and probably many others as well) --

    Re: ($200) Create a business report with rows of data that have multiple columns of d


    Hello


    I will come back in a couple of hours and also on Saturday, to see if there's a reply from your side.


    Thanks in advance,


    Wigi

    Regards,


    Wigi


    Excel MVP 2011-2014


    For more Excel memes: visit http://www.wimgielis.com ==> English articles ==> Excel memes


    -- Topics without [CODE] tags or a meaningful topic title, will be neglected by me (and probably many others as well) --

    Re: ($200) Create a business report with rows of data that have multiple columns of d


    Oh yes, have you paid 10% to [email protected] ? Thanks in advance.
    90% remains after that initial payment.

    Regards,


    Wigi


    Excel MVP 2011-2014


    For more Excel memes: visit http://www.wimgielis.com ==> English articles ==> Excel memes


    -- Topics without [CODE] tags or a meaningful topic title, will be neglected by me (and probably many others as well) --

    Re: ($200) Create a business report with rows of data that have multiple columns of d


    Hi Wigi -


    I think what I want is a macro, query, or template that will be able to take the data and manipulate it for me to starting showing correlations between zipcode and personas.


    The 3 questions that I definitely would like answered are:


    1)how many houses are in each persona - this is easy as it is simply a sum of each of the 67 columns
    2)how many zipcodes roll up into each persona
    3)how many houses are in each zip code.


    If there are other cuts to show trend that would be great as well.


    The biggest thing for me is that this data is way too large to show trends without putting it in a format to make it manageable as it would have to pivot 67 unique columns...


    Please let me know if you have other questions.


    Quote from Wigi;705116

    Thanks.


    Would it be a solution to grab the FULL table, create a different layout with a macro (a layout suited for a pivot table),
    and then define 1 or more pivot tables based on the changed layout?

    Re: ($200) Create a business report with rows of data that have multiple columns of d


    Thanks for the update, I will continue tomorrow.

    Regards,


    Wigi


    Excel MVP 2011-2014


    For more Excel memes: visit http://www.wimgielis.com ==> English articles ==> Excel memes


    -- Topics without [CODE] tags or a meaningful topic title, will be neglected by me (and probably many others as well) --

    Re: ($200) Create a business report with rows of data that have multiple columns of d


    Quote from Wigi;705171

    Thanks for the update, I will continue tomorrow.



    Hi Wigi -


    Based upon what I was able to do right now, there really is nothing in excel that will make this manageable due to the size of the data. Will play around with R this weekend myself and will follow back up on this thread if I need your help. Thanks for the interest, please stay tuned!

    Re: ($200) Create a business report with rows of data that have multiple columns of d


    Hello


    I was already working on this myself, so I would appreciate that you honour that effort as well. Even if you are working on this yourself too, which is an added value. In one way or another, you reach out to developers with this topic.
    By the way, can you confirm the payment of 10% to Ozgrid?


    In Excel, I wrote code to reformat the table, skipping the 0 values. If you do that, you are left with about 520000 lines, which is feasible in Excel.


    Wigi

    Regards,


    Wigi


    Excel MVP 2011-2014


    For more Excel memes: visit http://www.wimgielis.com ==> English articles ==> Excel memes


    -- Topics without [CODE] tags or a meaningful topic title, will be neglected by me (and probably many others as well) --

    Re: ($200) Create a business report with rows of data that have multiple columns of d


    Hello


    I worked on a solution involving a macro to format the data, then 3 pivot tables based on the changed layout.
    The full file exceeds 60 MB, how can I transfer this to you? Or will I send you a small file with only part of the data, the macro and the 3 pivot tables?


    I will send you my PayPal details for the payment of 90% of the stated amount. After the money is received and you confirm payment of 10% to Ozgrid, I will send along a small file with my solution.


    Thanks in advance,


    Wigi

    Regards,


    Wigi


    Excel MVP 2011-2014


    For more Excel memes: visit http://www.wimgielis.com ==> English articles ==> Excel memes


    -- Topics without [CODE] tags or a meaningful topic title, will be neglected by me (and probably many others as well) --

    Re: ($200) Create a business report with rows of data that have multiple columns of d


    Hi Wigi -


    Thanks for doing this so quickly. I agree that I was able to do this, but won't be able to do this at scale because I don't have a macro.


    In order to send me the file, can you upload it to Google Docs? If not, then maybe a file sharing service like mediafire?


    How long did it take for it to completely run on the data set?



    Also, I haven't used paypal in a while...looks like they charge for people to send money now?

    Re: ($200) Create a business report with rows of data that have multiple columns of d


    Hello


    A file sharing service could be a possibility, yes.


    The macro takes about 2.5 minutes in total. It needs to process almost 15,000,000 data points, so that's never going to be lightning fast.


    For PayPal: then please set up a PayPal account now (if you don't have one), as you have to pay 10% (USD 20) to Ozgrid. After that, 90% to me.F
    For me, I never pay using PayPal, so it could be that small transaction costs add to the payment, don't know, sorry.

    Regards,


    Wigi


    Excel MVP 2011-2014


    For more Excel memes: visit http://www.wimgielis.com ==> English articles ==> Excel memes


    -- Topics without [CODE] tags or a meaningful topic title, will be neglected by me (and probably many others as well) --

    Re: ($200) Create a business report with rows of data that have multiple columns of d


    Ok - 10% sent to Ozgrid and 90% sent to you.

    Re: ($200) Create a business report with rows of data that have multiple columns of d


    Thanks, I will find a filesharing service and let you know soon.

    Regards,


    Wigi


    Excel MVP 2011-2014


    For more Excel memes: visit http://www.wimgielis.com ==> English articles ==> Excel memes


    -- Topics without [CODE] tags or a meaningful topic title, will be neglected by me (and probably many others as well) --

    Re: ($200) Create a business report with rows of data that have multiple columns of d


    I'm uploading to my own SkyDrive/OneDrive, that's the best option I think.

    Regards,


    Wigi


    Excel MVP 2011-2014


    For more Excel memes: visit http://www.wimgielis.com ==> English articles ==> Excel memes


    -- Topics without [CODE] tags or a meaningful topic title, will be neglected by me (and probably many others as well) --

    Re: ($200) Create a business report with rows of data that have multiple columns of d


    Hello


    The file has been uploaded, you should have received an invitation email by now, including information from my side.

    Regards,


    Wigi


    Excel MVP 2011-2014


    For more Excel memes: visit http://www.wimgielis.com ==> English articles ==> Excel memes


    -- Topics without [CODE] tags or a meaningful topic title, will be neglected by me (and probably many others as well) --

    Re: ($200) Create a business report with rows of data that have multiple columns of d


    Hi Wigi- The file was too large to open in the format you sent me. Could you upload to the link that I shared with you (responded back to your hotmail account)?

Participate now!

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

Register Yourself Login