Selecting Data By Values
Mar 19, 2014
I have a worksheet with about 10,000 rows of data and from this raw data I need to create 60 or so separate spreadsheets for 60 different companies- so around 160 some row actually pertain to a given client. However, it's not fixed and from one month to the next the actual number of rows changes so I can't just use a simple range. There are two possible ways to mark that the data pertains to a new client. But it will be easier to just show a quick example:
1/1/2014
CompanyA
Total
CompanyA
1/1/2014
CompanyB
So anytime it runs into "Total" in column 1 it should know to start another iteration or when the cell in column b does not equal the cell directly above it. Below is a truncated version of what I have so far:
Sub MonthlyStatements()
'
' MonthlyStatements Macro
'
' Keyboard Shortcut: Ctrl+w
'
Dim taxi As String
Range("F32").Select
Workbooks.Add
[Code] ......
View 1 Replies
ADVERTISEMENT
Jan 23, 2007
I m trying to make a button to add values to another sheet in my xls. Ive done that... now i try to autofill the percentages from left and above one row....
View 9 Replies
View Related
Mar 10, 2014
Using VBA, I need to Select A1:C14.
The problem is that A1:C14 contains blank cells, and there is also an adjacent column D that I do not want to copy.
So, UsedRegion and CurrentRegion aren't doing it for me. (It selects Column D too.)
Obviously, this is an example...the real data set is an export and varies in size.
View 1 Replies
View Related
Jun 15, 2007
I'm trying to use data validation to restrict the user to only selecting values in a list which I create. Right now, the list is a named range. I'd like to get rid of the range and just use a named list. I create a name using the following as my list.
Insert > Name > Create
Name: Fruit
Refers to:
banana,apple,orange
When I try to use the name Fruit in my data validation, I get the message "The List Source must be a delimited list, or a reference to single row or column." I thought my name "fruit" was a delimited list.
View 4 Replies
View Related
Jan 11, 2009
i dont use excel that often, and normally only for really stuff, so apologies, but i couldnt find a solution.
I have a spreadsheet of expenditure, with a column for a category of what was purchased, and then the amounts in the next column, arranged chronologically. eg
01/02/08 food 20.68
06/02/08 petrol 44.65
07/02/08 food 117.65
etc, with various oither columns for week nos (for averages), comments and the like.
Now I would like to do some analysis, for example the mean weekly food spend. I think this should involve checking the category column for "food", and if found, adding the adjacent cell (containing the amount spent) to the sum (and then obviously dividing by number of weeks) - but i cant work out how to do this!
View 3 Replies
View Related
Dec 31, 2009
I'm trying to create a formula that searches through a row and selects certain values. I have a list of individuals in rows followed by their 18 scores in columns. What I'm trying to do is only select the best 8 of those scores and add them together. I tried a few things but this is a little over my head.
View 5 Replies
View Related
Feb 3, 2010
VBA and selecting non-zero values.. ...
View 9 Replies
View Related
Jun 21, 2007
I have a table of numbers to which a new number is added every day. I want to reference the last value in a separate cell. For example, in cell A1 I want to insert a formula that will look at Column B and select the last value. The values in Column B wil be added to each day, ie a new row will be inserted.
View 9 Replies
View Related
Jul 18, 2009
find a book with two sheets. One contains data and the other is the report that I must generate. In sheet one ull find the list of all the trucks received.
I would like to see all trucks that I have received in the month of say June in the report sheet as per its arrival date when I change the month on the top of the report.
View 2 Replies
View Related
Mar 5, 2009
I am trying to create a spreadsheet to select different values. I have 5 columns with type A values then 3 cols with type B values, a blk col then 1 cols with type B values and 5 cols with type A values then 1 cols with type B values and 2 cols with type A values a blank 1 col with a B value, 2 cols with A values and 2 cols with B values. [ A A A A A B B B Blank B A A A A A B A A Blank B A A B B ]
I need to select 5 lowest values from this set but a minimum of three A values need to be used. I have used the min, small 2, small 3 for the A set then used an IF statement for the fourth value. The formula returns the correct sum for values up to the fourth value. The problem comes on the fifth value. I am attaching the formula I started with.
=(MIN(F3:J3,R3:T3,V3:W3))+(SMALL((F3:J3,R3:T3,V3:W3),2))+(SMALL((F3:J3,R3:T3,V3:W3),3))+(IF((MIN(K3: M3,U3)<SMALL((F3:J3,R3:T3,V3:W3),4)),(MIN(K3:M3,U3)),(SMALL((F3:J3,R3:T3,V3:W3),4))))+(IF(SMALL((K3: M3,U3),2)<SMALL((F3:J3,R3:T3,V3:W3),5),SMALL((K3:M3,U3),2),(SMALL((F3:J3,R3:T3,V3:W3),5))))
View 4 Replies
View Related
Jan 30, 2008
I have four ranges of data in a worksheet that are sorted in order by the value in the final columns. Based on this value I would like to flag the highest five values from all ranges by adding a value in the adjacent cell. Is there a formula/function that I can use to identify the highest values from a number of data ranges?
View 9 Replies
View Related
Oct 15, 2013
I have set of data in excel which is basically a table of workload and I need to be able to select someones name from a dropdown list and all the other names are removed.
I've attached a screen shot of an example, I want to be able to select any name from a dropdown e.g. Select Trevor and then only cells which contain Trevor are shown (i.e. the other names disappear). BUT all the jobs 1-10 and Stage 1-3 have to stay even if Trevor doesn't work on them.
Screen Shot 2013-10-15 at 16.44.12.png
View 2 Replies
View Related
Feb 15, 2014
I have a code that works fine, however only uses one cell as a trigger. I need each cell in column M to run the code, so at the moment only M6 will trigger, and not M7.
[Code] .....
View 2 Replies
View Related
Jan 11, 2005
I'm attempting to write a macro that will select all the rows which contain a value in a specific column. Say for example, all the instances of "test" in column C, I need to select the entire row with that value in the column.
I should also mention that by using an autofilter, only specific row numbers are selected. The source spreadsheet is dynamic and will not always display the same value in the same row, and thus I need something a little more adaptable.
View 9 Replies
View Related
Jun 13, 2006
I am trying to select a range of identical values in a column. Basically I need to ask Excel to highlight any and every part of the worksheet where the value '0' (zero) occurs 30 or more consecutive times in any given column.
View 2 Replies
View Related
Oct 17, 2007
My worksheet contains several codes that I then have to lookup in a different worksheet in a different workbook to obtain the relevant data associated with that code. At present I do this by activating the appropriate worksheet, Using Find to locate the appropriate field in that worksheet and then using activecell.offset to get the needed data. This is the only time that these worksheets ever need to be activated so I was wondering if there is a way to do this without having to actually activate the sheet. As an exapmle this is what I currently do:
' variables defined earlier in the program, Tempsheet = workbooks(FileNm & ".xls").worksheet("Temp")
Spark = Cells(TRow, 8).Value
' Activate the reference Sheet
Workbooks("Waveguide Properties.xls").Worksheets("Spark Bends").Activate
Cells.Find(What:=Spark).Activate
COM = ActiveCell.Offset(0, 2).Value
Edge = ActiveCell.Offset(0, 3).Value
SparkArray(i, 1) = ActiveCell.Offset(0, 1).Value
SparkArray(i, 5) = ActiveCell.Offset(0, 4).Value
SparkArray(i, 6) = Spark
' Reactiave the workbook and worksheet I am working in
TempSheet.Activate
View 2 Replies
View Related
Dec 20, 2012
I have to correct a spreadsheet that takes a bunch of values and creates an average.
=average(N15;N29;N43;N57;N71;N85;N99;N113;N127;N141;N155;N169;N183;
N197;N211;N225;N241;N255;N269;N283;N301;N315;N329;N343;N357)*100%
I didn't create this. Anyway, the problem is that most of these cells will be empty unless a whole bunch of other stuff is filled. These N cells take several values, put them together and create a new one. As time goes by they are filled. While they aren't filled, they'll return the DIV/0 error.
So when that formula I pasted there uses all of these values, it also returns a DIV/0 error because it is getting info from cells that have this error.
I managed to correct that with this
=averageif(N15:N357;"<>#DIV/0!")*100%
It works like a charm save for one problem. When I use averageif I'm forced to use an interval. Along this interval other values show up (it is always a value ranging from 0 to 2). So when the formula works its magic the results are slightly skewed because of this other value that I don't want.
So I figure there must be two ways around this. The first one would be using something like averageif that lets me use several handpicked cells instead of an interval. If I try
=averageif(N15;N29;N43;N57;N71;N85;N99;N113;N127;N141;N155;N169;N183;
N197;N211;N225;N241;N255;N269;N283;N301;N315;N329;N343;N357;"<>#DIV/0!")*100%
It doesn't work, it says I have too many values. So if I could the exact same thing as I did with averageif but keeping all of these values it'd be super nice.
The other solution, less nice but equally effective I guess, is using this same interval but having more than one criteria. The first criteria would still be the one telling it to ignore DIV/0 error, the second criteria would be the one to ignore any values equal or lower than 2.
View 5 Replies
View Related
Feb 18, 2014
I have a spreadsheet with two date/time columns 'Date1' & 'Date2'. Each date/time column has its own column with corresponding values ('Var1' & 'Var2'). These dates cover the same time period, however values for 'Var2' were collected less frequently than 'Var1'. I want ONLY the values in 'Var1' that correspond to the dates in 'Date2'
I am trying to select values from one column 'Var1' which have correlating date/time in column 'Date1' that match the dates specified in 'Date2'. Basically I need the values from 'Var1' that match the same date/time as 'Var2'
See the attached image to make it clearer..
Excel2010
Excelhelp.jpg
View 3 Replies
View Related
Jul 11, 2014
I have got historical intraday-data of a stock index and first of all need to summarize them by seconds: The data looks like that:
time price
08.00.00 159
08.00.00 153
08.00.00 148
08.00.01 157
08.00.01 152
08.00.02 167
08.00.02 151
08.00.03 155
08.00.03 154
08.00.03 154
So I want to calculate the average price per second. Is there any formula to tell Excel that it should sort it by second and further calculate the average price of it.
View 5 Replies
View Related
Sep 15, 2009
when run a macro to make the macro select all the cells in a specific column that only contain data i.e. i have a spreadsheet 5 columns wide by 23 rows deep, the macro at the moment selects the 5 columns (this never changes) but the number of rows always changes 123, 56, 1, etc i need to to always select the rows that have data.
View 14 Replies
View Related
Dec 1, 2011
I have made a macro which generates a bar chart for many sets of data.
I'm having a problem at the moment however, as I need to generate a line graph on the same chart. How does one generate another x-axis and graph on a chart? Let me know if you want to see my current macro.
View 3 Replies
View Related
Jul 25, 2008
When I am trying to select a data range on another sheet within the same document, I get an error. My code looks like this:
activeworkbook.sheets("mySheet").Range("A1:D3").Select
View 9 Replies
View Related
Jul 22, 2009
I'm working on a macro that creates columnplots from a sheet of data.
The sheet is organised as a series of tables. Through a userform, the user selects the columns and rows that she wishes to include in the plot.
Is it possible to control the order in which the data is plotted?
For instance, the macro produces the following range to be plotted:
"$D$29, $B$29, $C$29,D33:D31,B33:B31,C33:C31"
where row 29 contains the table headlines, and rows 31:33 contains the data.
However, when I plot this, it always ends up as column B, then C, then D, rather than D first like I wanted.
I'm fairly new to both Excel and Visual Basic, so it's difficult to explain exactly what I'm after, but hopefully someone will understand what I'm after here..
View 9 Replies
View Related
Dec 21, 2009
I have that list in column B and I want to put only those cells that end in "RET" in column C and put only those that don`t end in "RET" in column D, by using an automatic method, otherwise I`m gonna have to do it many times in many worksheets manually ;/
View 9 Replies
View Related
May 16, 2006
I need a macro that will, (i) make a copy of the current sheet, (ii) let the user select the month to be selected from a list (Message box), then (iii) delete all other columns in the sheet that is not from the selected month and then, (iv) total the data of the selected month in the empty cell on the right.
The sheet contains data in the following way:
Column A = Description of data in rows
Column B to IV = in each column is a specifics day data
Row 1 = Branch Name
Row 2 = the date in the format 2006/05/16 for 16 May 2006
Row 3 – 361 = financial data
View 2 Replies
View Related
Sep 12, 2009
I have 2 worksheets where I need to transfer a certain amount of data from one to another depending on dates.
Sheet ‘A’, is the main data source. Col A holds all 365 dates from 1 Jan to 31 Dec(starting at A2), and the subsequent data for each day is held in cols B to P.
Sheet ‘B’ is the destination where the user will specify a key date in cell A3, then in A4 to A32, formulae populate the next 29 dates (ie so there is a continuous run of 30 days).
What I need to do, is to identify the same 30 day range from sheet B on sheet A, then to copy the data in Cols B to P for that range back onto sheet B (pasting in at D3)
View 8 Replies
View Related
Nov 28, 2012
I have a set of data spanning across 400 rows and I want to somehow be able to have a macro to randomnly select say 40 rows of data and highlight it..
View 5 Replies
View Related
Aug 5, 2009
I have ~1,000 rows of data utilizing columns A:C. The data is "grouped" by data that is in column C. Layout is as follows for example
Row01.....Column A......Column B.......Column C
Row02.....some data.....some data.....apples
Row03.....some data.....some data.....apples
Row04.....some data.....some data.....apples
Row05.....some data.....some data.....apples
Row06.....some data.....some data.....oranges
Row07.....some data.....some data.....oranges
Row08.....some data.....some data.....oranges
Row09.....some data.....some data.....oranges
Row10.....some data.....some data.....pears
Row11.....some data.....some data.....pears
Row12.....some data.....some data.....pears
I have been trying to come up with some code that will "select" groups of data and then perform an action on those cells. As an example:
View 5 Replies
View Related
Feb 12, 2010
I am having trouble with a number of workbooks when using vlookup (using the wizard rather than entering the formula directly)
First I select the cell with lookup value, then try to select the table array on the source sheet but the cursor changes to the hand symbol and I can't click on any cells. I have had this problem with many different workbooks. The only solution I have found is to copy the whole sheet, paste-special-values into a new sheet and use that as source.
View 11 Replies
View Related
Nov 22, 2011
I have the following table of data...
ABCDLetter--100.0%-100.0%-----100.0%----100.0%-100.0%---100.0%---100.0%---100.0%---100.0%--0.7%99.1%0.2%-
I would like to populate the Letter column with A B C or D depending on which contains the highest value.
View 8 Replies
View Related