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.
I have two excel sheets. The first sheet has a column with codes and another column with dates. There are more than one date for the same code. The second sheet has just one unique code. I would like to match the code on the second sheet with the code on the first sheet and return ALL the dates associated with that code to the second sheet - horizontally.
View 5 Replies View RelatedI 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 3 Replies View RelatedBasically 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?
Product Information
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
HRHomeware Rotisserie119.95
OGGOlson Gas Grill 159.95
OEGOlson Electric Grill 159.95
I'm trying to create a macro that can lookup values down a list, find that value within another list and copy the adjacent cells.
The values to look for will always be on the same column and the values to look into will always be in the same range or columns but not always in the same row.
For example.
AI:AI contains a list of ID's which will be manually input every day so they might have a different order.
C:AF has all the data to look into. C always being a list of ID values and I want for every ID found on AI:AI to be search for on C:C: and then once a match is found, I need specific adjancent cell values to be copied next to the ID found on AI.
The values that need to be returned are found on columns B,D,F,G,H,I,J,P,AF and they will always display on those columns.
I've been doing this based on vlookup and INDEX/MATCh formulas but I've come across some limitations for something I want to develop further so I'm trying to do this based on VBA.
I've been searching the forums for this problem but I can't seem to find any answers. Anyway, this is the problem. See screenshot.
I want to compare A1 for the values in column B, then return the corresponding cell (column C) in column D.
e.g. D1 = 2, D2 = 1, D3 = 4, D4 = 5 and D5 = 3.
Here's a simplified example:
ColA
ColB
ColC
Row1
A
Y
A
Row2
B
N
D
[Code] .........
I'm looking to return the values in column A adjacent to the cells in Column B equal to "Y". The kicker has been returning only the cells where the condition is true. Column C displays the desired behavior.
The closest I've been able to get is with a simple IF statement but I'm pretty sure the answer is a far cry away from there and likely requires an array formula. I'd prefer not to use VLOOKUP or OFFSET but will if the alternative is very complex.
I'm trying to find a way to:
Use a referenced lookup value from sheet "A", to return values, from several columns in sheet "B"
Things to note:
a) The lookup values sometimes repeat. I need all the associated values with each repetition as well.
b) The lookup values in sheet "A" are a comprehensive list, sheet "B" also contains some of these values but not all. Essentially, what I need to do is find a way to lookup each value in an account numbers column in sheet "A", against a different account numbers column in sheet "B".
If that value occurs in sheet "B" I want it to return the values from Columns X, Y, Z, (I want these values returned in sheet "A".
If that value does not occur in sheet B, the corresponding cells should remain blank.
If the lookup value occurs multiple times, I need all the corresponding values from each of X, Y, Z columns.
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.
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.
Example:
123 John Doe AR
123 John Doe CO
123 John Doe FL
456 Jane Doe MS
456 Jane Doe TX
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.
I have a sheet with Customer name in the first column and next 7 columns have unique serial numbers(alphanumeric) of the systems which that customer has purchased. In another sheet, i have the unique serial number in first column and I want to search this number in the 7 columns of other sheet and return the "Customer name" to this sheet. How do I do that ?
Vlookup searches only in 1 columns, so does match index. lookup didn't work as well.
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)
Next lCount
Nth_Occurrence = rFound.Offset(offset_row, offset_col)
End Function
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.
I have to match table column name with the column name in the sheets and get the values from column 6 in sheet2 if "# of values" in sheet1 matches with "Counter" of sheets for the same column.
if ("Table Column Name sheet1"="Column Name sheet2" then
if("Counter sheet2"="# of Values sheet1" then
return "Total # of rows Sheet2" from same row.
I am using Excel 2003.
Sheet 1 ....
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.
Is it possible to write some VBA code that will copy IDs from Sheet1 and then find these IDs in Sheet2 and return the values these IDs have?
View 4 Replies View RelatedI need to do a vlookup of the values of Sheet 1 Column A (Ref) in Sheet 2 Column A (Ref).
The results will be the concatenated values of Sheet 2 Column B + C + D + E to be displayed in Sheet 1 Column B (Address).
I'm not sure how I can do this using formula.
I've attached a example.
I have a data set with the weeks of the year as my column headers and a value of 1 (from a pivot table) in a distinct row value (removed for privacy) showing whether it existed in a given week based on pre defined criteria. What i need to do is ID the first (from the left) non blank cell in each row and then return that cells column header. I then need to do the same for the last (from the left) non blank cell in each row and then return that cells column header. On the attached I've put values in column N and O to show an example of what I need to return using a formula.
View 7 Replies View RelatedI have are two worksheets, "Sheet 1" which is a bill of lading form and "Sheet 2," in which columns A and B contain Part Numbers and Weights respectively.
What I'm wanting to have happen is when a user inputs a part number into Sheet 1 (say in cell B6), it calls a UDF that looks up the part number on sheet two column A and returns the corresponding weight from column B (to say cell C6 on sheet 1).
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.
View 3 Replies View RelatedI 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
On Pipe worksheet, pipe numbers 1-203 are listed in column A. Each pipe number has a flow rate that is listed in column E. I need it to look up the pipe number in column A on the Job worksheet and report the flow rate (from the Pipe worksheet) in column D for each pipe.
View 5 Replies View RelatedI am trying to find a solution for highlighting cells in a column that are repeats, ie. >3. I also need these cells to only be highlighted if the adjacent cell in the next column contains specific text. I have tried using conditional formatting with a countifs formula to no avail.
View 2 Replies View RelatedWhat I have In Column B, I have the datesIn Column I, I have engineers name What I need I want a macro to generate Serial Nos. (1,2,3....... n) in column A If an only if the date in column B is today's date and the engineer's name matches with the PC's username
The following is my code
[Code] ....
Above code runs without errors but does nothing.
Formula(s) to do as explained in the attached example.
Example_formula.xlsx
i have two sheets, one to display results (Reults tab) & the other tab containing the data (Data tab)
what i am trying to do is some how create a search function and have a forumula which contains a LIKE function that looks up the data table
RANGE = Data!A2:K255
the search needs to lookup the primary column Data!B2:B255 ... if any results are found .. show them on the results tab.. and if multiple results are found, display those as well.. (in either instance, the whole row of information in respect to the results need to be dislayed and hopefully no duplicates are found .. eg, Data!A:K of a hit)
is there a formula that can achieve this? oh, the search is TEXT based and there should be no empty cells within the dataset
after some MASSIVE googling, i have stumbled accross this
B1 = Search box (txt field)
A6 (which will be a hidden column) contains =MATCH($B$1,Data!A2:A255,0). this formula provides the first instance of the result and provides the row number
A7 contains =MATCH($B$1,OFFSET(Data!$A$1,A6+1,0,8-(A6+1),1),0)+A6.
this is supposed to look for the next row number which contains a match and provide that row number
and througout my other columns, i have
B6=OFFSET(Data!$A$1,A6,1)
B7=OFFSET(Data!$A$1,A6,2)
B8=OFFSET(Data!$A$1,A6,3)
and so on
2 things i cannot recitify..
1, the match has to be EXACT ... unfortunately i cannot use exact .. needs to be LIKE .. eg, i cant use the search word "boat" as the range of data has "boats"
2, it comes up with multile .. irrelevent results.
I have a problem with the formula that lookup all values in ascending order and returning all the corresponding values. eg: I was intended to lookup for the value in ascending order under the Total Occurrence and returning all the corresponding value under the Nos Group but encountered the same Nos Group was returned when there is same value appeared under the Total Occurrence.
View 3 Replies View Relatedhow 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 ...................
I need to sum values in a column, but only if the text in the same row of the adjacent column meets certain criteria. Below is a simplified version as an example:
A1 - Apple
A2 - Banana
A3 - Apple
A4 - Banana
B1 - 3
B2 - 2
B3 - 1
B4 - 1
I need a formula that will add up the cells in column B that have "Apple" next to them in column A. Apple would total up to 4 and Banana would total up to 3.
I have a table of data (say Column1 to Column 5) with multiple rows.
Column 1 to 4 will have the lookup values in multiple rows and Column 5 data should be picked up using vlookup or other lookup function.
I managed to somehow bring all these lookup values in (Column 1 to 4) in a single column in another sheet. I am now trying to use some lookup or other functions to match this single column and pick column 5 data in original sheet. Result i am expecting is lookup value in first column and next to it column 5 value.
It is basically a lookup wherein lookup value is spread over multiple rows and columns and result column is fixed. I tried using vlookup, but lookup value column and column number had to change every time when i moved from column1 to 4.