Search One Column For The Location Of That Value And Return The Row Value
I have a value stored in variable A. I need to search one column for the location of that value and return the row value. there is no chance for a duplicate entry. Is a loop my only option, or is there a find command in VB?
View Complete Thread with Replies
Sponsored Links:
Related Forum Messages:
Return Active Cell's Location/row
I'm having trouble identifing a way to return a location for the position of the active cell. I've searched Excel help with "Position, location, return, activecell, etc." and I can't seem to figure this out. I know that it's possible, so that's why I'm on here! ... Ok, say the active cell is currently "F1", and I need the location "F1" to identify the ROW to be used in a formula later, how would I go about that? The current contents of cell "F1"' will be "REPLACE", but I need to change the words "REPLACE" in "F1" and other cells labeled "REPLACE" in column F to the following formula (where the "1" in "A1" is is the current row):
View Replies!
View Related
Search A Column Of Dates & Return Data From Another Column
I am trying to get a formula that will search one column range “B” and pull data from another, “D”. Dates are down column “B” and some of those dates are repeated several times. In column “D” there is only one piece of data (a number) entered for each day. Eg, if 08/06/2009 has been entered 3 times in column “A”, there will only be data entered in 1 of the cells of column “D” and blank cells in the other 2. Column B -- Column D 07/06/09 -- 54000 08/06/09 08/06/09 -- 62000 08/06/09 09/06/09 -- 61000 I couldn’t get the LOOKUP function to work properly, as there are duplicate dates in column “B” and I often got a result of 0 returned. As I’ve only got 1 piece of data added in column “D” per day and any duplicate days would just have blank cells in “D”, I can actually get a SUMIF function to work, SUMIF(B3:B60,DATE(2009,6,8),D3:D60). Although it does work, it doesn’t feel right using it and I would prefer a formula that would return just the one cell, instead of the sum of a range of cells.
View Replies!
View Related
VBA Keyword Search To Return Row Numbers
i'm writing a few VB subs to help manage a database of mishaps, solutions and lessons learned, there are 11 columns and each databade entry has a separate row, there are approximately 1,000 rows. Columns are: ID- a unique iderntifier for each entry- integer Date- date the entry was made- date Project Number- a reference to an internal project file- string Operator- company we were working for- string Installation- where the work took place- string Category- type of problem encountered- string Application- field of problem encountered- string Issue- string Background- string Problems- string Lessons Learned- string...........
View Replies!
View Related
Search Column And Return Values
ok I have multiple columns where Col-A can have any number between 1 and 5. Col-B has a 3 digit number in it. i want to search all of column A and determine if there is a 1 in it, if so i want it to print the value of col b into a specified area. here is an example of my form. COLA | COLB | -------------- 1 | 324 | -------------- 2 | 325 | -------------- 1 | 327 | --------------- this is basics, i will be using this to search col-a, determine what number is in the column, then print the number found in col b, on another page in a specific place, on the other page i have squares labeled 1-50. in a grid pattern, 10 squares per row. im trying to get all the entries in col-a, that are a 1, to put whatever value is in col-b in the first box of the grid, whatever is found in col-a with a 2, the value found n col-b of that row, into the grid box labeled 2 etc......
View Replies!
View Related
Search For Text Return Value In Next Column
I'm trying to create a spreadsheet using several others in several different formats. What i need to do is search for a text string eg "EVP" in one column and return the numerical value of the cell in the column next to it - then sum all the values and create a table in the new sheet eg. EVP sheet1!(value)+sheet2!(value)...etc...
View Replies!
View Related
Search A Column & Return Many Results
I have a workbook with two worksheets. On Sheet1 I have two columns 'Date' & 'Serial Number' I want to search the 'Date' column for entries that contain todays date & then display the corresponding 'Serial Numbers' in the first column of Sheet2.
View Replies!
View Related
Vlookup: Search For Value In SECOND Column Of Table Array And Return Value From FIRST
I'm looking for a formula to search for a value in the SECOND (instead of first) column of a table array and return a value in the same row from FIRST (instead of the same or another) column in the table array. Formula would be searching for the unique production order number in the column B and return production line id from the column A. A1 production line_id B1 poduction_order_number A2 L1 A3 L2 A4 L1 B2 505212 B3 504234 B4 505663 I was trying vlookup(504234;B2:A4;2;0) to make formula go search from right columns to the left but then excel is switching the search table to A2:B4 and gives #N/D! One remark-there is no possibility to switch these 2 columns to simplify. I have to leave them as they are.
View Replies!
View Related
Search Partial String, Return Value In Next Column, Vba
I would like to search cells in column D for the partial string, "PIPE," (A full string may look like this: 'PIPE, 24"ODx0.375"WT API-5LX-65,ERW OR SMLS'). Then, if it's there, return the value "LF" in the corresponding cell in column C. If that string isn't found, then I'd like it to return "EA". I know this seems pretty easy, but there's a small problem. The word "BENT PIPE," could be in Column D, in which case, I would want it to return "EA" instead of "LF".
View Replies!
View Related
Match Text In Column Of Another Workbook & Return Nth Column Same Row
I have two excel files. File #1 is a varying length and has 4 columns with ID #s in Column A. File #2 is a two column file that contains a list of ID #s in column A AND modified ID#s in column B. I need to match the ID #s in column A from file# 1 to the list in column A of file#2 then replace ALL instances of the matching ID #s in column A of file#1, with the values found in column B of file #2. I've tried using the substitute function but I could only replace one found ID at a time.
View Replies!
View Related
Find Value In Column & Return Cell On Same Row- Different Column
I have an excel sheet I am working on and in columns F1:F2000 I have an IF statment, I need to be able to add more "IF"s to it but I will exceed the 1024 char limit. Is there a way I can put this formula into VB as a function called DocumentType() and then in excel F1:F2000 =DocumentType()? Is it as simple as: Function DocumentType() If(...........) End Function
View Replies!
View Related
Search For Two Cells In A Row In Column A
I need to build an if statement around whether or not two blank cells in a row are found in column A moving down from A1 The two blank cells will be between cells that contain data (so no I am not looking for the last cells in the column, matter of fact, the if statement cannot be true based on two blank cells at the end of the column, only inbetween data) Suedo If Two blank cells in a row are found between cells that contain data in column 1 do this Else Do this
View Replies!
View Related
Search Column And Insert A New Row
I am trying to loop through a list of numbers in column "U" Each time we encounter a 10 we insert a row before it and copy the text out of column "T" in the old row, and paste it into column "W" in the new row Then resume and loop to the next 10 and repeat the action till we encounter blank cells. I can do the first loop and insert the new line ok but then I’m out of my depth.
View Replies!
View Related
Column Search For Row Data
I have attached a small example. I have a list of data of employees. I want to be able to input a number (in column A in example) and to search the data records for this number. When the number has been found, the corresponding info data from that Row will show in columns B,C & D. I have tried this using LOOKUP etc but find that it is "hit & miss". I can input one ID number and the corresponding details will appear, but very often if I enter any other ID numbers further down the sheet I sometimes get the correct data or I might get the "N/A" error. The error seems to occur, I think, if the next input ID number is higher than the last. The ID numbers I input down column A will NOT be in numerical oredr.
View Replies!
View Related
Formula To Go Through Each Row And If The 2nd Column Is Yes Then Return The First Column
I have a collection of data like the following: Week1 | yes Week2 | no Week3 | no Week4 | yes Week5 | no ... Week28 | yes I need a formula to go through each row and if the 2nd column is yes then return the first column. I could then copy and paste this formula into a the next cell, add an argument saying the returned value cannot be the same as the previous ones, and so on. Essentially this is a series of nested IF statements. However, Excel prevents more than 7 statements from being nested, when I need all 28 to be searched. I cannot think of a way to get VLOOKUP, INDEX & MATCH, or defined names to make this work, because it needs to step through each row.
View Replies!
View Related
Search Column Delete Row If Value Found
I need a VBA code that will search column H for any value that DOES NOT start with "9" (this is a character field). If it finds a cell that doesn't start with "9", I would like it to delete the entire row. It will need to repeat this process for every cell in Column H that has a value and then stop.
View Replies!
View Related
Macro To Search A Column And Copy Row Data
I have a dataset that is large. I want to write a macro program that checks the number in cell A1, copies the number and then the rows contents to another worksheet/spreadsheet. Then jumps to cell A2 and if the number is different to that of A1 copy the data of that entire row over from that row to the next worksheet/spreadsheet. If the number is the same as A1 then jump over to A3 Then continue this process but rather than comparing it to A1, compare it to the new number that has been found in coloumn A. For example 7 A 7 A 6 AB 7 A So starting at the first line then jumping the next one then to 6 and finally 7.
View Replies!
View Related
Search Multiple Columns To Provide Cell Location
I have text (eg. 1-* or 10-* or 11-*) being entered in the following columns Q21:Q128 R21:R128 V21:V128 W21:W128 AA21:AA128 AB21:AB128 AF21:AF128 AG21:AG128 AK21:AK128 AL21:AL128 AP21:AP128 AQ21:AQ128 AU21:AU128 AV21:AV128 AZ21:AZ128 BA21:BA128 BE21:BE128 BF21:BF128 BJ21:BJ128 BK21:BK128 I would like Excel to search the above columns provide the cell location (eg. 1-10-*) is located in cell BF26
View Replies!
View Related
Return To Last Location
How do I write a VB Script to take the user back to where they came from. Example: User is on Sheet 1 and clicks a macro that takes him to Sheet 10. I need a macro that is on Sheet 10 that takes the user back to Sheet 1. BUT if the User is on Sheet 5 and clicks on a macro that take him to Sheet 10 I need that same macro on Sheet 10 to take him back to Sheet 5.
View Replies!
View Related
Lookup Value Then Return Value In Row 1 Of That Column
I have a list of values in column A (part numbers) In columns B to E I have lists of similar values (part numbers, with row 1 representing their location e.g warehouse, office, etc). In column F2 I would like to search for the value in A2 in the range B2:E100 and return the value in B1:E1 if the value appears... if it doesnt appear in any of the columns I would like 'not located' to appear in F2. I've been trying to do this for a while but am making no progress at all.
View Replies!
View Related
An Array And Return The Same Row In Another Column
I have an array with a lot of rows and columns filled with different codes. I need to look into this array for one specific number and return the value in the same row in an specific column. I can't use Vlookup in this case because it needs to have the values you are looking for in the first column and my values are spread in different columns.
View Replies!
View Related
Return Row# Of Max Value In A Column
I need (index? vlookup? if? match?). I did try this on my own and searched the board for help, but I couldn't seem to find something that applied. Sorry if this question is too simple or has been asked before. In Excel 2003 I want to return the row # of the maximum value in a columnar range.
View Replies!
View Related
Auto Return To Column A But Of Next Row
Is there an easy way to auto return to column A but of next row. I would like to be able to do this automatically say when column 87 is reached. I could just arrow down and hit home but there must be a way to automate this. I am currently in 2003 but will be moving to 2007 in a few months.
View Replies!
View Related
Save & Return To Last Location
User selects 'go to page B' on page A. They look at an object then click the object to go back to page A. Once back on page A they need to check data on page B, but in the same general location as where they just looked. This is over simplified, but pretty much sums up what I need to do. The code I have now is below, but keeps sending me to the last selection on my current page. Public Sub SaveLocation(ReturnToLoc As Boolean) Static WB As Workbook Static WS As Worksheet Static R As Range If ReturnToLoc = False Then Set WB = ActiveWorkbook Set WS = ActiveSheet Set R = Selection Else WB.Activate WS.Activate R.Select End If End Sub Public Sub SetSaveLoc() SaveLocation (False) End Sub Public Sub GetSaveLoc() SaveLocation (True) End Sub
View Replies!
View Related
Find & Return Value Same Row, Different Column
I would like to use a macro to find a number in a worksheet, other than the worksheet i'm using the macro in. The number - in this case a material code located in Column A - must be entered by the user in an inputbox, and then return (copy&paste?) the value of another cell (the price of the material - in Column D) in the same row. I recorded a macro envoking the find function, but then it only finds the number which I used during the recording. How do I change the code so that the user can input any number, or what code must I write?
View Replies!
View Related
Return Address,Value,Column/Row Of Selection
I am wanting to allow the user to select a cell in a worksheet and return the cell address and information/value from the cell. For example, if "Dog" is in cell A1 and "Cat" is cell A2 and the user initiates the Macro I would like the macro to to pop up a message box and say something like "Select a cell in Column A". When the user clicks on cell A1, I would like the message box to pop up and say, you selected "Dog" in cell A1.
View Replies!
View Related
Formula To Return The Value In A Column Based On Cell Entry In Row
Trying to write a formula that looks for the first number in the same row, then returns the value in that column in a different row. In the example included, cell A-7 should contain the formula. Within row 7, determine the column where the first number is listed (column D in this case), then return the value listed in row 3 of that column ('C' in this case). I've tried Lookup, Index, and Match functions, but can't seem to get the right combination.
View Replies!
View Related
Lookup Row & Column Heading To Return Corresponding Header
I have a list of names and the chores they need to do on a certain day. I need a formula that returns the chore the person needs to do when the date is filled in. Ex: Sheet2 Col:A has names filled in A2:11 , row B1:J1 has dates that you would input. formula goes in B2:J11 Sheet1 is the master with all the data the formula would be pulling from. column A2:A11 has the names , row B1:S1 has the chore that needs to be done. B2:S11 has the dates already filled in.
View Replies!
View Related
Find Value In Table & Return Column / Row Header
I am having trouble with getting a value (which is the row and column value) from a set of numbers. I have attached an example table to try and make things clearer. What I have is a table with a set of Row Headers, and Columns Headers all numeric. e.g Columns headed 1, 2, 3, 4, Rows 30, 40, 50 etc. I want to find the 1st occurrence of a value e.g. 1.0 in the table (by 1st Up mean closest to top left), and then give me the value of the Row and Column Header that corresponds to that. So for the example attached, I want to get the row Value 200 and Column 6 from finding the 1st occurrence of the number 1. I have tried various combinations of index and Match etc. But I can't get my head around the fact that the position of the 1st no. 1.0 can change, therefore I can't use any releative positions etc.
View Replies!
View Related
Find Data In Column Range & Return Cell Same Row But X Columns To Right
i have is 3 sheets in the same excel document. Sheet 1 is the mater sheet, which is a compilation of sheets 2 and 3, however the sheet layouts are different. The sheets consist of a list of names and details. Sheet 1 has all the names in the list, however sheet 2 and 3 only have partial lists that are in a different order from the original list. The details listed next to the names in sheets 2 and 3 are different and hence a straight forward row copy and paste will not work. What i need is to write a vba script that can take the name from the master list, search sheets 2 and 3 for the name and lookup the variables placed in next to the name, then update the master sheet accordingly, then continue to the next name on the master sheet and do the update again, and so on until all is updated. I have already thought about using lookup functions in excel however there is 1000 names on the list and around 60 details so it would be messy.
View Replies!
View Related
Text Search Returns Cell Text Contents Of Different Column In The Same Row
Search a worksheet for a user defined text string, and have excell return the contents of a predetermined column in the same row in which the text string was found. A prepopulated worksheet has the text "gold" entered in cell T278. 1. user searches for "yellow_metal" 2. Excell finds "yellow_metal" in row 278, say in cell A278. 3. Excell then goes to predetermined column (programed as part of macro or VB), say "T", and returns the text contents of the cell in that column, T278 in this example. 4. Excell returns "gold"
View Replies!
View Related
Insert Row At Variable Location
I had a quick question for something that should be simple but I can't figure it out. I have a column of sorted values, it goes A A A A A B B B B B and I want to insert a row to separate the A's from the B's. But there will be a random number of A's. I can figure out how to count where the A's end. But I can't figure out how to insert a row. Any suggestions? I tried the two below but they don't seem to work. The for section is counting the A's, and it works, it's what comes next that's giving me a headache!
View Replies!
View Related
Interpolation Given A Variable Table Location & Location Of Data Within
I am trying to develop a spreadsheet that will calculate a cost based on a matrix. I am attaching a sample of the calculation created so far. The end result is in cell M13 and is highlighted in yellow. I kind of layed the formula out in a few different cells, so hopefully it would be easy to follow. simplify this process with maybe another formula that I might not be aware of, or maybe show me how to get this done in VB code. I think VB code would be the correct way to go just not sure.
View Replies!
View Related
Insert Row At Specific Location And Auto Fill Formulas From Above
What I need is to insert a row at row 59 and autofill the formulas including drop down list from the above row. This is what I have so far (Thanks to Reafidy and shg). Sub ChkDates() Dim c As Range Dim DelRng As Range Dim ArcRng As Range Dim i As Long Dim l As Integer Application. ScreenUpdating = False Worksheets("Report").Activate For i = 60 To 8 Step -1 Set c = Cells(i, 33) If IsDate(c) Then ..............
View Replies!
View Related
|
TRACKER
