Code:
A B C
20 100monkeys 1
1 10avenue 0
1 10avenue 0
2 10avenue 0
1 10avenue 0
1 10avenue 0
[code]....
I need to write a function, that outputs "1" in C for only biggest number in A, if it is above or equal 20 for each list of values in B. Is this posseble with Matrix-Functions or should I look into VBA?
VB: Function f1(Matrix As Range) 'Does something and returns f1 = a double End Function
And a second function which defines and constructs a matrix of doubles to use as an argument in f1 to return a double:
VB: Function f2(dD As Double) Dim MatrixRed() As Double Redim MatrixRed(1 To dD, 1 To 10) For i = 1 To dD For j = 1 To 10 MatrixRed(i, j) = i * j Next Next f2 = f1(MatrixRed) End Function
I get an output error (#VALUE). I think it has something to do with MatrixRed not being a range anymore?
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.
I have adapted the use of StephenR's solution to a problem similar to that in the thread linked below. However I want to load the data directly into a new sheet instead of loading it into line 27 of the current sheet. This is the code I am working with, I think I need to change r2 somehow, not sure what to do exactly though.
[URL]
VB: Sub KonverterFraMatrixTilListe() Dim rng As Range, c As Long, r As Long, r2 As Long r2 = 27
[Code]....
Another thing that I really want is for it to run not only for rows 4-8, but for rows 4-last row as defined by the A column.
User# User_Christian User_Surname Role 1 bob smith operator 1 bob smith supervisor 1 bob smith warehouseman 2 john stone operator 2 john stone gunman 5 george brown Foreman
What Im after is:- a matrix with unique Role list across the top Unique ID & Name down the side (I can do all the above using recorded macro's) but the clever bit I cant even start to work out is how do I get matrix populated from the list?
I have been using Excel to record the routine daily issue of items to different groups in a matrix layout, I use a different workbook for each month with worksheets for each group. The matrix takes the form of the item issued being the left hand column and the date issued the top row of the matrix, the quantity issued is recorded at the intersection. Each item can have a different quantity issued on different days. I'm using Excel 2011 for Mac but could use PC Excel 2010. Is there a way to convert the data held in this way to a list? What I'd like to achieve is a list showing the Item, the Quantities Issued and the the Issue dates
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"?
Currently I have two tabs one with six columns of data. On a separate tab I have a matrix of two of the criteria. I would like to map the company data to the matrix, but recognizing the primary representative has more than one company he/she manages. Hence I don't just need the first entry, but all of his/hers maps to the size columns.
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
I am having a bit of a problem creating a formula for this report that I have to update every week. I need to be able to find the room nights and net room revenue values for the specific rate code in the "MATRIX" workbook.
On "CURRENT REPORT," I need D4 to pull up the value on "MATRIX" that equals "Room Nights" in column H and "CONABC" in column J. The same thing needs to happen for F4 but with "Net Room Revenue" in column H. The full report has about 500 of these codes in column J, and I need a formula that I can copy easily and will not be affected if codes are added or removed. This is super last minute - I need to finish this report by tomorrow morning, so take a look at the attachments.
Here's a sample of the document I have, the original has several thousand entries, so figured for the sample I'd limit it. The raw data is on sheet 1, and sheet 2 is what I want to have to more easily manipulate the data. I started doing it manually, but I'm sure there's a far better method that I just don't know about yet. sample.xlsx
For those that don't want to download the document:
I need to consolidate these two lists of data into one list.
First set is just a basic list of individuals with their data.
Second set is multiple entries for those same individuals. Each entry shows a subscription to a programme. The final single sheet should have one row per individual which shows all of the programmes they are subscribed to.
See attached, example sheet.
The real list is 3000 IDs, so need some kind of formula to do this.
I have a large amount of data, 40,000+ rows about how students do in remedial classes including from whom they took the classes. I have pulled from it a subset of student success as relates to the separate instructors. Now I would like to extract a consolidated list of those instructors for whom the student gain on criterion based pre and post course assessments is above a specific level. I have no trouble writing if statements that will look at two types of data, the course taken and the average student increase to determine which instructors meet or surpass the criterion, but I can only do that within a list that includes all the instructors. Because there are more than an hundred such instructors, it would be easier for the dean if I could provide a short list of those that meet specific criteria. I believe that it would be an array that I should use, but I have been fooling around with this for 2 days now, and am finding myself at a series of deadends!
I am trying to create a drop down list that when selected will display data in the 10 rows below and 4 columns, so a total of 40 cells. I would like the data to be based on the selection in the drop down list.
i have 27 different combinations of the above list with up to 10 different rows. I have a label for each one, but i cannot get the rows below my drop down list to display the proper values base don the selection in the drop down list.
I'm trying to put together a spreadsheet that tracks disc capacity increases, affected by any incoming projects. I've managed to do so for one project, but would like to for up to 10. The way i've designed the solution (i'm sure there are far more elegant ways, but hey) is thus:
A forecast worksheet keeps track of a grand total, taking information from sheets P1 -> P10 (being projects 1 to 10). I am unable to figure a way to add up all the increases from all 10 project worksheets with one succinct formula. What I use so far is: ='P1'!C83+SUMIF('P1'!E82,"=2009 - Q1",'P1'!D82) ..................
I am using the below array formula in G2 (that I then drag across) to show the score for all the times "mike" appears. I would like to match all the times "mike" OR "red" appears, so that the value in K2 is "99".
I have created a list for Cells A2:A10 which has the various roles of person. Gave it a name Designation. I have created a drop down list using this list by going to validation. =Designation
I want a function that would check the value entered and search this list would return TRUE or FALSE
I have 2 worksheets. One has data, the other is a receipt. In the receipt I would like a function to look at an ID # in a cell on the receipt, go to the data worksheet and find all of the information in a range of cells and return ALL data pertaining to that ID#. I haven't had any luck with VLOOKUP, unless I need to nest functions?
I have been writing a speadsheet using drop down lists and the Lookup function to link names to body weights. When using the dropdown list it displays for one name the incorrect weight. All the rest work correctly but one will not. From the list below when I select the name White it displays '68' rather then '80' as it should. If I change the name of White to the number 4, it will act correctly and display the weight '80'. It seems rather bizare.
I have two sheets open in Excel, the second sheet contains a huge list of data. I am trying to make a workbook that lets any user select from several dropdown lists and in the end, displays the (in this case) recommended material to use (and if possible the 4 best choices). The user will select from dropdown lists what the environment the material will be exposed to, the minimum required temp, and the maximum required temp.
I have a drop list for selection of staff in an area, but can i then do another droplist and use the sumif function to sum up the total time depending on the value of the list.
i.e., if i selected name 1 it would search all of the sheet to see if name 1 was there and then add the times.
I am trying to have a drop down list which is dependant on the result of a cell.
For example, if cell A1= CAT, then cell A2 should have a 'drop down list 1'; if cell A1=DOG, cell A2 would have 'drop down list 2'; if cell A1=RABBIT, cell A2 would have 'drop down list 3' etc.
Creating the drop down list is easy, I just want to know if it's possible to have different drop down lists depending on the result of a cell.
