Create Matrix From Another Table Of Values

Dec 26, 2013

I have a Table of multiple values. From this table, I need to lookup and match the corresponding value in another table, and return a "X" if the looked up values match.

For example: The table with the data in it is:

Apple
Bob

Candy
Bob

Cookie
Bob

Donut
Bob

Figs
Bob

The Table I need to create is: As follows: It will have Bob in the Top column, and the rows to the left of Bob will say, Apple, Asparagus, Candy, Cookie, Dancing, Donut, Fame, Figs, Zebra. I need to read the top table and populate with an "X" the values which have Bob.

View 9 Replies


ADVERTISEMENT

Create New Table (or Matrix) From Exported Set Of Data From Website

Jun 19, 2014

I have a set of exported data from a Project Management SW (activecollab). The result is an excel .xslx file with a table inside with a lot of cells I am not going to use. Additionally, what I would like to do is creating new tables on other sheets that use only the data I want from the export. For example, I have two projects and three employees. I want to create three tables with the names of the three employees. In each table I want the tasks done by them and the time they will spend on them in order to create a Gantt chart. I need a solution that allows me to create new tables with selected data from a bigger cluster (the export). you do not need to give me the exact solution, I only need to know whether it is possible or not and where could I get the info to do what I want.

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

How To Create Table Of Unique Values

Mar 17, 2012

I have a Column, say column A, setup with a lot of repeated data. Now what I want is to pick only a single value from each set of data and put them into a separate column. for example:

Col A
1
2
3
2
4
1
5
4
1s
a2
1s
a2
a2
6

What I want the excel to do is to pick from the above data only a unique value and put them into a separate col, like this:

Col C
1
2
3
4
5
1s
a2
6

View 5 Replies View Related

How To Make Matrix Table Into Data Table

Jun 17, 2002

How to (by vba or whatever):

1) convert the Matrix data into the data table, and;
2) convert the data table into the matrix data

Matrix data (example)
share Ashare Bshare C
springsellholdbuy
summerbuybuysell
autumnsellholdhold
winterholdsellsell

data Table (example)
springshare Asell
summershare Abuy
autumnshare Asell
wintershare Ahold
springshare Bhold
summershare Bbuy
autumnshare Bhold
wintershare Bsell
springshare Cbuy
summershare Csell
autumnshare Chold
wintershare Csell

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

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

Create Table Of All Possible Combinations Based On 4 Values

Mar 6, 2012

I'm trying to create a table of all possible combinations based on 4 values (8,4,2,-2) into 6 columns. An example is below of what the table should look like

Field 1Field 2Field 3Field 4Field 5Field 688888888888488884488844488444484444488888288882288822288222282222222222288888-244-2-2-2-2444444884422

View 7 Replies View Related

Create Matrix Of Data Automatically

Jan 30, 2013

I have 2 types of data: Product & Store

And the data would go like this:

A
Corn Flakes
Frosted Flakes
Froot Loops

B
Store 1
Store 2
Store 3
Store 4

The number of entries of course is a lot bigger, that's why I'm trying to make it auto.

So what I need is to have a table created that would mix both data type in 2 columns, like this:

Product
Store

Corn Flakes
Store 1

[Code]...

How would this be done automatically by a formula?

View 9 Replies View Related

Create Matrix Of Pricing By Conditions From Tables

Aug 21, 2008

i have many tables for many carriers with the same format, and i need to create a unified table of them, the tables look like this

-------A---------------------B-----------------------C---------
----Destination----------Country_Code-----------Price_Per_Minute
1------UK-------------------4479-------------------0.256-----
2------USA------------------1148------------------0.300------
3------Jordan----------------9626------------------0.899------
4------India------------------9198------------------0.345------

so let us consider that these are the prices for X company and i have another ten tables for other 10 companies, what i need to do is to create a unified table that includes all the companies prices for all the destination depending on the Country_Code as it could be considered as a primary Key and without any duplications and to include the all the data in all the tables as some destinations and prices are not available in all the tables, so the tables contains different data, the unified table should look something like this

-------A----------------B----------------C------------D---------E------
----Destination-------Country_Code-----X Price-----Y Price----Z Price----
1------UK-----------------4479-----------0.256-----0.254------0.356----
2------USA---------------1148------------0.300-----0.876------ ---
3------Jordan-------------9626-----------0.899------0.987------0.324---
4------India---------------9198-----------0.345----- -------0.564--

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

Userform's Listbox Values Depending On Values On Certain Matrix

Aug 16, 2008

I have problems with my userform's listboxes. I have two listboxes, and I want second listbox's values to be dependent on first listbox's values.

And even more complicated, I need second listbox's values to be dependent on values on certain matrix.

In that matrix, row headings are listbox1's values and column headings are listbox2's values. How ever there are blanc cells on that matrix aswell. So if there is a blanc cell(s) on a row which (heading) is selected at listbox1, then I don't want that column (heading) which intersects with the blanc cell to be included to my listbox2 values.

