Identifying Numeric Data With MS Query

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


ADVERTISEMENT

Match Numeric Data With Query Text Data

Oct 2, 2007

I have some code that goes through some data that is imported from a database via a query.
This has been working for a few years without a problem.

I lookup and employee number on my sheet to the employee number in the database table.

this is my code that checks the employee number.

Set rs = Worksheets("Employee")
If rs. Cells(r, 2) = cells(1,1) Then 'if employee number matches
......

The problem is that now the database application has adapted an alpha option (it used to be numeric only). In order for my code to work I have to change the employee number on my sheet by putting a ' in front of it.

Example if the number was 127 I need to enter '127 in the cells.

I have a few thousand in my data.

Can I add some code to my macro so I can still enter just the number in the cells?

View 4 Replies View Related

Identifying Non-Duplicates In Numeric Array

Jul 18, 2012

I have organized account numbers from two systems onto a spreadsheet, with numbers from System 1 arrayed in Column A and numbers from System 2 arrayed in Column B. I need to evaluate the numbers in both columns and isolate the numbers that are NOT DUPLICATES across the two systems (Columns A and B) and return a list of non-duplicate numbers in Column C. Here is what the table would look like:

System 1
System 2
Non-Duplicates

173
215
173

[Code] .......

What is the best approach to use?

View 4 Replies View Related

Identifying Numeric Values Within A Range Formatted As Text

Jan 1, 1970

how to convert number into text (acutal formating)

Eg. : 150500

One Lac Fifty Thousand Five Hundred

View 14 Replies View Related

Numeric String Manipulation Query

Dec 23, 2009

I have a sizable spreadsheet, which has one column where the cells are mostly numeric strings of up to 6 numbers - there are variations, which I will mention.

What I need to do, is to treat the numbers as individual figures, and add them, then divide by the number of figures. e.g. cell value '123456' I need to process as (1+2+3+4+5+6)/6.

The variations are that '0' has to be added as 10, and any letters need to be added as 12. '-' and '/' which are the only non-alphanumeric values should be ignored.

View 9 Replies View Related

Create An Conection With MS QUERY To A Csv And Query Data

Sep 6, 2008

I have a csv file on another drive on the network that i need to query. I believe that ms query would be the best way. I know that a DSN needs to be setup but this macro will be used by various users who wont know how to do that. thus I would like to create one via VBA every time the task needs to be run.
I haven't a clue how to do this and i need it to be explained to me in general terms with words of one syllable!

View 9 Replies View Related

Identifying Peaks In Data

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

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 View Related

Identifying Duplicate Data Between Workbooks

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

Identifying Data To Selected Criteria

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

Noting Duplicates And Identifying Data Within

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

Identifying Varying Data And Rank Formula

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

Identifying The Same Transaction With Multiple Rows Of Data

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

Data Scrubbing - Identifying Duplicates And Assigning One Number

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

Identifying Data In One Column Based On Highest Figure Data In Another Column?

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

Extracting A Numeric A Specific Numeric String From A Description Cell

Oct 22, 2009

I am trying to do is extract the volume size of products in 'ml'
from 10k plus products from a description field cell.
this description field could also contain the weight of the product in grams
so I cannot just do a search for a numeric string ,
it has to be associated with the milli-litres statement .

is is possible to do a sort of ' *ml ' search and then select and copy to another cell ???

View 9 Replies View Related

Excel 2010 :: Formula To Indicate Alphabetical Data Connected To Numeric Data

Jan 8, 2014

I have a question about using conditional formatting in excel (2010); I made a table with the following columns:

"Supplier Name" "Supplier Lead Time" "Internal Lead Time" "Total Lead Time". I made two different scenarios to show different supplier lead times and different internal lead times, and used the minimum function in the "Total Lead Time" column to find the smallest total lead time to select the best supplier.

The last thing I would want to do with this set of data, is plug in a formula that would somehow indicate which supplier corresponds to the shortest total lead time (which supplier has the smallest supplier lead time). I'd like to be able to use a formula that enters the name of the supplier in a designated cell, which I could indicate as the "Preferred Supplier" cell. If this is not possible perhaps there is a way to highlight the supplier's name with conditional formatting?

View 2 Replies View Related

Convert Numeric And Scientific Symbol To Numeric Only

Jun 4, 2014

I have a column of several thousand entries listed as numeric with a scientific symbol eg.

1.4mSv
19.53mSv/1mSv (some have a mix and or alpha/numeric range)

I want to convert them to the numeric value only. I'm extracting to a chart which is not recognising the alpha and throwing the data out. I tried find and replace, trying various options within the 'replace format' tab with no joy.

View 6 Replies View Related

Finding A Non Numeric (or Numeric) Character In A Field

Sep 1, 2008

I have a few thousand products codes (i.e ABCD123BLA08 or SHU267BLA) They are non standardised in length or structure. I wish to pull out everything upto the end of the third numeric digit.( ie ABCD123 and SHU267) I cannot use left as they are all different lengths. Ideally i would serach for a non numeric char after the number then use left up to that point. Search can't do this, FIND can't do this.

Summary
ABCD123BLA08 would be ABCD123
SHU246BLU would be SHU246
I147ORT08-12 would be I147

View 9 Replies View Related

Excel 2010 :: Access Data Connection Query - Missing Data All Of Sudden

Oct 19, 2012

