I'm working on this sheet and I can't figure out how to do it.
I'm trying to determine the variable "selling price" on the basis of given values, buying price, Net profit and Expenses.
One aspect of my Excel-based project involves comparing the operator-entered part number (in Excel) to a predetermined list of part numbers in one column of an Access database table. Right now, my program is telling me that every part number entered in the spreadsheet (50+) does not match any part number in the database, which I've verified to be incorrect. I've checked that both the spreadsheet part number and the database part number are of the string datatype.
To the best of my knowledge, my looping logic seems valid and robust. To the best of my knowledge, there are no hidden characters in either the database cells or in the spreadsheet cells causing this apparent mismatch. I'm completely stumped at this point as to why my program doesn't detect any matches between the spreadsheet and the database table. Below is the Sub containing the code for checking that the part numbers match:
[Code] ....
This issue seems to be a hybrid issue between Excel and Access with (to me) more of the issue on the Access end.
I have data that looks like this:
day# id amount
1 56575 0
1 56675 0
1 56680 0
1 56683 0
1 56681 0
1 51810 0..............
How do you write an excel macro that looks at the number in the first column (day #) and finds all the duplicate id#s in the second column that are in day 1and adds the amounts together in the 3rd column then writes the first column number (day#), second column number(id#) and the third column (sum of the amounts of duplicate Id#) to an new worksheet. Then the macro would loop through day #2 and do the same thing. Notice that the values in the id column are unique in this data set below this is how I would like the data to look. I have accomplished this in a pivot table but my problem is I need a cvs file to export the final data into an external database which is why I need a macro.....
I am a behavior specialist and I have created a spreadsheet to determine what possible antecedents trigger certain behaviors. In other words, I want to know what occurs, like being around an unfamiliar person, right before a person engages in an "acting out behavior" like becoming verbally or physically aggressive. In my spreadsheet I have a number of possible antecedents with a number of possible behaviors. It is tracked by the date that the behavior occurred.
View 6 Replies View RelatedI am trying to determine if the value in cell Y2 also exists anywhere in cells AE2:QE2.
My initial stab at it is
[Code] .....
This results in a #VALUE! error (I am pretty sure that's because excel doesn't like the AE2:QE2 part).
I would do a formula-driven conditional format for this, but I have 2000+ rows that need to be acted on, so that's not feasible.
Is there any way to have excel check if the Y2 value exists in AE2...QE2, cell by cell?
I would like to be able to output the rows in the attached spreadsheet to separate sheets on the basis of whether they have a Y or an N in the four rightmost columns - i.e. I want to make SOLO, DUO, TRIO and FULL BAND sheets.
I would ideally like these sheets to update automatically when I change the data in the main spreadsheet.
I am using Office/Excel 2007 and Windows 8
I want to determine the number of values SUMIF/SUMPRODUCT functions used/checked to arrive at the answer, i.e. 95 for row 2 and 116 for row 3. A function that can give a 3 and 2 respectively.
Example:
Results for Row #2 gives: 95 with 3 values picked up under "Rate"
Results for Row #3 gives: 116 with 2 values though three places were checked up under "Rate"
How can I count the number of these values referencing at the "Rate" like in the functions used.
I managed to use SUMIF/SUMPRODUCT functions to calculate totals based on "Rate" in row one.
I tried COUNTIF function, but allows only field/heading.
A
B
C
D
E
F
G
[Code] ..........
results for each formula used
95 formula: =SUMIF(B$1:G$1,B$1,B2:G2)
95 formula: =SUMPRODUCT((B$1:G$1=B$1)*(B2:G2))
3 formula: =COUNTIF(B1:G2,B1)
See the attached excel table. I need the cell E4 to keep adding the values typed in the cell D4 on daily basis.The cumulative shouldn't be changed if there is no value in the cell D4. E4 should accumulate and keep the totals typed in D4...
Cumulative Input.xlsx‎
I need a macro that will create a sheet at the end of the workbook.
Sum data from a variable amount of sheets and display that data on the created sheet.
Here is a step by step:
Starting on sheet 5.
Column D has a variable amount of part numbers in it. These part numbers would be different between the ascending sheets.
Column T, U, V has an inputed number in it that would need added up across all duplicate part numbers in all the sheets.
(Note: The data would also need started on row 4. Everything above row 4 is headers)
Here is a small example:
D E T U V
13019090W Part A1
68705500 Part B1
64202900 Part C-11
59634600 Part D1
26005300W Part E1
I need the macro to start with sheet #5(starting on row 4). Check to see if there is data in column T, U or V. If there is, to create a new sheet at the end. And copy the entire line into that sheet (starting on row 4).
After that, to check every sheet after (excluding the newly created one, starting on row 4) for data in Column T, U and V. And then check for duplicates in Column D on the newly created sheet. If there is a duplicate to add/subtract that number in Column T, U and V to the SUM in column T, U and V in the newly created sheet. If there is no duplicate, to copy the entire line to the new sheet.
So that when finished. On the new sheet, you have the SUM of T, U and V for everything that has data in T, U or V for all of the previous sheets, plus the entire line of the first instance (excluding the first 4 sheets).
I've got a formula which makes a word "Order" (column B) meet the closest value of 35.00 (column A)
How to modify the formula so that ''Order" meets the closest value of 35.00 in the range (>=35;<39) throughout the column?
There are about 20 approximate and precise values of 35.00 in the column A which have to be met by "Order".
I've been trying to change the value comparisons and precisity (pecentage) to set up the range from 35.00 till 39.00.
but encountered a problem that "Order'' often meets two closest values of 35 till 39, often one of them is going under 35.00 e.g. 36.50; 34.00.
Consequently how to change/substitute the formula, parameters, value comparisons etc. to meet the requirements!
See the workbook attached. 13_05_det_closest_value.xlsx‎
What code can i use to determine the same rows in 2 different columns and compare the data in those two cells?
View 9 Replies View RelatedI have a document in which I am trying to determine the length of time between a start date & time and a finish date & time. The format of the time/date cells is
mm/dd/yy hh:mm:ss
Basically, I am just subtracting the first cell from the second. This works fine as long as both dates are in the same day, such as
START FINISH ELAPSED
08/20/14 23:42:22 08/20/14 23:43:59 0:01:37
However, if I have a situation such as
START FINISH
08/18/14 23:00:15 08/19/14 0:03:22
the ELAPSED cell fills with ######## with a tooltip stating that "dates and times that are negative or too large display as ######." The actual result in this example should be 1:03:07.
How do I get consistency amongst my resulting formulas?
I am trying to figure out a simple formula on determining class for students according to their achievement /grades. There are 9 classes altogether ( 7A, 7B, 7C, 7D, 7E, 7F, 7G, 7H and 7J). There are three columns for subject ( MATHS, ENGLISH, SCIENCE). So lets say the student get Grade A, A , A : The student will belong to Class 7A.
As attached.. TEST PSR.xls‎
My question is about determining whether or not a particular letter is uppercase.
I've written code that reads from a text file one character at a time. And I know that the following determines if each character is equal to the letter "a".
I try to do the following I get an error 'Subscript out of range'
View 3 Replies View RelatedI am trying set up my spreadsheet so that when a number is put into one column. The next column is filled with a number that will correspond to the other number specified from a range of data.
Distance Sheet no.
55
68
93
135
186
So in the sheet No. column I want it to read the number in the Distance and put in the Corresponding sheet number.
So if:
Sheet 1 is : a distance of 1 though 20
Sheet 2 is : a distance of 20 though 40
Sheet 3 is : a distance of 40 though 60
Sheet 4 is : a distance of 60 though 80
Sheet 5 is : a distance of 80 though 100
Sheet 6 is : a distance of 100 though 120
Sheet 7 is : a distance of 120 though 140
Sheet 8 is : a distance of 140 though 160
Sheet 9 is : a distance of 160 though 180
Sheet 10 is : a distance of 180 though 200
How can this be automated without writing a ton of nested if statements.
This is an example but in reality I have 29 "sheets" with different ranges to separate.
I have about 20 files with check posting dates stretched over several years. Maybe a million checks total. The company uses 13 four week periods each year, not a calendar month/year. I need to look up each posting date and attach the correct period to it.
I could do a VLOOKUP with one row for each day of the year, but that would require 2 x 365 row's for just two years. There must be a more elegant way, maybe using an array or some IF statement.
Attached is a sample file, xlsx, with the perior/year info and sample posting date info. Can someone point me in the right direction so that I end up with the correct period/year in column 'C' for the applicable posting date?
There are a lot of worksheets with a lot of similar data from which I have to determine the threshold. So if possible I would like to create a macro.
I want to know the threshold value when Y=2.
The threshold can be between different Input Values (=Stimulus Intensity (µA)).
For example: the threshold of amp P2-N1 & P3-N3 lies somewhere between 200 and 252µA. The threshold of P1-N1 lies somewhere between 318 and 400µA.
Is it possible to determine the threshold value with a formula?
The table I'm trying to query contains the beginning and end date the data is effective. The end date seems to always be either 27 or 34 (4 or 5 weeks) after the start date.
This is the table I'm trying to query:
[URL]
I want to have it query the whole history going back 2 years, and be refreshable by other users on demand.
If I put in dates that do not match up exactly with the start/end dates they have set, I get something like this:
[URL]
I've got a plan of how to approach the data extract, but how can I determine whether or not the web query found a specific table (and should therefore try a different date offset)?
I am putting together a spreadsheet and I want to loop through a series of columns (G to L let's say) and in those columns I want to look at a range of rows (4 to 17 let's say). And if that range has no values in it, I want to hide that column and then move on to the next column. I am having a bit of trouble figuring out how to determine if the range is blank and then building that into a loop.
View 5 Replies View RelatedI've recorded a macro that selects a bunch of cells so I can work with them. However, it's hard-coded to the bottom cell of H1551, and I need it to work no matter how large the range is.
Code:
''' Concatenate column H with B & F
Application.Goto Reference:="R2C8"
ActiveCell.FormulaR1C1 = "=CONCATENATE(RC[-6],"" "",RC[-2])"
[Code]....
I have a situation where I have a column with 10 numbers in it... say A1 - A10 each contain an equation that will solve to a numerical value. If one of these does not solve and gives me a "#VALUE!" response, how do I tell a function to look for that? I'm specifically attempting to use Match to find it, in this case "=MATCH("0",A1:A10,0)".
View 4 Replies View RelatedIn cell A1 I have a date entered as text as "Apr 2007". (That's the way my tool pulls it. Format can be changed if it helps)
I was able to pull the Quarter and year (Q2 2007) using...
A2 ="Q" & ROUNDUP(MONTH(A1)/3,0)&" "&YEAR(A1)
I need to pull the next three quarters and their year. (Q3 2007, Q4 2007, Q1 2008)
I am tracking attendance of individuals in an Excel spreadsheet. I would like to determine how many consecutive days each individual has attended. The spreadsheet has two pertinent columns which contain the individual's name and the date of attendance. How can I determine the number of consecutive days of attendance?
View 5 Replies View RelatedHow can I determine if the user selected "disable" at the initial macro prompt screen.
Basically, I would like a formula/function that can say whether Macros are switched on or off by the user.
I have a spreadsheet with a variable number of xlButtonControl type form controls. The actual number of buttons depends on the amount of data that the spreadsheet is being asked to summarise. All the button controls use the ".OnAction" property to point execution on click to a common procedure.
Unfortunately that much is what it is and is beyond my control, so I just have to work with that.
What I would like to know is if there is a property or method I can access from within the common OnAction procedure that enable me to identify which button was pressed. If this was a CommandBar environment I would use the ActionControl property. Since it's not, and since I can't change it (not even to use ActiveX controls!), does anyone know an equivalent that I can use in this case?
I have a huge list of accruals and payments. Accruals (positive) are entered, and at a later date are offset by the payments (negative). Im trying to make a schedule so i can determine which are left over.
This is easy to do manually for a small amount of rows. However im dealing with 5000 rows and i do not want to manually match it would take many days to do.
Ive tried a duplicate remover. To get it to work i made an absolute value column for the negatives, and compared it to the positives column to find the duplicates. This works to a point. However, If i have three accruals for 100, and one payment for 100, all are identified as duplicates which obviously is not what im looking for.
I need to get it where one accrual is matched to one payment. if there are 3 accruals, 2 payments, 1 is not a duplicate. if there is 3 accruals, 1 payment, 2 are not duplicates.
Using only basic formulas (no VBA then), I need to solve the problem of a VARIABLE SUM of values:
Starting always from the value 1:
- if “control” is “x”: the formula has to write in its “memory” the value 2 and in the next row the new value will be the sum of the two values (1+2), so 3;
- if “control” is “y”: in the next row we repeat 1.
From this point:
- if “x”: we add to the last value of our series another value that will be the last used value and in the next row and in the next row the new value will be the sum of the left value with the right value of the series (in this moment 1+3), so 4;
- if “y”: the formula has to delete from its “memory” the two values that formed the previous sum (in other words the external values of the series) and in the next row the new value will be the sum of the left remained value with the right remained value of the series maintained in “memory”. If remains only a value in the next row we’ll write that single value.
N.B.: if “control” is “z”: the formula must hold in “memory” the sum of values and write that sum in the next row but without considering because all the operation are “suspended”! When “control” will return to “x” or “y” operations of summing or cancelling will start again.
At the end: when everything will restart from the beginning of a new session with the value 1 and “forgetting” entirely what happened above??
There’re 3 cases:
- when all the values are deleted, in the next row will restart a new session with the value 1 “forgetting” entirely what happened above;
- when the result of the sum is >= $A$2, in the next row will restart a new session with the value 1 “forgetting” entirely what happened above;
- when the value of the column “heart” is >= $B$2, in the next row will restart a new session with the value 1 “forgetting” entirely what happened above.
Excuse my english and if you need any clarification… just ask!
No need for this in only a column, you can use all the intermediate columns you may need.
After spending 3 weeks on this I really hope that someone could help me solving this VARIABLE SUM of values.
I want to determine the range in the countif function automatically and relating to a date (i.e. today).
In the attached sheet there are two employees who worked during a certain period, a day worked is 1
Then I would like to count how many days each employee has worked up until today, counting the 1's in the row of that employee until today.
Without VB, is there a way to write a formula which when given a certain month, will output the date of, let's say, 2nd or last friday of the month?
View 14 Replies View Related