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
ADVERTISEMENT
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
Jan 20, 2014
I have a complex list of text (a chart of accounts) with various roll ups for subtotals on one worksheet, and monthly download on another worksheet showing the name of the account and the value. I wish to use match the text to return the value in the front sheet and avoid taking the simple route of linking the cells in case additions are added during the year.
I've tried using IF and VLOOKUP formulas without success - i guess becasue I cannot sort the table in descending order?
What would be the best sort of formula to search using the text, and return a value on the same row?
View 1 Replies
View Related
Apr 17, 2009
I have 12 columns of data. In those 12 columns of data I have 3 digit numeric IDs. I want to count the unique number of IDs for each row. I have about 14K rows.
What would be the best way to do so?
Some rules about how the data is stored. I have 12 columns of data with anywhere from 1 to 12 columns having data for each respondent. Data always fills left to right and never skips columns.
I have attached an example file that represents how the data is stored and the output I would like (Unique Count).
View 7 Replies
View Related
Aug 9, 2012
I have a complex request which I think can be done either via vb or a macro, not sure which is best?, will try to explain:
The table shows a list of items that make up various BOM. there are quantities of each item required at the different BOM level
0 = Top level
1 = Level Below
2 = Level below that etc, etc
If the top level (0) has a quantity greater than 1 then all the items at the lower levels should be multiplied by that quantity
THEN move to the level 1's and whatever the quntity is in the level 1 field, multiple all lower levels by that number
if the quantity fields are blank, then always multipy below by 1
This needs to continue on down the chain of levels
So basically you end up with the correct quantites of each item at each BOM level.
View 9 Replies
View Related
Nov 14, 2009
upon further consideration I thought that it would be best to probably do verything in two passes or maybe not see below macro to get an idea of how the end process should function
View 4 Replies
View Related
Aug 18, 2008
I have made a function (udf), called linjenr. It's input is an n by n matrix, and it returns a n by 2 matrix. This works fine in the worksheet. However, when I'm trying to use this function (udf) in a sub it fails, i.e. I get the error "object does'nt support this property or method". How do I use an own made function that returns an array in a sub macro? (ctrl shift + enter..)
Sub linjegenerator()
Dim nodematrise As Variant
Dim antlinjer As Integer
nodematrise = Range("F6:T20")
antlinjer = WorksheetFunction.Sum(nodematrise) / 2
If antlinjer < 3 Then
MsgBox "This is not a network. Minimum 3 lines."
ElseIf antlinjer > 2 Then
If antlinjer = 3 Then
'Range("F25:G27") = linjenr(nodematrise)
ElseIf antlinjer = 4 Then
'Range("F25:G28") = linjenr(nodematrise)
ElseIf antlinjer = 9 Then
Range("F25:G33") = WorksheetFunction.linjenr(nodematrise)
End If
End If
'antlinjer = WorksheetFunction.Sum(nodearray) / 2
MsgBox "Antall linjer = " & antlinjer
End Sub
View 9 Replies
View Related
Jul 11, 2014
I have the following arrays.
A1=Array("A","D","F")
A2 = Array(Array(0, 1, 4, 5), Array(2, 0, 1, 1), Array(2, 3, 11, 3))
B1=Array("D","X","F","J")
B2 = Array(Array(6, 0, 2, 1), Array(1, 1, 6, 1), Array(7, 9, 1, 0),Array(0, 3, 2, 2))
I would like to print in column A all the unique/common values between Array A1 and Array B1. The common values are A,D,F,X,J. And I'd like to print in Columns D:G the A2(i) values and print in columns I:L the values B2(i) in the corresponding row of values of arrays A1 and B1. If some string only appears in array A1 (i.e. "A"), this means that string "A" have values associated in array A2, but not in B2, then print zeros from I:L and if the string only appears in array B1 (i.e. "X" and "J"), this means that strings "X" and "J" have values associated in array B2, but don't have valus in A2, then print zeros from D:G for the same row of X and J.
View 7 Replies
View Related
Aug 20, 2008
I have a 10 x 15 matrix named "Range1"
I have named the range of Column headings in that matrix as "Weight", and the range of Row headings as "Height". And then all the matrix cells contain some values.
Now I need to find the value from intersection of height and weight (please see the attached excel file)
Attention: I need to use excel VBA code to do the trick. (Not Worksheet formula using for example INDEX and MATCH)
View 6 Replies
View Related
Mar 15, 2008
A have a table like this:
X 1 2
A 9 5
B 8 4
I would like to have a condition that when is equal to B and 2, return the corresponding paragraph in the matrix (in this case 4)
View 5 Replies
View Related
Mar 8, 2013
I'm trying to write a formula that will return the sum of multiple cells within a larger matrix, based on a number that is manually inputted into a separate table. As you can see in the top below, I have data that is grouped by both "Zone" and by individual "Stations". The Zones/Stations on the y-axis are "Start" points, while the Zones/Stations on the x-axis are "Finish" points. What I'd like to be able to do is input a Zone ID number into the bottom table (Shown as "1" below) and have the formula either return the greatest sum of "Finish" numbers. In this example, the greatest sum is 12, which is all numbers going from Station 1 and 2 in Zone 1 to Stations 1/2 in Zone 2 (3+2+4+3). In the bottom table, you'll see the "Greatest Finish" as "2", which is Zone 2. And the total from this sum is 12, as described previously. If there is a way to return either the "Total #" or "Greatest Finish" zone.
The actual matrix I am working with is much larger (150x150), and not all zones have the same amount of stations. I'm assuming this might complicate things a bit.
Finish
Zone 1
Zone 1
Zone 2
[Code]...
View 6 Replies
View Related
Feb 20, 2008
I have the following formula: =IF(C319<=300,9,IF(AND(C319>300,C319<500,6.75),IF(C319>500,6))) It returns 9 if C319<=300 but returns FALSE for all other numbers. I want the funtion to return 9 If <=300 and 6.75 if C319 is greater than 300 but <=500 and if it greater than 500 it should return 6.
View 5 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
Aug 13, 2014
I have a range of say B1:Z2. In row B1:Z1 I have dates in the format dd-mmm-yy. In row B2:Z2 I indicate presence of a person by "P". This is at infrequent appearance i.e. p,p,p,blank,blank,blank,p,p,p,p,p,blank,blank,p,p,blank,p,p,p, etc. I am trying to find or workout a formula that would find the most repetitive Ps in row B2:Z2 and give me the sum thereof. I.e, from the above example it should be 5. It does not have to count specifically P. Something that counts the most non-blank sequence of cells in the range should also do.
View 8 Replies
View Related
Nov 20, 2013
I am looking up a risk matrix to return the risk rating... it is very simple, it works on one sheet, but on a different sheet (looking up different data, but same basic format) it returns exactly the opposite rating.
My formula is: =INDEX($G$6:$K$10,MATCH(I19,$G$5:$K$5,0),MATCH(J19,$F$6:$F$10,0))
Here is F4 to K10
I have data validation on I19 and J19 based on the cells below
Consequence
Priority
Very Low
Low
Medium
High
Very High
[Code] ..........
BUT... when I put Very Low and Very Low in the two cells (I19 and K19) I get Very High as the return.
View 1 Replies
View Related
Apr 9, 2014
I have two data columns: the first is a list of the last 100 calendar dates, and the second is an ON/OFF (or 1/0 if you prefer) value corresponding to each date. If the last value in the second column list is ON, I want to find how many continuous days the value has been ON for. If the last value in the second column is OFF, I want it to return a zero. Note that in the 100 day span of data, the value may switch between ON and OFF several times, but I only want the continuous, uninterrupted ON days at the end of the date range.
View 8 Replies
View Related
Apr 5, 2008
I've created a userform that has one ComboBox (ComboBox1) and two text fields. I am trying to get the userform to return information to my worksheet in the same row as the name that is displayed in the ComboBox. This is my VBA code.
Private Sub Cmdpayment_Click()
Dim iRow As Long
Dim ws As Worksheet
Set ws = Sheet4
iRow = Cells. Find(What:=Me.ComboBox1.Value, After:=C5, LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False).Activate
ws.Cells(iRow, 12).Value = Me.txtpdate.Value
ws.Cells(iRow, 13).Value = Me.txtpayment.Value
Me.txtpdate.Value = ""
Me.txtpayment.Value = ""
End Sub
View 8 Replies
View Related
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
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
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
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
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 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
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
