Posts by JF

  • Hide/Show more cells

    Re: Hide/Show more cells


    Quote from Dave Hawley

    Don't feel bad, how do you think I found out originally :)


    I did not catch that until Dave pointed it out....

  • Dynamic Range by Row not Column

    Re: Dynamic Range by Row not Column


    I defined the named ranges slight different.


    Code
    DailyWorkUnits:  =OFFSET('Raw Data'!$B$7,0,COUNTA('Raw Data'!$7:$7)-16,1,15)


NumDays:  =OFFSET('Raw Data'!$B$1,0,COUNTA('Raw Data'!$1:$1)-16,1,15)



    This of course assumes there is no data past the last column that you want (i.e. no future dates - dates are added once the data is available)


    As for the other issue (Dates on axis that are not in data) is becasue the data is formated as a date. If you change the data to text, then it will only show the values that are within the range.

  • Find Duplicates

    Re: Lead me on the path


    Quote from jadown

    Nore thanks for the reply. But do you think you could help me at all? No one else has responded. I am new to this site so I don't know how long to get a reply that can help. But my problem is finding duplicates in column A and if they are duplicates then sum the total of column B. If column is not null, and the sum of column B = 0. Then delete those rows from the spreadsheet. It's been so long since I actually did any coding.


    Can you sort by Col A and then do subtotals by Col B. Then delete anything with a subtotal of 0

  • list updates

    Re: list updates


    Quote from Ad Vice

    Thanks for the speedy reply. The lists aren't acually identical. I have maybe a list of 800 that have to come off a list of 5000. Would this process still be the same if i were to remove the 800 from the original 500?


    Thanks,


    Ad Vice


    What I mean is are the names identical in both list (if they appear in both)


    so if list 1 has Joe Smith, does list 2 have Joe Smith or Joseph Smith (for the same person). As long as they are the same (for the appropriate name) then yes it will work.


    This also does not take into account 2 people with the same name unless there is another identified (such as a middle initial).....

  • Finding 5 most used values. Mode Function??

    Re: Finding 5 most used values. Mode Function??


    Quote from brad2157

    I read about and also tested the "Frequency" function with the example listed in the Excel Help file. I am not sure if this will work like I need it to. I see that given an array (number list) , it will list the number of times that the bin array (number to search for) is less than or equal to.


    Still not sure how I can make that give me the top 5 most used number values in a array. I may be looking at this from a different angle. I do appreciate your help.


    If you listed each number in the array, the frequency formula will show the top 5. (or at least with consecutive numbers), I did not try with skipping numbers.

  • list updates

    Re: list updates


    Quote from Ad Vice

    Hi there, this is my first post. I was just wondering if anyone can tell me if I can take two lists at once and delete the names that appeare from the second off the first withough going through manually?


    Thanks very much,


    Ad Vice


    As long as the data (names) in both lists are identical, yes. Lets say list one is on sheet1, A1:A500 and list two is on sheet2, A1-A600


    use column B as a working column in sheet1


    enter the following in C1


    Code
    =COUNTIF(Sheet2!$A$1:$A$600,Sheet1!A1)


    Drag this formula down the column to the last row.


    This will put a number in Column B. A 0 means it did not find a match on sheet2. anything above 0 is a match. You can then sort your list by Column B and delete any rows where b is > 0

  • Hide/Show more cells

    Re: Hide/Show more cells


    You can try the following. It uses Cell C1 and D1...


  • Running Totals

    Re: 2 questions ??


    Quote from mytquik

    sry for the confusion batman, but by running total i mean the total of all the figures in column D. So all of the figures from d2 to d112 are added up at the bottom of column D.


    Now for the tricky part once I input data into column M (which is the same figure that is in column D just reentered) I want the amount in column D removed from the total at the bottom of column D. Maybe an "IF" "THEN" would do it, but i dont know how to write it.



    Did you try my post? just change the referances to d2:d112 and m2:m112 and it should work

  • Running Totals

    Re: Running Totals


    Try something like this (adjust ranges accordingly):


    Code
    =SUMPRODUCT(((D2:D8)<>"")*(D2:D8),((M2:M8)="")*1)



    for your second question...


    if the running total is on d10 on sheet1 and you want it in d10 on sheet2


    in cell D10 on sheet2 put

    Code
    =Sheet1!D10

  • Show only 6 months data.

    Re: How to keep the latest 6 monthes data while data will be updated from month to month


    As long as there are no blank cells (Between data) in the header row, you can use the following:


    Code
    Sub delOldCol()
fc = (Range("A1").End(xlToRight).Column) - 6
 If fc >= 2 Then
  Range(Cells(1, 2), Cells(1, fc)).EntireColumn.Delete shift:=xlToLeft
 End If
End Sub


    This of course assumes that you have an entry for each consecutive month without gaps in data. If you skip any months, then it will keep the last 6 entries.

  • Referencing a form field in Yes/No Msg box in Access VBA

    Re: Referencing a form field in Yes/No Msg box in Access VBA


    You can try something like the following. The field it is checking in the example is a date field called "Effdate". The default value is now() so if the user changes the date, it prompts for confirmation.


  • Breaking Up And Recombining Numbers

    Re: Breaking Up And Recombining Numbers



    Try this, it worked for me...

    Code
    =TEXT(VALUE(LEFT(A1,2)),"00") & TEXT(VALUE(MID(A1,3,2)),"00") & TEXT(VALUE(RIGHT(A1,3)),"000")

  • Count row entries on worksheet(s)

    Re: Count row entries on worksheet(s)


    Quote from MrJay

    I have several worksheets. The 1st is a total sheet. Worksheets which follow have 1 or more row entries, starting at row 2. I must detect and count when an entry has been made per sheet, and, for the front sheet, display that count and add it to a total, which is to be displayed as well. :? :?
    Can it be done?
    :thanx: in advance.....
    MrJay :rock:


    Couple of questions....


    1. Do you need to see the old count and the new count or just the total count for each sheet?


    - if it is just the total count for each sheet, try using either a count() or counta() function. For Example:


    Sheet1 is the total sheet
    A B
    1 Totals
    2 Sheet2 =COUNTA(Sheet2!A:A)-1
    3 Sheet3 =COUNTA(Sheet3!A:A)-1

  • Parse and pick Random rows

    Re: Parse and pick Random rows


    Quote from Derk

    I don't have time today to check your code, but since it worked well for the managers I suggest you try it for the users. Modifying an approach that worked to a similar situation is usually the best way to go rather than starting from scratch.


    Thanks

  • Parse and pick Random rows

    Re: Parse and pick Random rows


    I now have a similar situation.


    I have a long list of data for several different users (multriple lines for each user - say 10 or 20%). I need to pull a random percentage of lines for each user.


    In my other situation, I was using autofilter to copy each group (in that case grouped by manager), to a seperate sheet and then pulling the required number of rows (based on a static figure). Would this still be the best approach or is there a better way?


    This is the code I have currently for getting based on static number.


  • add number of day in a range of dates

    Re: add number of day in a range of dates


    What about the using the datedif() function. Just use the start date and the last date and it will give the total days between them.


    Code
    =datedif(start date, end date, "d")


    the "D" tells it to count the # days between the two.

  • List down numbers on top, text at the bottom

    Re: List down numbers on top, text at the bottom


    Quote from jpacifics

    I've attached a screenshot of the file i am working on. You see here that those with text are on top of the list rather than those with numbers.


    Thanks.


    What Column are you attempting to sort by? If it is either Col B or C, then try sorting Ascending.