All of the strings are in one column and are in General format. They contain a few different types of strings: some only numbers, some numbers separated by commas (which makes them NOT numbers according to ISNUMBER), some text only, some text and numbers (separated by comma), some are 3 numbers, each separated by comma, etc.
ldJBlt9.jpg
What I'm looking to do:I want each of these distinct types filtered out someway. I've already done ISNUMBER to filter out the values which are numbers only, no commas. Perfect.
For the next step, I'd like to filter out cells which have only numbers and commas. The comma itself makes them considered not numbers, so how do I add the condition that I'm looking for cells with digits and a comma? A wildcard?
Edit: I've found the following formula which can check if it contains a comma, but doesn't specify between digits or text. Just commas. =IF(ISNUMBER(FIND(",",A1)),"TRUE","FALSE")
I am creating a workbook with multiple worksheets. When row H on the first worksheet matches certain criteria, I want the whole row that this specific cell is in to copy to a separate worksheet of the same workbook in excel so that a list of these rows automatically compiles. How do I do this?
I have requirement wherein, I have to put filter conditions on two separate columns in the same sheet & putting hard coded value eg. "XYZ" in third column for this selected range. I am using macro autofilter but its giving me errors for multiple filters.
I have a column that contains combined data of numbers and letters. I want to filter that column to exclude cells that contain the letter "d" or "f". I am trying to create a pivot table from the results of the filter to only display the ID #s that do not contain the letters "f" or "d".
The data inside the fields is not the same length and is not listed in a particular order. I have over 14,000 records I need to filter.
Data set example: The items in red are an example of the cells I would like to exclude from my worksheet. Is there a way to filter without deleting the data?
I want to find a macro that will auto filter on for example "Banana" would filter to rows 4,5 "Orange" would filter to rows 1,2,5 "Apple" would filter to rows 1,3
I am trying to create a Pivot Table that filters on a range of data.
I have a sheet with lots of data on it, and separate sheet where I have created my Pivot Table and my list I wish to use as the filter values.
I need to extract 630 specific Departments out of thousands and report the movements against those depts. The 630 will change as time goes on and I want to just have a range where I can update the values as and when changes occur. I initially tried using Filter on Department but realized this was going to be a very long winded process.
I am fairly new to macros and I am currently working on a project where I would like to create a 2 buttons which will filter by date and by owner. The spread sheet I have is fairly large and is added to weekly.
I have two objectives that I am trying to reach:
First I need to filter for all past due task items from the Thursday of the week I am working in back and for a specific person (owner) by the finish date
Second I need to filter for upcoming tasks one week out for a specific person (owner). The objective of this is so that on any given day a user can find any upcoming tasks that are due one week out.
If this is not an option. My thought was to create an input box where the user can input the date and the information on the spread sheet will pull all past due items or upcoming tasks. These two items need to pull seperatly not together.
I know I can create a macro by autofiltering by columns however I am trying to avoid having to go in and change the date each week.
Start date is column E Finish date is column F Owner is column L
Is there is some way to filter based on the value in a specific subcolumn.
Using the example of a list of salespeople and their transactions over the year, who sell multiple products, the PT is Sales Person name for the Row Labels, and Type of Product for the columns. The resulting PT has 3 columns, for each of the products - e.g. table, chair couch, and the Values are the total number of that item sold. Is there any way I can filter, so that I will see only those sales people who have sold 3 tables or more lets say. (Thus enabling me to quickly see what other products those sales people have sold.)
(The actual situation is a lot more data heavy than that - it is actually a list of donations for a non-profit, coming in from hundreds of people, across a dozen different categories; I am trying to analyse the extent to which people who gave for a particular category (Direct Mail solicitation) also gave across other categories.
So far I've manipulated things by inserting a '% of row total' value into the PT and then using countif/sumif functions outside of the PT to figure out how many people donated solely to this category (= 100% of row total), and how many donated to other categories too (= more than 0% of row total, less than 100%), which gives me a decent summary of sole donations to this cause vs other categories too, but doesn't visualise what the actual other categories donated to were. I've also investigated making a PT of the existing PT, but I'm not sure if that's even possible...)
(I'm using Excel 2011 for Mac, but if there's some other version that would make this possible, I may be able to use another computer.)
I have a spreadsheet that lists users plus 4 columns of data horizontally and other data vertically. See image forum_sheet.png
My problem is the sheet is getting wider and wider as users are added. What I would like to be able to do is have a dropdown or something that allows me to select a users name which would then display their data set [ four columns ] on its own. I have tried validating a dropdown list but this only works on a single column.
What's the best way to look at two sets of data in excel and make comparisons between them. For instance how many matches (and what are the matches). Workbook attached as an example.
So I have two sets of data, I've attached a worksheet which has them both. The datasets have different intervals, but I need to drag "Colour" from Data2 into Data1 and associate it with the correct interval. Sometimes the intervals match up and sometimes they don't - which could be a problem, but as long as its within a small spread it's fine. The "Colour" column in Data1 is filled with my desired result, the source being Data2.
I have several data sets taken from an instrument recording the same sample. The data are noisy so I would like to average them. Annoyingly, the operating software for the instrument allows you to specify start and end values, but not the amount of points in between (unbelievable, I know). I therefore have data which looks like:
25, 1 25.1, 5 25.6, 7 25.8, 10 25.9, 12
and
25.1, 1 25.4, 4 25.7, 6 25.9, 14
So each replicate has a different sampling rate (the data set is much larger than the example above though). Is there any built-in Excel function to standardize these data to a fixed amount of x values, and perhaps averaging both the X and Y values of all points within the bin?
I have a large set of data in an array of many cells. I need to subtract a fixed amount from each of these cells without changing the location of the cells as there are many different references too them Is there a way to do this?
I own a custom print shop and I am trying to generate skus based on variables..Column A will only have one item (the product code), column B will have the sizes, Column C may contain more or less colors depending on what is offered for the shirt style, Column D hopefully can contain all the combinations. All this data will be in contained within one worksheet..
I'm trying to work out a way to use a MAX function and IF Function to determine how i can find the highest number since a trade began. For example, i have a countif function that is identifying each trade (Column B) so what i want to do is to find the highest number for each trade (Column A) which is represented by dummy variable 1,2,3,4 etc. Please see example below: what i want to do is write code which runs for 3500 lines and finds the highest price since trade began and trade's are represented by 1,2,3,4,5,6,7 etc.
If I have ten numbers which I would like to split into 5 buckets (1,2,3,4,5) how can I best achieve this?
A trivial example:-
The series 12, 12, 18, 18, 25, 25, 34, 34, 40, 40 would neatly spilt into 1, 1, 2, 2, 3, 3, 4, 4, 5, 5.
Similarly
The series 12, 13, 18, 19, 25, 27, 33, 34, 40, 41 would neatly spilt into 1, 1, 2, 2, 3, 3, 4, 4, 5, 5.
A non-trivial example
The series 12, 13, 40, 41, 42, 50, 70, 71, 89, 90, 91 I would like to be split into 1, 1, 2, 2, 2, 3, 4, 4, 5, 5, 5.
So groupings are based on how close consecutive numbers are to each other and there must be at least one value in each bucket (1,2,3,4,5). The number of data items can vary form 5 to, say 20 or so.
I have 2 datasets. 1 of them is 5103 lines and other one is 5121. They contain the samde data but as you can see one of the dataset have 18 more lines than the other. Because of this I can not copy a columns from one dataset to other because the values will not match with the correct ones because of the line differences. How can i compare for different rows or how can i copy at least the 5103 line correctly and than fill other 18 by hand ?
I am collecting data the temperature of an animal, i am also collecting ambient temperature at the same time when i plot the two data sets there is an 89% Correlation so i know the animal temperature is affected by the ambient temperature. i want to be able to take this influence of ambient temperature out of the recorded temperature. I dont think its a linear relationship
I have a list of people whose direct reports have either completed or not completed a task at work, and there are four tasks. The records look like this (I've removed the direct report's name, which is to the left):
I want to pivot the data so that for each manager I can see the % who completed Task 1, the % who completed Task 2, etc. preferrably in a horizontal format.
So, I can get this easy enough for the first task:
Count of Task 1Column LabelsRow LabelsCompleteIncompleteGroup 1Danny Ledbetter67%33%Mickey Smith75%25%
However to the right of this I want the results for Task 2, 3, and 4. The best I can do is the following:
Column LabelsCompleteIncompleteRow LabelsCount of Task 1Count of Task 2Count of Task 1Count of Task 2Danny Ledbetter67%67%33%33%Mickey Smith75%75%25%25%Grand Total71%71%29%29%
The results for Task 2 are incorrect. I've tried % of Row, % of Parent Row, and all the other calculations, and can't seem to get it. I've also tried putting the fields in the Column Labels box, the Values Box, etc.
I can even get this if I put the SumValues field in the Row Labels, which yields a vertical result, but still, the values are incorrect (Mickey has 100% completion under Task 2):
Column LabelsRow LabelsCompleteIncompleteDanny LedbetterCount of Task 167%33%Count of Task 267%33%Mickey SmithCount of Task 175%25%Count of Task 275%25%Total Count of Task 171%29%Total Count of Task 271%29%
I have a large collection of datasets, sorted/grouped by rows. I need to perform some calculations against these SETS (Ave, Low, Median, etc.).
The number of member in each SET is variable (1 to NN), the number of SET Members is included in the data. Example:
Code: 3 Data Sets: Row Set Members Price Ave 1 A 4 $1.00 2 A 4 $1.25 3 A 4 $1.50 4 A 4 $1.75 5 B 1 $4.00 6 C 3 $10.45 7 C 3 $14.50 8 C 3 $17.75
how to compute the AVE value for each SET above?
I am thinking if I can determine the FIRST Row number for each set, add the MemberQty as an ~offset to determine the LAST Row, then use an Indirect reference to compute the ave, something like:
=AVERAGE(INDIRECT("D"&A1&":D"&A1+C1)) The SET's Ave value should be added to EACH row in each SET.
I am struggling trying to point a formula like this to the First Row of the NEXT set.
My problem is that whilst dealing with large datasets, i'd like to be able to create a macro which will give me a pop up box to input different types of keys words to search within a dataset. Once these keys words have been found, i would like excel to highlight the entire line so it can be extracted into a new worksheet.
i have list of users with name, company, division, subdivision, date ...
the list can contain more than 10000 users and i want to be able to switch 2 users using a listbox/filter/ validation list.
there can be multiple users with the same name even company so a simple validation list which is limited to 1 column will not suffice. the interface for the switch should be fool proof so anyone can switch 2 users.
I am facing a problem with WorksheetFunction.Correl I got information, that matching class properties of Worksheetfunction is impossible. This is my first experience with macro's and I hardly know how to deal with this problem.I got some excel file from my friend and tried to do this in the same way. Macro was supposed to calculate the Pearson's coefficient of correlation for orders from 1 to 100 of autocorrelation in order to calculate Q Box-Pierce statistic.Data is prices of stocks for some bank and correlation coefficient is calculated for simple returns on stocks.
I need to find the median of Column C and Column H and then divide the median sum of Column C by the median sum of Column H. My data contains blanks so I obviously need to count cells with data only. I am trying to avoid hard coding any cells and am looking for a single formula.
I have two different data sets that I have plotted using XY(Scatter) w/ smooth lines. Is there a macro or sub that will calculate the volume or area between where the two data sets cross?
I am currently using Excel 2013 inside of Windows 7.
I have 3 different data sets and in each data set the only unique identifier is the card number. I currently have to manually sort each of these fields because of the different variables in each data set. My overall goal is to align every user across each row so I can verify whatever status I have for the Card # in the first data set is the same across the other data sets. Is it possible this process can be automated by using a VBA macro that sorts, aligns and leaves empty spots when needed. Once again the only unique identifier that matches across all data sets is the Card #.
User Name Card # Status User Name Card # Status User Names Card # Status
John Smith 12345 Active John Smith 12345 Valid John J. Smith 12345 In Use
I want to create a chart on a sheet. If I were charting one data set it would be real simple; I could do so by selecting the range of data values which are in a single column with the respective dates in another column and chart it with data on y, dates on x.
Each point was sampled on a day, only one point per day. I have a table for each person, one column has the data point, the other has the date it was sampled on. I would most like to pair each for not every person has a datapoint sampled on each day.
I have ten individuals with that metric (dataset based on the same sampling). I want to add and erase a line on one chart for any and each person by clicking a toggle button for that individual so we can do any comparative thought of.
