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
etc

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

View 3 Replies


ADVERTISEMENT

Finding Row And Columns For All TRUE Values In A Matrix

May 21, 2013

I'm using multiple data tables to run sensitivity analyses on a very large model. At the end of the analysis, I've got a matrix that tells me which scenarios are TRUE or FALSE (based on multiple criteria). I'm looking for an elegant way to extract the combinations that give TRUE.

Column1
Column2
Row1
FALSE
TRUE
Row2
TRUE
FALSE
Desired Output
True Scenarios:
R1, C2
R2, C1

View 1 Replies View Related

Merging Columns And Rows Of Data Into A Matrix

Aug 3, 2014

I've been trying unsuccessfully to find a way to merge data into a matrix, but can't quite figure out a way to manage it. I have a list of employees in column A, in the list there are employees, many with multiple instances, and in columns C onwards there are dates in dd/mm/yyyy format.

Each line represents the number of nights in a trip, from 1 to 35 nights and the start to finish dates. Where as some employees may only have one row with one date others may have 20 rows with between 2 to 35 dates.

I'd like to merge this into a matrix with one line for each employee showing which nights in the year they were away (tab matrix) which has a day of the year to illustrate which employees were away on which nights of the year.

I've uploaded an example to highlight what I mean.

I've tried a pivot and an index match formula but couldn't get either to work.

View 7 Replies View Related

SUMPRODUCT Of Columns Based On Matrix Position

Mar 20, 2009

I have created the following attachment to illustrate my problem. I have a square matrix -- say a 10x10 matrix (with data in B2:K11, and column headings on row 1, row headings on column A). I would like to get the sum of products of two columns in the result section below -- cells B14:K23, based on the position of the formula.

For example, cell B14 is the 1,1 item of the matrix so I would like to get the sumproduct of B2:B11 and B2:B11 (or SUMSQ(B2:B11) in this case); alternatively cell I16 is element 8,3 in the matrix so I need to get sumproduct of I4:I11 and D4:D11.
I tried the following formula using the OFFSET function (for cell B14 which is then copy/pasted throughout B14:K23)

View 5 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.

