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 Complete Thread with Replies
Sponsored Links:
Related Forum Messages:
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
Search And Return Values In A Range Group
I have a workbook that has rows of data starting at A5:G5 seperated in groups of 25 rows. Example: A5 is numbered 1 and A6 is 2 A7 is 3 and so on to 25 then skip a row and start numbering again to 25. What this is for is employees are placed in groups of 25 to be eligible for an award for safety. What I want to do is Range G5:G650 is to be a place where an "X" is placed beside an employee who has had an accident then Range H5:H650 would return "Employee had an accident" I already have this part working. Then I want to code a subroutine that would search for the "X's" and return the entire row data for each name in that group of 25 on a seperate worksheet. This would then be printed so we would know which group's to exclude. I know how to make it return the row of data containing the "X" but how can I make it grab the entire group? There will be multiple groups of 25 each and each time an "X" is found within a group create a new worksheet for that group. 650 employees / 25 = 26 groups
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
How To Use VBA To Search Strings And Return Values Or Cells Nearby
Im needing to search through the attached document which is truck logs at a mine and use vba to search through the list for each of the different shovels eg SHVL1, SHVL2 & SHVL3, and when the code finds that string, to display the tonnage 2 colums back from it. Im about half way there a i think but am having trouble with strings, if it was numbers i could do it no problem
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
Search Range For Current Month & Year Return Corresponding Values
I have a workbook with two spreadsheets in it. On the second sheet there is a large table, which column headings are months and years (e.g. Jun-07 Jul-07, Aug-07, Sep-07....). I was trying to write an excel vba code that would search the first row (column headings) to find the current month and year, and copy the corresponding column along with two previous columns (months) to the first sheet. I would like to have a code that will be able to do this in Jan-08, Feb-08, or Jan-09 as well.
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
Search Values In A Column And List Them In A ListBox
I'm afraid I've run into some trouble with the Listbox function, which I really love, but I can't quite get past this hurdle. I want to have a listbox which populates by checking an entire column for values, ignoring blank cells, and, if value is present, to also check to see if any value is present in the cell one column to the right and one row down from the cell that has the initial value. If both these conditions are true, to then display both values in the Listbox. In other words, the Listbox would contain two columns. During this routine, when checking value is present in the second cell, I'm guessing that an IF statement would increment a variable signifying the cell reference by + 1 for column and + 1 for the one row down?
View Replies!
View Related
Search 1 Column All Worksheets & Go To Found Values
I need a VBA script that can display a search box on multiple sheets within the same workbook similar to using ctrl + F and search values in column B only. If there is text or the row happens to be empty then it should skip that and only search numbers. Also the numbers in column B range from 50000 to 89000 and if there is a wrong number entered then I want to have a pop up box saying Error: invalid value or something like that. Sub search_box() Range("B49000").Select Cells.Find(What:="some#", After:=ActiveCell, LookIn:=xlValues, LookAt:= _ xlWhole, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:= _ False, SearchFormat:=False).Activate Cells.FindNext(After:=ActiveCell).Activate End Sub
View Replies!
View Related
Search Criteria Bring Values Back To A Results Column
i have the following macro which finds instances of a search criteria and brings them back to a results column. what i need is instead of having the word GD: appear before each result i need it once at the begining of the results LastRow = Sheets("design owb").Cells(Rows.Count, 10).End(xlUp).Row Application.ScreenUpdating = False For i = 26 To LastRow Target = Sheets("design owb").Cells(i, 10) With Sheets("design") For J = 2 To 54 For K = 4 To 11 If Target = .Cells(J, K) Then Sheets("design owb").Cells(i, 11) = Sheets("design owb").Cells(i, 11) + "GD: " & .Cells(J, 15) + " | " End If Next K Next J End With Next i Application.ScreenUpdating = True
View Replies!
View Related
Macro To Search Text & Return Values
I have a spreadsheet containing several names. I want a formula or macro that will search a string of text within the cell. If the text exists within the cell, I need a code to populate. For example, In cell E2, search for a phrase anywhere in the cell that contains "COM". In cell H2 look for the exact phrase "ASB". When "COM" is found in E2 and "ASB" is found in H2, in cell U2 return the value "COM ASBTVL". In my case, E2 may not contain "COM", it may contain "DEN" instead. In that case, if E2 contains "COM", and if "H2" contains "SVC", then in U2 populate "COM APPSVC" I'm guessing in my code I will need to list a set of criteria that will search row by row and return certain values when finding the text I specify. Would a macro be best for this? Does anyone know how I would put that together?
View Replies!
View Related
Populate And Return Values Up To Column
I have my lookup value in column A. The vlookup result in Column B. =VLOOKUP(A1,ALL!B2:Y10482,2,FALSE) I now want to drag this across to populate and return values up to column Y. When I drag the formula or copy it to Column C it does this: =VLOOKUP(B1,ALL!C2:Z10482,2,FALSE) The lookup value, table array, and column index number have now changed to column B, C:Z, and 2, when I need it to increment like this: =VLOOKUP(A1,ALL!B2:Y10482,3,FALSE) The only thing I want to change as I paste it into the adjacent field is the col_index_number. Is there a way to do this horizontally without manually changing the formula 26 times?
View Replies!
View Related
Way To Return Column String Values
I have made this function that returns the string value for a column based on it's number but it starts getting more complicated after I've been thru the alphabet once and have to return two string values to move across the chart. PHP Function colnum_string(ByVal num As Integer) As String colnum_string = "IV" If num = 1 Then colnum_string = "A" End If If num = 2 Then colnum_string = "B" End If If num = 3 Then colnum_string = "C" End If If num = 4 Then colnum_string = "D" End If If num = 5 Then colnum_string = "E" End If If num = 6 Then colnum_string = "F" End If If num = 7 Then colnum_string = "G" End If If num = 8 Then colnum_string = "H" End If If num = 9 Then colnum_string = "I" End If If num = 10 Then colnum_string = "J" End If If num = 11 Then colnum_string = "K" End If If num = 12 Then colnum_string = "L" End If If num = 13 Then colnum_string = "M" End If If num = 14 Then colnum_string = "N" End If If num = 15 Then colnum_string = "O" End If If num = 16 Then colnum_string = "P" End If If num = 17 Then colnum_string = "Q" End If If num = 18 Then colnum_string = "R" End If If num = 19 Then colnum_string = "S" End If If num = 20 Then colnum_string = "T" End If If num = 21 Then colnum_string = "U" End If If num = 22 Then colnum_string = "V" End If If num = 23 Then colnum_string = "W" End If If num = 24 Then colnum_string = "X" End If If num = 25 Then colnum_string = "Y" End If If num = 26 Then colnum_string = "Z" End IfEnd Function Is there a better, more simple way to return the string value for a column from it's number reference?
View Replies!
View Related
Find Records And Return Values To New Column
I have one Column (Col A) which has a list of values. In Col B I would like to filter out the values in Col A based on some criteria. For example, below I would like Col B to show all of the records in Col A where the LEFT six characters = "Active". Col A: Inactive_Jim Active_John Active_Frank Inactive_Betty Inactive_Lucy Active_Tina Inactive_Jill Active_Tom Col B would result in: Col B: Active_John Active_Frank Active_Tina Active_Tom I'm sure this is elementary, but I can't seem to figure it out.
View Replies!
View Related
Find Values In 2 Columns And Return A Value In A 3rd Column
I have 2 spreadsheets, I need to collect the data onto 'figures 07' from column D on 'spreadsheet 07' by date, based on the name matching. ie cell B4 on 'figures 07' should be 166 and cell C4 should be 72. I've tried IF and LOOKUP but they don't give me the correct answer Auto Merged Post;Here is the second spreadsheet, it wouldn't let me attach both of them on my first message
View Replies!
View Related
Lookup Column Values In Another & Return Value If Match Found
I need to create a macro that will loop through a list of names on Sheet 1 / Column A, and if that name is listed on Sheet 2 / Column A, then it will set Sheet 1 Column B (for that one name) to "1". The problem I've run in to is that both the lists change in length each time I have to run this (so no set range). I've been able to get this to work with two ( nested) loops, where the first loop goes through the Sheet 1 list and the nested Loop looks for the name on Sheet2, but it is extremely slow. Is there any simple VBA code or formula that I'm missing that could do this? I've tried to do something with the " Find" VBA command, but I can't see how it would apply to this if the list changes in size.
View Replies!
View Related
Lookup & Return Values Of Adjacent Column To Textbox
I have an excel sheet with cols of information e.g IDNo, FirstName,LastName etc. I have created a form in VB where you enter the IDNo (& Click on a command button called View) It should populate designated textboxes on the Form. Thus it should check the col where the IDNo is stored, find the IDNo and populate the adjacent cols i.e FirstName, LastName in designated textboxes on the form.
View Replies!
View Related
Lookup Column Values On Another Sheet & Return Adjacent Cells
Essentially, sheet b is a list of 900 people I need to mail to. Sheet a has 3000 rows of people, many of whom don't need the mailing. It has mailing addresses that we need in sheet b. We have two worksheets, sheet a and sheet b compare sheet b, column c (email addresses) to sheet a, column x, (email addresses). If they match, move sheet a, columns d, e, f, g, and h to sheet b.
View Replies!
View Related
Return Range Of Numerical Values In Single Column Based On Frequency Percentage
I have a single column of numerical values that may repeat many times within the column. I require a flexible Formula: Use an Input Cell for the specified and changeable Percentage(s) %. Column may be filtered – so only take into account Visible Filtered Cells. The Formula will calculate and Return the numerical range of values that fall between the specified percentage % (using the Input Cell) e.g.; 70%. The Formula should Return two numeric values: a Start Value and an End Value – NOT necessarily the minimum and maximum per se BUT the MIN and MAX of the values that appear 70% of the time in the column; therefore, taking into account Repeat / Duplicate values. The calculated Results: the two numeric values will be returned to separate cells on a new Sheet.
View Replies!
View Related
Function To Sort A Range By The Values In A Specific Column And Return The Range
I was just recently forced to create my first UDF and after how well it worked I now am very interested in learning more. I am trying to create a function to sort a range by the values in a specific column and return the range. I know this should be really simple but for some reason my code dies whenever it gets to my inner-most loop. I need to use this in a larger function but for now this is my only question. I did find that Excel 2007 has built in Functions for this but my company still uses 2003. My Public Function SortRange(rngToSort As Range, valCol As Integer) Dim Swapper As Variant Dim i As Integer, _ j As Integer, _ k As Integer For i = 1 To rngToSort.Rows.Count For j = 1 To rngToSort.Rows.Count - i If rngToSort(j + 1, valCol) < rngToSort(j, valCol) Then For k = 1 To rngToSort.Columns.Count Swapper = rngToSort(j, k) rngToSort(j, k) = rngToSort(j + 1, k) rngToSort(j + 1, k) = Swapper Next k End If Next j Next i SortRange = rngToSort End Function
View Replies!
View Related
Search And Return A Value
In the attached file, I am trying to lookup what vendor is assigned to a part, and enter the vendor number in column J, K or L, depending on the number that is assigned. For example, the top part number, RAD001, has a 2 in column N, indicating vendor # 015354 is the Alternate vendor # 1. It has a 1 in column V, indicating 034180 is the Primary Vendor, and it has a 3 in column Z, to indicate that 015229 is the Alternate Vendor # 2. What formula would I need to put in cell J2 so that if a 1 is found in row 2 it will return the value 1 position left of where the 1 is found? I will then put a similar formula in columns K and L to return the vendor number if a 2 or a 3 is found. The end result will be that for each part number you will be able to see what vendor is primary, alternat 1 and alternate 2. I want to make this a formula because the actual sheet will be thousands of part numbers long, and I will need it to update the vendor information if the primary vendor is changed.
View Replies!
View Related
Search Value And Return An Associated Category Name
I'm attaching a spreadsheet in order for you to understand my question, and I will explain it based on that, ok? I apologize in advance for english mistakes, since it's not my 1st language. I want to search the salary value in column B and, in Column C, I need the job title to be posted based on what is written in Column J. The table h2:i8 contains the maximum salary a person on that position can earn. So when you look the salary on column B, it should seach in column H and compare to the maximum value, and return the description on Column I that corresponds to that salary range. So if somebody earns 6500, it should be qualified as a supervisor, because its salary maximum is 7100. I have searched the forum here and found similar problems, with suggestions to use vlooup and Index. But I just can not figure it out.
View Replies!
View Related
Search And Return Address For All Matches
I'm looking for a Formula in order to retrieve all addresses of all matches equal to the search criteria located in cell: E2 The results are hand-typed in col. G This is a case of a TWO-DIMENSIONAL "Arbitrary Lookup" Arbitrary. The range A1:C10 was named: TBL and the formula should refer to TBL as it can be dynamic in size. PS: can anybody tell me why I don't get any E-Mails when an answer is posted?
View Replies!
View Related
Search Engine Will Return All The Information
i want is to create a search engine in excel that allows users to input the clients name and the search will return all the info on the client such as the issue and their policy number. Is this even possible? Using Access is not an option. I know I can use Access but most of my users dont know how to use Access and I want to make things as simple as possible. Thanks so much in advance who ever succeeds at provding an answer u're a genius cuz I have no clue where to start lol.
View Replies!
View Related
Search Tabs And Return Results
I would like to summarize all "yes" respose contained in 15 tabs. See the attached. I would like results to be returned to the "summary tab" when user answer "yes" in the signficant column in the "process activity 1 through 15" tabs. If the answer is yes, I would like to pull the process name, aspect and impact detail.
View Replies!
View Related
Search In TextBox, Return In Listbox
I'm using a Textbox macro to search my database for a specific date, and return the company name of all entrys for that date, into a ListBox. Now this is the only way I'm prepared to look at doing, and I have managed to do it...... partially - as stupid as it sounds, I cant get multiple results to list in the ListBox itself, and for the life in me I cant find out how to do it. Also, once the options have been brought back into the ListBox, I then need code which will then populate further locked TextBox's which the rest of the company info, when selected from the ListBox. I know its asking alot (or maybe not) but I believe, (unless ive done it a really awkward way, I'm not too far away, I just dont know the code to enter, to be able to do it. Private Sub CommandButton1_Click() Dim Nullstring Application.ScreenUpdating = False If TextBox1.Value = "" Or Nullstring Then MsgBox "Please enter a date to search for" GoTo error1: End If.....................................
View Replies!
View Related
Search A Table, Return An Array
0000AAAAABBBBBCCCCCDDDDD 1111SugarRaisinDirt 2222MilkWaterWaterFlour 3333FlourSugarDirt 4444EggsWaterFlourSugar 5555WaterMilkDirt I looked for a way to represent my data in a cleaner way than this but couldn't find any tools....a point in the right direction would be helpful. I guess the old plug-in that converted Excel->HTML isn't available? Column A is a finished product and B-D are the ingredients to make it however values in Column A are also ingredients (sometimes multiple times). I want a formula that searches for values in Column A within the table B1:D5 and returns an array, if possible, of the finished products where Column A was used. For example, searching for Sugar would return "Flour, Water". Searching for Water would return "Milk, Eggs" (I'd rather not have Milk listed twice but beggars can't be choosers).
View Replies!
View Related
Return Cell Containing Search Criteria
If I have a 'key' value in a cell in one sheet, i want to use that value to find the cell in another sheet containing the 'key' and return the row number of the cell, if more than one value then I would like to be able to loop through all the rows containing that 'key' value returning the row number of each hit, kind of a programmatic version of vlookup?
View Replies!
View Related
Find To Search And Return Multiple Dates
I am working with a macro into which you input a date, the macro searches for that date (in column D of the data sheet), then copies any row with that date onto a new sheet. This works great, but now I need to be able to search for more than one date at a time and return any rows that cantain ANY of those dates. For example, I would want to search for any row containing 01/01/07 - 01/07/07 or any row containing 01/01/07, 01/02/07, 01/03/07...01/07/07.
View Replies!
View Related
Search For First Instance Of Text & Return The Value
I have a list of product numbers in col. A. In col. O I have a list of file names that contain the product numbers as well as additional characters. I need a formula that will search col O for the first instance of the text in cell A2 and return that value. The next formula will return all instances that contain the text found in A2.
View Replies!
View Related
Search Multiple Files & Return Corresponding Value
I have a bunch of quotes in Excel format which have 5 columns ; Part number, Description, Qty, Cost, Total Cost, Unit List,Total List. I need a piece of code to open many excel files, look for a "part number" beginning with ED,DS,AP,MP or MDS and return the corresponding value in the "total cost" column then record the value in a separate spreadsheet.
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 For Text Within A String, Lookup And Then Return Item
I have a spreadsheet! I have two sheets, one of which contains film names and the other contains our tag structure for our website (which is a list of tags, their keywords and the primary parent channel they live under). What I am trying to do is search the film titles and if any of the words match either the tag name or keywords then return the relevant channel. Example: Sheet 1 - Films How To Apply Bridal Makeup How To Fight A Donkey How To Write Excel Formulae Sheet 2 - Tags Channel / Tag / Keywords Tech - Microsoft - windows vista xp word excel Tech - Computers - internet pc Tech - MP3 Players - iPod Zune So, for film 3 on Sheet 1 it would recognise the word Excel in the keyword list and return the channel Tech.
View Replies!
View Related
Search Table & Return Heading Of Found Value
In a large spreadsheet that receives external data, I have codes (U, N or V) allocated at various times to different rows as shown in sample worksheet attached. Elsewhere in the spreadsheet, I need to display (for later export) these codes along with their respective number in a sorted list. Please review the attached: In the spreadsheet you'll see a column of codes, the next column is the data reference number, then to the right is three columns, one for each code. As displayed in the sample book attached, each Code column is to display the data reference number (from column 2)that matches that column's code. What formula can I use to list these numbers in the appropriate columns?
View Replies!
View Related
Search Range, Return Adjacent Cells Of Matches
I can solve my problem with a pivot table, and with VBA easily...however, I'm interested in knowing if this can be done with formulas (array formulas using index/match I'm assuming). Goal: On sheet1 I have one column with products, then the column next to it will have an "Y" in it if the product is to be selected (blank if not). On sheet2 I want to create a list of the products that were selected (having the "Y"). The only thing stumping me is that I do not want spaces between the product list on sheet2...just a nice continuous list. Example: Sheet1 cup Y bowl Y spoon fork knife Y Sheet2 cup bowl knife ..not.. cup bowl knife
View Replies!
View Related
Look Across Values In 12 Columns, Return The Number Of Unique Values
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 Replies!
View Related
|
TRACKER
