Finding Matching Values In Different Sheets
Oct 11, 2011
I have in Sheet 1 a list of product codes (that should be only numbers, i.e: 1010) on column A, product description on B and a sales column on C.
On Sheet 2 I have on column A I have in the same cell a letter "C" the product code and the product description(i.e.: C1010 Onions). On column B I got the sales total. ( I dont care about the letter "C" it can be deleted)
I would like to use a VBA code to find the same product codes between sheet 1 & 2 and copy the sales total from sheet 2 to sheet 1 when the product code is the same.
View 1 Replies
ADVERTISEMENT
Oct 26, 2008
I have a sheet that returns 4 numbers where 2 will match. Is there a command that will return that matching value?
For example:
cell 1 = 5
cell 2 = 9
cell 3 = 4
cell 4 = 9
something to return the value 9
View 2 Replies
View Related
Jan 4, 2012
I'm trying to store a value in ColumnA Row1 Sheet1 in a variable and check to see that value exists in Sheet2. If the value is in Sheet2, I want to copy the information in Row1 Sheet1 and paste it in the row of the matching value in Sheet2. I need to then check the value in ColumnA Row2 Sheet1 and check again for it in Sheet2 and I need to continue to do this until the data ends in Sheet1.
Code:
Dim x As String
Dim y As String
A = 2
B = 2
[Code]...
The above is the code I've been trying to use. It has two flaws:
It won't let me copy into the row of Sheet2 (not a big deal though)The rows get all mismatched and it doesn't actually search ALL the rows in Sheet2 so it misses some of the values that actually DO match (the big issue) I've racked my brain for hours trying to figure it out using the above method and I think I've reached the point where I'm overthinking it way too much. I also found the "Find" method but I couldn't quite teach myself how to use it.
View 9 Replies
View Related
Aug 13, 2014
I have a spreadsheet where I have two different sheets; a summary sheet, and a more detailed sheet that lists out items on invoices.
The summary sheet shows invoice number, amount invoiced, and total paid against that invoice.
The detailed sheet goes into more details, and shows whats on the invoice, and any reasons for them not being paid.
I want a formula on the summary sheet that picks up the invoice number from the details sheet, and then brings through the figures. The details sheet may have more than one line for an invoice number, so the formula needs to be able to add up too.
I've tried a vlookup, but it doesnt seem to work, and I dont think it will do what I want.
View 2 Replies
View Related
Jan 27, 2014
see attachment below for easier comprehension of what I need to be done. Note that I have around 20 subjects (2 in example) and about 15000 values per subject (5 per subject shown) Basically, I want matching rows to be aligned (see grey) and non-matching rows to be deleted, throughout the 20 or so subjects.
Exemple.xlsx
View 6 Replies
View Related
Jan 5, 2014
I am working with a nonprofit to set up their financial ledgers. There is one workbook with 12 sheets, one for each month. The goal is to be able to set up a formula that searches through all 12 sheets for every donation that a specific individual has made. For instance, let's say that John Smith gave x amount of money on 1/1/2000, y amount of money on 1/10/2000, and z amount of money on 2/2/2000. This data will appear on 2 different worksheets. I have the following formula, which allows me to look through one sheet at a time:
{=INDEX(Jan!$B:$B,SMALL(IF(Jan!$A:$A=Smith!$A$2,ROW(Jan!$A:$A)),ROW(1:1)))}
Where Column A in each sheet is individual name, and column B is amount donated. Sheet "Jan" is the data for the month of January, and sheet "Smith" is the culmination of John Smith's donations for the full year. Using this array function, I am able to retrieve all data for John Smith in the month of January, but I can't find a way to make one function that searches for all of John Smith's donations in each month. Is there a way to build an array function in VBA that would accomplish this?
View 3 Replies
View Related
Nov 9, 2008
My spreadsheet consists of two columns, column A holds a series of numbers whilst column B, again holds a series of numbers but with an added single letter on the end.
What I would like to do is find a function which will match the numbers and letter in column B with the corresponding numbers in column A and then place the result in column C ....
View 10 Replies
View Related
Apr 2, 2013
I currently have two tabs. Tab A (Error Report) spit out a bunch of records that I need to find in the raw data/remove them. The value in column C in Tab A refers to Column A in Tab B (Data), and Column D in Tab A matches with Column N in Tab B, and Column E in tab A refers to Column C in tab B.
I want to know how I can find the records that match those 3 columns in the First Error Report Tab as I need to find those records and delete them as they are, as you can see, errors!
View 1 Replies
View Related
Oct 27, 2008
I have two spreadsheets, spreadsheet A and B. In each spreadsheet there is a column labeled 'event' and a number in that column. I need to match the event number in spreadsheet A with the same number in spreadsheet B. In spreadsheet B in the same row as the event number is another value that I need copied back to spreadsheet A.
Manually I would have to copy the event number from spreadsheet A and use 'find' in spreadsheet B. Then check in the same row as the event number in spreadsheet B for a specific number, copy that, head back to spreadsheet A and paste it next to the event number.
I've tried to create a macro to do this but when using 'find' to match the event number in spreadsheet B it keeps on using the first event number I used instead of moving down a row onto the next number. I can upload the spreadsheet if needed.
View 2 Replies
View Related
Jul 14, 2014
I have two tables, both contain a column of ID numbers and a column of addresses.
I would like a method to match the IDs from table1 to those of table2 - if no matches are found, search for matching addresses, if a match is found then output the ID in table1, the address, and the corresponding ID in table2.
If still no matches are found, output the ID and address from table1 and indicate that it didn't match.
Then I want to check table2 records - i.e. search for matching address between the records and output the table 1 ID, address and table2 ID were partial or no matches are found, as above.
View 3 Replies
View Related
Jul 7, 2009
I have 2 pretty large zip code lists, we will call them columns A and B. I know that I have some from A that match to zips in B, but I have alot more zips in B that don't match to column A. I need to identify all of the zips in B that do not match a zip in column A.
View 9 Replies
View Related
Dec 11, 2012
The matrix:
Col A Col B Col C
2012-11-08 07:26:16Check-InJohn
2012-11-08 13:23:58Check-OutJohn
2012-11-08 13:24:01Check-InJohn
The goal:
Since i have a given:
1. Name(which exists in column C)
2. Date (which exists in column A - formated has (AAAA-MM-SS HH:MM:SS)
3. Event (which exists in column B)
4. Hour*
I want to know the nearest hour that matches all criterias.
In the example on top, if i give:
John, 2012-11-08, Check-In, 14:00:00
I want the result: 13:24:01
If, for example, the date doesn't exist, the result could be a simple error message.
View 3 Replies
View Related
Sep 27, 2013
I need to find if there are matching addresses in 2 different excel files. If the same address appears in both files, I would like the new worksheet to return the address along with the sale price from the 1 file and the rental amount from the other file.
I have tried using vlookup but the problem is the exported data file contains the street number in one column and the street name on another column. I have attached a truncated example of both the rental data and the residential sales data.
View 5 Replies
View Related
Jan 28, 2010
I have a cell (A1) that contains text.
That text will match a single value in a reference list (i.e. range of cells e.g. Z1:Z26).
When the match occurs (e.g. A1 = Z16), I want the format of the reference cell (fill color, text color) to apply to the cell of interest (A1).
The reference cells are manually formatted.
I know that I could create a long list of conditional format formulas instead of using the reference, but it seems that there must be some way to tell excel: if the cell value contents are equivalent, the formatting should be as well.
View 9 Replies
View Related
Feb 17, 2010
Is it possible to have a formula where it will retrieve a specific value on one sheet based on matching values? Unfortunately, hlookup wont work as there are several values in the table where the 'lookup_value' is the same.
Example:
On sheet 1 (titled BSRC), I have a table which has a list of values in column 1. On column two I have the 'lookup value' which needs to be matched and all values underneath it to be retrieved from sheet 2 (titled CSRD). The 'lookup_value' range is on row 9 of sheet 2.
I have attached a sample spreadsheet for clarification.
View 7 Replies
View Related
May 15, 2014
I would like to align the matching values in columns A and B but I want the values that correspond to b in c,d and e to go with the column b value.
C D and E don't necessarily have a value in there but if it does it needs to move with B
View 5 Replies
View Related
Jun 3, 2014
My requirement is to match 2 columns of two different sheets. I know about Vlookup for exact matching but here I neeed to do partial matching.
For Example: Sheet1 Company Name= Opera Technologies
Sheet1 Address= 104/Main Street
Sheet2 Company Name= Opera
Output should be like this in different column: Opera, 104/Main Street
View 4 Replies
View Related
Apr 28, 2009
I have 2 sheets with lots of data in them. On the first sheet in the A column i have data that looks like this: 01234567 On the second sheet in the D column i have data that looks like this: 1234567 It's basically the same data except without the 0 in front. The first sheet has like 60k rows and the second sheet has around 18k rows. I need to find out what data from the second sheet is in the first one. I'm attaching an example.
View 2 Replies
View Related
Oct 29, 2009
If I have a list (in sheet 1) of names listed in column "a" and numbers listed in column "b" and the same thing (on sheet 2) and I want to run a macro that takes the data from (sheets 1 and 2) and lists the matching names in column "a" (in sheet 3) and the number associated with the name (in sheet 1) in column b and the number from (sheet 2) in column c
View 2 Replies
View Related
Sep 19, 2012
I am trying to combine the data in two sheets.
In Row A sheet one, I have "name" In Row A sheet two, I also have "name"
If row A sheet two matches the data in row A sheet one, I want the corresponding data in row D sheet two to move to row E sheet one.
View 1 Replies
View Related
Mar 24, 2007
I have two majors (classes) listed on two differents sheets and need to put the matching classes on a third sheet.
View 3 Replies
View Related
Jul 10, 2014
is it possible to provide a formula to match as per the uploaded file. Data in Sheet one columns A and B, compared to what is in Sheet two column A I have a lot of data, and only need a Y for yes, and N for No as a result.
View 3 Replies
View Related
Feb 10, 2014
I have two sheets with a Unique ID that I'd like to match and generate a third sheet. The third sheet would consist of First Name, Last Name, ID number where the phone numbers match from between sheets.
Sheet 1: A = Fname, B = Lname, C = Phone
Sheet 2: A = message, B = Phone
I'd like to make a Sheet 3: A = Fname, B= Lname, C = Phone, D = Message
Is my only option to try and get on a PC and use MS Access? Never had to do this in Excel before.
View 1 Replies
View Related
Nov 16, 2011
I have one workbook in that 6 sheets have.
Here I want match the total one sheet to another but each sheet there is no fixed ranges so that I have confused.
How to find out Each sheet total and match each other.
View 3 Replies
View Related
Jan 8, 2008
I want to filter data using two sheets. i want only the names in a field that are listed on BOTH sheets. i have a two lists of names and i want only the names that are included in both sheets while still including the rest of their information.
View 4 Replies
View Related
Apr 18, 2008
I am trying to find a way of comparing entries in one spreadsheet to another spreadsheet and generating a third sheet of those that do not match.
As an example, I have a spreadsheet of approved supplyers, with name, reference and date in each column, and a speadsheet of used supplyers, some with references and some without. I want to compare the supplyers used to the approved supplyers list by reference number (which are in the same format), and produce a third sheet listing all those that do NOT match e.g. unapproved supplyers.
I need to make sure that the third spreadsheet only lists each seperate supplyer once, even though they may have been used several times, AND it must also include supplyers without a reference number.
I will use this to compare many spreadsheets with the same data, but of varying length and would like to use a macro to perform this function.
View 6 Replies
View Related
Jun 14, 2008
I'm working with 2 sheets. Both have the same headings, A1 is CustomerCode, C1 is JAN, D1 is FEB and so on. The CustomerCode column doesn't match exactly between the 2 sheets, because some of the customers from Sheet1 aren't listed on Sheet2. I need a formula to put on Sheet 3 that will Sum the values in JAN, FEB, etc from the first 2 sheets for matching customers, and also bring over the totals from the customers on Sheet 1 that don't have a match on Sheet 2.
I have attached an example of how the finished product would look in my case. But also, here's this:
Sheet1 Charges
CustomerCode JAN FEB
ABC 30 25
AEF 20 15
BBB 50 30
CED 15 20
Sheet2 Misc Charges
CustomerCode JANFEB
ABC 510
BBB 3 8
CED 2 4
Need Sheet3 To look Like this:
Sheet3 Total Charges
CustomerCode JANFEB
ABC 3535
AEF 2015
BBB 5338
CED 1724
New customers are added often, so I have a SQL Query that I can put in column A of sheet3 that will pull the same CustomerCode list as sheet1, so getting the names to sheet3 shouldn't be an issue.
View 3 Replies
View Related
Jan 17, 2014
I'm not sure if this is best handled in the Formulas & Functions section or elsewhere, but this is my best guess. I have a spreadsheet used for tracking hardware issues with data in two worksheets:
1. PageCount
Column 1: Date
Column 2: Serial#
Column 3: PageCount
2. Tickets
Column 1: Date
Column 2: Serial#
Column 3: TicketNumber
What I'm trying to do is find a way to extract from those two sheets the date, serial number, page count, and ticket number where the date / serial # are the same and dump this to a new sheet. I've thought that I may need to concatenate the date / serial number together into a single cell or something, but I'm really at a loss as to how to pull this data. Part of me thinks this should be done in SQL, not a spreadsheet, but that's how our reports are produced.
View 1 Replies
View Related
Apr 16, 2013
I have this sheet, which is just an example (the actual one has many more records).
subset.xlsx
I received this sheet (again, example)...
new.xlsx
I need to add the new sheet to the old sheet, so that they are merged into one sheet. Also, if the same "LOC" appears in both sheets, I need to overwrite all the records for that "LOC" in the old sheet with the records from the new sheet.
View 3 Replies
View Related
Apr 25, 2009
There are two sheets: Sheet1 and Sheet2
Columns in Sheet 1 are the following:
company PERMNO number, dates, market capitalization (no data in this one)
Columns in Sheet 2 are the following:
company PERMNO number, ticker, dates, market capitalization
In Sheet2, there is a whole range of dates (between 1990 and 2004) for every single company, whereas in Sheet2 there is one or 3-4 dates for a every single company.
I would like to match the exact date/dates in Sheet1 in the range of dates in Sheet2 and then if they match, copy the corresponding cell value from the market capitalization column in Sheet2 to the empty market capitalization column in Sheet1. I also want that the company PERNO numbers match.
In short: if PERMNO numbers match, match the date/dates in Sheet1 within the range of dates for the same company in Sheet 2 and copy the market capitalization value to Sheet1.
Example:
Sheet1:
A B C
1. PERMNO DATES MARKETCAP
2. 13123 199803
3. 13123 199904
4. 65456 200005
5. 44550 200104
6. 44550 200211......
View 9 Replies
View Related
