Matrix Query Using Text?

Mar 18, 2014

with the table give below, i need a formula to get the sustainability. Eg. cell D1 =VG and D2=G, then D3 value should be G (as per the table below).

Prev Yr
Current Yr




View 2 Replies


Text Matrix Translation

Jul 25, 2007

i have a text file with the following data

A |xxxxx
A1 |.x...
A2 |..x.x

I need to get an output better on text (excell is limited with rows and the matrix can have up to 5000 rows and lines)
i tried to open the file with excel but every line is a string and i can't separet it

the output will be

A 1,2,3,4,5
A1 ,2,
A2 ,3, ,5

it will save me a lot of time

View 9 Replies View Related

Extract Numbers From Text Into A Matrix

Jul 9, 2007

I have a large number of text strings representing chemical formulas. They include letters for the element names and numbers for the number of atoms of each type.
For example:


The element name has either one or 2 letters (one capital, one small), if there is no number and no small letter after the capital letter that means that there is only one atom of this sort(like in C18H35NO3 - there is one N atom). If the element is not listed in the text string it means that it is not found in that particular formula (i.e. the numerical value is 0). Is there any function that could help converting such a vector (say A1:A3) into a matrix that will have the following form:

C H N O S Na
18 35 1 3 0 0
4 7 0 0 1 0
11 16 0 2 0 1

View 9 Replies View Related

Fill Matrix From Text File

Mar 28, 2008

I have a particular problem to solev. I am an average Excel user with no VBA experience, so. I want to construct a matrix with 72 columns and rows filled with symetrical values eg

1 2 3 4 5
1 0 5 7 8 1
2 5 0
3 7 0
4 8 0
5 1 0

I have a CSV file with the values in the form of {column},{row},{value} eg 5,70,8. how to get these simply into Excel? This is not a one-off operation, the text file is an export from AutoCAD and will be run fairly frequently, so doing it manually is not an option!

View 6 Replies View Related

VBA To Send Text File To USB Dot Matrix Printer

May 22, 2014

How do I send a text file to a usb dot matrix printer as opposed to a parallel port.

I have an Excel spreadsheet that prints labels on a dot matrix printer. Until now the printer used a parallel port. My Excel workbook generates a text file "C:PRINTLABEL.TXT". I then execute a shell command to run a batch file called

"C:PRINTLABEL.BAT". The BAT file contains the line
copy C:PRINTLABEL.txt lpt3
The shell command contains the lines (not all lines are shown)
On Error GoTo ErrorHandler ' Enable error-handling routine.
Exit Sub ' Exit to avoid handler.

Now I have to deal with a newer dot-matrix printer (EpsonLX300+II) that is able to use a USB port.

