This "If" wont work, does anyone have an idea why? I have try hard coded, with "" and without, i tried to assgind the field value to another double patameter and still nothing. This is my last try where i converted it to double type
View 7 RepliesI have a program that uses a query to grab data from overseas. I have an issue where some precision is apparently lost and I was curious if there was a way around this.
As an example of what is going on I have a specific piece of data I am tracking. The value is 14.9 in the SQL database and it's datatype there is a real of length 4. After grabbing this item from the query I checked the record set and the value is still 14.9. After it reaches the worksheet though it is displayed as 14.89999962. The destination cell is currently formatted as General but I have tried Text as well. Both produce the same results. As this tool is very general I would not have it formatted as a number as I don't know what I am getting back exactly.
I know computers store real/single values inprecisely but I see it everywhere else as 14.9. I would think Excel would display what I am seeing. Is there anyway around this short of formatting the cell as number with 1 decimal place?
I am getting a multiplication error in excel when I multiply 1796 X 156 the answer should be 280,176. However, I am getting an answer of 280,187 any suggestions? I am working with mutiple cells ect.
View 9 Replies View RelatedI want to have a restriction that the cell that is to be changed (not the target cell) must have a certain precision eg 0.1 or 0.01 cause i wouldnt want a solution where it changes to 73,67,I would rather it changed to 73,70
View 9 Replies View RelatedIs there a way I can get excel (2007) to limit itself to 2 decimal places for all numbers entered? So effectively I don't want use the formulas TRUNC or ROUND (or any other formula) but I would like rather excel to behave like my calculator where I can limit the decimals to 2 no matter how many I input.
In other words, every time a input a number, excel will round it up automatically to 2 decimal places and the number available will have 2 decimals only. Is there any option hidden somewhere in the Excel options that I couldn't find?
I have a number of houses & a number of water plants (as in water processing plants, not the green kind!) Each water plant can provide a certain % water coverage to all properties. 'All Properties' is classed as the number of houses + the number of water plants. The 'coverage' as a percentage is given by the formula:
View 5 Replies View RelatedI have a data output from MS Query that outputs full date/ time stamps, that is dd/mm/yyyy hh:mm:ss. I've tried =round(), =left() and setting the "precision as displayed" option in the advanced options for the workbook, but the full value remains.
I've also in the past seen other outputs imported into Excel (though not my own) come through with strange formats that can only be properly changed having used the "text to columns" tool, but this has made no difference either.
Unzip Code - Works without Variables, Breaks with Variables.... This has been driving me bananas...
I have the
In the attached Excel file, the data has been place from d2 to dyo2.
For example, value "2607" is placed from cells va2 to wk2.
I need to merge cells from va2 to wk2.
I can't use horizontal alignment - center across selection because my boss wants it to merge only.
I have a list of data which doesnot match with my database and am hence failing to understand what to do.
2 sheets attached.
Was trying to find the employee ids of the advisors from the database.
I would like to open 3 excel files (with identical names) from 3 different mapped drives eg M,N and O. How can I use Excel VBA to rename these 3 files based on their original locations and save them in a folder in the C drive eg M_Filename,N_Filename and O_Filename.
View 3 Replies View RelatedI am retrieving data from 2 different sources in a database.
i am having problem with identical data being classed as different, see below.
******** ******************** ************************************************************************>Microsoft Excel - Book1___Running: 11.0 : OS = Windows XP (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)boutA3=
ABCD11.9.2.1**Access*Security***21.9.2.1**Access*Security***3FALSE***Sheet1*
[HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box
PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.
These two cell are identical (have checked theer are no random spaces) however the formula is saying that they are not equal. They are also in identical formats.
I need a formula to sum column C if data in column A and Column B are found anywhere else in the list. For Example
A............B.......C
0103019..C.......5
0103019..1.......2
0103019..14.....1
0103019..C......4
Column D would show
9
2
1
9
=If(ISNA(VLOOKUP(B2, 'TableB'!$Y$2:$Y$100,1,FALSE)) , "",Column A in tableB)
I want to search tru all column B in table A. If i find an identical value in table B i want the field in table A to show the corresponding field in Column A in table B.
Also if there is 2 occurrences of column B in table B, i want the field in table A to show both values.
I have three large txt files. All three are the same number of columns but are of a different number of rows. (Each month the number of columns will increase but the 3 source files will still contain an equal number).
Therefore I need to open the first text file, select all data and paste it into my master file.
I then want to open the second txt file and paste all the data at the first empty row of my master file.
I will then open the third txt file and paste all the data at the first empty row of the master file.
Finally I will close the txt files.
I will then have several other amendments to make to the master data file.
So to my problem....
Opening the txt files is fine.
However I want to select all the data in the first txt file but excel gives me an error message. (On the line highlighted in my code). I remember there being a function in VBA that selects all data around a cell, can anyone remember how to do this?
My code so far is:
Workbooks.OpenText Filename:= _
"C:CUSTOMERPass 1.txt" _
, Origin:=xlWindows, StartRow:=1, DataType:=xlDelimited, TextQualifier _
:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, Semicolon:= _
False, Comma:=False, Space:=False, Other:=False, FieldInfo:= Array(Array _
(1, 1), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1), Array(7, 1), Array(8 _
, 1), Array(9, 1))
Cells.Select
Selection.Copy
ActiveWindow.WindowState = xlMinimized
Sheets("Data").Select
ActiveSheet.Paste
i have got two tables each on a different sheet. The column headings are not the same but in both tables column A contain names. Users can enter names in column A in the first table, and these should be copied to the second table. However, users can insert rows in the first table which should automatically be copied to the second table to ensure the tables haven an identical number of rows (names)
View 2 Replies View RelatedI need to extract the text located before last specified character () From C:PicturesPersonalChristmasDSC_00001.jpg ----> C:PicturesPersonalChristmas
I found a nice formula in another thread but that one returns the text located after the last "". The formula is =RIGHT(A1,LEN(A1)-FIND("@",SUBSTITUTE(A1,"","@",LEN(A1)-LEN(SUBSTITUTE(A1,"","")))))
I have a list of transactions on accounts that have the account name multiple times. I want a count of just those account names but without the copies for the different transactions. I have like 5 entries (transactions) on say Bob Smith's Towing I need it to combine all five entries into one or delete all but one so I have a true count of how many accounts I have.
View 14 Replies View RelatedI'm working on two almost identical lists but because of their nature, some rows are repeated on one of the sheets and so on. I need help matching some cells onto the other sheet. I tried the VLOOKUP formula but it doesn't work since some rows are repeated... I need the match to link especifically a Name with it's correspondent Value, regardless of the position of the name in either of the sheets and whether it is repeated or not.
View 7 Replies View RelatedHi guys, I am looking for a macro to do the following:
Go down a column and identify all accounts that are identical, then add up the values that are 5 columns to the right of each account and paste them on another sheet along with the account code (there are 12 values for each account to be added with the other identical accounts).
I've attached a spreadsheet, because I understnad this might be kind of confusing. In essence:
1. Go down column C
2. Look for accounts that are identical
3. Add up the values for each account in columns G:R
4. Copy and paste the added values to another sheet along with columns C:F for each account to identify it.
I need to find ALL occurrences of a date on a worksheet, but could not find a macro on our forum.
I found this macro on the web.
I have a database with 6 columns in play (there are actually other columns but they are not relevant). I'll call the columns A through F. I would like to be able to match certain counterpart rows together, do a sort placing the counterpart rows adjacent to one another, and then count how many pairs I have. (Some rows will have no counterparts.)
Here is a micro-illustration of the database:
______A______B______C______D________E_____F
R1___01-03___54____959____nsneakr___24____yes
R2___01-04___67____454____adidaht____53____yes
R3___01-10___42____344____calb3wd___11____no
R4___01-19___67____454____adidaht____53____no
R5___01-25___54____959____nsneakr___24____yes
R6___02-02___54____959____nsneakr___24____no
R7___02-14___54____959____nsneakr___24____no
I basically need to devise a formula or script that pairs together two rows that fit the following criteria:
1) The rows are identical in Columns B, C, D, and E.
2) The rows are not identical in Column F (i.e., one half of the pair should have "yes" and the other half should have "no")
3) The rows are as close together as possible according to the date sequence in Column A. For example, Row 1 should pair with Row 6, and Row 5 should pair with Row 7. Row 1 should not pair with Row 7, and Row 5 should not pair with Row 6. **This criterion seems tricky because R5 and R6 would technically fit the requirement for pairing, were it not for the fact that R1 comes earlier in the sequence.**
I want to delete some rows that are identical. Aside from creating a macros to do this, is there a way to find a row with a specific criteria and delete it if value returns TRUE?
View 1 Replies View RelatedI have two identical drop down lists (months i.e. Jan, Feb, Mar ...) in two different sheets but in the same workbook. Is there any way where the user changes the month in sheet 1 & the other drop down list in sheet 2 changes automatically to the same month selected by the user in the sheet 1 or vice-versa?
View 1 Replies View RelatedI have successfully used dynamic lists that remove items that have been selected from a dropdown menu already, however, is there any way dynamic lists can be set up to handle multiple identical items and then if there are say 3 identical items in the dynamic list, once 1 has been select only 2 remain in the list...?
View 1 Replies View RelatedOn the top, you'll see that even though there is an equation in cell "K" and merged cells "O and P" that the cell is reflecting a blank box (how I want it to look). Within the same workbook, a different worksheet that has the same exact cell settings is showing "$0.00" in those cells. I can't figure out how to get it to be blank! Yes, I tried the copy/paste from the cell that I like to the other worksheet but it doesn't work. Again - all of the cell settings are the same.
View 3 Replies View RelatedI am creating one big workbook with several different worksheets. I need to make the first two columns of every worksheet identical.
I would like to make it so that when an item is changed in any cell in any column on any worksheet, the other worksheets automatically change to become identical.
I am trying to join 2 tables which have identical columns. The columns are:
A B C D E F
Date : Name : ID : Violation : Date Sent : Comments
Table 1 has information in columns A-E, Column F is blank
Table 2 has information in columns A-F.
I want to be able to check the information in Columns A-E in both tables and add the information in Column F from Table 2 to Table 1 where Columns A-E are an exact match.
In each row of my data set, columns A thru E contain different three-digit numbers, in ascending order (e.g. 012 312 344 798 880). No number is repeated more than once in a single row, and no two rows contain the same set of five numbers. Column F contains a value (these are what I'd like to sum).
The variables I want to create a formula with are each three-digit numbers, call them X and Y. I want to find the pairs of rows where one row contains X in one of the first five columns, and the other row contains Y and the same other four values as were contained in the four non-X cells in the X-row (note that X and Y may or may not be in the same column, since each row is in ascending order). If X=344 and Y=955, an example of a match would be one row with 012 312 344 798 880 and another row with 012 312 798 880 955. For each X and Y combo there could be many such matching pairs of rows (with different sets of the other four values). I'd like to sum all the values in the X rows of such pairs (and then I'd do the same with a different formula for the Y rows).............
I have a conditional format which uses this format MAX($N$84:$N$94) to colour the cell containing the number but I now wish to amend this formula so that if an identical number also appears further down in the column only the first of the cells containing the identical number(s) will be coloured but none of the others,
View 4 Replies View Related