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:
View Complete Thread with Replies
Related Forum Messages:
Return Matches & Non-Matches From Delimited Cells Against List Range
In my spreadsheet, on the first worksheet called "Working". Column A, called "Results", contains carrot ^ delineated string values in each cell (i.e."john^apple^pear^banana^grape^love^heart^pickle"). The majority of string values in every cell in my "Results" column match a "source" column of Pick-List Values, called "Fruits" in the same spreadsheet, however found in a different worksheet called "Lists" (also in Column A). I want to perform 3 functions against my "Results" Column on worksheet "Working":
1.) Report In Column B: Analyze column "Results" by cell and return carrot delineated string values for only those that match my "Fruits" Pick-List
2.) Report In Column C: Analyze column "Results" by cell and return carrot delineated string values for only those do not match my "Fruits" Pick-List
3.) Report In Column D: Identify and return all unique values in "Column C" as a List.
Attached is a simple example of what I'm trying to accomplish called " Sorting.xls"
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?
Search A Range To Enter Text In Adjacent Cell
When i press say a command button " Add Repair Information" i want the User to Enter the serial number which i have given it a range called SerialN from the excel sheet and enter a repair action. Is there a function in Excel which will allow me to search the Serial Number and allow me to put the Repair action across in the cell on the same line?
Search To Find Matching Cells And Copy/transpose Adjacent Data To Original Sheet?
I'm trying to find a way to search a second sheet in a workbook for specific criteria outlined in a first sheet (in my attached example, from A3 downwards within the 'list of search criteria' sheet), and then to copy any secondary data found against a successful search match to the original sheet, transposed against its corresponding matched search term.
As you can see in the example, the search term 'bindi' (A4 in the 'list of search criteria' sheet) appears in the 'data' sheet 3 times - the secondary data for these occurences ('feathery', 'Fibonacci', 'glassy') is copied to the 'bindi' row on the first sheet and is offset with each copy to produce a transposed-esque effect of copy and paste.
If it's any help, there are a maximum of 9 matches for a single search term in the real document.
Thanks in advance for your help... I tried to adapt a previous solution given to me for a similar question but failed miserably. I bow humbly to your expertise!
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.
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
Select A Range Of Non-adjacent Cells
I have a series of data values in non-adjacent columns in an excel spreadsheet.
In the following example, assume the | (vertical bar) refers to the start of
a new cell:
Using the SUMIF formula, I want to add all numbers which are preceded by a
cell containing the letter F.
What I need to do is specify a range of every second cell in the row (starting with Cell A1) for validating they equal F, and a range of the alternating cells (starting in column B1) for the range containing the data to add. How can I specify these ranges (I can't name each cell individually as I have more than 30 cells to add up in my real life situation and the IF function allows selection of no more than 30 values)?
A Formula That Searches A Range And Gives The Sums Of The Adjacent Cells!??
I am looking for a formula that searches a range of data and then returns the sum of the numeric values in the adjacent cells. I have a calendar style spreadsheet with 2 columns for each day. In the first column it has a list of names and the second column a number.
I would like a cell to return the sum of the numeric values next to each name. All the names are entered into the columns randomly and are unorganised. The same name will appear multiple times in the range and have different numbers next to it each time. I am currently using (for example) =COUNTIF(H6:AL14,"bob") to tally up the number of "bobs" but really what i want to do is find a formula that in plain english does:
Search a range of columns and where cell = "bob" take the value of the adjacent cell (to the right) and total them up!
i have attached an image - i hope it makes sense what i'm trying to do - i would like Q3 to total up the numbers to the right of all "bobs" across all the days - so it should return 42
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.
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
Search A String For A List Of Matches
I am looking for a way to check for the presence of a member of a list in a field. Not for an exact match, but for the text to be somewhere present. I know I can use the "search" function to find text within text, but I don't see how to find the presence of any member of a range of values (a list) within the text.
Search In Two Sheets, Then Copy Matches To 3rd Sheet
I have a Excel file with alot of data in it. I need a macro that will create a report for me and relive me from alot of manual work. The 1st sheet is named "Projects". This contains the search arguments. The 2nd sheet is named "Database". This is the sheet where I want to search in. The 3rd sheet is named "Report". This will contain the results of the search. So the going will be something like this:
Copy row 7 from "Projects" into row 7 in "Report". Then use the value in that row, column E (named Search code). Search for rows that has this value in column E in the "Database" sheet. Copy all those rows to "Report" sheet. Copy row 8 from "Projects", leave two rows of space and paste into "Report". Repeat the procedure mentioned above. Repeat this until reaching a row in "Projects" that has no value in Column A.
Search A Range Of Cells For A Range Of Values
I'm trying to do is search a range of cells for a range of values AND then see if it matches one other value in another range of cells. In this case,
T3:T49 can equal P6 or P5 or P4 or P3 but the cells can only be counted if U3:U49 is 'w' as well
How To Return Exact Matches Only Using VLookup
I have the following formula which, if text is in A1, will lookup from a list of worker's comp codes to locate the code in A2 and return the wording for that
However, if there is not an exact match to the code in A2, it returns the wording for the next closest code.
Is there a way to make it so that it will only return the wording for the exact match and return "invalid code" if the number in A2 is not found on the list on the "Codes" sheet?
Return A List Of Unique Matches
I need to return all the unique matches from a set of data.
For example, when a product is selected, I need to list all the possible venues it is sold from. I will use this venue list to populate my drop-down listbox, as opposed to having a 100 venues in the list, I just wish to have the applicable ones.
Example of data set I need to lookup in:
prod 1 venue1
prod 2 venue 2
prod 1 venue 3
prod 1 venue 10
prod 2 venue 6
I would like to use a formula if I can (not a macro), to list that prod 1 is only sold in venue 1,3, 10.
It would be great if one could use a vlookup type of formula and it could return a list of each occurence of the look-up'ed cell.....
Identify Matches And Return Values
1) Check to see if a value in column A of worksheet A appears in column A on worksheet B.
2) If it DOES, then display the value from the same row from column B on worksheet B, in column B on worksheet A.
3) If it DOESN'T, display the value "EACH" in column B on worksheet A.
Compare 2 Columns And Return Matches
I have two columns, one with the target text (D:D) and one with text I want to compare it too (A:A). If the text in column (D:D) matches the one in (A:A), I want to return the value in (B:B). I attached a sample of the problem i'm dealing with.
Vlookup: Search A Certain Range Of Cells
1. can i limit vlookup on sheet2 to search only a certain range of cells on a sheet1.
example, I want vlookup to look in sheet1, colum A - Y but only look between rows 1 and 100
2. If 1 can be done, and i happen to add a row in worksheet1 (making it 101 rows vice 100) will the vlookup code on sheet2 include that 101st row or do i have to redo the range manualy.
Two Criteria - Multipule Matches That Are The Same - 1 Answer To Be Return
I've been looking through past threads and I can't find what I need to do anywhere. There was one formula that came close: =SUMPRODUCT((A2:A100="Aimbry")*(B2:B100="LW")*(C2: C100)). but this adds up all the matching results.
What I need to do is match the info in columns A and B and return a number in column C. The problem is that there are multiple rows with identical entries and I just want to return one of them. So I was thinking a version of the above that produced a mode or average would give the desired result.
Return Row Number That Matches 2 Criteria
I have a produced an Excel workbook which uses a VBA sign in/out userform.
Once you sign in on the Userform the sheets update. A list is completed of the times people enter and leave.
To make the code easier I currently have the name being returned to the excel sheet and performing a ďmatchĒ function to return the row number. This row number is then used to carry out what I need to happen in this row. However, as you can see from attached doc (and the brief example below), based on IDnumber "2", the match function returns row 5 not row 8.
I need to have the row number returned for the IDnumber where the Out cell is blank.
This should be the last occurrance of the IDnumber
1 ID# In Out
2 1 9:00 13:00
3 4 9:00 13:00
4 3 9:00 13:00
5 2 9:00 13:00
6 5 9:00 13:00
7 6 9:00 13:00
8 2 14:00
Search For Specific Characters In A Range Of Cells
I have a column of values of peoples initials and I want to cound how many times one persons initials occur in those cells. I can do this if the cells just contain one person i.e. "CH" or "DH" but if the cell has two or more peoples initials i.e. "CD DH" I can't do it as it only matches the exact search criteria.
How can I total the numebr of times a required set of initials appears in a specific column of cells? Not bothered is it requires a macro or a formulae, just can't seem to figure this one out.
Search Range Of Cells For Alphanumeric String
I tried to find out how to post my table but the link in that thread lead me to a notice saying I wasn't allowed access to the page.)
Cells A2:G2 contain various alphanumeric strings.
If any cell contains "SD#" (# = a number which might include decimal places) then I want to extract the number.
If more than one cell contains "SD#", I want to add the numbers together. The total goes in cell C6 and it needs to change if the contents of A2:G2 change. E.g. if A2 shows SD4.75 and B2 shows SD8, the total in C6 would be 12.75
So I'm looking for a formula for C6....
How To Search For Duplicates In A String Across A Range Of Cells
if I have a column of 5 cells (E1-E5) that contain a single character, how would I search a range of columns and rows (A1/5 - D1/5) with these?
So if E1-E5 each contain "1", i would like to return true when one (or more) of the A - D columns also contain "1" in each of its cells.
Columns A - D will contain a string of more than one character so it needs to search the string.
E1 = 1
E2 = 1
E3 = 1
E4 = 1
E5 = 1 and
A1 = 123
A2 = 134
A3 = 142
A4 = 412
A5 = 213
I would like this to return true because the A cells do contain the values in the E cells.
I guess this is like looking for duplicates but with a search string twist!
Lookup Function Required To Return Multiple Matches
I have a list with 8 columns. In the majority of instances, Columns A & B will have duplicate information. I need to be able to search columns A & B & return the information from them PLUS all info contained on the same row's from columns C/D/E/F/G & H. EG:...
My column headings are as follows...
Type Region Contact Phone Email ADD1 ADD2 ADD3
In column A (type) would be the type of contact eg:business/personal/family etc.
So... If looking for family I'd need excel to show me the region, contact, phone, email, add1, add2 & add3 for each family record. Hope this makes sense!
There are over 1000 records i need excel to search through & return matches.
I'm an intermediate excel user at best so finding it complicated. Tried vlookup but that stops when it finds a match & anything else seems over my head.
Compare Column In Different Workbooks & Return Corresponding Value Of Matches
I have two separate workbooks, each with a great deal of information, such as name, ssn, dates, statuses, etc. I would like to compare the SSN column for both workbooks, and when it finds a match (which there would be many) to copy the MOS cell from the same row to the other workbook. Here is my breakdown with names:
Workbook 1: Macros.xls
Workbook 2: MASS 162.xls
The SSN column is column C in both workbooks
When there is a match found between the SSN column in Macros.xls and the SSN column in MASS 162.xls, I need to copy the MOS cell (column E) from the MACROS.xls workbook to the MASS 162.xls workbook into the same row, but in column M, which is currently empty.
Enter A Value In A Textbox, Search For It In An Worksheet, And Populate Other Textboxes With Adjacent Values If The Value Is Found
I want to enter a value in a textbox, search for it in an worksheet, and populate other textboxes with adjacent values if the value is found. Anyway, the problem is that if the value is not found, I get a debug error.
test1 = TextBox1.Value
Find_Range(test1, Cells, xlFormulas, xlWhole).Select
TextBox2 = ActiveCell.Value
TextBox3 = ActiveCell.Offset(0, 1).Value
I'm sure there's far better code to do what I need, but I tried to keep it simple. With the above code, it only works if the value is found. If it's not, I get an error. So how do I make it so that if the value is not found, the value of the textbox2 is "Not Found" or something...
How To Return Value Adjacent To Cell From Another Sheet?
I have one column of about 10 names (each of which is unique). The cell adjacent to these names is empty and I want to insert a value into this cell.
The value I'd like to insert into this cell is located in another sheet attached to the same file. This sheet has the same layout, except the unique name has the value next to it and the order of the names is different.
I'd like the formula to determine where the name from the first sheet matches the name from the second sheet. I'd like it to then display the data associated with that unique name in the first sheet, adjacent to the name in the blank cell.
Lookup & Return Adjacent Value From Left
I have two worksheets. The first one contains 500 rows:
Column A - Company Name
Column J - Formula that calculates a 'company score' based on other data in columns B-H
Column K - Number of directors
The second worksheet contains over 3000 rows:
Column A - Name
Column B - Company
Column C - Designation
I want to caculate a score for each person (from worksheet 2) that does the following:
Personal Score = ('Company Score' / No. of directors) * Designation Weighting
Where Designation Weighting (numerical value) is determined by Designation (text). There are 10 different and mutually exclusive types of text in the Designation column.
My problem is compounded by the fact that each person (in worksheet 2) can be directors on several companies. The Personal Score that I want to calculate needs to recognise which different companies they are directors of and sum the scores derived from each company.
Lookup & Return Adjacent Result
I have a list of people with SSN, about 3000. This list contains business done by each individual, which can be in all 50 states. I have another list of the same individuals with just their name and addresses and what we call "client number". I need to put the client number with the list for the individual states. See example list:
List of individuals with client numbers
123 John Doe
456 Jane Doe
List of individuals with state business
John Doe AR
John Doe CO
John Doe FL
John Doe MS
John Doe TX
Jane Doe MS
Jane Doe TX
Jane Doe AZ
I need for those client numbers to appear in the column before the names on list with states.
123 John Doe AR
123 John Doe CO
123 John Doe FL
456 Jane Doe MS
456 Jane Doe TX
Lookup Left Most Column & Return Adjacent Value
Basically what I am trying to do is use a Lookup formula on a sales invoice where the product name and product price will be automatically entered into the sales form when the product ID is entered. What would the formula be?
RegionProduct IDProduct NameProduct Price
Product IDHPPHomeware Pizza Pan19.95
Product NameSPPStone Pizza Pan 29.95
Product PriceHCDHomeware Casserole Dish19.95
Units SoldHCPHomeware Cookie Pan 9.95
Total SaleCWChina Wok 19.95
HEWHomeware Electric Wok29.95
WCMWilson Coffee Machine29.95
HBMHomeware Bread Machine49.95
HBMDHomeware Bread Machine -Deluxe89.95
OGGOlson Gas Grill 159.95
OEGOlson Electric Grill 159.95
Return Largest Number Within A Range Of Cells
I'm looking for a formula to enter within a particular cell which will return the largest number that appears within a range of cells, for example the range H:133 through L:136. If it's any easier, only one number will appear within the range, though it could appear in any cell within that range.
Lookup Cell And Return A Range Of Cells
I have data inputted into cells a-o in rows 1-250. I want to search, lets say row 1, (I do want to search all 250 rows if that info is relevant!) and if cell m is blank then I want to return all the info from row 1 into row 260. If there IS data in cell m then I want to return nothing. Bottom line I want to return all the data in all the rows that contains nothing in cell m.
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.
Find Date In Column & Return Adjacent Cell Value
I have a spreadsheet with a column A of dates April 08, May 08, June 08 etc. Adjacent to each of these dates is a value in column B. I want to select the appropriate value relevent to the current date and use it elsewhere. Therefore if it happens to be Oct 08 when I open the spreadsheet I want the value in column B, adjacent to Oct 08 to be represented.
Lookup Nth Occurrence Of Date & Return Adjacent Cell
I have a list of dates in column AF. I would like to retrieve a value from the 2nd occurrence of a date, so I used the function for the Nth Occurrence on this website as follows:
Function Nth_Occurrence(range_look As Range, find_it As String, _
occurrence As Long, offset_row As Long, offset_col As Long)
Dim lCount As Long
Dim rFound As Range
Set rFound = range_look.Cells(1, 1)
For lCount = 1 To occurrence
Set rFound = range_look. Find(find_it, rFound, xlValues, xlWhole)
Nth_Occurrence = rFound.Offset(offset_row, offset_col)
The excel formula I am using is as follows: =Nth_Occurrence(AF2:AF622,AE2,2,0,1)
Where AE2 is the date I am looking up. My question is this: When I put in a date ("1/12/2007") instead of AE2, i get the correct value. I tried giving AE2 and the AF column the same date formatting (3/14/2001), but this doesn't work. The date I am searching for also changes, so I cannot just type the date into the excel formula.
Pivot Table Chaos: Blank Cells Return 0, Empty Cells Return Nothing
Hi, I desperately need help with a pivot-table and can't find anyone else who's ever had this problem...I have a very large healthcare data table which for simplicity I will describe as follows. In essence the first two columns respectively are (A) location and (B) month. Let's say the third column (C) is the number of cases of the disease kidpox treated in each health centre during that month. The fourth column (D) is the total number of cases of everything treated during that month in each clinic. The fifth column (E) is the proportion of cases of this disease over all consultations for each health centre and month, and the formula in E2 is .....
Find First Blank Cell In Column & Return Adjacent Date Less Than Or Equal To Today
how to make the data look like a table with three columns. Other than the date, it is space delimited. I have a tracking spreadsheet where Column A is populated with dates for the year. Column C contains daily values.
I don't always start entering daily values on the first day of the year, e.g., this year the first value in Column C corresponds to March 9. All values in Column C are contiguous - there are no blank cells until the value in Column A is greater than today's date code. I would like to use a formula (rather than VBA) to look down Column C and find the first non-blank entry where the value in Column A is less than or equal to today(). In this case, the formula should return the value for March 9, 2008.
CREATE TABLES LIKE BELOW?Column A Column B Column C
March 1, 2008Saturday
March 2, 2008Sunday
March 3, 2008Monday
March 4, 2008Tuesday
March 5, 2008Wednesday ...................
Color Cells Based On Adjacent Cells Being Empty & Add Text
Iím trying to make my life a bit easier, by adding a few macros and formulas to the spreadsheet (Everything was done completely manually before I got here!!!).
What I would like to do is take two columns, which contain a start and end time for work shifts, and colour them GREEN once I have entered a name in the Worker column (Along side the two with the time), and also to fill a cell with a Yes or a No. Iím aware of auto conditioning, and Iíve tried to have a play to get this to work, but I just canít work it out.
I have posted a link to an image which shows what I want. I hope I've explained it well enough!
Find All Matches In Column Range
The objective of the code is to determine if a value in Column A exists:
- If it does, then copy data from a different worksheet into the row of the worksheet where the value resides
- if it doesnt, then go to the last used row in Column A and add then copy data from a different worksheet into the new row
The portion of the code im having trouble with involves the Finding of the value in Column A, and also the error handling when it doesnt find it.
The code below doesn't handle the error correctly.. for the data set im running the code on, it should find a value in Column A (which it does) but then it also runs the ErrHandler portion..which shouldnt happen.. it should only run the ErrHandler portion if it cant find the value in Column A (which therefore means a new record is required)
Dim fitem As String
Dim nmax As Integer
Dim nRow As Integer
Dim rFoundCell As Range
nRow = 10
nmax = Sheets("Availability").Range("schedulechange").Rows.count
Do While nRow <= nmax ....................
Using The =IF(ISNUMBER(SEARCH Function To Search Multiple Cells)
I' having trouble using the =IF(ISNUMBER(SEARCH formula to search multiple cells.
I can get it to work to search one cell (as below):
=IF(ISNUMBER(SEARCH("Same",G10)),"No Change",'Aug 09 Matrix'!F10)
(In this case the respone is No Change as Cell G10 contains "Same")
However cannot get it to work for several cells. I have pasted below and highlighted the function I would like it to perform:
=IF(ISNUMBER(SEARCH("Same",G10:R10)),"No Change",'Aug 09 Matrix'!F10)
Essentially the other cells in that row (G10:R10) all contain "Same", however for some reason it is identifying this as FALSE and putting in the data from the 'Aug 09 Matrix' sheet.