Finally I want to insert the selected values from listboxes and the value from the intersection of those listbox values (headings) on that matrix to worksheet.

I included an attachment, where you can see my point better. However, as you can see, now the listbox values are not dependent on that matrix. Otherwise it is working like I want it to work.

View 11 Replies View Related

Summing Matrix Values Based On X & Y Axis Values

Feb 24, 2009

The simplified example below is intended to calculate the sums from the data values in the upper data set and place them in the appropriate cells in the lower summary table. Example......

I would like to find a single-cell formula that will handle the multiple instances of either the X or Y headings (ex: there are two 'Yes' Rows) and still reach the appropriate summations. I would like to accomplish this without resorting to Macros or manual cell references if possible. I've acheived some limited success with array formulae so far, but find that the multiple instances of 'Yes' cause only one row to be summed before the formula finishes.

View 2 Replies View Related

Conditional Formating Between A Matrix And A Table.

May 25, 2006

I have a matrix that has been imported into excel from access. The column header is product number and the row header is name. I am then importing the data from an access query that puts the product/name matches in a small table to the right of the matrix (will be hidden when completed). I need to shade the corresponding cell in the matrix if that name has a match for that product. I tried =AND(MATCH(name details),MATCH(product details)) as my formatting equation, but that does not account for whether the matches are in the same row or not, so any combinations of products and names that appear in the whole table are shaded. I am not sure what other commands I can use to get the formatting equation to make sure that the matches are in the same row. Below is an example of my setup, "S" means it would be shaded.

------ 1----2----3----------------Name-------#
Name------------------------------Bob--------1
Bob---S----S----------------------Susan------1
Jane--------S----------------------Jane-------2
Susan-S---------S----------------Bob--------2
----------------------------------Susan------3

View 2 Replies View Related

Copy Ranges From Matrix/Table

May 14, 2008

The "Sub Shortcut()" line is throwing the error.

Sub Shortcut()

Dim CellString As String

Dim BString As Boolean

Dim StrShort1 As String

StrShort1 = "Copy"

Sheets("Office - Generic").Activate

Range("A3:B14").Select

For Across = 1 To 12

View 5 Replies View Related

Converting Data Table To Matrix Format

Jan 28, 2009

I am trying to convert the data I have to matrix format. I have attached a sample of the data in the file I have attached. The data is in the worksheet "Original" and the matrix format that I am trying to convert is in the "Transformed" worksheet. Can someone please help me with the VBA code for going from "Original" to "Transformed"?

View 11 Replies View Related

Locate And Retrivie Data In Table/matrix

Sep 12, 2005

I have a dataset in one work sheet. The data, cells, can be lovated by three identifiers. Each column has an identifier (month) and each row have two identifier that has to combined. Region and Year.

Below is an example that shows the layout of the data.

1234
10120011.812.275.031.6
10120022.272.113.724.09
10120032.263.243.232.65
10120042.061.792.384.59
10120054.012.373.655.16
10220011.211.920.814.4
10220020.571.331.853.3

How can write a formula/macro where I can put the three identifiers and retrive the data/cell into a new work sheet?

View 14 Replies View Related

Dynamic Comparative Matrix Table With Data Validation Conditions?

Jul 8, 2013

I have attached a sample data sheet which i am working on . ITs a comparitive matrix trable with Input validation between 0&2 . I also want the table to be dynamic .If i want to increase or reduce no. of rows and clumns i should do it some how . More details are mentioned in the attached file .

View 1 Replies View Related

Formula For Values In A Matrix?

Dec 28, 2011

I'm having trouble with a formula. Column A is a list of names. Row 1 is a list classes. What I need to be able to do is select a class from Row 1 in drop-down list in C14 and have it return the list of names in list starting at C17 based on the 'X'. In this example for SCIENCE it should return Tim Mark Jen. The actual file has 50+ names and 50+ classes.

Sheet1

ABCDEFGH1 MATHSCIENCEHISTORYLANGUAGEPEARTMUSIC2
JoeX XX X 3TimXX X X4MarkXX X 5SarahX X X6BrianX XX 7JenXX X 8
KathyX X X9 10 11 12 13 Class 14 SCIENCE 15 16
Required 17 Tim 18 Mark 19 Jen 20 21

View 3 Replies View Related

Conditional Sum From Matrix With Non Numerical Values

Feb 27, 2012

I am trying to conditionally sum numbers from a matrix. On the vertical axis, there will be duplicate values (text) who's rows should be summed, and on the horizontal axis I need to sum in between two numbers that will be in sequential order (such as dates). The real tough part for me is that the data field that I will be summing from has non numerical values, like dashes. Obviously, I don't want to sum the dashes, but it throws off any formulas I have tried (like sum arrays).

My hopes are to use as little memory as possible so my file size wont get too big and to not have to use macros (I do not really use them), although I am willing to try if they are basic.

In the example below, I want to sum rows for the letter "A" and in between numbers "2" and "4."

