Assuming that the entries are text values:
For the next to the last entry:
=INDEX(A:A,MATCH(LOOKUP(REPT("z",255),A:A),A:A,0)-2)
For the last entry:
=LOOKUP(REPT("Z",255),A:A) .....
I have a list of U.S. cities and their crime numbers. The list contains roughly 8700 records. What I need to do is pull the records for 10 specific cities from that list. Exactly which cities are singled out may change from year to year.
And this is something I'll need to do every year when the crime stats are released by the FBI.
I have a list with many names, several of them appearing more than once. I would like to extract the unique entries an then make the list of them.
I can do that using the filter. But can I do it with formulas ? (In order to, whaterever change is made to the first list, get the extracted one immediately updated).
I have a List like this (a large one in practical)
A B 1 cat dog 2 rat cat 3 bat cat 4 cat rat
At the bottom I want to generate an array which gives what are the text entries of this list.(ignore multiple occurrences)
so the answer should be
cat
rat
bat
dog
I'm looking to build a football data sheet on matches played with a column that adds up the last 3 match goals for the team in that row. I've only managed to get as far as adding up all goals for a team in a static date range using SUMIF. I imagine this is more complex and requires some kind of changing sum range that's relative to the cell I'm in.
I'm intermediate at best so any array formulas and VB script would preferred to be avoided if possible.
in the watered down example below, I'd like to add up all home team goals for Hereford in the last 3 matches, therefore excluding the game on the 10th (row 1) giving a total of 5. I'd like this formula to copy down from a13 to future matches and therefore update. So the next week's match will add goals for hereford from e13,e11,e8 but exclude e2, e5. Yes you've guessed it... I'm trying to work out recent team form to predict match outcomes for financial gain...
I have a row of golf scores "I3:X3" they either have a number value or "DNP".
I want to SUM only the last five number values and exclude the rest. The list will get a new entry every week. ie I want to SUM the last 5 most recent scores. There may be 15 or 16 scores or only 3, but I want the last 5 (or less) most recent.
i need to count the number of used entries in a range.
so fex the range is A1:A10 but there are only 2 entries what i search is the number 2
is there a VBA function or so doing that.
ps i have this range.Rows.Count but this is counting me the length of the range even if there are no entries..
I'm looking for a way to count entries of numbers per day of the week within a date range. Example
Start End Mo Tu We Th Fr Sa Su TOTAL
2/10 2/23 1 1 1 1 0 1 1 12
3/1 3/31 1 2 1 1 1 1 2 40
I don't know if that possible in excel or its to much
I have a column of values in "£"s and i need to count how many entries there are within a range. I have been able to do it with text but not money values with £ signs!!
View 2 Replies View RelatedI have a spreadsheet I use for creating golf score cards. I select the golfers from a drop down list and display a message if a golfer has already been selected. The ranges I want to use are A7:A15, A25:A33, A43:A51, A60:A68. My code is below and I am getting an error saying 'compile error, argument not optional' and UNION is highlighted.
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Union(Range("A7:A15, A25:A33, A43:A51, A60:A68"))) Is Nothing Then
With WorksheetFunction
[Code]....
count unique entries in the Range A1:A10
i have data which repeats but i ant to count only unique entries?
I have a range (A5:DG5). Some of these cells are empty, some are populated.
I would like to display a vbYesNo box that shows the value of each populated cell on a different line, with no gaps for non-populated cells.
Option Explicit
Dim myCount As Byte
Dim myColumn As String
Dim myRow As Long
Sub Worksheet_Change(ByVal Target As Range)
myCount = 0
If Range("B" & myRow).Value <> "" Then myCount = myCount + 1
If Range("C" & myRow).Value <> "" Then myCount = myCount + 1
If Range("D" & myRow).Value <> "" Then myCount = myCount + 1
If Range("E" & myRow).Value <> "" Then myCount = myCount + 1
If myCount > 1 Then....................
The above code pops up the message box when a second entry is made within the specified range, but OK button does not function, contents are not cleared, and Excel locks up.
I have a very big range of data from B4, to a variable other end from which I would like to delete all entries equal to 0.0000 leaving just those with an entered value.
I guess it's just an if question cycling through the rows and columns? Slight complication is it's on the 3rd sheet of a Workbook, as set out in the sample file.
After this manipulation has been done, I then wish to copy the data from the range B4: end of data into the same cells in the output sheet.
My check register was exported to a excel spread sheet where the deposits and debits are all in one column E. The debits are entered as a negative number. I want to create two separate debit and deposit columns with the appropriate data entered. What the best way of moving the data accordingly?
View 3 Replies View RelatedIm building a booking/diary system for work.My system runs 9am till 5pm, 10 min slots. The team is 4 people. So I have 4 columns, one per team member, and each row is 10 mins. 9am, 9.10, 9.20 and so on.
I want to limit it so that if say 2 people already have something in at a time it wont let another person type in that slot. I would prefer a none VBA sollution if possible due to the ancient computers in running it on, but not too fussed if thats the route I go down (as I suspect I will)
The sollution I was considering was having a COUNTA for each range, and if it is 2 or over, not let people type into the other slots. This would also let me add exceptions by using a COUNTIF to minus other characters if needed, but im not sure if I can make excel "block out" a time slot. My other sollution would be similar, but have them to autopopulate the empty cells with an "X" if they are 2 or over as thats what I use to block out slots (breaks, lunches etc)
My other problem is that if they then type into the cell, it will obviously type over the formula, which makes me think it will have to be VBA.
I“working on an excel sheet where i“m copying a range to another place. So far so good. Then I want to remove all double entries for each rows in the new range. I managed to get a code working for one row. When I want to loop it for all the rows in the copied range, I get a an error popoing up when the loop starts working on the second row. Error 457: "This is already associated with an element of this collection" The line creating this error is coll.Add cell.Value, CStr(cell.Value)
Sub Sortere_StederBeta()
Dim coll As New Collection
Dim lcount As Long
Dim cell As Range
On Error Resume Next
Range("B4:U33").Copy
Range("B36").Select
Selection.PasteSpecial Paste:=xlPasteValues
For i = 36 To 65 Step 1
Range(Cells(i, 2), Cells(i, 22)).Select
Set coll = Nothing........................
The problem seems to come from the fact that the Coll (New Collection) is not reseting for the next loop. I tried to set the Coll to Nothing but doesn“t have any effect.
In an Excel sheet, in A1 I have written B1 and in A2 I have written D20.
I have a data panel in B1:D20.
I would like to call a vba function my_Fun that acts on my data panel and returns a double in cell A3.
Instead of writing my_Fun(B1:D20) in A3 I would like to be able to write my_Fun(A1:A2), where the entries in A1 and A2 are called, rather than A1 and A2 being used to define the range themselves.
I've been struggling for hours on what should be a simple formula. I have 6 columns containing various dates. On each row I want to count of the 6 columns how many dates were unique and after 3/15/09. I've been using the following formula however it still counts a cell even if it's prior to 3/15/09. =SUM(IF(FREQUENCY(A1:F1,A1:F1)>3/15/2009,1,0)). I've attached a sample file for reference.
View 2 Replies View RelatedBasically in cells b12:b26 (15 cells) I need to enter the type of item (which is limited to 2 items). I made a drop down list for that range which works perfectly, therefore the user can choose either item 1 or item 2 or blank. However, I need to figure out a way so that item 1 can be chosen a maximum of 10 times in the range, and item 2 can only be chosen 5 times in that same range..
I am assuming that you can't add that condition to the drop down list... So I was thinking of adding an extra column duplicating b12:b26 (c12:=b12 etc...)- and entering a data validation there.
I was going to enter the following:
if item 1 is chose : =NOT(COUNT($c$12:$c$26)>10) &
if item 2 is chosen: =NOT(COUNT($c$6:$c$6)>5).
However, for some reason it doesn't work - it doesn't block the entry if I try to enter 11 item 1s or 6 item 2s.
I have been trying to figure out how to use a named range (on another sheet) to validate the user entered data in a specific column. All my attempts at utilizing worksheet_change event have ended in errors. I've created a simple workbook that shows what I'm trying to do and have attached it to this message. The 'Sample (Data)' worksheet has the data table that would be completed by the user. On the 'Validation' worksheet I have named the range to be used to validate the data as 'rngVal'. So when something is entered under the 'Expense Type' heading, the code would verify that the value entered was contained in the 'myVal' validation list. If it was not present in the list, a msgbox would instruct you to try again. After stealing bits of knowledge and code from many of the posts on this site I cobbled together the following (which results in an error at the 'set rngFind' line):
Private Sub Worksheet_Change(ByVal Target As Range)
Dim rngFind As Range
'Set Target = Cells(ActiveCell.Row, ActiveCell.Column)
Set rngFind = Range("rngVal"). Find(Target.Value)
'If data in column C changes, do the following
If Target.Column = 3 Then
'If the value is found on the validation list, do nothing.
If rngFind Is Nothing Then
Else
MsgBox "You must enter one of the following in this cell:"
With Application
.EnableEvents = False
.Undo
.EnableEvents = True
End With
End If
Else
End If
End Sub
Using the DCOUNT function is generally a straight forward proposition but I'm not getting the expected results and would like for someone to take a look and help me understand why.
Goal: create a count of unique entries within a defined variable date range
I have a data table with duplicate values and need to count unique entries, the result of which will be used in a calculation. Due to a requirement to track the counts in a rolling 30-day period, the flexibility of daily selecting the date ranges is a necessity, which is why I chose to use DCOUNT and feed dates into the criteria cells.
I've been attempting to use the DCOUNT function but I'm not getting the correct result.
Oddly, after duplicating the table and formula on the "Count Repeated Items Once" page, even those results are incorrect.
It seems, too, that COUNTIF does not like (accept) dynamic named ranges. Hard coding the range into the formula yields a result of TRUE, but using a dynamic named range gives FALSE. Anyone else experience this and is there a work around (that is, if I have not erred in its use)?
I need to count the number of unique text values in the first column given a condition in the 2nd column. For example, the formula that I'm looking for should give a result of "3" because it is an SME, and there are 3 unique companies that are classified as SMEs.
Company Name
Company Type
Company A
SME
Company B
SME
(I cant seem to make the tables visible but Company A and Company D right below the header should be in one cell) This should still yield a value of "3" despite having Company A and D in the same cell.
I have a column that is filled with different names. Some names appear more than once. There is one name in fact that appears four times.
I am looking for a formula, or VBA if that's necessary, that will look through the range Y2:Y50, and return a value of 4, because the person that has the most duplicates, her name appears 4 times.
If everyone's name appeared only once, except for a person whose name was in that range twice, then the formula would return a 2.
I have a range that contains 3 cells on top act as titles ie TYPE NUMBER and AMOUNT.
I need to extract a range from the original range where the TYPE changes.
eg
TYPE NUMBER AMOUNT
OSF 1002 10.23
OSF 1004 15.68
SA 1006 100.25
SA 1005 17.89
CW 1112 15.56
So for the above example I would need the 2 OSF entries and its corresponding NUMBER and AMOUNT to another location on the worksheet. The same for the other TYPEs. The number of entries for each TYPE varies so I will not in advance how many entries of each TYPE I will end up having.
I've tried several ways and came close to the extract with the 2 formulas below. If there is a way to combine these 2 formulas, how to combine or any other function to complete the extract?
View 1 Replies View RelatedI have following data from which I want to extract only numbers.
Abc ch#25981Abc ch#25982Abc ch#25983Abc ch#25984Chq#25894Chq#25895Chq#25896Chq#25897Aed chq#25897 Bounced
I have a problem for extracting dates of leave from the attendance. A sample WB is attached. In the attached WB, I want to extract date(s) on which leaves were taken.
View 9 Replies View RelatedI would like to know if it is possible to extract just the number of a row from a range. for instance range("c5") would give a row number of 3. Its probably simple but its evading my attmept to work it out.
View 4 Replies View RelatedUsually I use data connections (using http://www.....) to pull several worksheets(tables) from another spreadsheet. Currently I had a set of worksheets that created in an unstructured manner, the tables are not entered from Row 1 (see attached mockup). Besides using VBA programming, if there a simple way to connect and extract these tables to a fresh spreadsheet for computation. So the users can continue to perform data-entry and this consolidated spreadsheet will refresh whenever open for reporting and visualisation.
View 4 Replies View Related