Posts by Hoowzer

    Hi All -


    I am using the script below to look at column A and if N/A is present it will delete that entire row of data. I'm using this script on a data set that has roughly 400-500k rows and when I run it, the script just deletes everything. When the script is used on smaller data sets ~ a dozen or so it works fine. Could you let me know how to fix this?


    Re: Adding progress bar to code


    Thanks Trunten - This is helpful to see the code.


    Quote from trunten;705629

    Short answer is don't, it adds unnecessary time to the execution of your code. VBA is only single threaded so you have to waste time updating a progress bar.
    If you're sure you want one though the quickest way to do it is through the status bar otherwise it gets a bit more complex.


    Re: Adding progress bar to code


    Hi Smallman -
    Yes, I am trying to delete all rows with a N/A in it. I used your script but it didn't work...It just filtered, but deleted everything else. I want to delete the N/As but keep everything else...



    Hi -
    I'm very new to VBA and wanted to add a progress bar to my script below. How would I go about adding one so I know how much of the job is completed

    Code

    ?



    Re: How to fix equation to find top 2 highest values


    Quote from venkat1926;705510

    for sarah's 0 whlich one do you want the first one or the second one


    Im hoping it will identify a tie and show both column headers. (for the 1st highest score)



    Assuming 1 was the highest and lets say there was a tie for the 2nd highest score (if it were a 0) the 2nd highest score would be a N/A as 0 is not a score.

    Re: How to fix equation to find top 2 highest values


    Hi -


    This is close, but still doesn't solve for this:



    [TABLE="width: 500"]

    [tr]


    [td]

    John

    [/td]


    [td]

    0

    [/td]


    [td]

    0

    [/td]


    [td]

    1

    [/td]


    [td]

    0

    [/td]


    [/tr]


    [tr]


    [td]

    Sarah

    [/td]


    [td]

    0

    [/td]


    [td]

    1

    [/td]


    [td]

    0

    [/td]


    [td]

    1

    [/td]


    [/tr]


    [tr]


    [td]

    Stacy

    [/td]


    [td]

    2

    [/td]


    [td]

    2

    [/td]


    [td]

    0

    [/td]


    [td]

    0

    [/td]


    [/tr]


    [/TABLE]



    For example - Sarah the highest value is 1, but the 2nd highest would be 0, but you have 2 columns which are both zero.



    Quote from venkat1926;705394

    see attached sheet1


    B2 formkula
    B3 formula
    B3 is copied down


    I do not understand how 0 comes here.

    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?

    Hi All -


    I have the following 2 formulas that have helped me to determine the top 2 scores and return the respective headers, but they aren't perfect and I need help.


    =INDEX(T1:V2,1,MATCH(MAX(T2:V2),T2:V2,0))


    • The above returns the max, however doesn't take into account scores that are a tie or 0 values


    =INDEX(T1:V2,1,MATCH(LARGE(T2:V2,2),T2:V2,0))

    • The above returns the 2nd highest value, but some instances the output is correct (if there is a tie or a 0).


    How can I fix both of these equations and also determine the 3rd highest score?



    Your help would be appreciated!

    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


    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?

    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!