Finding Start And End Columns Using MATCH Function
Jan 22, 2014
I have a table as follows: [URL] - note: cells containing times and temperatures are numeric values, I've used custom formatting to add " mins" and "°C" on the end
In one cell, a user can input a time period (in mins), and in another cell, I would like to output the start and end columns (in letter/s) for the chosen time.
For example, in the link above, if the value inputted '30' then the start column is 'C' and the end column is 'G'. If the user had inputted '60', then the results would be 'H' and 'K', etc.
I am able to find the start column with the following equation:
=SUBSTITUTE(ADDRESS(1,MATCH(VALUE,C2:P2,0)+2,4),"1","")
I am struggling, however, to find the end column for the chosen time.
If each time had the same number of temperatures then it would be a simple case of adding the number of temperatures per time, minus 1, after the "+2".
My first idea was to copy the above function, but to change the "0" in the MATCH function to "-1", in order to find the next greatest time value. However, it appears that using "-1" in the MATCH function only works if the range is in descending order - my times are always in ascending order.
I have a further problem in that the time periods are not always the same values (i.e. it won't always be 30-60-90-120, it could for example be 30-45-60-75). So as far as I know, there is no way in advance of knowing the next time period up (and then subsequently taking one column to the left).
One possible workaround I have considered involves the following:
- Add a new row between rows 2 and 3, which has the time values in each cell (i.e. from column C, values of 30-30-30-30-30-60-60-60-60-90-90-90-120-120) - effectively duplicating the time row, but not merging cells
- Use the COUNTIF function to find the number of cells in that row containing the chosen time
- Add this value, minus 1, after the "+2" in the above formula, i.e. =SUBSTITUTE(ADDRESS(1,MATCH(VALUE,C2:P2,0)+2+COUNTIF(C3:P3,"="&VALUE")-1,4),"1","")
If a number in cell M1 is also anywhere in column A (column A has unique numbers), then cell W1 (date) should equal the content of cell V (also date) in the row that that number appears in column A. If M1 is blank (or there is no match in column A), then W1 should equal V1.
In another sheet I have "Project " , " Start " "End" . I need to find the start and end quarter and fill in. The output should be "
HR 2014/Q1 2015/Q3 crm 2013/Q1 2017/Q3
ATTACHING A FILE- In sheet- raw data - i need to search on ID- and in "Test.xlsxData" i need to fill in the start and the end date- as shown in Data sheet.
1. start at home position (yup can be done ) 2. select the whole column or row (again can be done ) 3. scan through from 1 end to other to find the first non empty cell ???
i have a situation where i just want to copy data from another app open the sheet up and paste the data anywhere i want.
then have the macro find the 1st (or last) cell of any row or column and give me the cell reference.
I can for a for loop but going for 1 to the end of a column ?? and multiple that n times well something quicker can be done surely??
what i was thinking of was something like selecting a column then testing the column as a whole for a cell with something in it be it text space ( i takeit a cell with a space is non empty ie testing with isempty on such a cell would be false? ) or numbers.
I need to find out the total movement in start times per week for the employees.
I have used MIN and MAX to find the earliest and latest start times, and then a subtraction to find the difference.
This works brilliantly until my employees who start around midnight.
If they have started before and after midnight, it takes midnight as the earliest time and 23:00 as the latest, giving a difference of 23 hours instead of 1 hour.
i.e.
Start Times 23:00, 23:15, 00:15, 00:30
Range of Movement 00:30 - 23:00 = 01:30
I've tried, =$B$9+($C$4>$B$9)-$C$4 but this does not work when the MIN reads 00:00.
I have a program that requires the last 8 days to be total, unless there are 2 days off in a row. So my cells need to sum the previous 8, but then stop when two zeros show in the column and then restart following. One zero should continue to total the previous 8 days. As shown below, for 1/10/14 I should see 57.5. The sum needs to restart on 1/13/14 to show a total of 10.5. I have tried multiple formulas with no luck.
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)
As everyone realizes that WORKDAY function can return a working date that exclude weekends and any dates identified as holidays. However, what if I want to return a working date excluding my designated holidays but including weekends?
For example,
Holidays are 1 Jun 2009, 2 Jun 2009 and 4 Jun 2009
Start Date: ??? Finish Date: 8 Jun 2009 Duration: 5 days
The Start Date should be 30 May 2009.
Seems like I am not able to use WORKDAY function to calculate the start date.
Anyone know why the NETWORKDAYS() function will return a value of 1 if the start and end date are the same? Does the function always assume that the start_date is at 12:00AM and the end_date is at 11:59PM, because it seems to also show that there are 3 days between 11/04/08 and 11/06/08.
Is there a way to start match from a relative position?
Say a match I have in column "A" returns 344. Is it possible to start a new match for column "B" from that spot, instead of having it return a match starting at the top?
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 written a basic search which pastes the results on to another sheet.It works, but i have a small issue with it.If I search for "A" it will give me all the results for "A" but it will also give me any result with a combination with "A" in it, eg. BA or BBA etc I need to have results based on the exact search. What do I need to do to make my search result and exact result?
This is currently what i have
Sub Rating() ' ' Rating Macro ' Dim Wrd As String[code]......
I've got 2 sheets. Sheet 1 is where I am inputting all the products I'm producing and giving them serial numbers, if I produce the same part twice the name of the part is the same but the serial number changes. It looks something like this...
Sheet 1:
A B C
1 Part Produced Serial Number
[code]....
While Sheet 1 is constantly been filled with the parts being made with their individual serial numbers. Sheet 2 is constantly being filled with the incoming orders, But only the A Column of Sheet 2 is being changed... as an order comes in I simply type into sheet 2 what product the customer is looking for.
The problem I am having is that as I write in that another "handle" for example as been ordered into sheet 2, my normal MATCH forumla is bringing up the same Serial Number "1001" over and over again. Really I'm wanting the "B" column to update to the next available serial number, so for example "1002"
Sheet 2... as it's coming out right now (wrong)...
A B
1 Part Ordered By Customer Serial Number
2 Handle 1001
[code]....
The formula will need to go into Sheet 2 'B' Column, and I'd like it to come out looking like this...
I have some intersting issue. Actually there are lot of functions in excel but my purpose is somewhat is solved by VLOOKUP , MATCH, OFFSET, INDEX functions.
Prob: Here i have a database contains as below A1 B1 C1 D1 111 123 i/p o/p 222 301 111 321 333 456
If i keep 222 / 333 in C1 i will get output at D1 as 301 / 456 respectively. But if i keep 111 , i want a desired result to be most lastly updated value in the array that is 321 but it is printing 123.
let say if have A1 B1 C1 D1 111 123 i/p o/p 222 301 111 321 333 456 111 500
then if i enter 111 in C1 then i want to get 500 but still am getting 123 only. tht is the first hit. i want to return a value which has lastly updated.
So if i use any fuctions from above i may getting correct results for 222,333 but i want result for 111.
Thoughts and rules: 1.The new entry will added at the end of the array. 2. A1/B1 are only numbers. 3. free to use any functions
U can have flexibility to create one more array or even update the column B1 if any i/p matches in column A1. So there will always a single output for each unique input. Imean to say u can have a array with
A1 B1 C1 D1 111 321 (replace 123 with 321) i/p o/p 222 301 (remove this entry) 333 456
I recently installed 'windows 7 ultimates' , and after i insalled Excel 2007 .. the columns start from right to lfet (..., C, B, A), I want to reverse it to start from left to right (A, B, C, ...), I don't know whether the new ystem 'windows 7 ultimates' has some effect on it to be modified like this. moreover, the language of system is English. so how to solve it
I want to delete a row in a spreadsheet by finding the row that contains a particular value. I use MATCH on spreadsheet #1 to identify the row number on spreadsheet #2 to delete. I need the macro to select the row number on spreadsheet #2 and delete it.
I don't know the syntax for the commands in VBA to write this and I'm coming to this forum for help because I'm too lazy to learn VBA. I've recorded macros to add rows, populate cells, hide sheets, etc. The following code is extracted from a macro I recorded to try to get started, and was hoping to be able to substitue a reference to sheet1 within the parentheses at
Rows("4:4").Select
but I can't make this work. The following is the code I was trying to adapt.
Sheets("Sheet2").Select Rows("4:4").Select Selection.Delete Shift:=xlUp End Sub
What im trying to do is match columns A & B from AUDIT Sheet to Columns A & B in MASTER sheet. If they match then pull columns C, D & E from MASTER into AUDIT.
I have been trying to see if I can peform the following, I want to be able to have Day 1 (Col B) by using the Start and Finisg Columns Place a 1 in column H for every hour between the Start and Finish times.
This needs to be done for each of the Day in column B, ie 7 Days.
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.
Im using vlookup to search a table and fill out a calendar with dates that various employees are gone. They can have multiple trips in one month and thus will be in the table multiple times. I have the calendar set up so days go across the top and names go along hte left side. so each cell is identified by name and day. I then have each cell using the vlookup command to search the table. now if one person has multiple trips a month is there a way to compare multiple entries in the table?
I've read the how to for finding the nth occurrence using index/match but the example given does not really help solve my issue. The file I've attached is a condensed version of the actual file, which has more columns but I deleted all but the necessary ones for clarity. What I am trying to accomplish:
On sheet1 there are three columns, Business, Amount, and Closing Date.
Not all the business names have a closing date and the spreadsheet is sorted alphabetically by business name, so sorting by closing date, and using the method used in the topic " find the nth occurrence in excel", is not an option.
On sheet2, I would like to see ALL the business names that have a closing date in the respective month, as opposed to just the first. Then to the right of the business names I have the sum of all the amounts in that month, but I figured out how to get that one already.
I have several property codes in column E e.g THA 134,THB 224, C 122, 223 AD. The letters A, B, C, D denote a type of property but do not always appear in the same place in the cell (sometimes 3rd character, sometimes first character etc). I want to put in a formula in column F, so that if the character A, B, C or D is in column E then the result in column F will be "A type", "B Type" etc....
