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