I need to remove any data which is say 20% above or below the overall observed data, in order to compute simple High, Low and Average excel function calculations.
Is there any way to remove such outliers when calculating averages, lows, highs etc?
We have an online system that generates xls dump files. These essentially contain a table with lots of information about every test case we perform. From this table I am interested in three fields: TestID, Location and TestCaseStatus. With these fields I need to generate : A drop down menu to filter by location
A table containing the count of each test case status for the relevant location. Every Row represents a TestID and the columns a status.
I was hoping you would be able to give me an idea of a good way to tackle this. My initial thought is that SQL maybe useful to import the desired table columns and then further filtering would have to be done through nested for loops. I'm sure you can suggest a better way though.
Also, if possible, I would prefer the raw data not to be displayed in my analysing spreadsheet - only the summary should be shown.
Is there a way to exclude outliers when doing a calculation? For example, I am calculating the degradation in sales for a given period. During that period a store may have had an extraordinary sales day that is skewing my slope up/down.
Store9/19/29/39/4Slope Should ReturnA15145013-1B131450151C1511413-1D13141151
Is there a good way of excluding an outlier in an average calculation. In the example below will I exclude 1000 from the average-calculation.
The way to decide excluding-values can either be a percent based on the range or everything that is a higher than a user defined value. It can also be more than one outlier.
A user defined function is OK with me, if it is impossible to use the built-in functions.
I have the following code that selects a range based on the rows that have data in the sheet, starting with row 10:
[Code]....
This works well, but by default it captures all columns with data. I'm not sure how to then change it to only capture specific columns.
For example, if I only wanted columns B:D for rows with data, changing it to
[Code] .....
does not work, because the SpecialCells selection overrides that and still returns all columns with data.
Is there an easy way to drop column(s) off of the currently selected range after SpecialCells captures the range?
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.
I am using Excel 2007 on Windows 7. I would like to remove duplicates as follows: If column A = column A, column B = column B, and the two rows are within one hour of each other, then remove all duplicates, leaving one instance of the row. As you can see, sometimes the duplicates are right next to each other, and sometimes have rows between them. (Rows 1 & 2, Rows 30 & 32). Even though this sample doesn't show it, sometimes there can be tens of duplicates within an hour, not just two.
Excel 2007
ABCD1EruptionI Can't Stand The Rain (78)3/7/20129:12:55
AM2EruptionI Can't Stand The Rain (78)3/7/20129:10:02
AM3ChicagoJust You 'N' Me (73)3/7/20129:05:59
[Code] .........
I have a combination chart with data from Sun thru Sat (7 days). My manager wants to display only the weekday data (M,T,W,Th,F) and remove the weekend data (Sat,Sun). However, I also need this data to be included in other charts I need. I was thinking in years past, that I was able to remove specific data from the chart directly without having to alter or change the spreadsheet data? I have copied a second set of data and hid the weekend data, however, I have gaps in the data, which I need to remove in the chart display. Not sure what to do, or the best avenue?
View 2 Replies View RelatedCurrently I've been able to remove the prefixes and suffixes using the replace option as those were consistent across the data set, I'm now left with a series of numbers that look like this:
11.225D2
11.24c1
11.28.r1
11.31.1.2h1
11.36.g1
11.39 A2
11.39B1
11.3c1
In most cases I need to remove the information after the last period. Some of the data has a space at the end, instead of a period and then some have no space or period but have the letter that needs to be dropped. I'm using xl 2011 for MAC.
In my spreadsheet, column A, I have a list is part #'s. I have found at the end of my part #'s there are several spaces in each cell. I need to remove these from the cells so that my formulas will be easier to use. Is there a way to do this? The part #'s very in length and format. They would look like this "100020x0 ".
View 2 Replies View RelatedI am working on importing contacts from Zimbra to Outlook/Exchange.
Outlook is not properly importing the contacts and is consistently importing only some of the data for each contact. So, I played around a lot with the import file and have found that if I delete out all the columns that have no data in them for any contact record, that Outlook imports no problem, perfectly.
So...here's my issue. I have multiple contact lists for each user that I am importing. We're talking about 100 different CSV files to sort through, some of which have at least 500 records (rows) in them. It is a lot of scrolling up and down left and right to find columns manually/visually that have no data in them.
Any manner in which I could add a row at the top of each CSV/excel file, input a formula, drag it across all columns that will tell me if there is data within that row? I tried using a filter, but because a lot of the data columns have no data in the first row across multiple columns, it doesn't seem to be accurate.
I need to give the facility for a line to be edited (Columns A to E would remain as-is, only F would change. I also need to give the facility for a line to be removed completely.
Private Sub CommandButton1_Click()
Dim shDest As Worksheet
Dim shSource As Worksheet
Dim Rw As Long
Set shDest = Sheets("BLOCKTIMES")
Set shSource = Sheets("DATA ENTRY")
Rw = shDest.Range("B65536").End(xlUp).Row + 1
With shDest
'B2 to column B; D4 to Column C; C3 to Column D...........
I have a data sheet which I need to filter in some way with multiple criteria. For example
I have a sheet called Training which has 4000 rows of data. Column F is labelled Cost ID and under this column I have 400 different IDs of which I need to see data for 155 out of the 400.
Is there a simple way of removing the unwanted data?
I have data in the form of a table. For example a list of duplicate names, each of these names have a corresponding date (lease expiry date). The problem is that the duplicate names have varying dates. So the remove duplicates function does not work because i need to remove the duplicate names with the older dates. I want the latest dates to remain behind.
Data Currently: Solution should be:
Sue1/3/2014Mike8/8/2014
Jay25/4/2013Sue1/3/2014
Jay25/4/2013Jay25/4/2013
Mike8/8/2014
Mike8/8/2014
Sue1/3/2014
Sue25/6/2012
Sue1/3/2014
Sue5/7/2012
Jay2/2/2011
Mike5/5/2010
How to remove all the " symbol out of a string of data?
The " symbol appears at different places and i need to remove them all completely. the Find & Replace tool doesn't work because when you have say 3/4" and want it to just read 3/4 it automatically converts it to 03-Apr. even when you have changed the cell formatting to text.
I'm trying to find out the rule for de-duplicating data. I am removing duplicates based on an identification number in a data set of about 6000 records, including the duplicates (some records appear about 4 times). Due to the nature of the data I'm working with, there are only a handful of records that are "true" duplicates, i.e. some of the records appear 4 times but there is a difference in terms of location, etc and some are true duplicates in that there is no difference.
I need to know how Excel removes duplicates - does it only keep the first line that it finds for that identification number? Also, is there a way that I could create a rule for it to keep the record with the highest rate for example?
I have some data that is seperated by spaces. I need to split the data so each is in a seperate cell and then remove the spaces. I can do this if there is only one space inbetween the data, but that is not always the case. I attach a workbook with examples.
View 6 Replies View RelatedI have a worksheet with columns A to E which hold the following data:- Week No, Budget Code, Budget Description, Name and Hours. However, as you can see in the attached spreadsheet there is not always a value in the hours field.
What I want to achieve is to automatically exclude any of the rows of data in the Pivot table that includes zero in the hours field (such as the rows highlighted in yellow) and also not to show if/when the pivot table is refreshed.
I'm doing some simple data extraction, e.g.
A B
1 bob 3
2 mandy 4
3 charlie 6
4 dave 1
5 steve 5
So I had in c1 to c5 = =if(b1 > 3, a1,"") autofilled, which works fine, but I end up with,
gap
mandy
charlie
gap
steve
how would I get,
mandy
charlie
steve
also is it possible to have an if statement in 1 cell change the value of another cell?
e.g.
in a1
if(b1>5, c1="yes",c1="no"), can't seem to get it to work
removing empty rows from my set of data.
I have a big set of data that I'm copying and pasting into excel. When I paste it there are 2 empty rows between each line of data. I want to remove these empty rows, without affecting the data. I tried 'Go to special...' and deleting all empty cells and stuff but I couldn't get it to work. The problem is that there are blank cells in some of the rows that I want to keep. When I try to remove the blank rows, I also end up removing rows of data that I want to keep.
I've made an example for you to try and explain it better - attached.
remove all of the rows that are completely empty, but leave any row that has any data in it (at any point), that would be awesome!
I have this Macro that removes the selected items from the listbox1 and sheet called "Form" the only problem with that is that I have another sheet with a formulas in it that referes to the cells/ranges in sheet "Form" and so when I remove the range in "Form" worksheet the range still exists in my other worksheet where the formulas are and this is causing the errors.
What modifications I need to make to my macro to be able to delete data/ranges from sheet "Form" and sheet "Training Cost" ? so if I delete range A5:D5 in sheet Form it will also delete range A5:D5 in Training Cost.
[Code] ......
I have several fields in a row that contain names of files e.g. 123.xlsx. Some fields will contain file names that will be duplicates of each other and some will be blank entries (although the blank entries can be changed to a value such as 'n/a' or 'no' etc if required).
I require only the non duplicate values to appear in the final cell, each separated with ';'.
My data is in row 2 of a spreadsheet and in every other column (A,C,E,G,I,K,M...for 45 instances in total).
I have used the following formula to identify the unique values (example below for the first four cells):
=A2&IF(C2=A2,"",","&C2)&IF(OR(E2=A2,E2=C2),"",","&E2)&IF(OR(G2=A2,G2=C2,G2=E2),"",","&G2)
This works well and if there are several blank entries then I use a SUBSTITUTE function to change the multiple ',,,,' to a single ';'. So I only see the unique file names in the final cell, separated with ';'.
However, the above formula becomes longer and longer when each cell is added to it. I have over 40 cells that need to be added and I wondered if there was a better way of doing this?
I need to remove any rows containing "EEE" in the size column.
I also need to remove any discontinued ("D" in the "Type" column)styles that are currently not in inventory. For example 05-5490 does not have any available in either width "D" or "EW" and should be removed; however, 28-112 does have stock and should remain in the spreadsheet. Anything with an "F" in the "Type" column should be left alone.
I run a report each week and would like to combine it with my Updated Members workbook and then remove duplicates.
Is there a way to add this new data (ActiveList.xlsx ) to the last row of data of my (UpdatedMembers.xlsx) Sheet and remove duplicates. (I would like the new additions to stay on the bottom of my original data in the UpdatedMembers sheet, so that I can add notes to their row)
I am still learning my way around excel and all of my attempt to do this have been unsuccessful. how to pull this off.
Currently trying to solve a niggling issue with removing duplicate row data from competition entry databases using the following code...
Code:
Sub remove_duplicates()
Dim lastrow As Long
Dim i As Long, j As Long
On Error GoTo errhandler
Columns("A:G").Sort Key1:=Range("A2"), Order1:=xlAscending, Key2:=Range("B2") _
[Code] ......
errhandler:
'MsgBox Err.Description
End Sub
What I need to happen is for duplicates to be based on both the name and email matching (columns A + B) and then remove all duplicate rows, whilst ignoring but preserving the content in columns C,D,E and F on those rows that remain. Column G contains either "Yes" or "No" depending on a users membership status. Sometimes duplicated data will contain identical name and email address but have different membership status in column G where someone was logged in when submitting a form the first time, and then logged out the next (potentially a mixture of the two over multiple times). Therefore I need any duplicate users who have different values in the G column throughout the database to be detected by the macro and we must ensure that the single row retained in the final non-duplicated list displays "Yes" in column G for that user.
Duplicate users who are always "yes" or always "no" work fine, its only the ones with mixed values for column G that need to be analysed and their return forced as Yes in the final list.
The final list should preserve the original column structure and thus be only one row per unique user.
The above code appeared to work the first time with a sample file, but when running the macro on one of my full size lists (3k - 5k rows) it returns 2 rows for each user who started off as a mixture of Yes/No... ?
Both rows will say yes in column G (so that part is correct!) but I have to re-run the macro again on the same dataset for it to remove the duplicate yes rows to get the list as it finally should be!
So I can achieve the result by running it twice, but this is a bit of a cheating way to do it and also the next stage after this involves something similar across multiple worksheets so I need something rock solid to build upon!
I have a 5 column document with about 3000 entries that I need streamlining. THe first column is a student's name, then grade. The next 2 columns are for Honour Roll Standing (A or B) and/or an Effort Honour Roll Standing (E). Finally there is also a "term" column indicating if they received this award is Term 1, 2 or 3.
An example source file is found here: [URL]...
As you can see, there are many students who have received an award in all 3 terms, however there are also some students that only received standing for 1 or 2 terms.
I need to manipulate the data so that there are no duplicate names and all the data regarding Honour Roll standings and which term it occured in are all laid out in one row per student.
IDEAL COLUMN HEADINGS:
NAME | Grade | Term1 HonourRoll | Term1 Effort | Term2 HonourRoll | Term2 Effort |
etc etc etc
I have created a dynamic chart but I want the axis to only reflect data greater than 0. Can the axis' of a chart also be dynamic with the dynamic chart?
Upon request...Will email spreadsheet if need to review.
I have a workbook with a Pivot Table full of user data and security reports. The table is structured in a way that shows, numerically; each username and how man roles are assigned. I need to remove the username/row combo's that are listed as 1 or less. Obviously if I simply highlight the fields I want to delete it won't work. How would I go about removing such rows?
View 2 Replies View RelatedIf I update my data, then refresh the pivot table, old values still show as an option. How can I delete these items? If it matters, my pivot table's name is PivotTable2.
View 1 Replies View RelatedI have an excel sheet with approx 30,000 rows of data.
These are all keyword phrases.
They can be related to any subject, but for this example these are related to the root keyword phrase of "car Rent"
I have some software which basically pulls in keyword phrases from search engines and meta tags etc, including misspelled keyword phrases.
My problem;
The data is uncleaned.
In other words there might be I think it's called "Carriage return" data in there, so the row of data might be very deep (Instead of a row height of say 10.5 it could be anything, IE some could be 100 or 200 even).
There are unwanted characters, for example; ()[]{}+?!""^*
(If it could delete all unwanted characters except for letters/digits)
There is a problem I see, that if it removes _ or - between words, that it will join the words together which won't be of any use. If it deletes anything with a letter either side of it or a letter and digit, or 2 digits, 1 either side it would then need to add a space to replace the hyphen.
So for example; if there was a phrase in the list like ....