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?
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)
I will post a rudimentary example: Account Name/Column A Truman school, San Diego Truman school, San Diego Truman school, San francisco Truman school, Atlanta Truman school, Atlanta
In column B, I would like it to assign a unique numeric id based on the TEXT only.
So Truman school, San Diego gets a string of numbers: 1234 Truman school, san fran: 1235 Truman school, Atlanta: 1236
My spreadsheet is about 6,000 records with a great deal of the above situation going on. So school names are identical with different locations assigned. I am trying to import to a database and don't want a lot of duplicate records. That's why I'm trying to assign a numeric value.
I was using SUMIFS on a large amount of data, but wasn't getting back the exact sum that I needed.
It turns out that I had cells with values similar to the following
Code: 000123 0123 Basically, text fields with the same numerical values, except for the number of leading zeros. Due to the nature of the table, the number of leading zeros is important.When I did SUMIFS, it counted the sum of the two values together. For example with the following: Item Batch Qty
ABC 123 1
It appears that even if a numeric value is stored as text, SUMIFS treats it like a number.
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!
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?
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.
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.
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?
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.
I want find the data in some rows that same with one or more cell and automatically fill the data. And for more details, I have attached the examp file (Examp.xls).Antoni
I'm trying to find a formula that looks at Column A on both sheets (each client is allocated a unique number) and if they match enter in column D of the referral sheet the month they were seen but only if it is a 1st contact (appt type on column D of contact sheet)
I have an excel workbook with 2 worksheets. One worksheet shows the MASTER LIST of COMPLETE Customer Names (e.g. ABB Supplies Incorporated). The other worksheet has information on customers but the customer names typed in are incomplete (e.g. ABC Supplies). I need a macro that would look do a comparison of the customer names in the 2nd worksheet to the Master List worksheet and pull the data (complete name, address, etc.) for those that would match (partial match since company name is 2nd worksheet is usually incomplete).
I have a lot of data to filter / sort. I want to initially to create a filter for a column of data - which has the format similar to hierarchical paths to files. The data is a mix of text/numbers. e.g.
Doing an alphabetical sort of this date would return the following order. As you can see while each strings in unique - there are many instances where they are simialr - if you ignore the unique numeric values at the end of the string.
So what I want to do is to create a filter for the strings - but ignoring the numeric bits at the end i.e.
reg_[0-9]+_+[0-9]+/d
The strings are obviiously of varying length and the number of hierarchical paths is different, so I can't split string on "/".
Similarly folder paths names can contain "_" so can't split string on this either.
As I don't know how many "/" or "-" instances there will be in the string I don't believe I can use the find function. Also as the amount of number will be different i don't think I can use =right(a1,X) either.
I may be able to search for the pattern above - as this is probabay unique - so maybe it's something like the following pseudo code:
Function GetString(txt As String) As String With CreateObject("VBScript.RegExp") .Pattern = "reg_d+(_)+d+//d" GetString = .execute(txt)(0) End With End Function
If I do require VBA code - how do I then use this for creating a column filter? Or will I have to extract the filtered data first from the column (and its associated row data) into another worksheet to use?
Once I have the filter in place I want to create tables using the filtered data - so for example each column value above has a lot of associated data values in each row e.g
I have a workbook with data on sheet1 and a summary on sheet 2. I want to use two drop down boxes on sheet 2 (B3 and B6) to look up matching data from sheet 2. What formula is best to enable these two look_up values to be used? I saw an example where the two values were separated by a comma within {} i.e. {B2,B6}, but only got an error when I entered the formula into my Excel 2010 sheet.
I have previously used VLOOKUP, but this comes back saying I have to many arguments. I can attach the workbook if that makes it easier to explain.
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.
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:
I have a combo box where I select "Income" or "Expense". In relation to that, In the next column called "Category", I use the offset formula in "Validation". Depending on whether I choose Income or Expense, I will get different dropdown options for "Category". What I want to do is if I choose Expense, in the column named "Amount" beside category, I want to have it so that it places the numbers in brackets or simply a negative sign. Just as reference, I use the SUM function to total up the numbers at the bottom of the sheet.
I have attached a sample copy "Data Validation Test"
I have a somewhat challanging task and have looked in all my vba books for a solution but am coming up blank. I am have a text file with just numbers in it and i need to extract each number and input it into an array so i can manipulate them from there. I have tried "for each" and " loop" statements but cannot get it to work.
I have a name in one column and the data in another. I am trying to match the name and the category and turn the column data into a row data. I have attached a sample of two sets of data since my explanation here may be lacking. Orriginal data is in columns A and B. Basically I think I need a double text lookup (matching the name and category) and put the data in a row format.
I need to convert numeric data to proper dates. Example: a cell currently reads 100875 but I need it to display 10/08/1975.
I've already found a VBA script that properly formats new data as you enter it (keying 1298 results in 1/2/1998), and I'm familiar with using =DATE(left,mid,right) to coerce Excel into spitting out a date in a certain format.
The difficulty I'm having is that I need to make existing data display correctly, without adding another column to accommodate reinterpretation of said existing data through a formula. Essentially I'm looking to avoid having to re-key several thousand date entries.
With the numbers first, in ascending order. Then the letters, grouped by letter ascending, and the number following them. But I can't have it where it looks at the first number after the letter (A1,A186,A20). It has to look at the WHOLE number and then sort it.
With the numbers first, in ascending order. Then the letters, grouped by letter ascending, and the number following them. But I can't have it where it looks at the first number after the letter (A1,A186,A20). It has to look at the WHOLE number and then sort it.
