All Categories› Poker› Poker Chat

You need to be logged in to your Sky Poker account above to post discussions and comments.

You might need to refresh your page afterwards.

Sky Poker forums will be temporarily unavailable from 11pm Wednesday July 25th.
Sky Poker Forums is upgrading its look! Stay tuned for the big reveal!

Excel Help...

2»

Comments

  • bbMikebbMike
    edited January 2015
    There are drugs and machines for that too, no need to suffer in silence these days 
    · Share on FacebookShare on Twitter
  • gerardirlgerardirl
    edited January 2015
    Didnt get a chance to look at this in work today Brian.

    I suggest you use the sheet I sent you or create one similar to it naming each column week 1 to 52 perhaps.

    To record a macro select tools, macro, record new macro and name it (ie main, mini or total)......once you do that it records any action you make until you select stop recording.

    Once you select record macro then copy the entire sheet 1 into sheet 2.
    In sheet 2 add the total points for each player for main then sort their name and total in descending points order , then hide all the cells where the numbers appear (ie the workings) with the exception of the total column. 
    Then select the option to stop recording macro.

    You can have 3 macros if you wish, one for main on sheet 2, one for mini on sheet 3 and one for total on sheet 4.

    That way once you enter the points each week on sheet 1 you can run the 3 macros and you will instanty have 3 tables updated. Make sure to clear the contents in sheets 2,3 and 4 before you run the macros.

    Hope that works for you.

    Ger
    · Share on FacebookShare on Twitter
  • GlenelgGlenelg
    edited January 2015
    In Response to Re: Excel Help...:
    In Response to Re: Excel Help... : Yeah, well... I have to do a lot of things manually these days.
    Posted by Slipwater
    Is that what the kids call "a selfie" ?   ;-))
    · Share on FacebookShare on Twitter
  • SlipwaterSlipwater
    edited January 2015
    In Response to Re: Excel Help...:
    Didnt get a chance to look at this in work today Brian. I suggest you use the sheet I sent you or create one similar to it naming each column week 1 to 52 perhaps. To record a macro select tools, macro, record new macro and name it (ie main, mini or total)......once you do that it records any action you make until you select stop recording. Once you select record macro then copy the entire sheet 1 into sheet 2. In sheet 2 add the total points for each player for main then sort their name and total in descending points order , then hide all the cells where the numbers appear (ie the workings) with the exception of the total column.  Then select the option to stop recording macro. You can have 3 macros if you wish, one for main on sheet 2, one for mini on sheet 3 and one for total on sheet 4. That way once you enter the points each week on sheet 1 you can run the 3 macros and you will instanty have 3 tables updated. Make sure to clear the contents in sheets 2,3 and 4 before you run the macros. Hope that works for you. Ger
    Posted by gerardirl
    Hmmm... sounds complicated, but I'll give it a bash :)

    Thanks for your help.
    · Share on FacebookShare on Twitter
  • SlipwaterSlipwater
    edited January 2015
    Sigh.

    Sounded good in theory, but the practice of it...
    · Share on FacebookShare on Twitter
  • gerardirlgerardirl
    edited January 2015
    Where did you get stuck brian...I can talkyouthrough the steps if your on fb.

    Did you run the macro?
    · Share on FacebookShare on Twitter
  • GELDYGELDY
    edited January 2015
    In Response to Re: Excel Help...:
    In Response to Re: Excel Help... : Yeah, well... I have to do a lot of things manually these days.
    Posted by Slipwater
    more a case of choosing to than having to, at least with excel. 

    do your best to use the advice being provided. if you can automate the production of the leaderboard it will make your life so much easier.
    · Share on FacebookShare on Twitter
  • jdsallstarjdsallstar
    edited January 2015

    This forum thread tortured me, ive been trying for a few days trying to get this lol

    Think I've finally cracked it though but it'll take me a day or two to set it up and test it so if your still struggling with it i could try finishing this one.

     

    In case it helps here's how I made mine:

     

    What i basically did was create a workbook with 365 sheets on it to paste the results of each main/mini event. So one workbook for main and one for mini. A macro then lifts the player names from each daily sheet, dumps them into another sheet and then removes duplicates to create a unique player list. Using this unique list i then pull the points for each player into a data sheet using vlookups for each day. Using this data sheet i then used a countif formula which counted wins, FT and top 20s. I then used a macro that copied the results from this data sheet (name, points, Wins, FTs and top 20s) and pasted them into a league table sheet and then sorted them by points, then wins, then ft's then top 20s and finally by alphabet.

     

    For the brag board it would be just a case of running a macro that combines the datasheet for the main and the datasheet for the mini and then run the same league table macro described above. Havent got this far yet but cant see it being any problem

     

    The only concern i have is about the size of the workbooks once it starts filling up with results.

     

    Anyway.... lol let me know if you want me to finish it off. No worries if you do or dont I had to figure it for my own sanity anyway lol yes i'm sad like that :)

     

    GL mate

    · Share on FacebookShare on Twitter
  • unknown_usernameunknown_username
    edited January 2015
    In Response to Re: Excel Help...:
    This forum thread tortured me, ive been trying for a few days trying to get this lol Think I've finally cracked it though but it'll take me a day or two to set it up and test it so if your still struggling with it i could try finishing this one.   In case it helps here's how I made mine:   What i basically did was create a workbook with 365 sheets on it to paste the results of each main/mini event. So one workbook for main and one for mini. A macro then lifts the player names from each daily sheet, dumps them into another sheet and then removes duplicates to create a unique player list. Using this unique list i then pull the points for each player into a data sheet using vlookups for each day. Using this data sheet i then used a countif formula which counted wins, FT and top 20s. I then used a macro that copied the results from this data sheet (name, points, Wins, FTs and top 20s) and pasted them into a league table sheet and then sorted them by points, then wins, then ft's then top 20s and finally by alphabet.   For the brag board it would be just a case of running a macro that combines the datasheet for the main and the datasheet for the mini and then run the same league table macro described above. Havent got this far yet but cant see it being any problem   The only concern i have is about the size of the workbooks once it starts filling up with results.   Anyway.... lol let me know if you want me to finish it off. No worries if you do or dont I had to figure it for my own sanity anyway lol yes i'm sad like that :)   GL mate
    Posted by jdsallstar
    Nice work sir.....

    C1=+A1+B1 ENTER - Anything more in excel & my brain explodes :)
    · Share on FacebookShare on Twitter
  • bbMikebbMike
    edited January 2015
    In Response to Re: Excel Help...:
    This forum thread tortured me, ive been trying for a few days trying to get this lol Think I've finally cracked it though but it'll take me a day or two to set it up and test it so if your still struggling with it i could try finishing this one.   In case it helps here's how I made mine:   What i basically did was create a workbook with 365 sheets on it to paste the results of each main/mini event. So one workbook for main and one for mini. A macro then lifts the player names from each daily sheet, dumps them into another sheet and then removes duplicates to create a unique player list. Using this unique list i then pull the points for each player into a data sheet using vlookups for each day. Using this data sheet i then used a countif formula which counted wins, FT and top 20s. I then used a macro that copied the results from this data sheet (name, points, Wins, FTs and top 20s) and pasted them into a league table sheet and then sorted them by points, then wins, then ft's then top 20s and finally by alphabet.   For the brag board it would be just a case of running a macro that combines the datasheet for the main and the datasheet for the mini and then run the same league table macro described above. Havent got this far yet but cant see it being any problem   The only concern i have is about the size of the workbooks once it starts filling up with results.   Anyway.... lol let me know if you want me to finish it off. No worries if you do or dont I had to figure it for my own sanity anyway lol yes i'm sad like that :)   GL mate
    Posted by jdsallstar
    You don't need 365 sheets, just one sheet that holds the new day information then when you run the macro to copy it into the sheet that holds day by day use a user-entry box command so it asks you which day you're on. Use this value to offset the cell you're pasting the data to.

    · Share on FacebookShare on Twitter
  • bbMikebbMike
    edited January 2015
    In Response to Re: Excel Help...:
    In Response to Re: Excel Help... : You don't need 365 sheets, just one sheet that holds the new day information then when you run the macro to copy it into the sheet that holds day by day use a user-entry box command so it asks you which day you're on. Use this value to offset the cell you're pasting the data to.
    Posted by bbMike
    You might want to keep the set of lookup formulas then just paste special values from there into the correct column.
    · Share on FacebookShare on Twitter
  • jdsallstarjdsallstar
    edited January 2015
    In Response to Re: Excel Help...:
    In Response to Re: Excel Help... : You don't need 365 sheets, just one sheet that holds the new day information then when you run the macro to copy it into the sheet that holds day by day use a user-entry box command so it asks you which day you're on. Use this value to offset the cell you're pasting the data to.
    Posted by bbMike
    Yh I would have preferred a 1 sheet entry but it was just beyond me lol i kept running into problems due to the dynamic player list. i.e. lets assume it's 30th March and we've had a full 3 months of results entered. The list of players who have played is around 1k, how do I copy the 20 players who have gained points into that list into the correct date from a 1 sheet entry?
    · Share on FacebookShare on Twitter
  • SlipwaterSlipwater
    edited January 2015
    It's crazy how much of this goes over my head, but I'm determined to get this...

    Thanks for the assistance, folks :)
    · Share on FacebookShare on Twitter
  • GELDYGELDY
    edited January 2015
    as you are discovering there are many ways to do this:

    i like to think of a good struture being:

    a control sheet
    data entry sheet/s ie each daily result side by side
    interim calculation sheet/s ie running points total, unique name list etc
    result sheet/s ie ytd, mtd leaderboards

    and use the macro to do the work of getting data, calculating answers, working out unique name lists etc. technically don't even need the interim sheets but it makes it easier to debug and its nice to see some of the info. also although using lookups and offsets is sufficient for this task, it is so much cleaner, robust and more expandable just to code a solution (ie all results in arrays in the macros and use "if then else" loops to calc the running totals)

    PS you can then change the scoring system and recalc the lot with a press of a button
    · Share on FacebookShare on Twitter
  • MachkaMachka
    edited January 2015
    In Response to Re: Excel Help...:
    This forum thread tortured me, ive been trying for a few days trying to get this lol Think I've finally cracked it though but it'll take me a day or two to set it up and test it so if your still struggling with it i could try finishing this one.   In case it helps here's how I made mine:   What i basically did was create a workbook with 365 sheets on it to paste the results of each main/mini event. So one workbook for main and one for mini. A macro then lifts the player names from each daily sheet, dumps them into another sheet and then removes duplicates to create a unique player list. Using this unique list i then pull the points for each player into a data sheet using vlookups for each day. Using this data sheet i then used a countif formula which counted wins, FT and top 20s. I then used a macro that copied the results from this data sheet (name, points, Wins, FTs and top 20s) and pasted them into a league table sheet and then sorted them by points, then wins, then ft's then top 20s and finally by alphabet.   For the brag board it would be just a case of running a macro that combines the datasheet for the main and the datasheet for the mini and then run the same league table macro described above. Havent got this far yet but cant see it being any problem   The only concern i have is about the size of the workbooks once it starts filling up with results.   Anyway.... lol let me know if you want me to finish it off. No worries if you do or dont I had to figure it for my own sanity anyway lol yes i'm sad like that :)   GL mate
    Posted by jdsallstar
    Slipwater, although my macro I posted a couple of days ago solved the exact problem in your original post, reading this thread I now see there is a bigger problem to solve.

    If the problem could be clearly stated then I'd have a go at solving it but it looks to me like jdsallstar is 90% of the way there.
    · Share on FacebookShare on Twitter
  • mac24mac24
    edited January 2015
    In Response to Re: Excel Help...:
    In Response to Re: Excel Help... : Yh I would have preferred a 1 sheet entry but it was just beyond me lol i kept running into problems due to the dynamic player list. i.e. lets assume it's 30th March and we've had a full 3 months of results entered. The list of players who have played is around 1k, how do I copy the 20 players who have gained points into that list into the correct date from a 1 sheet entry?
    Posted by jdsallstar
    i hope I am understanding this right and not giving duff advice...

    you have players name in col a (only using 4 as an example), col b is the running total

    name        points

    player 1    50
    player 2    45
    player 3    40
    player 4    35

    player 3 plays, the other 3 do not

    insert column b to look for new values, set the lookup range using a string  character e.g. vlookup sheet1!$a$1:$b$20,2,0  to lock the range. insert column c and use if is null else statement for totals. =if(b2="",d2,d2+b2)

    copy col c paste as values, delete column b, delete col d, rename column to today

    on the set up of the spread sheet, rather than setting up 365 individual workbooks, you can instruct the macro to create a new workbook, copy todays data into it and rename as today.

    Alternatively, using a single workbook for all data and a summary page, if you put an index column into the workbook you can define where to paste the new source data in your macro at the bottom of the workbook using a count of the number of active rows.

    hope this makes sense

    regards

    mac
      
    · Share on FacebookShare on Twitter
  • jdsallstarjdsallstar
    edited January 2015
    In Response to Re: Excel Help...:
    In Response to Re: Excel Help... : i hope I am understanding this right and not giving duff advice... you have players name in col a (only using 4 as an example), col b is the running total name        points player 1    50 player 2    45 player 3    40 player 4    35 player 3 plays, the other 3 do not insert column b to look for new values, set the lookup range using a string  character e.g. vlookup sheet1!$a$1:$b$20,2,0  to lock the range. insert column c and use if is null else statement for totals. =if(b2="",d2,d2+b2) copy col c paste as values, delete column b, delete col d, rename column to today on the set up of the spread sheet, rather than setting up 365 individual workbooks, you can instruct the macro to create a new workbook, copy todays data into it and rename as today. Alternatively, using a single workbook for all data and a summary page, if you put an index column into the workbook you can define where to paste the new source data in your macro at the bottom of the workbook using a count of the number of active rows. hope this makes sense regards mac   
    Posted by mac24

    Unfortunately I dont understand or else it's way beyond me . Probably the later ;)

    But I've made my 365 sheet and I'm not changing it now :)

    I do need help with creating my unque player list. I thought I could go to sheet 1 copy a1:a1000, go to my player list paste in A1, go to page two copy A1:A1000 to back to my player list sheet go to cell A1 press crt+down to take me to the end of the data and paste. I'd then repeat this for all the sheets in the workbook in the workbook then finally remove duplicates.
    The ctrl+down bit isnt working for me. Any suggestions?

    I only know basic macro stuff so be gentle :)

    · Share on FacebookShare on Twitter
  • mac24mac24
    edited January 2015
    In Response to Re: Excel Help...:
    In Response to Re: Excel Help... : Unfortunately I dont understand or else it's way beyond me . Probably the later ;) But I've made my 365 sheet and I'm not changing it now :) I do need help with creating my unque player list. I thought I could go to sheet 1 copy a1:a1000, go to my player list paste in A1, go to page two copy A1:A1000 to back to my player list sheet go to cell A1 press crt+down to take me to the end of the data and paste. I'd then repeat this for all the sheets in the workbook in the workbook then finally remove duplicates. The ctrl+down bit isnt working for me. Any suggestions? I only know basic macro stuff so be gentle :)
    Posted by jdsallstar
    try this in your vba editor on the new data page



    sheets("current data").range "a1:a1000".copy 

    sheets("new data").select

    range("A1").select


    dim last as integer
    last = activesheet.UsedRange.Rows.Count
    last = last +1

    range ("A" & last).Select

    selection.paste


    then run your de-dup and paste to all other sheets

    unfortunately, I only have Excel starter which doesn't have macros so i'm trying to do this from memory. let me know if it doesn't work, and I'll do it on my works computer in the next day or two

    regards

    mac
    · Share on FacebookShare on Twitter
  • jdsallstarjdsallstar
    edited January 2015
    Maybe i didnt emphasize the word basic enough :P

    It's cool thanks anyway i'll ask one of the wiz kids in work!


    · Share on FacebookShare on Twitter
  • mac24mac24
    edited January 2015
    In Response to Re: Excel Help...:
    Maybe i didnt emphasize the word basic enough :P It's cool thanks anyway i'll ask one of the wiz kids in work!
    Posted by jdsallstar
    Hi,

    no problem. if you cant get it to work and want to forward your sheet to me I can insert the macro and put a command button in for you too. let me know and i'll PM you my email address.

    best regards

    Mac
    · Share on FacebookShare on Twitter
  • SlipwaterSlipwater
    edited January 2015
    Jesus.

    It's going to take me a few hours to read through this, let alone put it into practice.

    My brain wasn't designed for this ;)
    · Share on FacebookShare on Twitter
  • SlipwaterSlipwater
    edited January 2015
    In Response to Re: Excel Help...:
    This forum thread tortured me, ive been trying for a few days trying to get this lol Think I've finally cracked it though but it'll take me a day or two to set it up and test it so if your still struggling with it i could try finishing this one.   In case it helps here's how I made mine:   What i basically did was create a workbook with 365 sheets on it to paste the results of each main/mini event. So one workbook for main and one for mini. A macro then lifts the player names from each daily sheet, dumps them into another sheet and then removes duplicates to create a unique player list. Using this unique list i then pull the points for each player into a data sheet using vlookups for each day. Using this data sheet i then used a countif formula which counted wins, FT and top 20s. I then used a macro that copied the results from this data sheet (name, points, Wins, FTs and top 20s) and pasted them into a league table sheet and then sorted them by points, then wins, then ft's then top 20s and finally by alphabet.   For the brag board it would be just a case of running a macro that combines the datasheet for the main and the datasheet for the mini and then run the same league table macro described above. Havent got this far yet but cant see it being any problem   The only concern i have is about the size of the workbooks once it starts filling up with results.   Anyway.... lol let me know if you want me to finish it off. No worries if you do or dont I had to figure it for my own sanity anyway lol yes i'm sad like that :)   GL mate
    Posted by jdsallstar
    Hey mate... I understand each of the words used here, but not the context in which they have been said!

    Can you give this a bash if you get a chance, please? If not, no worries - I'll figure it out eventually.

    · Share on FacebookShare on Twitter
  • jdsallstarjdsallstar
    edited January 2015
    In Response to Re: Excel Help...:
    In Response to Re: Excel Help... : Hey mate... I understand each of the words used here, but not the context in which they have been said! Can you give this a bash if you get a chance, please? If not, no worries - I'll figure it out eventually.
    Posted by Slipwater

    Yes mate no problem. I'll give it a go over the weekend and let you know Monday. My friend in work just figured out a macro I was struggling with so should be straight forward enough now......hopefully :)


    · Share on FacebookShare on Twitter
  • SlipwaterSlipwater
    edited January 2015
    In Response to Re: Excel Help...:
    In Response to Re: Excel Help... : Yes mate no problem. I'll give it a go over the weekend and let you know Monday. My friend in work just figured out a macro I was struggling with so should be straight forward enough now......hopefully :)
    Posted by jdsallstar
    Thanks buddy :)
    · Share on FacebookShare on Twitter
  • jdsallstarjdsallstar
    edited January 2015
    Hi slip sorry didnt get round to looking at this over the weekend but i will get on it again over next few days.
    · Share on FacebookShare on Twitter
  • SlipwaterSlipwater
    edited January 2015
    In Response to Re: Excel Help...:
    Hi slip sorry didnt get round to looking at this over the weekend but i will get on it again over next few days.
    Posted by jdsallstar
    No worries, mate - I've been a little snowed under anyway!

    Thanks for trying.
    · Share on FacebookShare on Twitter
  • LARSON7LARSON7
    edited January 2015
    Loving the diary :)
    · Share on FacebookShare on Twitter
  • day4eire76day4eire76
    edited February 2015
    Think I found a solution all results have been done for January.

    Mail me

    Pat
    · Share on FacebookShare on Twitter
  • SlipwaterSlipwater
    edited February 2015
    In Response to Re: Excel Help...:
    Think I found a solution all results have been done for January. Mail me Pat
    Posted by day4eire76
    You're a lifesaver, buddy. Thanks.
    · Share on FacebookShare on Twitter
Sign In or Register to comment.