I created a slick little excel sheet with the data coming in automatically via Access query. It has been working fine for months. Now all of the sudden there are a bunch of cells with missing data. The weird part is it's not as if whole columns are missing data, more like 90% missing. When I go to Access and run the query all cells are populated as the should be. There have been no changes to the query at all during this time.

btw...I am running Office 2010

View 2 Replies View Related

Data Connection From Web / Web Query Returns Code Instead Of Selected Data Table

May 31, 2014

A web query I am trying to use to bring an online data table into my worksheet is broken. Now, instead of returning the data table nicely into my worksheet, it imports the code of the webpage, instead, and turns my worksheet into a mess.

The query used to work but there was recently an "upgrade" to the program that populates the web table and the query no longer works and just returns the code for the page, instead. I can see the html code for the table in all of the code it returns but I really need the table to import cleanly into excel.

I can't talk to the people who changed the web program.

View 3 Replies View Related

External Web Data Query Fails To Insert Table Data?

Jul 9, 2014

An external data web query points to a web site that offers a foreign exchange rate calculator. In my browser, I selected the specific currency pair I need and used the resulting url in the web query. The "New Web Query" pane resolves the url correctly and allows me to select the table data I want (the little yellow arrow turns to a green check mark). However, when I hit "Import" and select the target cell, the only data returned is the parameters from the url and an error msg (This web query returned no data...). How do I get the query to pass the parameters to the website correctly?

View 3 Replies View Related

Pass Parameter From Excel Through MS Query To MS Access Query

Nov 26, 2012

I have an MS Access query that contains a parameter. The parameter is a date field, and I have configured that in the Access query. If I run the query within the MS Access user interface, it prompts me for the paramater value as expected, and runs just fine. However, I want to connect to this query from within Excel as a data source.

I have created a connection to the Access file using ODBC from within Excel. In the MS Query window, I am merely selecting all of the fields resident in the MS Access query, and returning all values. In other words, there is no selection criteria in the MS Query. I have done this many times with Access queries that DO NOT contain a parameter, and everything works fine. However, in this instance, I need to pass a parameter through to MS Access in order for the query to run. At the moment, I get the "Too Few Paramaters...1 expected" error message. This makes sense, because I haven't figured out how to pass the paramater to MS Access.

Is there a way to structure this that does not involve VB code? If so, I'd love to know how. I have tried creating parameters in MS-Query with the same name, but although I get the prompt it doesn't connect with the Access query as the source for the parameter value.

If the solution requires using code, I'm good with VB Code in Excel...is there VB for Excel code that could make this happen?

Failing that, I guess there must be (I've seen a few in my search thus far) Access VB Code that can make this work. I'm very rusty using VB with Access, so this is my least favored solution. However, if this is the only option, keep in mind that I need to pass the paramater ultimately from a user who will initiate the process using Excel.

View 3 Replies View Related

Query Parameters Which Takes The Date From The Cell Into The Query

Mar 29, 2007

Need the query parameters which takes the date from the cell into the query. How should I modify my query if it needs to take the date from a cell?? The bold one date should be picked from one of the cell in sheet 2.

My query is this
WEB
1
http://fc-web-phl1-101.phl1:8090/gp/...runReport.y=12

Selection=15
Formatting=None
PreFormattedTextToColumns=True
ConsecutiveDelimitersAsOne=True
SingleBlockTextImport=False
DisableDateRecognition=False
DisableRedirections=False

View 2 Replies View Related

If Statement Numeric Or Alpha Numeric

Jan 12, 2010

I have a column that has cells with values as

19352510
C084111X AA
24253081
A001290U AA
19599291
48413321

I want to write an If Statement is a column next to each entry that denotes two options either Broker or Agent. Where the code is say 19352510 then Broker and where it is say C084111X AA then Agent.

Broker codes will never contain a letter. The Agent code will always start and finish with a letter.

View 9 Replies View Related

Remove All Numeric Data In A Cell

Jan 25, 2010

I have a data import from our client something like this:

a1:12/11/2009 shoe bags blank-** $700.00 $0.00 $3,949.00

How can i delete all the numeric data in the cell?

i need only the text: shoe bags blank-** in a2

View 3 Replies View Related

Macro To Pivot Non-numeric Data?

Jan 13, 2014

I am trying to create a macro that will essentially "pivot" some non-numerical data I have. One column contains data with leading zeros. I need to retain these leading zeros. The attached document has two tabs; one that displays how the data comes into excel titled "Data" and one titled "Desired Output" with the desired output from the macro.

View 4 Replies View Related

Examining Alpha Numeric Data

Aug 15, 2007

I am trying to make a worksheet change the fill color depending on the data in the cells. I have two issues I cannot find the answers to.

View 12 Replies View Related

Programing Cell To Allow Numeric Data Only

Mar 7, 2007

how format or programing any cell for only accept numeric value and not alphanumeric value.

View 12 Replies View Related

Using Data Validation For Numeric Format

Dec 17, 2012

How to use data validation to restrict the input format into a cell. I'm trying to limit a cell to the following format: either ###/###' or ##/##'. NOTE: the apostrophe at the end. Here is what I've been trying to get the thing to work for the 2nd case:

=AND(ISNUMBER(LEFT(H1,2)+0),MID(H1,3,1)="/",ISNUMBER(RIGHT(H1,2)+0),MID(H1,3,1)="'")

But I'm getting an error when I input a test into the cell, e.g. 12/45'.

View 9 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved