Identifying Incorrect Data.
Feb 10, 2009
In column H I have a list of numbers seperated by a space, the number of lines can change. In column L I have a list of numbers which can change either expand or retract.
I would like to check each cell in column H and if any numbers are not listed in column L then it/they should be shown in column G.
Example1 H2 shows 6 11, therefore cell G2 should show 11.
Example 2 H6 shows 5 6 9 11 therefore G6 should show 9 11
Sheet1 HIJKL1Container ID26 11 135 8 11 245 7 11 355 7 565 6 9 11 675 6 9 Excel tables to the web >> Excel Jeanie HTML 4
View 9 Replies
ADVERTISEMENT
Aug 1, 2014
I just want to ask the user to enter the date in a mm/dd/yy format, however, the date received is some how changing.
For example I execute the following code and get 8/01/14 instead of 8/21/14
[Code].....
I have even tried pulling the date from a formatted cell with no luck.
View 5 Replies
View Related
Apr 22, 2008
I setup this function to pull my staff's schedule an auto generate the forms i need however the lookup function works properly sometimes and improperly others i checked the syntax and it is correct how ever the wrong data is continually returned. I have attached the file if anyone can help. the VISUAL PLAN tab is the one contaning the formula and it pulls data from PLAN, CURENT SCHEDULE, AND CATAGORY SUMMARY however the only formulas hat i have a problem with are the ones referencing CURENT SCHEDULE.
View 9 Replies
View Related
May 5, 2009
I've collected some data from a GPS logger regarding the speed of an athlete. I want to calculate how many sprints this particular athlete undertook during a training session.
Sprinting is defined as a speed of > 20 kph.
One sprint would be the attainment of one peak >20 kph before decreasing below 20 kph.
It's easy to identify the 3 peaks and thus sprints from the xy scatterplot in the attached file, but I'm struggling to find a way to calculate this.
View 11 Replies
View Related
Sep 8, 2008
I am using VBA to export an access query into excel, the query works fine in access but when importing the data into an excel spreadsheet, it doesn't display the date column headers in the correct format.
The problem I have is that the dates 1 to 12 are displayed the wrong way round eg:
In Access date column headers from query are:
09/01/2008, 11/01/2008, 12/01/2008, 14/01/2008, 15/01/2008 etc.
But when exporting to excel, the above dates are shown as:
01/09/2008, 01/11/2008, 01/12/2008, 14/01/2008, 15/01/2008 etc
It seems to be changing round the dd/mm when I export, but only upto 12 when the day is 13 or more it is displayed correctly.
Public Sub bttnDMA_Click()
'DMA Figures in Excel format
'Creating the Recordset
Dim cnn As ADODB.Connection
Set cnn = CurrentProject.Connection
Dim MyRecordset As New ADODB.Recordset
MyRecordset.ActiveConnection = cnn
Dim MySQL As String ....
View 9 Replies
View Related
Apr 22, 2009
I am looking for the easiest way to find duplicate Work Order numbers that exist in 2 separate Workbooks. EX. Workbook 1 Sheet one contains the numbers 1-100 in A1:A100
Workbook 2 Sheet one contains X amount of the numbers between 1-100 located.
somewhere in A:A. For arguments sake let's assume those numbers are 3,6,33,87,99.
What would the formula be to return the values that are in both of the workbooks?
View 5 Replies
View Related
Mar 11, 2014
I have small table i would like to create. Now, it can be done manually ( but its be very very time consuming) but im sure of a way using IFs and VLOOKUPs so that the data selection can be done automatically...
so in column 1 i have various valuations from 0 to anything 50mil plus that i need to then separate into 4 different columns based on their size. so column A would have 0 - 250k, column B 251k to 500k, column C 501k to 1million and etc etc...
vals 0-250 251-500 501-1mill
555,000 300,000
150,00 75,000
Please see attached ... testing values 1.xlsx‎
View 2 Replies
View Related
Feb 9, 2010
I have attached a sample sheet which deals with property sales data, in reference to a two-part question.
1. If the row has two, or more, rows share the same value in column 'E', it needs to be identified with a 'Y' in Column G. In looking through old threads, this seems possible, though I could not find and answer I could 'bend' to work. If this is possible, can the following be included?
2. If two or more rows share the same value in column 'E', list the identifiers (value in Column A) for the others in Column H, separated by commas "," or slashes (preferred) "/".
ie.
"00370600000700 'NICHOLLS JOHN W & CARLA R 11/27/2000 85000 '260647 W Y '00370600000800" and
"00370600000800 'NICHOLLS JOHN W & CARLA R 11/27/2000 85000 '260647 W Y '00370600000700" or
"'00370500000801 'FRAHM FREDERICK/ERIK/KRYSTYNA 06/17/2004 110000 '288904W Y '00370500000802/'00370500000803"
The sample sheet attached includes 26 rows of data with several 'doubles' and one 'triple' 'duplicates'.
Please note that in the 'real' file, it has slightly less than 200,000 rows and I have seen 'dupicates' up to 40 with the same value in Column 'E'
View 14 Replies
View Related
Dec 6, 2006
I am trying to slim down my database results in Excel via MS Query by searching for Part ID's that are numeric (we have parts that also contain letters....I want to weed those out).
In all my searching on the web, I thought the ISNUMERIC() function should be the function for this, but I keep getting an ORA-00904::"ISNUMERIC":invalid identifier....
Is this function supposed to work or is there another function that will do this
This is my SQL statement so far, which works to get parts that are 6 characters long only:
SELECT PART.ID
FROM SYSADM.PART PART
WHERE (LENGTH(PART.ID)=6)
When I change it to this to get parts that are numeric, it gives the error above:
SELECT PART.ID
FROM SYSADM.PART PART
WHERE (LENGTH(PART.ID)=6) AND (ISNUMERIC(PART.ID)=1)
View 9 Replies
View Related
Sep 17, 2009
i am trying to work out how to use the rank formula to rank numbers in column B and keep them in unison with Column A.
So Column A has say 5 1's with column B having different scores then continuing under 1 in A is 2 and so on is there a way to continue the ranking formula without manually changing the cell ranges?
so =rank(B1,$B$1:$B$7,1) but can i do that if A =1 and then A=2 etc ?
so if A=1,rank(B1,$B$1:$B$7,1)
View 7 Replies
View Related
Sep 21, 2009
I currently have a database that is similar to the example I've pasted below:
TypeDateTimeA10/01/200910:00B10/01/200910:00C10/01/200910:00D10/01/200912:00E10/01/200912:00
As you can see the Date and Time are repeated for several rows. This is how the data I receive comes through as A,B and C refer to a single transaction and D & E refer to another transaction.
Ideally what I would like to acheive is:
TypeDateTimeTransactionA10/01/200910:001B10/01/200910:00C10/01/200910:00D10/01/200912:002E10/01/200912:00
Where Excel can compare the date and times of each row and look for matching rows above and below it and then fill in a column next to it indicating that x number of rows are linked to a single transaction - preferably labelling them in some order to I can tell how many transactions there are.
View 9 Replies
View Related
Feb 11, 2014
I am involved in a software conversion that is taking 4 full time folks over 5 weeks to clean up and assign an alpha-numeric sequential number to each vendor, client. Each scrubber is reviewing an excel spreadsheet containing the names, addresses, FID, telephone, etc. of our vendors and customers. This information is being pulled from 2 separate sources. We are assigning a BP # to the main office location and not retiring that one. then we go on to identifying the dups. All dups get a Y to be retired, but if they have a different address then the main one, we place a Y to bring that address over under that BP#.
Ultimately, we end up with 2 systems combined into one dumping all old numbers assigned and giving each vendors, customers, etc. a new BP# that may have muliptple addresses.
How can we assign a alpha numeric number without going through each individual line...over 900,000 of them to do. Key is to identify duplicate addresses and duplicate names. Some names might be RK Electrical or Robert King Electrical but the address will be duplicated usually.
View 9 Replies
View Related
Jun 19, 2014
I am trying to write a formula that shows the best grade for each each student and the subject in which
that grade was achieved.
In a previous post i was shown how to identify the highest grade
MAX(IF(A$3:A$34=A7,C$3:C$34))
I hit CTRL, SHIFT & ENTER to activate the formula - Result in column E
However, I also want to identify the subject in which they scored their highest grade in column F
I have two problems
1. I don't know how to write a formula that brings in the subject based on the grade for each student
2. I don't know how to write a formula in case there is a tie (see DAVID L)
View 11 Replies
View Related
Feb 22, 2007
I did a data table yesterday and it worked. I tried again today and the results are incorrect. They are coming out as a constant (the same result as the original formula). Has anyone had this happen before and figured out how to fix? My spreadsheet is fairly complex. Does the data table formula need to refer to the "base" cells? For example, if the formula refers to cell F15, but in F15 the formula is +C15, does my data table formula need to use C15?
View 3 Replies
View Related
Dec 7, 2007
I have tried everything I can think of to get this to work.
I am searching column B for anything containing "Tease", then if found, look to see if column J contains a "W".
Excel doesn't report an error, but this doesn't work:
=SUMPRODUCT((B3:B40="*Tease*")*(J3:J40="W"))
View 10 Replies
View Related
Apr 29, 2009
I am having problems with the following formular in that it does not return what I am expecting.
I have 2 columns of data that hold Y/N values and I would like either "Yes" or "No" to be displayed in Col C for the 4 possible combinations that the interaction of Y and N in columns A&B give.
The data is
Col A:A = Y or N
Col B:B =Y or N
The four possible combinations and outcomes are:
Col A = Y & Col B = Y outcome in Col C = "Yes"
Col A = N & Col B = Y outcome in Col C = "Yes"
Col A = N & Col B = N outcome in Col C = "No"
Col A = Y & Col B = N outcome in Col C = "No"
The formular I have tried is:
=IF(OR(AND(A1="Y",B1="Y"),A1="N",B1="Y"),"Yes","No")
However while three of the outcomes for the combinations are correct (N/Y, Y/N, Y/Y), the output for the N/N combination is incorrect and returns "Yes" instead of "No".
Just wondering if anyone has any ideas as to what is wrong with this?
View 9 Replies
View Related
Nov 28, 2012
I've created a spreadsheet to 'translate' number/letter combinations (sub-levels in the UK education system) into a numerical value. I looked up how to do this on google and, through a bit of trial and error, worked out that the lookuptable has to be in alphabetical order, etc.
My problem is when I enter in any of the 'P' values (see attached), it returns 0.5.
View 4 Replies
View Related
Jun 24, 2014
I have a lot of calculations going on so I thought I would name some of the cells where factors come from to make it easier to follow.
I made a typo and named a cell wrong, is there a way to delete it? Right now I have a correct and incorrect name attached to the cell.
View 3 Replies
View Related
May 27, 2009
I have the following code, taken from an example off the web. But I would like to change the save path to just C/: rather than copy the save path of the original file...I would also like to not close the active workbook that I am working in....how can i achieve this?
Sub TwoSheetsAndYourOut()
Dim NewName As String
Dim nm As Name
Dim ws As Worksheet
If MsgBox("Copy specific sheets to a new workbook" & vbCr & _
"New sheets will be pasted as values, named ranges removed" _
, vbYesNo, "NewCopy") = vbNo Then Exit Sub
With Application
.ScreenUpdating = False
On Error GoTo ErrCatcher
Sheets(Array("Copy Me", "Copy Me2")).Copy
On Error GoTo 0
For Each ws In ActiveWorkbook.Worksheets
ws.Cells.Copy
ws.[A1].PasteSpecial Paste:=xlValues....................
View 2 Replies
View Related
Mar 1, 2012
I have two pieces of code in module 1 and module 2 respectively:
This code is meant to find the first row number that has a string value of "" in column A:
HTML Code:
Sub Macro1()
Dim cell As Range
Dim r As Double
Dim p As String
For Each cell In Worksheets("stock in").Range("stockcode")
p = cell.Value
r = cell.Row
If p = "" Then Exit For Else
Next cell
End Sub
BTW column A will contain formulas that evaluate to "" so I assume cell.value will = "" even if it contains a formula?
In the second module a sort is executed on the range based on the number of rows:
HTML Code:
Sub Macro2()
'
' Macro2 Macro
'
Call Macro1
ActiveWorkbook.Worksheets("Stock in").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Stock in").Sort.SortFields.Add Key:=Range("A4:A" & r) _
, SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
[Code] .......
My code is falling down because I think my variables arent scoped right or my code is in the wrong place. Also macro1 will eventually be executed from a button on a different sheet (I dont know if this will matter).
The whole point of this code is to get around the fact that if you perform A - Z sorts on cells that contain formulas those that evaluate to "" will be at the top leaving lots of blank rows before actual visible data.
View 9 Replies
View Related
Jun 27, 2012
I have a line of code that compares cell values with a variable.
Code:
dim rmycell as range
If rmycell.value = MyTarget then
This works fine when rmycell.value is empty or has a value. However, sometimes rmycell has a #VALUE! Error. When debugging the code I can see this error showing rymcell with a value of "Error 2015". The MyTarget variable is "abcdefg", so the If statement should resolve as
Code:
If rmycell.value = MyTarget then
becomes
Code:
If Error 2015 = "Abcdefg" then
Which should be a negative result. Except it isn't. The code that is conditional on a positive result runs whenever there is a #VALUE error, just as if the 2 values were equal.
View 3 Replies
View Related
Aug 22, 2007
I'm trying to create a new function in Excel and for some reason the syntax is incorrect and it won't let me use it in the sheet. I do not know what I'm doing wrong so any help would be appreciated. This function will allow me to interpolate the term structure of interest rates between dates (I think) My code is
Function INTSPOT(spots, year)
'Interpolates spot rates to year
Dim i As Integer, spotnum As Integer
spotnum = spots.Rows.Count
If Application.WorksheetFunction.Count(spots) = 1 Then
'Single rate given
INTSPOT = spots
Else 'Term structure given
If year = spots(spotnum, 1) Then
INTSPOT = spots(spotnum, 2)
Else
Do
i = i + 1
Loop Until spots(i, 1) > year
INTSPOT = spots(i - 1, 2) + (spots(i, 2) - spots(i - 1, 2)) * _
(year - spots(i - 1, 1)) / _
(spots(i, 1) - spots(i - 1, 1))
End If
End If
End Function
View 9 Replies
View Related
Aug 26, 2006
CTRL/END key combination should goto and select the last cell in a Worksheet. So for a Worksheet using Rows 1-4135 and Columns A-Z it should indicate Z4135 as the last cell
My current worksheet uses these Rows and Columns, but CTRL/END indicates last cell as being AB4506. (Inflated by 371 Rows and 2 Columns)
Deleting the unwanted Rows or Columns, or "Clearing contents" of them does not affect the result given by CTRL/END which still shows AB4506.
How can I adjust/correct the last cell indication?
Usual work-around is to select the wanted portion of the worksheet and copy it to a new blank worksheet, but is there an easier way?
View 5 Replies
View Related
Oct 3, 2006
I have a userform on which there is a frame containing 8 option boxes. After a query, the results are displayed in the form. If I have a value of 1 in a cell, optionbox1 is checked; a value of 2 checks optionbox2, etc. It works great EXCEPT when first initialized. At that point, it checks the last optionbox, even if the number is 1. I have built a next and previous feature to scan the data, and when I return to the first entry, the correct box is checked. I tried coding blanks into the fields prior to populating them, but I still get the same results. Is there some explanation available so that I may remedy this? I'd really like the first piece of data to be correct.
View 2 Replies
View Related
Mar 13, 2007
I have a text file which have a column with data like xxx . however, when I open the file with excel and it displayed incorrect value like xx.xxx, x.xxx
I tried to change the format cell to text, but it still cannot display original data...
View 9 Replies
View Related
May 14, 2008
Usually when you try to open a file that someone else is using, it lets you know the file is in use, and lets you know who is using it. However, the situation I have is that when a user tries to open the file that is in use, it tells them that the file is in use, but gives them the wrong user who is using it!
For example, User1 opens the file and closes it again. User2 comes along and opens the file and stays in the file. User3 comes along and tries to open the file, but instead of saying User2 is using the file. It says User1 is using!
The file we have was originally built in Excel 97, but we using it through compatibility mode in Office 2007. The file is needed by various users, but putting it on shared access is not an option.
View 9 Replies
View Related
Jun 5, 2014
If a player does not qualify, their name and score should not be included in the final results.
I currently have their Name missing, but the NUMBER is showing up.
I've explained more in the attachment.
INDEXARRAY.xlsx
View 3 Replies
View Related
Jun 9, 2014
I have a query around a hyperlink.
Is there a function that i can add to a cell to display if the hyperlink path is valid
i.e., to show that the hyperlink path does not exist, I am feeding the hyperlink from 2 cells ...
View 1 Replies
View Related
Jun 12, 2009
I have one column: in the first cell comes text (beginning either with the letter "L" or "R") and afterwards, in the next cell, comes a number (either "7" or "8"). Basically the column is made up of alternating cells containing either text (code of a movie) or numbers (responses to the respective movies). I want to find a formula which writes either:
- "correct" if the number "7" follows a cell containing the letter "L" or an "8" follows a cell containing the letter "R";
or
- "incorrect" if the number "7" follows a cell starting with the letter "R" or the number "8" follows a cell starting with the letter "L".
Basically the 8 is always correct with an R and the 7 with an L: ...
View 6 Replies
View Related
Aug 30, 2007
In the attached spreadsheet I'm using VLOOKUP to create a cross reference between worksheets JS and ITEM. If you will look at the ITEM worksheet cell reference H13 & H14. The correct value for H13 should be AMC, not 729. Is there a way to use the value in the Class column and Item ID column in combination to get the value AMC? Would MATCH & INDEX work? I'm not familiar with Match & Index. I'd appreciate some help here. I've got 15,404 records to evaluate this way.
View 14 Replies
View Related