# 1 2 3 4 5
A2 6 5 4 2
B4 5 6 4 1
C-----
A4 2 1 2 1
E1 1 2 2 2

Y Variable: A
X Variable Min: 2
X Variable Max: 4

Correct Result = 20.00 = (6+5+4+2+1+2)

View 3 Replies View Related

Generate Matrix Of Buttons From List Of Values

Feb 20, 2013

Maybe this problem is not too much complicated for some of you experts. I have this:

I have in sheet "Products", in column A, a lists of Products from row 2 to N. (Product 1 , Product 2, Product 15, Product 37, etc).

How to generate in sheet "Menu" a matrix of buttons with the label of each button taken from the value of each cell in column A of sheet1.

I mean, the buttons would have the label Product 1 , Product 2, Product 15, Product 37, etc. Each button associated with macro "My_Macro".

I would like that the buttons be organize as simetrical as possible (regarding number of rows and columns)

* I'm attaching sample file with input in sheet "Products" and output desired for those few products. The real list could have 100 Products.

View 6 Replies View Related

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

Lookup The Values Contained Within The Matrix To Get Distances Between Them

Oct 8, 2009

I'm updating my database of distances between different cities in our records... but the source that i have to lookup is actually a distance matrix....something like this...

From/ToMumbaiPuneDelhiKochiBanglore
Mumbai024014501800900
Pune240015501650700
Delhi14501550028002400
Kochi1800165028000500
Banglore90070024005000

Now i need to lookup the values contained within the matrix to get distances between them. I'm hoping if i can do that i dont have to actually manually sit and list down all the possible combinations of cities and distances between them but directly lookup the distances from this matrix.

View 2 Replies View Related

Return Row Number Of Complex Matrix Values?

Oct 30, 2013

In the attached sample file, one can see the setup of my data. I'm trying to reference the row number of a certain cell in a matrix with many cells of the same value. I have different Headers of each row, which should make it possible to use INDEX, MATCH or VLOOKUP. but i don't seem to be able to get it right.

Description.I need to return the "class" och each of the capability elements, i.e. If the capability in "Soups" is marked ("X"), at class 3, i need the integer "3" to be shown here.

View 12 Replies View Related

Multiselect Listbox Preselected Values From Matrix?

Apr 3, 2014

How do I preselect items in a listbox based on combobox selection and values stored in matrix.

I have attached the file with a description of what I mean

View 7 Replies View Related

Mark Existing Matrix Or Array Values

Oct 28, 2006

I have this example array:

A1
B1
C1
D1
A2
B2
B3
C3
D4
C5
D5
E5

...along with this example table:

.12345
A
B
C
D
E

In my table, I want to notate every occurence of A=1,A=2,B=1, etc; by placing an "X" in the correct cell. So, the table should look like this:

.12345
AXX
BXXX
CXXX
DXXX
EX

I cannot figure out the formula. I've tried this:

=If(VLOOKUP($L6,$I$6:$J$15,2,False)=M$5,"X"," ")

....but it seems to only return the first value found, and not all occurences.

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

Create Unique List From Matrix & List Frequency Of Each Occurrence

May 23, 2009

I've searched the forum and believe this thread to be the closest to what im looking for, but its doesnt completely apply: matrix to list conversion. I have an attachment to support my questions (see attached). I have a matrix of words (strings) that contain repeating and non-repeating contents. The matrix also has spaces which are of no value. Essentially, the VBA needs to ' analyze' this matrix and create a consolidated frequency list (as shown in the file).

The matrices are HUGE and therefore some of the clumsy VBA i am using is turning out to be a bit inefficient. The file also contains formulas and such that I cannot use AutoFilter nor can I use Insert or Delete rows...so the VBA shouldnt use those either.

View 6 Replies View Related

Populate Cells In A Matrix From Goal Seek Values Based On

Aug 5, 2009

wish to write an VB macro in Excel (2003) that populates a 6 row by 10 column matrix (for a 3 D graph) by inserting values from matrix row column headers in cells in another worksheet and using goal seek to derive a value which will be pasted in the respective cell in the 6X10 matrix

Specific example: Matrix (Row Headers A2 to A7, Column Headers B1 to K1, Cell values are in B2 to K7). Please note row and column headers are numerical values since this is for a 3D graph

Macro to be written

Pick a value (numerical) from row header A3 in Sheet A and paste it in cell B6 in another sheet (Sheet B). Pick anothet value (numerical) from column header D1 in Sheet A and paste it in cell B7 in Sheet B.

Run a goal seek scenario such that cell S10 in sheet B is equal to 50 by changing cell G8 in Sheet B

Copy the value chosen/optimised by Goal Seek from cell G8 in Sheet B and paste it in Cell D3 (which is the relevant cell for the values for intersection of headers A3 and D1) in Sheet A

Macro should populate all cells in the matrix in Sheet A (matrix cells to be populated are B2 to K7) in a similar fashion

The macro should be locked in to the cells which are either being copied from or pasted into so that it still references them even if new columns or rows are inserted.

View 10 Replies View Related







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