Posts by JF
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.
Re: Copying a cell to a specified cell
No problem... Looks like mine needed to be tweeked a bit anyway.
Re: list updates
You're welcome and welcome to the forum.
Re: Lead me on the pathQuote 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
Re: list updatesQuote 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?
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).....
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.
Re: list updatesQuote 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,
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
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
Re: Hide/Show more cells
You can try the following. It uses Cell C1 and D1...
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
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.
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.
Re: Breaking Up And Recombining NumbersQuote from minitman
I have a number which consists of three parts for a single number of 7 digits. I need to break it up into the three segments of 2 numbers, 2 numbers and 3 numbers and then recombine them back into a 7 digit number. I can do this with Left() & Mid() & Right(). The first attempt gave me a 5 digit number instead of a 7 digit number. I then tried to use the TEXT command and it errored out. Here is the code I tried:Code
Range("rAInvNo").Value = Text(Left(Range("rAInvNo"), 2), "00") & Text(Mid(Range("rAInvNo"), 3, 2), "00") & Text(Right(Range("rAInvNo"), 3) - 1, "000")
What am I doing wrong ?
Any help is appreciated.
Try this, it worked for me...
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.....
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
2 Sheet2 =COUNTA(Sheet2!A:A)-1
3 Sheet3 =COUNTA(Sheet3!A:A)-1
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.Code
Sub PrepareList() ' ' Macro Created 1/6/2006 by JF ' Application.ScreenUpdating = False '---------- Get Num Rows code --------- GetInput: Fintot = InputBox("How Many Rows For Final Sheet, 750 or 1000?") Select Case Fintot Case "750" 'MgrRow = Application.RoundUp(750 / MgrCount, 0) Sheets("Sheet2").Range("L2").Value = Fintot GoTo GOODNUM Case "1000" 'MgrRow = Application.RoundUp(1000 / MgrCount, 0) Sheets("Sheet2").Range("L2").Value = Fintot GoTo GOODNUM End Select GoTo GetInput GOODNUM: '---------- end Get Num rows -------------- ' Clear the working sheets Sheets("Final").UsedRange.Offset(2, 0).Clear Sheets("Sheet3").Select Cells.Select Selection.ClearContents Range("A1").Select ' Use Advance Filter to create unique list of managers and copy to work sheet Sheets("Sheet1").Columns("D:D").AdvancedFilter Action:=xlFilterCopy, _ CopyToRange:=Range("A1"), Unique:=True Columns("A:A").EntireColumn.AutoFit ' Read list into array tmpRowCt = Range("A65536").End(xlUp).Row Sheets("Sheet2").Range("M2").Value = tmpRowCt - 1 Dim MgrArray(1 To 1000) As String For x = 1 To tmpRowCt MgrArray(x) = Range("A" & x + 1) Next 'clear work sheet Sheets("Sheet3").UsedRange.Clear Range("A1").Select 'Create Advanced Filter Criteria and filter Master Sheet, copy to work sheet TotRow = Sheets("Sheet1").Range("A65536").End(xlUp).Row For x = 1 To tmpRowCt - 1 Sheets("Sheet2").Range("H2").Value = MgrArray(x) Sheets("Sheet1").Range("A1:F" & TotRow).AdvancedFilter Action:=xlFilterCopy, CriteriaRange:= _ Sheets("Sheet2").Range("E1:J2"), CopyToRange:=Range("A1"), Unique:=False ' Code to get random rows and copy to final sheet. 'Sheets("Sheet3").Select MyRc = Range("A65536").End(xlUp).Row MgrRow = Sheets("Sheet2").Range("N2").Value Randomize Sheets("Sheet3").Select i = Range("A65536").End(xlUp).Row For J = 2 To i Cells(J, 15) = Rnd Next J ' sort by random number Range("A1:O" & i).Sort key1:=Cells(2, 15), header:=xlYes 'Select approriate rows and copy to final sheet If MyRc <= MgrRow Then NR = Sheets("Final").Range("A65536").End(xlUp).Row NR = NR + 1 Range(Cells(2, 1), Cells(MyRc, 5)).Copy Sheets("Final").Range("A" & NR) Else NR = Sheets("Final").Range("A65536").End(xlUp).Row NR = NR + 1 Range(Cells(2, 1), Cells(MgrRow + 1, 5)).Copy Sheets("Final").Range("A" & NR) End If ' end of random row code Sheets("Sheet3").UsedRange.Clear Range("A1").Select Next x Sheets("Sheet2").Range("E2:M2").Clear Sheets("Final").Select Range("A3").Select Application.ScreenUpdating = True End Sub
Re: List down numbers on top, text at the bottomQuote 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.
What Column are you attempting to sort by? If it is either Col B or C, then try sorting Ascending.