Match 2 Cells On 2 Worksheets And Copy Contents Of Another Cell
Jan 3, 2014
Setup: I have 2 worksheets with between 8 and 9,000 rows on each
Column A in both worksheets Sheet1 and Sheet2 have an email address in them.
Not all addresses in sheet1 will be on sheet2 and visa versa
Column J on Sheet 2 contains a date
What I need: Column M on Sheet1 is empty
I need a formula to place into Column M on Sheet1 that will
Look at Sheet1:A
Locate the corresponding value on Sheet2:A
Pull the date from Sheet2:J same row into Sheet1:M
View 2 Replies
ADVERTISEMENT
Apr 29, 2014
I have 2 inventory reports: what my store has and what my supplier has. I need to copy Tab1:K# to Tab2:T# provided that Tab1:A#'s contents match Tab2:A#'s contents. The A column represents the SKU of the item, but there is a difference in the amount of SKU's in each (my store sells ~6,000 items, supplier has ~10,000 items), so it's not as simple as sort by column A and copy pasting column K to column T.
For instance:
On Tab 1, A2's value is [1], K2's value is [9.38].
On Tab 2, A70's value is [1], K70's value is blank, but I need it to be [9.38], to match Tab 1's respective SKU.
I almost thought I had it figured out with VLOOKUP, but I cant seem to get it right... It doesn't reference the correct number.
Screenshots for reference
First tab, from the wholesaler: [URL]
Second tab, store's stock: [URL]
View 2 Replies
View Related
Mar 9, 2012
Been a while since I've worked with macros within excel and I can't seem to get what I remember being a basic macro to work whatsoever. I have 2 worksheets containing a massive amount of data and need to pull some cells from one into the other when values in 2 columns match.
To better explain, sheet1 has ID numbers in column G spanning for roughly 1700 rows. Sheet 2 has corresponding ID numbers in column EO. The data I need to copy over is in columns EP and EQ on sheet 2.
So I'm trying to build a macro to compare the values in Sheet1_Column_G to those on Sheet2_Column_EO and when a match is found, copy the value in EO and the adjacent entries in columns EP & EQ over into columns X, Y, and Z on sheet 1.
Couldn't get a VB function together to save my life so I tried working with MATCH & INDEX and didn't get very far either. I've included my current function below.
=IF(ISNUMBER(MATCH(G1606,Data!$EO$527:$EO$601,0)),INDEX(Data!$EO$527:$EQ$601,MATCH(G1606,Data!$EO$527:$EO$601,0)),"Not Found")
View 7 Replies
View Related
Dec 17, 2013
I have a script that copies data to files based on many cells contents but where I am having a problem is creating series numbers for each file.
File-01.txt
File-02.txt
..
File-100.txt
In my current code I copy files to folders by date and each folder I need series of files (Lab Testing series)
In column A1:A100 I have a series of numbers 01, 02, 03 ...100
Column B contains the Files to be saved
C:LAB2012Jan1file-01.txt
C:LAB2012Jan2file-01.txt
C:LAB2012Jan3file-01.txt
C:LAB2012Jan4file-01.txt
...
..
C:LAB2012Dec31file-01.txt
So this works fine
I now need the Script to do is to loop to Column A and select Cell 2 and do the File Copy again on the Next series
C:LAB2012Jan1file-02.txt
C:LAB2012Jan2file-02.txt
C:LAB2012Jan3file-02.txt
C:LAB2012Jan4file-02.txt
...
..
C:LAB2012Dec31file-02.txt
When Complete repeat until it reaches the end of column A
Since my Cells are populated by all the data in the workbook I thought at the end of my copy script I would take the next Cells data in A and put it in Cell H8 where all the constants are for the file names.
Column B is built using
=IF($C1="","",$I$1&$G$4&$D1&""&$I$8&$H$8)
=IF($C2="","",$I$1&$G$4&$D2&""&$I$8&$H$8)
View 1 Replies
View Related
Apr 21, 2009
I have a list of items in column A of Sheet1 and the same list in column A of Sheet2. Both list will contain the same items, however not necessarily in the same order.
What I want the book to do is; when I click on an item in the list on Sheet1, it takes me to the same item in Sheet2.
Is this possible with VBA?
View 10 Replies
View Related
Aug 11, 2007
I am developing a Workbook where I import data to one worksheet and then disseminate it to other worksheets. I am not sure if I am overthinking the process, but I am having difficulty writing the macro and getting it to run through the entire list of rows.
There are 4 Columns in the one worksheet, named "Update":
Column A supplies the name of the worksheet that will be updated
Columns B-D contain the data that will update the sheets.
The process that I am trying to write the macro for is a follows:
1. Select/copy the range, starting with B1:D1 on "Update"
2. Navigate to the appropriate worksheet to be update, determined by A1
3. Identify the first blank row, starting with A5
4. Paste the data B1:D1 to A5:C5 (or next available line)
5. Navigate back to "Update" and begin the process again with the next row B2:D2
This process should continue until there is no value in Column B on the "Update" worksheet.
View 7 Replies
View Related
Feb 28, 2009
I work for an IT company who build their own PC's. And we use a simple Excel 2003 spreadsheet to piece-together and price-up PC's for retail, I've attached an example of the spreadsheet for you to see. It uses basic formulas so we can see at-a-glance things like VAT figures, margin, profit etc. Each PC we sell is contained within one Excel file, and each PC has it's own Workbook tab.
The problem I want to solve is whenever we change a component or price, we have to do each system manually, which gets very repetetive and time consuming. What I'd like to be able to do is change the price of, let's say, the motherboard, and have the price be reflected across each of the PC's using that same motherboard on each seperate tab. This same system would apply when we change a product name, product ID etc.
View 2 Replies
View Related
Apr 8, 2008
- I copy some records from a Database into an Excel sheet in workbook (say W1) in the Worksheet WS1.
- The i look up for values in a column (say column B) of W1 in another worksheet in another workbook(say W2) .
- If i find a match in Workbook 2(W2) ,Worksheet 1 , Row 2, Column A ; i look at the values in the Column B, F,G on the Row 2 itself.
- Each of these values can be found in the corresponding Worksheets 2;3;4 in the Workbook2(W2) .
- Then i need to pick up all these values from worksheet 1;2;3;4 in W2 and contatenate them and put them in the Workbook W1 against the Row .
- Similarly i process all the non zero rows in the workbook W1.
View 2 Replies
View Related
Apr 27, 2012
I think I could get this to work but the INDEX command is not returning an index of the row like it is documented. It is returning the value in the cell. In order to get this to work, I need to return the index of the row. I need to know what row the match found the item on because the value I need to copy is going to be in column J of that same row.
I have two worksheets within 1 excel document.1 worksheet is a hand filled and excel programmed worksheet (Name is website-upload)1 worksheet is a hand filled and excel programmed worksheet (Name is website-upload)
1 worksheet is a hand filled and excel programmed worksheet (Name is website-upload)
The other worksheet is a table from a database connection worksheet. (Name is ODBC_Products)
Each worksheet has a Sku column in it.
website-upload: Column AODBC_Products: Column B
Each worksheet has a QTY column in it.
website-upload: Column KODBC_Products: Column J
Goal: I would like to search the Sku column ODBC_Products and find a match for a sku that is listed in the website-upload sku column and then when I find that match, display the qty from the ODBC_Products worksheet to the website-upload worksheet.
Notes: I add columns and delete columns all of the time on this worksheet so I would rather do this in excel vs. a macro. I figure this can be done with something like:
Returns N/A
Code:
=INDEX(ODBC_Products!B2:B1000,MATCH('website-upload'!A2,ODBC_Products!B2:B1000,0),1)
OR
Returns an Error
Code:
=INDEX(Table_database_name_products[productcode],MATCH(A2,Table_database_name_products[productcode],0),1)
Notes:
Here is an example of what I have: [URL]
The website-upload image says Website Qty column (J). That is supposed to say column (K)
View 9 Replies
View Related
Sep 3, 2013
I have a spreadsheet with 60k lines. One of the columns contains barcodes as per the example below, with a suffix of -XXX-XXX where the XXX are numbers. I want to highlight all the cells where the -XX-XX are the same number (as in the example below -303-303) how I can do this with a formula as I've around 30 spreadsheets each with 60,000 lines I need to sort through?
2500071189898-303-302
2500071189898-303-303
2500071189898-303-304
2500071189898-303-305
View 4 Replies
View Related
Jul 7, 2014
Let's say I have a workbook with 7 worksheets named, for example, "Instruction", "Begin", "Worksheet 1", "Worksheet 2", "Worksheet 3", "End", and "Data". (in that order)
What I want to do is run a macro to go to whatever worksheet that is in between "Begin" and "End" and copy, for example, cells $C$1:$D$10; then paste as formula into worksheet "Data" starting from cell C1 and then down a list (i.e., copied cells from "Worksheet 1" get pasted as formula into "Data" cells C1:D10; then copied cells from "Worksheet 2" get pasted as formula into "Data" cells C11:D20, and so on and so forth).
But if I were to add more worksheets (e.g., "Recipe" and "ToDo") positioned in between "Begin" and "End" and run the macro again, it'll either 1) re-copy all the formulas from the included worksheets back into "Data" including the formulas from the newly added/placed worksheets or 2) it'll add the formulas from the newly added/placed worksheets and paste into "Data" at the end of the list.
Can create the macro to run based on the position of worksheet, and not based on the name of worksheet, since ultimately there will probably be over 10 worksheets between "Begin" and "End".
View 4 Replies
View Related
May 27, 2014
Basically, i have a workbook that contains 2 worksheets with stock data for two stock holding sites, we're consolidating our stores and need to confirm what stock is held at both sites. I need to compare column C on both sheets to find if an item exists on both sheets, and if so copy the entire row from Sheet 2 onto the next free row on a new sheet. Col C on each sheet contains the stock number, however, on Sheet 1 the stock number is abbreviated ot the final 7 letters, with Sheet 2 showing the FULL stock code.
I need whatever is in "Sheet 1:Col C" (for example '1234567') to be compared to whatever is in "Sheet 2:Col C", and if a match is made (for example 9999-00-1234567) then the row containing the match be copied to the next free row on Sheet 3.
View 1 Replies
View Related
Jul 13, 2009
I need a macro that can search a sheet for a match and then copy all 7 cells to the right of the match. I have attached an example of the sheet that will serve as the database to search, and a userform example that will be similar to the userfrom that will display the copied cells when a match is found. I plan to copy and paste the 7 cells to a different sheet so that the userform can display the results with the control source property. I do not need a way to add to this database. I know very little about searching a database so.
View 6 Replies
View Related
May 21, 2014
Userform that people use. I need validating the users input into the form.
1. Need to validate that A1 only has 3 letters (Alpha)
2. Need to validate that A2 is not left blank
3. Need to validate A3 has only 10 digits
My state is that when the user hits the submit button in the user form the macro checks for these validations above and if everything is ok emails the sheet. However if the user havent inputed the data correctly a msg box will pop to let them know what is wrong. If thats the case i do not want the sheet emailed. below is the email code i'm using
HTML Code:
Sub SendMail()
' Copy the sheet(1)
ThisWorkbook.Sheets(1).Copy
' Send Email
.SendMail Recipients:=Array("email.address@email.com"), Subject:="Test" & Format(Date, "dd/mmm/yy")
.Close SaveChanges:=False
End With
End Sub
View 2 Replies
View Related
May 29, 2012
I want to check the contents of all the cells in S Column to see if it equals "Process Recover/Rebill"
If True, then Change corresponding cell in C Column to "Manual Correction"
I have this VBA and it doesn't look like anything is processing. It appears that I must have something missing in the If line..message about missing.
(tot_new is the number of rows of data) I begin in the 3rd row.
For Counter = 3 To tot_new
If Range("S" & Counter & ").Value = "Process Recovery/Rebill" Then
Range("T" & Counter & ").Value = "Manual Correction"
End If
Next Counter
View 3 Replies
View Related
Jun 21, 2012
How can I modify the following formula to find the contents of reference cell, for example if the cell reference (G$3) is equal to 9 I want it to use 8 instead.
Code:
IF(ISERROR(INDEX(tra!$E$2:$E$1100,MATCH(G$3,tra!$C$2:$C$1100,0))),"",
INDEX(tra!$E$2:$E$1100,MATCH(G$3,tra!$C$2:$C$1100,0)))
I tried using G$3-1 but this doesn't appear to work.
View 1 Replies
View Related
May 7, 2014
In column A, i have cities.
In column B, i have dates.
I would like column D to have both the city and its corresponding date e.g. "paris (12/06/2013)"
However, I am getting "paris (41437)". Is there anyway to make the date appear properly?
View 3 Replies
View Related
Apr 5, 2009
I would like to do now would be to only copy the rows that also contain the word sold in column C. I guess that I could do this in two stages, first copying the rows based on the year then copy again based on the word sold in column C. It would be nicer to make a slight modification to my code and do it all in one step.
View 2 Replies
View Related
Dec 19, 2011
I am new to VBA and am having difficulties in getting a find function to successfully locate search criteria within cell. When the search criteria matches exactly that of the cell contents then the code works; however should the search criteria only form part of the total cell contents (such as a seach for "the" in a cell containing "the cat sat on the mat") the code doesn't recognise it.
Essentially, I need the code to search a range for the required string and if found within a cell activate that cell and populate a combobox with the full cell contents of the activecell.offset(0,-2).
The relevant section of code attached below:
Dim role_count as range
Dim role as string
If Application.WorksheetFunction.CountIf(Range("Role_Count"), Role) 0 Then
Range("role_count").Select
Selection.Find(What:=Role, After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
, SearchFormat:=False).Activate
Me.Controls(ComboBoxName) = ActiveCell.Offset(0, -2).Value
View 4 Replies
View Related
Nov 19, 2007
I have a list of about 2 million phrases. The list was created by combining two lists in all possible combinations:
a list of about 800 street names
a list of about 1000 property names
example
church street flat
church street apartment
house church street
house king's road
etc
what I would like excel to do is the following:
look at each cell and determine which of the 800 street names it contains. write that streetname into adjacent cell.
this would then leave me with the original column, where each of the 2 million cells has been assigned one of 800 streetnames.
for example:
church street flat | church street
church street apartment | church street
house church street | church street
house king's road | king's road
that's all. I think that can't be too difficult, and probably it has already been discussed here in the forum. unfortunately I did not know for which keywords to search, that is why I couldn't find the posts.
I hope somebody can help me as I need to get this sorted for work as soon as possible. All semi-automatic ways I could think of to accomplish this would take me days that I don't have.
View 9 Replies
View Related
Nov 6, 2009
I have an exported Excel worksheet1 from the parts database get every Friday. We get RFQ of parts list each week from multi customers about 3000+ parts as they send in MSword, MSoutlook or MSexcel to me I make into Excel worksheet2. I do not like to type in every part into the parts software I have my words for that software Cough Cough. The exported excel speadsheet tells the part numbers, Location, Qty, Price, ETC I would like to take the Excel RFQ list and have it populated from the other speadsheet
SAMPLE
A1 PART number that on both spreadsheet
A2 QTY on both there request and our stock
A3 Price
A4 Location
So want a compare worksheet1 with worksheet2
A1 = A* the populate A2-A4 with worksheet1 data
View 5 Replies
View Related
Jul 6, 2014
I will have 2 columns. In column B, will be a list of invoice numbers. Now each one starts with the "job number" (Ex. 51APGC01) then a dash and unique number per invoice. (Ex. 51APGC01-01)
In column A, will be a list of Purchase Order numbers sent by the customer so we can bill. Now, sometimes we get one PO for multiple invoices, (Ex. 03, 05, 06, 07.....)
What I am looking to do, is when I get these PO's, I enter them in and then I print a report for our billing Dept. The way the output line (cell) will read, is Job Number, then each unique number: (51APGC01-03, 05, 06, 07)
There are multiple jobs and each one has it's own report, so the job number will be dynamic and defined else where in the workbook. I can define that. It's just getting the numbers after the "-" and putting them in only.
View 5 Replies
View Related
Oct 4, 2012
Have a slightly unique issue that I can't seem to find the specific solution for. I am wanting to filter a range, using row 1, then copy the visible cells (Excluding the title row 1) and paste into the same location, so over the data as Values.
I can do the filtering part and the selection of the visible row. Only trouble is pasting it to the same location.
this is the code so far - anything better would be great
Sub Macro2()
Sheets("GF_Scoring Database").Activate
Dim extvalue As String
Dim rng As Range
[Code]....
View 2 Replies
View Related
Nov 23, 2008
I am trying to copy a column of cells from one sheet to another, but also want to keep all the formatting. The origin sheet has times, but when I copy these to the destination sheet they are displayed as decimal numbers (using the code snippet below). I can change these back to times by formatting the cells using the format painter after the macro completes but I would like the VBA to do this for me. (using 2002 SP3).
View 4 Replies
View Related
Dec 2, 2011
I need a way to copy part of contents from a cell, the cell contains product information like size, name and weight of a product, and I need a copy the weight to be put in it’s own cell, here’s a copy of one cell “E65 MAPP SKDV 5 DIGIT 90G ST.K” the information I need from this is “90” the number is always followed by a capital G, but it’s between two and three digits where the lowest is 55G and the highest is 300G. Is this possible?
View 6 Replies
View Related
Mar 9, 2012
I would like to copy the contents of a cell from a sheet in workbook 1 into a textbox that is on a userform in workbook 2. This is what I have but I get a runtime error 438:
Code:
ActiveWorkbook.Sheets("ID").Range("a1") = Workbooks("Key.xlsm").userform1.TextBox1.Text
View 8 Replies
View Related
Mar 10, 2004
I need a macro to automatically copy the contents of a cell to a text box.
View 7 Replies
View Related
May 13, 2013
Basically lets say I have in cell A1 to A10 floor access data i.e. Users who used their ID cards to access a room.
Now the data always starts with either Rejected or Admitted then the user's name and then the card number and the access floor etc. the card currently has.
What I am looking for is that the macro should first check if the cell has Rejected or Admitted written in it - this I can do myself using =Left(A1,8) which should give me the helper column I need.
Then based on this I want it to only copy the name of the individual i.e. it should look in the cell and only copy whatever is written between "Rejected" and "(Card". The cell data is something like this:
Rejected Doe, John (Card #123456) at ABC 123 Floor1/Floor2/Floor3/Floor4 Door 1 [In] [Clearance]
View 4 Replies
View Related
Jun 6, 2014
I have a line of code that returns a run-time error 1004 whenever it is passed through. All I am trying to do is copy and paste. I am missing some glaring error? (It is only a selection of the code to highlight the part I am having issues with. "maxdate" and "d" have been set)
VB:
Dim ws, ws1 As WorkSheets
Set ws = ThisWorkbook.Sheets("Data")
Set ws1 = ThisWorkbook.Sheets("Target")
[Code].....
View 5 Replies
View Related
Jul 8, 2014
I have a loop function that goes through a list of employees and i want to move their name to a different sheet(monday, tuesday...) so that I know who is in on what day. is there a simple way to have it add the next name to the bottom of the list?
View 3 Replies
View Related
