In column E, I want the median of the last 6 entries in columns K to EJ, where EJ is the oldest entry, and I want to median the most recent 6, that is, the last 6 entries counting inward from column K toward EJ. There will be blanks, and each row will have different columns filled, most columns empty, so I want to median the last 6 entered numbers.
View 2 RepliesUsing AVERAGEIF on a row containing both positive and negative values one can easily calculate the Average of only the positive or negative values. I need to filter the 20% outlier on each case, how can I do that with TRIMMEAN ?
This is an example of the current formula using Averageif for the positive values:
=IF(C3=0;0,0001;AVERAGEIF(Auxiliary!B29:BL29;">="&O3))
For now O3 can assumed to be 0.
What I need to do is sort certain entries in longer list (column A, it is in .csv format and needs to be in it so coordinates and names and ID, all sorted with commas) and I have another list (column C) which is shorter list of certain IDs. I googled and tried and got some results for the basic structure but the fuction seems to fail. It doesn't matter how I get that third list done, but there is only one criteria: since the list in column A is really long and those entries need to keep the .csv formatting, the function should copy that info what is in the matching cells.
Let me try to put it simple: .csv cells from column A that have matching ID from column C should be copied to column B (or N).
the function I'm working with right now is
=IF(ISERROR(FIND($C:$C; A1))=TRUE;"0";A1)
My key is in row A (up to 50,000 keys) my entries are in column B. There could be between 1 to 20 entries per key (variable). i need to move the entries across so my key remains in column A and the entries appear in columns B, C, D, etc. Need to end with one row per key.
from this:
823
car
pool
window
345
pen
123
pen
[code].....
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 RelatedI need to assign to a variable, the total number of text entries in col. A (or alternatively in a named range).
View 5 Replies View RelatedUsing 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 have a list of data and I want to identify the unique entries for both columns but the second column has to unique to the unique values in the first column.
Example List
Fruit
Color
Apple
[Code]....
to assign a variable to equal a Constant variable, then I need to find the last unused row on the worksheet, then paste that variable down the column (1-12200 or so rows). I also need to assign Strings for the first two Rows in the target column.
View 14 Replies View Relatedmy existing macro, as the run takes to much time to complete. (at least 20min) I've already tried several loops, but no one worked for me. Following situation: There are two excel files, entries in column 73 - 85 will be copied from WorkbookRust to the other workbook if the numer in column 5 is the same. Not every cell within this columns contains data, so the macro should automatically jump to the last entry in each of the above mentioned columns, instead of predefine the range as you see in the code below. After the data is copied to the other workbook, it will be filtered acc. to Sub FilterMain and then copied back to WorkbookRust. As already said, the whole thing works, just to lame.
Sub Allmacros()
Dim WorkbookRust As String
WorkbookRust = ActiveWorkbook.Name
ChDir "C:Documents and Settings vogtMy DocumentsRüstplausch"
Workbooks.Open Filename:= _
"C:Documents and Settings vogtMy DocumentsRüstplauschCH_Revenue_2008.xls"
Sheets("Main_Overview").Select
Windows(WorkbookRust).Activate
Application.run ActiveWorkbook.Name & "!UpdateEntries"
Application.run ActiveWorkbook.Name & "!FilterMain"
'not ask to overwrite existing file
Application.DisplayAlerts = False
Workbooks("CH_Revenue_2008.xls").Save
Workbooks("CH_Revenue_2008.xls").Close
End Sub
Sub UpdateEntries()......................
I am trying to autofill dynamic ranges that have column variables (d) and row variables (x)... I am having a hard time with the syntax on this
View 9 Replies View RelatedI have a Listbox that outputs data to Column A. Is there a way to auto populate Column B with 4 items for each Entry in Column A?
The 4 items that will populate in Column B will always be the same.
I have attached an example to better explain what I am trying to describe.
Let's say I have a dataset with blank or X in column A, and a dollar value in column B. I want to sum the dollar amounts for column B only if there is a corresponding X in that row in column A. The way I currently do that is by creating a new column C, making a if statement to display the value of B only if there is an entry in A, and then doing operations on that new range. I figure there has to be an easier way. I don't know how to use VLOOKUP, despite trying to read the helpme a bunch of times. Is that relevant?
View 8 Replies View RelatedWhat i want here is the total for a particular id.
View 3 Replies View RelatedI have column E showing elapsed time =($D$5-$C$5)*24. I want two totals from that column. One the total time elapsed and two the number of cells that have an entry as not all of them will. With the time formula, a simple sum function adds all of the columns.
View 3 Replies View RelatedI have data like this
namefoodripeness
andyorangeC
andyyogurtD
andykiwiE
andystrawberyD
andyyogurtC
andybanannaA
andyorangeB
bobbanannaA
boborangeA
bobstrawberyA
bobkiwiB
bobyogurtC
bobpearC
stevekiwiC
stevebanannaC
steveorangeA
steveyogurtC
and i would like the data to look like this..............
orangeyogurtkiwistrawberyorangepear
andyCDEDC
bobACBC
steveACCA
not sure how to do it.
I have a table with Companies in a column and Countries in the heading row. Now in this table I have an entry "1" against certain countries for each company.
I need to get a concatenated (Company&Country) list, but I need this is all in a single column.
Companies list.xlsx
I have an application that on the "Main" sheet, is to extract two numbers then search for them on my "Listpoint" sheet and finally return the text to the right of the search data (e.g. K3)
Working left to right - the user pastes upto 12 lines of code into C3-C22. Formula in E3 extracts object Nos. Formula in K3 substitutes "first" number if it is a zero (with number from A3).
Left to do - Uses data (H3) to search Listpoint sheet colums C and B for a match. then returns text from Column S.
Note Listpoint has 1000 rows to search.
Main
ABCDEFGHIJKL1 2Osn Number Prog Point Text Extracted
ObJNos Zero replaced Name of Object 1 38 10 IF POINT 0|199 ON OR POINT 8|191
ON THEN RETURN FALSE 0 1998 191 8 1998 191
Example found Text 4 20 IF POINT 0|106 OFF THEN RETURN FALSE "HOLD OFF"
[Code] ............
I have 620 columns of data with each column consisting of 17 rows. I need to be able to count the number of unique entries in each column. I recorded a macro that would do what I wanted, but it just used absolute references for each column, which would make the process rather tedious. Is there a way to make the macro less restrictive?
Sub Macro1()
Columns("B:B").Select
ActiveSheet.Range("$B$1:$B$17").RemoveDuplicates Columns:=1, Header:=xlNo
End Sub
I'm working on a project with tables. I'm not sure if I can use a function or I have to vba. I want to retrieve the lastest 5 entries for a specific column (column AA), but not blank ones. Then I want to retrieve latest 5 entries for column AC. On a seperate sheet I want the entries to be seen in order and I want it to change as new data is entered into the table. In the end I'll have the latest 5 entries for Internal (Column AA) and then after that the latest 5 entreis for External (Column AC).
View 3 Replies View RelatedRange("A1").Select
Selection.Copy
Range("B2:B58").Select
ActiveSheet.Paste
Selection.Font.Bold = False
End Sub
in the section of this Macro, how do I simply copy A1 and paste it to all entries in column B. My range will vary (it won't always be B2:B58).
So,
Range("B2:B58").Select
In the same worksheet, I would like to format column "U" based on the alpha character in column "T" and also format column "AA" based on the alpha character in column "Z". There are three alpha characters that will be used in columns "T" and "Z" ( upper case and lower case), "A" with the format of $ #,000 & "H" with the format of $ 0.00 & "P" with the format of percent %. I want to format column "U" & "AA" prior to entering a value.
Column "T" & "Z" are a pull down menus containing only the values listed above (a,A,h,H,p,P)
I had asked and received an answer regarding the above pertaining to columns "A" and "B" and was given an answer that works, however, today my boss changed the configuration of the spreadsheet and for the life of me, I could not get the code to work using the new parameters.
From column A,(lets say cell 10 onwards) I need to copy all the unique entries into column B (cell 10 onwards). However, I dont know what length column A will be. What would be the best way in VBA/Excel to do so ?
View 4 Replies View RelatedI have a specific issue that I have not been able find in here or on the internet. I need to count entries in one column based on a date range using corresponding dates in another column.I found countif, countifs, dcount, etc but have not been able to get any code to work. I don't know if my excel spreadsheet attached correctly but I can explain it since it's a simple one. Column A is filled with report dates that generally increase from 10/1/2012 t0 9/30/2013. Column B is filled with the number of "AFIS searches." If an AFIS search was not conducted for a specific report then the cell is blank. I need to count the number of AFIS searches between the dates of 1/1/2013 and 3/31/2013.
View 9 Replies View RelatedI tried to solve above problem using Data Validation formula. Data validation does not work if somebody copy paste the duplicate values,it works when somebody types duplicate values in next column. So if I want to enter lot of urls to a column then it is completely impossible to type so must have to copy paste from the browser. I have attached a sample sheet, howl to solve this problem either using formula or using Data validation in sample 1 and sample 2 respectively.
View 1 Replies View RelatedIf I sort out the entries in the column customer alphabetically. I want the their respective entries in the further columns to be sorted with them. How do I do that?
Hot Inquiries.xls
This may be a basic question but I haven't been able to find the solution:
I'm using Excel 2003. I have a list with repetitive entries in one column and unique entries in the other columns. Example
0001 a
0001 b
0002 c
0002 d
0003 e
0003 f
I need to filter the list so that only unique entries in the first column show up, but I need the rest of the information to carry with it. Example:
0001 a
0002 c
0003 f
I can't use the Avanced Filter > Unique Entries Only function because the information in the adjoining columns is not unique.
I need to count all unique entries in column A (list of names) (this bit I can do!)...however:
Column B gives each name a value of A or B, I need to count all unique names with an A and all unique entries with a B.
eg
Matt A
Matt A
Matt A
Bob A
Tom B
Tom B
This would give a count of 2 for A's and 1 for B's.
I have a table which looks like this:
Name 1 IDNumber Name 2 Name 3 Column 5
Tom20148 John Malmo
Tom20148 Will Malmo
Bob20206 Will Malmo
Tom20206 Will Paris
Bob20206 Rob Rotterdam
Bob20207 John Rotterdam
Ray20207 John Paris
Tom20208 John Malmo
Ray20208 Rob London
Ray20209 Rob Paris
Bob20209 Will Malmo
Is it possible to have excel go through this list and assign each row a number in column 5 based on the names and the IDNumber? Basically, I would want each entry that is identical in name 1, 2 and 3 to be assigned numbers 1, 2, 3, 4, 5 etc based on their IDNumber. So Tom/John/Malmo with the IDNumber 20148 would get the number 1 in column 5, while the next match (Tom/John/Malmo/20208) would get the number 2 in column 5. For each different match of Name 1,2 and 3, I would want the count in column 5 to start at 1. So Bob/Will/Malmo/ 20206 would get number 1, Bob/Will/Malmo/20209 number 2 etc.
I have data like this
namefoodripeness
bobbanannaA
boborangeA
bobstrawberyA
bobkiwi B
bobyogurtC
bobpear C
[Code]...
Not sure how to do it