I have attached an extract from a Conveyor drum design document.
Cell E7 contains a relatively simple formula to extract data from the supplied table. I have tried time and again to make this work, but the best i can come up with is the formula shown, which returns an incorrect value.
In cells D2:BM75 I have data about grades either in the format 3a, 3b or as an X which denotes not making progress. Across row1 (D1:BM1)there are column identifiers outlining the subject followed (Maths, English etc). Row 2 onwards contains the pupil name. I need a method of identifying which subject they have an X in for each student. Note that there could be blank cells with no grades present.
Example:
.....................Maths....French...History...Geography Jon Smith........X..........3b.........X...........5b Bob Brown.......3c.........X..........4b..........X
The output for Jon Smith (in cell BM2) would be Maths, History The output for Bob Brown (in cell BM3) would be French, Geography
We're selling language courses that have a different price depending on when a course is taken. We're trying to build a price quote application that needs to look up a certain school and course and price driven by a start date.
How can I build a lookup function that says: pick the price of school X and course Y when the start date falls between dd/mm/yyyy and dd/mm/yyyy?
I manage to build a look up function with MATCH and INDEX when the condition of start date is exactly matched but dont know how to instruct it to match a value between a start and end date.
I am trying to write a code in excel vba where I need to match specific text in multiple columns and if text is matched then I need to insert new blank column after the specific text.
My code is like this.
Dim Col_num As Integer Col_num = Application.WorksheetFunction.Match("Customer Defined 06", Range("6:6"), 0) + 1 Columns(Col_num).Select Selection.EntireColumn.Insert Cells(6, Col_num).Select
This code is working fine in single condition but when I enter another condition then its giving me an error i.e Type Mismatch.
I have a worksheet that has 6 columns of numerical data that all column datasets are referenced together for each row. So for example the data in row 5 in all colums is related.
Column C through F have 4 numbers that refer to the data in the first 2 columns. These are also ordered by row.
I need to look in column A for 0.00, and column B for 3.14 Both of these must be in the same row or I need the closest value. When these values are found in columns A & B, return the values from the same row in Columns C through F.
I'm trying to figure out how to write a VBA Match function that can look for multiple criteria and return the row number of a successful match.
I have about 255,000 rows of data on the worksheet "Filtered". Column B contains my Item Number and Column D contains the supply source. I want to find the row where ItemNumber and SupplySource match my variables and then return the value from Column C.
I can do a match for one criteria, but where I'm having problems is getting it so the two matches are on the same row.
I have a data set where the row headings repeat a lot. I have 5 headers repeated probably 30-45 times each. Eg. Truck, Car, Van, Tank, House, Car
At the moment I am using the formula... =INDEX($B$2:$M$45,MATCH($O$2,$A$2:$A$45,0),MATCH(O4,$B$1:$M$1,0))
which will only return the result of the first occurrence of the cell it is looking for (for eg. I am looking for the values in the cells in columns labelled 'Car', is it possible for it to look past the first occurrence in the data set and find all the values?
b2,b3 Formula = Indirect(Address(match(a2,????,0),1,1,1,"Data"))
i dont know what is the correct range inside the match fuction to identiy the row number inside the Data sheet, i think the solution would be match(a2,Data!a2:a13,0) but things might get complicated if you have multiple worksheet such as sheet2: Data , sheet3: Data2, sheet3: Data 3
and each table has unsorted details compared to others sheets (Data vs Data 2)
I have a spreadsheet with multiple sheets, which can vary from project to project, and they all have the same center sections of the headers. Is there any way to automate the filling in of all these headers based on the first sheet?
I need to create multiple workbooks from a template, but the header on the sheets need to change based on a list of numbers. I know you can generate multiple sheets from a template within the same workbook. Can you export all the sheets once they are generated to separate workbooks/files? Also I need to change the text in lets say cell A1 on every sheet based on a list from the main sheet. So the main sheet has a list in column A: 1, 2, 3, 4 etc. Once the sheets are generated from the template cell A1 on each generated sheet needs to have a different number from the list i.e. 1, 2, 3, 4 etc. It would be nice if i can set the format of cell A1 on the generated sheets so when then number is inserted it large text and bold.
I'm currently faced with a spreadsheet that has data formatted like this: A 1 RandomRowofData1 2 RandomRowofData2 3 RandomRowofData3 4 RandomRowofData4 5 RandomRowofData5 6 RandomRowofData6 7 RandomRowofData7 8 RandomRowofData8 9 RandomRowofData9
Every 9 rows, a new "set" of data repeats itself (wow, this is so hard to put into words)....
I need to figure out a way to get the data in column "A", every 9 rows, to transpose itself into 9 separate columns.
I am trying to go through each worksheet and if the worksheet name is Hematology then the header columns will be put into the listbox (ListBox1). The first row of the header is the parameter and the second is the units. Ideally I'd like column 1 to have the first headr row and column 2 to have the second header row. Once the listbox is completed, the user can select multiple columns by the header and those columns will be deleted. I have the ListStyle set to 1-fmListStyleOption and MultiSelect set to 1-fmMultiSelectMulti
The only thing I get when I run the rubroutine is a userform (Hematology), an empty listbox (ListBox1) and my two command buttons (Nothing to Delete and Remove Parameters).
Private Sub Hematology_initialize() Dim Wrkst As Worksheet Dim Header1 As Range HeaderRange1 As String
For Each Wrkst In Worksheets If Wrkst.Name = "Hematology" Then For i = 1 To Wrkst.ColumnCount Set Header1 = Wrkst.Cells(5, i) HeaderRange1 = Header1.Address & ":" & Header1.Offset(1, LastColumn).Address With Hematology.ListBox1 'Clear old ListBox RowSource .RowSource = vbNullString 'Parse new one .RowSource = HeaderRange End With Next i End If Next Wrkst End Sub
I am having some trouble getting a formula to work. I am building a report that pulls figures from a pivot table in another workbook. I am using a vlookup with match function to get the column index to find the relevant data I want. Where I need to add two columns together I am using sum, with the vlookup & match formulas nested in them e.g.:
Where: F13 = Employee number Column C on the pivot 156 workbook is where the employee number is based. The Match formula is then getting the column index from the column headings of the pivot table ie. "FAID"
This in itself works fine, as long as it finds a match in the column headings. This is where i get the error as in the above function "COMD" is not in the pivot table. However I need to keep it included as it may appear on a future pivot table. Is there a way of getting the sum function to complete even though later in the formula it can't complete the vlookup? So it will ignore it, or assume the value is zero if it can't find it? The formula probably needs to do this for all the vlookups as some headings may drop off in future pivot tables.
I am trying to match data from 2 independent sets, formatted slightly differently so not sure which function would work best for me. From the attached file, I am trying to match the date and time stamp (in cell A1) with that from the other data (in this example in cell E1) and return the data (from cell F1) to cell C1. So basically any date and time stamp before 04/03/09 04:00 will return a value of 44 (this value should appear, therefore in cells C1 - C30)
Basically where the columns say 2011 or 2012 AND 1, 2, 3. I want to be able to have it index the number below based on the GL number on the left and both the year and period on the top. I think that you can do with using the sumproduct function with the binary, but the computer is a little dated and it takes a while to run those calculations.
I have a userform with two listboxes in them, i have set the columns in the listbox to 9 and would like to populate the column headers in the listboxes with the column header of one of the sheets.
let me start by saying that I know an example workbook would be useful here, but the part I'm struggling with is the [managementroster.xlsm] file, and there is A. no way I can release it to the internets and B. its so huge/complicated I couldn't even begin to reproduce a portion of it, scrubbed of data, and hope to maintain its functionality in a meaningful manner.
[Code]....
This formula checks a staff number on this spreadsheet, and then goes and looks at the staff number on the roster. Once found, it returns that staff members roster, but changes any manager codes in the MRC list to Mgr, and changes all other roster codes to Free.
I now need this formula, before altering roster codes to Mgr or Free, to only return codes that are a match for another table (or after really. I don't particularly care, so long as only codes are shown that match data from another table). I think an index/match function would do the trick, but this forumula is already at the edge of my excel ability, nesting another function within it is completey beyond me. The relevant cells for the index/match function would be:
This first Match function targets the column. $E3 is the date required, $BA$1:$DN$1 is the range the dates are entered in Match: Lookup value = $E3 Lookup array = '[ManagementRoster.xlsm]Vacancies!'$BA$1:$DN$1 match type = 0
This second Match function targets the row. $A$4 is the department name, $B$434:$B$452 is the range where all departments are entered
Match: Lookup value = $A$4 Lookup Array = '[ManagementRoster.xlsm]Vacancies!'$B$434:$B$452 match type = 0
Index: array = $BA$434:$DN$452
So I think my final function is
[Code] .....
But I have absolutely NO idea where it would fit within my first formula, or how to code it so that my original formula only reproduces results that are found in both sheets, or anything.
See attached file, "Rate Sample Index-Match Formula".
I need a formula to return the value at the cross section of two (2) lookup values that match. This formula will be input into column D under, "RATE" on the 1st tab, "TEST FILE".
In the 1st tab, "TEST FILE" there are a series of columns as follows;
A = Service B = From C = To D = Rate
In the 2nd tab, "RATES" there is a series of rates with drivers From (green) & To (blue)
The formula needs to do the following;
1. Lookup the "From" value in column B on tab, "TEST FILE" and match to column B2:B59 on tab, "RATES" both highlighted in green 2. Then Lookup the "To" value in column C on tab, "TEST FILE" and match to row C1:BH1 on tab, "RATES" both highlighted in blue 3. Then return the value at the cross section of the match "From" (point 1 above) & "To" (point 2 above) in range C2:BH59
For Example;
The rate From SYD To CBR = 0.33. I have highlighted this in yellow on both tabs to show where the formula needs to lookup the data to return the answer.
Additionally, if we were to add service as an additional lookup match how would this work?
I am having such a difficult time creating a macro that will reduce the 5+hours I have to spend each week manually copying & pasting all of this data. I making an IMMENSE difference in this worker bee's life!
I have a workbook with two sheets (Sheet1 & Sheet2). Sheet1 has license #'s in column A and the state that the license belongs to in column B like this:
COLUMN ACOLUMN B11111Alaska11112Alabama11113Arkansas11114Arkansas Sheet2 has three columns. Column A has the license #'s, column B has the state that the license belongs to and Columns C shows a line-of-authority tied to that license #.
I'm trying to write a macro that will compare the license # and state in Sheet1 to the license # and state in Sheet2. If it matches, append the contents of Column C to the corresponding row in Sheet1.
Here's the thing...Sheet2 contains the entries for all licenses in the company (so this table is HUGE). And there are multiple entries for each state license # (notice how there's two entries above for AK license # 11111 - one for the Property line and one for the Casualty line.
After my macro is run, I want Sheet1 to show all the lines-of-authority on a single line. So if I ran my macro on the above example, after it's run I would have this in Sheet1:
COLUMN ACOLUMN BCOLUMN C11111AlaskaProperty Casualty11112AlabamaProperty Casualty11113 ArkansasLife Health11114ArkansasLife Health
I need to match cells e3:h3 whenver the dates change in in cells c5:c9 and then put there result of cells b5:b9 into the corresponding cells of e5:h9.
Basically what I'm saying is that I want to keep a track of all previous pay amounts from each pay period. So when the next period changes the date it also copies the new pay amount to the corresponding date of the previous pay section.
I'm trying to use match function to find the max value that is less than a given value. look at my file. I think it should return F as the value, why is it returning G?
I'm using the match function to tell me in which column the first positive/non-zero number appears. However, the formula is returning the # of columns in the range. Here is what I'm using:
=MATCH(1,H6:CA6,TRUE)
I'm getting 72 back as a result, which is the number of columns between column H and CA.
I have the below code sent in by a MR Excel forum user
Public Function BigMatch(rngIndexColumn As Range, varMatchValue As Variant, rngMatchRange As Range) As Variant Dim lngCol As Long Dim rngCell As Range lngCol = rngIndexColumn.Column
For Each rngCell In rngMatchRange
If rngCell.Value = varMatchValue Then
BigMatch = Cells(rngCell.Row, lngCol).Value
End If
Next rngCell
End Function
when using the BigMatch Function = BigMatch(any cell in the column you want to pull from, the value you want to match, where you want to find that value)
I am using 2 different sheets, Technology & Lessons learnt: when I try the BigMatch Function
I would like to extract Bill cost from "rule" sheet and insert it to "data" sheet using index-match functions. My problem are multiple criteria and multiple matches.
The criteria are Column: Cost type, Power, Penalty Category (The logic goes like AND function).
And I want it to extract "Bills" value from "rule" sheet.
There are multiple matches in all columns (Cost type, Power, Penalty Category) which cause wrong extraction.
How do i solve this problem with index-match function?
I have a very long function I created. But I need to edit it in a matter I have never done before.
Below is my function and i will like to add a new condition to it.
IF the text value in cell C7 is equal to any text value in Workbook2 B10:K10 and the value underneath that cell (I.e if B10 then B11 etc..) is Deleted then I want to have one of the conditions of my function below display "Deleted".