# Posts by JF

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.

• ## Copying a cell to a specified cell

Re: Copying a cell to a specified cell

No problem... Looks like mine needed to be tweeked a bit anyway.

• ## Copying a cell to a specified cell

Re: Copying a cell to a specified cell

Try this (it worked in my test)

Code
Range((Range("L23").Value)) = Range("C15").Value

You're welcome and welcome to the forum.

• ## Find Duplicates

Re: Lead me on the path

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

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,

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??

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.

## Files

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,

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

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)

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?
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.