How do I print the plain txt file to the printer using the USB port (LPT1/2/3 are all parallell ports, so the batch file does not work as it stands.

View 4 Replies View Related

Creating Adjacent Matrix From Incidence Matrix

Aug 26, 2007

I have a data chart on 200 people with overlapping membership in 20+ groups, represeted as binary (1=member, 0=notmember), for example: ...

View 9 Replies View Related

Return Inverse Matrix Of Large Matrix

Aug 22, 2008

Using VBA, I wish to work out the inverse matrix of a large matrix (100*100), but keep getting the # Num! Error. I am using the minverse function. I have defined variable as "variant", does this give me the same possiblities in terms of number size as the variable "Double"?

View 9 Replies View Related

Matrix - Identify Cell Content In Column For Row If Matrix Content True?

Mar 21, 2014

For a table like the one below produced for the sake of example (actual is much much bigger) I want to make it list rows that are true for a certain column for a certain variable in the matrix. So for say water terrain, which types of activity can I do i.e. swimming. Or for Offroad the activites which I can't do i.e. Run and Swim.

Jog nym
Run nyn
Walk nyy
Swim ynn

View 10 Replies View Related

Query On Text To Columns

Jul 11, 2014

Attached is the sample working file, i have a user data embedded into the comments columns for each user reference. However, i need to extract the wrapped comments column.


View 14 Replies View Related

Find Text String In Web Query

Feb 18, 2010

I want to find the number within this webpage and report it to a Cell in excel....

View 6 Replies View Related

Web Query Returning Text But Not Numbers

Oct 3, 2009

I have this web query to retrieve Stock Market data for individual scrips which used to work fine till 2 weeks ago:

Since then all it returns is the text in the tables. Numbers eg dates and stock prices come as blanks.

These show up alright in the browser (Mozilla as well IE).

Some other tables at this site plugged into query return complete data. eg [url]

I tried many different scrips with same results.

Since it was working fine till 2 weeks ago AND other tables at this site are working fine, I suppose the site changed something.

View 10 Replies View Related

Open Large Text File With Query?

Sep 20, 2006

I have a large text file (greater than 64k lines) which I need to import into excel. I only need anything imported from the text file that is for dates I specify. So I would like to run a line by line query and only import the lines I need.

The dates are always in position 43-50 (dd-mm-yy) in the text file itself, so the code would have to look at that section, see if the date is within the range specified and only import if it is (and then move onto the next line).

View 5 Replies View Related

MSN Money Web Query Has Numbers In Text Format

Aug 28, 2009

I use web queries to download stock options from MSN Money.
If I manually refresh the queries (right click -refresh) the downloaded
prices are in number format.

If I use:

View 3 Replies View Related

Converting Number To Text From Refreshed Query

Jan 29, 2009

I am importing data from an access database that is continually refreshed. Some of the data that is returned to my Excel sheet is in binary, where a "0" represents an unchecked box on the database program and a "1" represents the same box, but with a check mark. I want to change the data so that when it is updated on my Excel sheet it is converted to text as either "Yes" or "No" instead of "1" or "0", respectively. I'm sure this is pretty elementary stuff but I have tried VBE and cell formulas and can't come up with an answer.

View 3 Replies View Related

Conditional Formatting Query: Changes Colour When Certain Text Is Entered

Sep 10, 2009

I have a column of cells that changes colour when certain text is entered into them, ie "TIME" will make the fill colour brown. However, when other text is entered into the cell alongside "TIME", the conditional formatting doesn't change the colour of the cell. How do i get around this so it picks up on the "TIME" text even when other text exists alongside it?

View 2 Replies View Related

Export Excel Query To Multiple Text Files

Apr 2, 2012

I need to create a function which will take a objectname (a query or table) and a file name format

The objective is to export this query or table into multiple text files each with a maximum of 1000 lines per text file. These files are used as an input for SAP - the interface only takes a maximum of 1000 lines per file.

I am having three issues with the code below.

1. How do I split an excel query based on line numbers? Is there a way to get the equivalent of ROW_NUM in oracle ? Or do I have to loop through it and maintain a counter?
2. What is the best way to split the file ? Loop within loop? I need the files to be tab separated (no header required)
3.The rst.RecordCount is acting strange - when I pass a name of the query - the property returns the right no. of rows, however when I pass a any table name it only returns 1 - is this the expected behavior - or am I missing something

Function ExportAsText(strObjectName As String, strFileName As String) As Long
' Purpose: Export any given query, table to mutliple files each of certain length
' the no. of lines per text file is defined by the config parameter SAP UPLOAD, LINE LIMIT PER TEXT FILE
' in the LKUP_CONFIG database

Dim db As DAO.Database 'This database

[Code] ..........

View 1 Replies View Related

Create Matrix From A Matrix

Apr 29, 2013

Below there are all the information needed to understand my problem.

M(1 to R,1 to C): matrix with R rows and C columns [element known]

M(i,j): elements of the matrix M in position i(row)-j(column) [element known]n

View 3 Replies View Related

Macro To Query Column For Date If Found Place Text In Cell

Aug 30, 2012

I am trying to make a macro that will query a column J4 for a date, if the date is found say "Wednesday, July 4, 2012" (J8) get the text from Column K8 "Independence Day" and insert the text in Cell (H1).

The Macro Prints sign in sheets with the date in H2, it only prints Monday through friday. Then skips the weekend and the next date will start the following Monday.

The Federal Holiday schedule is in column range J (Date) and K (Holiday)

This is where I am stuck ....


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


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

Query An Access Query With VBA Function

Jul 8, 2008

I'm trying to query a query in Access 2003, from Excel 2003.

The query in Access looks like:
AccessQuery: [SELECT VBAFunction(field1) FROM Table]

The query in Excel looks like:
ExcelQuery: [SELECT * FROM AccessQuery]

I use the following VBA code in Excel to excecute the query:

With ThisWorkbook.Worksheets(cDataSheetName).QueryTables.Add(Connection:=strConnection, _
Destination:=ThisWorkbook.Worksheets(cDataSheetName).Range("A1"), Sql:=strQuery)
.RowNumbers = True
.Refresh BackgroundQuery:=False
iResultRowCount = .ResultRange.Rows.Count
End With
When I execute this code I get the error message 'SQL Syntax Error' (Error 1004). When I remove the VBA function from the query in Access, it all works fine.

View 9 Replies View Related

Edit Query Using Query Wizard

Apr 25, 2006

I created an Ms Excel Database Query to bring in data from MS Access. (versions 2002 of MS Excel and Ms Access). The query works fine initially. I can right click, choose Edit Query and change my criteria. Results are returned almost instantly.

My problem is that, once I save the workbook, or autosave happens, I get an error when I right-click to Edit Query: This query cannot be edited by the Query Wizard..

View 2 Replies View Related

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

MS Query - Parameters (parameters On A Query But Use A Wildcard To Return All Instances)

Nov 17, 2009

Is it possible to set parameters on a query but use a wildcard to return all instances? I have a query that I want to be able to set multiple parameters on but give the user the ability to select as many or as few parameters as they want to see. 2 of the parameters are number fields and 2 are text fields with no spaces.

View 4 Replies View Related

How To Get Value From Matrix

Jan 26, 2013

I need to get the fee value, if I write "Name" and "Time".

Please read the simple example file named "Fee from matrix".

View 3 Replies View Related

Sum Matrix

Jun 15, 2009

Given one array variable (matrix n x n) M, I want to use the function sum, to sum along the rows.

I can use Application.WorksheeFunction.Sum(M)

but this will sum all the elements

and I would like smtg like this

Just sum the first line, or the i-th line.

View 9 Replies View Related

Correlation Matrix In VBA

Jan 16, 2006

I have code to create a correlation matrix (NxN, where N is the number of columns). This is done by selecting an area that is NxN, entering the function and range, then hitting ctrl +shift + enter (array formula).

However, I want to convert this to accept VBA arrays, rather than a data range, and give the output in form of an array as well.

Function CorrmatK(dataRange As Object) As Variant
On Error Goto 20
Dim r As Integer, n As Integer, rr As Integer, i As Integer, j As Integer, k As Integer, doit As Integer
Dim x() As Variant, mc() As Double, ss() As Double, m() As Double, ob As Object
r = dataRange.Rows.Count
n = dataRange.Columns.Count

[Code] ......

View 7 Replies View Related

From Matrix To Column!

Jan 29, 2009

I NEED some code to convert a matrix to a singel column and it should be reversed to!
see attachment!



A1 B1 C1 D1
A2 B2 C2 D2
A3 B3 C3 D3



View 4 Replies View Related

Matrix From Columns

Mar 29, 2009

I have to create a matrix from columns in this format:

A1 A2 1
A1 A3 4
A1 A4 6
A1 A8 4
A2 A5 2
A2 A8 3

with a lot of empty cells (here, for exemple A1,A6 & A1,A7 ) and (A1,A2) = (A2,A1)
I'd like to have :

_ A1 A2 A3 A4 A5 A6 A7 A8 .....
A1 0 1 4 6 0 0 0 4
A2 1 0 0 0 2 0 0 3

The source data is in the attachment. Would it be possible to do it automatically?

View 3 Replies View Related

Copyrights 2005-15, All rights reserved