ActivityWaterRoadOffroad
Jog nym
Run nyn
Walk nyy
Swim ynn
y=yes
n=no
m=maybe

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

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
Application.WorksheeFunction.Sum(M(1,)

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.

VB:
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!

example:

FROM:

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

TO:

D1
C1
B1
A1
D2
C2
B2
A2

View 4 Replies View Related

Matrix To Column..?

Nov 23, 2009

I have this code to take an area of data and perform the SUMXMY2 formula and output the results to another sheet. My problem is that the results are being outputted in a semi-matrix and i needed them in a column so i can them perform a sort. Is this possible and can anyone shed a light on the best way to do it?

View 2 Replies View Related

Inverse Of Matrix Using VBA?

Jan 23, 2014

I'm trying to do the inverse matrix of a matrix captured by an inputbox, but it doesn't work

This is the code:

Code:

option explicit
option base 1
sub matrix()
Dim M() As Double
Dim B() As Double
Dim Y() As Double
Dim columnas As Range

[code]......

the error is: Error 13 the types do not match

View 7 Replies View Related

Moving In A Matrix

May 23, 2007

I have a matrix A with 12 rows and 10 columns. My problem is if in the cell(i,j) there is data then the same data should appear in a similar matrix B, but in a cell which is 15 cells behind the cell(i,j).

That is it should start counting upwards from cell (i,j) in B and once it reaches the top of the matrix it should continue counting from the bottom of the immediate left column and go up. When it reaches the 15 cell from cell(i,j) in b, it should print there the value that was in cell(i,j) of A.

View 9 Replies View Related

Mileage Matrix

Mar 4, 2010

I am trying to figure out a better way to do my mileage for when I drive for work.

Currently I need to look at a sheet and see where I started and where I stopped and then I’ll see the distance.

Kinda look something like this.

Home
Work
School
Home
031Work
304School
140

What I would like to do is type in the “to” and “from” cell and have it automatically know the miles based on the chart above.
DateToFromDirectionTimeMiles3/4/10Home
Work

View 9 Replies View Related

Correlation Matrix

Jul 25, 2006

I wasn't able to attach the file because it was too big, but you can download it from here www.easygcc.com/correl.rar. On the sheet called " Correlation" there is matrix, I am baiscally trying to fill in the correlation formula into every cell so the matrix is filled out. The data for the correlation calculation comes from the sheet called "Tech Data". I have filled in the correlation formula into a few of the cells as an example, but I don't want to continue doing this manually but rather have a macro do it for me. Otherwise this will take for ever. My macro is also a part of the file, if you would liek to look at it and maybe fix what I already have.

View 4 Replies View Related

Matrix Lookup

Dec 4, 2006

I have a 5 x 5 matrix. When values are entered in 2 cell there needs to be a matrix lookup and the corresponding value needs to be entered into the 3 cell. Example :

- A B C D E
V 1 2 2 3 3
W 2 2 3 3 3
X 2 3 3 4 3
Y 3 3 4 4 5
Z 3 3 4 5 5

So if X and D are entered into the 2 cells the 3rd cell should show 4.

View 7 Replies View Related

Matrix Transformation

Feb 14, 2007

I would like to do the following transformation in excel:

imagine the following matrix:

1 2 3
4 5 6

I would like to transform this into:

1
4
2
5
3
6

This is a simple version of my matrix, in reality it is much bigger, and I do not want to copy paste each column by hand. Any ideas?

View 2 Replies View Related

How To Create A Risk Matrix

May 1, 2014

I'm trying to create a risk matrix like the attached example. I want to be able to enter a value and to have it to appear in the right quadrant.

View 2 Replies View Related

Extract Data From Matrix

Jun 22, 2008

I'm running a small messenger company and I've got a geographic area divided into five zones: 1,2,3E,3W,& 4

Deliveries within 1 zone will be charged for one zone.
Deliveries from Zone 1 to Zone 2 will be charged for 2 zones. Etc.

I've got a list of zones in my From column and also in my To column.

I need to write a formula that will give my customers the Zones Charged in a seperate cell.

I learned how to do something like this back in school, but don't even know what to call it anymore. Maybe it's some kind of comples IF function.

FREE Deliveries to anyone in Long Beach, CA to the winner!!!

View 12 Replies View Related

Finding Data From A Matrix

Feb 17, 2009

On my example sheet it shows how many cables and how the cable length per job. What i need is for the hour/job column to be populated from the Hours sheet by matching the column and row. e.g 4 cables at 40 metres would give me cell D7 (27.89), as 25 metres would be no good - so rounding up would be required.

View 2 Replies View Related

Get First Higher Number From A Matrix

Apr 7, 2009

I need a function that can get, from a matrix, the first number that is higher than the number in a given cell.

View 9 Replies View Related

How To Create Block Matrix

Feb 12, 2013

I'm struggling to find the right Excel functions to fill up a block matrix. Here are all the details of my problem.

I have a list of firms and relative products. Each firm can produce either 1, or 2 or 3 different products, for instance

firm A produces product x
firm B produces products y and z
firm C produces product j
firm D produces products k, h and q

Suppose the data appear on a sheet in column order, that is, one column for the firms and one for the products with a space between A, B, C and D when one firm is a multi-product producer. I need to create a 0-1 block matrix in Excel setting 1 when the product is produced by the same firm and 0 otherwise, i.e. for the above example

x y z j k h q
------------
1 0 0 0 0 0 0
0 1 1 0 0 0 0
0 1 1 0 0 0 0
0 0 0 1 0 0 0
0 0 0 0 1 1 1
0 0 0 0 1 1 1
0 0 0 0 1 1 1

I can do this in VBA but not in Excel, and unfortunately I need to provide an Excel solution.

View 3 Replies View Related

Convert Matrix To Table

Jun 4, 2014

I need to convert a Matrix to a Table. Something like this:

Input file:
Capture1.PNG

Output :
Capture2.PNG

View 4 Replies View Related

Matrix (find Any YES Strings)

May 22, 2008

I have a set of 4 columns with YES/NO statements in them.
I need to find any YES strings, of three or more, within one column, uninterupted by a YES in another colum.

EXAMPLE:
_____A ___B__C__D__E
1 1/1/2008 YES NO NO NO
2 1/2/2008 NO NO YES YES
3 1/3/2008 YES NO NO NO
4 1/4/2008 YES NO NO NO
5 1/5/2008 NO NO NO NO
6 1/6/2008 YES NO NO NO..................

View 4 Replies View Related

Covariance Matrix From Worksheet

Jul 18, 2009

I have a spreadsheet with M-1 rows and N colomns. I want to calcolate the covariance matrix of the spreadsheet and i would like like output an other spreadsheet. I tried to do my self, but no so much result. here my code, but there is a mistake

View 14 Replies View Related

Matrix To Single Column

Jan 2, 2006

I have an array of data with column headings and changeable row "item names"
in the left most column. The intersecting matrix of data contains quantity
values at the intersection cells of column and row headings. I need to
automatically reorganize the data as a list in a column with the row "Item
Names" at the left but and repeated as necessary but instead of the
additional columns of quantity values, I need to have this data listed in a
single vertical column along with an additional column with the original
column header that corresponds to the quantity value. I think there must be
some kind of lookup function to do this but I can't find the right way to do
it.

View 13 Replies View Related

Turning Data Into Matrix?

Oct 18, 2011

I have 2 lines with pairs and 3rd with corresponding values, nee to construct a matrix out of it with formulas

Example
Need to turn

AAABBDBCDCDC0.30.20.130.550.840.43

into

ABCDA11-0.31-0.21-0.13B0.311-0.551-0.84C0.20.5510.43D0.130.841-0.431

So the one that stands above in the initial file goes to the top line in the matrix. 2 line -> column in matrix. In case there is no match need to seek reverse in 1,2 and reflect as 1-VALUE in the matrix

View 3 Replies View Related

Auto Calculations For A Matrix

Oct 26, 2011

I need C3 to auto calculate depending on the value of A3 and B3.

Example:

If A3=Minor and B3=Possible Then C3=Low L4.

So there are 25 unique possibilities depending on what A and B =

Is there a way to get around this or will i have to resort to a Macro??

This data is based on the Matrix Below.

ConsequenceMinorModerateSevereMajorCatastrophicLikelihoodAlmost CertainHigh
(H10)High (H13)Extreme (E17)Extreme (E23)Extreme (E25)Quite PossibleModerate (M6)High
(H11)High (H14)Extreme (E19)Extreme (E24)PossibleLow (L4)Moderate (M7)High
(H12)Extreme (E18)Extreme (E22)UnlikelyLow (L2)Low (L5)Moderate (M9)High
(H16)Extreme (E21)Very UnlikelyLow (L1)Low (L3)Moderate (M8)High (H15)Extreme (E20)

View 6 Replies View Related







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