Copy Corresponding Data Based On Matching Cells?
Jul 15, 2014
I am trying to copy a row based on the value of a cell.
I have two sheets in my workbook and on sheet 1, I have a part number and a description. On sheet 2, I have part numbers again, but this time I the description is broken up into the format I need.
What I am trying to do is have excel search on sheet 2 for the part numbers, then copy the information that corresponds to the part number into the correct column.
I have tried using Vlookup. But if the part number in row 2 on sheet 1 match the one in row 8 on sheet 2, this will copy over the data from row 2 whereas I need row 8.
If this would be more doable using VBA, that is fine by me. I haven't been able to figure out anything in VBA or in excel formulas up to this point.
View 7 Replies
ADVERTISEMENT
May 23, 2014
I am trying to build a staff roster. The staff rotate over a 4 week cycle. the name of the staff member, and their shift needs to be looked up from the key then matched with the particular week. the name and shift then need to populate specific cells.
I have attached the worksheet so you can see what i am trying to achieve.
View 2 Replies
View Related
Feb 13, 2014
I have 2 worksheets, A and B. In both worksheets there is common data in column A (account ID). I would like to find a way to return all of the data for the matching row in worksheet B and have it pasted into the matching row in worksheet A.
So in the example below, I am looking for a function that will match on Account ID in both worksheets and then paste the results from Dataset 1, 2 and 3 into the row with the matching Account ID in worksheet A.
Worksheet A
Account ID
Column to paste matching rows from Worksheet B
1
2
Worksheet B
Account ID
Dataset 1
Dataset 2
Dataset 3
1
AAA
BB
CC
4
EE
DD
FF
View 1 Replies
View Related
Aug 13, 2014
I've got 3 pairs of columns and I need to sort through them and align the cells in columns E&F with those in A&B and C&D. The cells I need to match up are the times (columns A, C and E)
Example - convert this:
A...............................B..........C...............................D.........E...............................F......
BID TIME.....................BID.......ASK TIME....................ASK......TRADE TIME................TRADE
30/07/2014 14:21:04.....6.10.....30/07/2014 14:22:37.....6.13.....30/07/2014 14:21:04.....6.13
30/07/2014 14:21:06.....6.11.....30/07/2014 14:22:54.....6.13.....30/07/2014 14:22:37.....6.13
30/07/2014 14:22:37.....6.11.....30/07/2014 14:22:56.....6.13.....30/07/2014 14:22:54.....6.13
30/07/2014 14:22:54.....6.11.....30/07/2014 14:22:56.....6.14.....30/07/2014 14:22:56.....6.13
30/07/2014 14:22:56.....6.11.....30/07/2014 14:22:59.....6.13.....30/07/2014 14:22:59.....6.13
Into this:
BID TIME.....................BID.......ASK TIME....................ASK......TRADE TIME................TRADE
30/07/2014 14:21:04.....6.10.................................................30/07/2014 14:21:04.....6.13
30/07/2014 14:21:06.....6.11........................................................................................
30/07/2014 14:22:37.....6.11.....30/07/2014 14:22:37.....6.13.....30/07/2014 14:22:37.....6.13
30/07/2014 14:22:54.....6.11.....30/07/2014 14:22:54.....6.13.....30/07/2014 14:22:54.....6.13
30/07/2014 14:22:56.....6.11.....30/07/2014 14:22:56.....6.13.....30/07/2014 14:22:56.....6.13
............................................30/07/2014 14:22:56.....6.14............................................
............................................30/07/2014 14:22:59.....6.13.....30/07/2014 14:22:59.....6.13
I don't know VBA so hopefully there's a way of doing this with a basic Excel function.
View 2 Replies
View Related
Nov 12, 2009
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!
View 9 Replies
View Related
Feb 20, 2014
I have two worksheet "ABX" and "ACX" from which I want to find the matching data for specific columns B, D and E only.
Matching data will be in sheet "Match" and vice versa.
Data are present dynamically.
View 1 Replies
View Related
Apr 21, 2014
Copy rows from one Sheet to another based on a separate cell value But specifically, I am trying to copy row values from Columns C through column Z in Worksheet 1 of file POHeader.xlsx to row values Columns N through AK in file POReceiv.xlsx when the (Purchase Order #) values in Column A of each file match.
The reason is behind this is - one file has the unique Purchase Order number as the key without associated parts and the other file has the associated part number as the key with purchase order number attached.
I don't know whether I need to use VBA or if I can just use an index and match function.
View 9 Replies
View Related
Jul 1, 2008
I am working on a spreadsheet for a shoe company. I have separate columns for the size, model, color, and item number of a shoe. I get everything except for the item number from a written document; I then have to find the item number for the shoe from another excell document called the Master List.
I was hoping there would be a way to have Excell auto-fill the item number for me. For example, if a shoe is a Red, Athens (the shoe model),size 12, its item number (which can be a pain to find) listed in the row of the Master List is aaabbb. So I want to just enter in the size, color and model number, and have Excell find the item number for me, and fill it in.
I have enclosed an example. Sheet 1 is the sheet I would be working on. Sheet 2 is a portion of the Item master list, which is actually 50k lines.
View 8 Replies
View Related
Feb 15, 2007
I have two ranges AA:DD and F:G, I have to find matching pair in AA:DD and in F:G and copy H and add as a comment to D, its hard to explain. see my attached workbook.
View 5 Replies
View Related
Aug 28, 2007
On my worksheet (which I have stripped down significantly to be able to upload here), I am looking for a way to copy certain cells over when the TCR (machine) numbers match. So, on the spreadsheet, if there is a 1 on the De La Rue sheet in the A column, it will then copy the value that is in cell B (directly next to it) and paste it into the balance sheet tab in column J in the row that has the same number in it. It would then do this for all machines in the balance sheet.
Then it would go to the "Ecoin Amounts" sheet, and do the same match, copy, paste, but this time it would be if the machine number matches, it takes the value from column D and the value from column H and pastes these into the balance sheet sheet in columns C and E respectively.
As you can see from the balance sheet tab, I tried using a VLOOKUP function, but when I import my initial data to the sheets, and manipulate it the way that I want, it gives be a reference error. I could possibly circumvent this by writing the VLOOKUP amounts as part of my macro, but I wanted to see if this could be done any easier.
View 2 Replies
View Related
Sep 2, 2007
in writing a macro that does the following.
Right there data in cell A1 lets say LTP BY001 and then the next data in column A is LTP BY002 in cell A9. What I need to do is copy LTP BY001 from cell a2-A8 and then LTP BY002 From A10 until LTP BY003 appears lets say A15. Therefore need LTP BY002 copying from A11-A14. I need that to go right the way down the workbook at everytime their is a number change.
View 6 Replies
View Related
Jun 29, 2006
I have two columns with the same data just totally different orders the third column (associated with the second) has data that I want to sort. I want to keep the order of the first, rearange the second so they match, and have the 3rd column follow the second to the proper location. i need to keep the order of column 1 so i can post into a massive spreadsheet. Theres gotta be a quick formula for this i just have no clue
View 2 Replies
View Related
Dec 7, 2011
I have a spreadsheet and I was wondering if there is a way to copy from a specific range in say A1:A5 in Sheet1 and paste into Sheet2 where the column matches cell A1 in Sheet1.
I am trying to do this in VBA and I was wondering if there is a way to do this.
View 9 Replies
View Related
May 6, 2014
The sheet I have attached has three tabs one is a master sheet another is the production input and the last is an engineering input section, I have coded the production input section to input into cells and email the engineers when they have a new job, the issue i have is with the engineering input.
The engineers can currently go into this sheet and input the job number onto the engineering input section and it will show them the current issue from the master sheet (I have already entered 1002 in it), what I want them to be able to do is input into the white sections below the description and when they click on update entry a macro takes the job number entered into B3 and saves the information onto the mastersheet in the correct column/row which corresponds to the job they are updating.
However the engineers may need to go into the same job several times and update different information therefore the fields need to not paste into the cells if the input sections are blank.
Masterfile.xlsm
View 1 Replies
View Related
Jul 10, 2006
I'm trying to use a vlookup or some formulae in sheet 3 that looks at AGDP and matchs column A with a number in statement file worksheet column b. If they match not all of them will then the row is cut and paste into Sheet 3.
View 9 Replies
View Related
Nov 12, 2006
I have 2 worksheets in excel.
1. No. Description Code Req Unit Amount
1 Description 591032 1 pc ?
2 Despription 614804 25 pcs ?
etc.
2. No. Code Description Unit Qty Items Amount
1 591032 Description pc 1 84.30 84.30
2 614804 Description pcs 25 0.89 25.25
etc.
I would like to copy the amount in worksheet 2 to paste it to worksheet 1, by searching using code value. Advice: the codes are in different places in worksheet 1 & 2
View 3 Replies
View Related
Jul 25, 2007
I have a workbook with 3 sheets. AP, Move, Match. AP & Move will be about 40,000 lines.
I need a macro that will take the data from those two sheets and match them up on the match sheet by column A Line by line with all data moving over. An example is attached.
I'm not sure if this is possible, but please let me know if you need more info because I really don't want to do this manually. Anything that doesn't match I just need a blank line. I can add the true-false formula, but if it would fit in the macro that would be cool to.
View 7 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
Mar 9, 2013
I have a list of names in Column B (Starting at B5) with assignments to them in Column A. I want the people who receive the file, to enter their name in B1 exactly as it appears multiple times in sheet. And hope to use conditional formatting to highlight (change the back ground color) of each cell their name appears in.
I've used a number of formulas in the Conditional formatting including "=(ISNUMBER(MATCH($B5:$B100,$B$1,0)))", Countif's and "Not(isnumber)..." but can't find a formula that picks up the whole text.
View 3 Replies
View Related
Mar 3, 2009
I am trying to create a spreadsheet for an online gift registry. They require that the spreadsheet have the photo's url's in a column. I already have the spreadsheet filled with my data. In the spreadsheet, Column D is filled with unique numbers, some with parenthesis, (ex. 52011, 52011(2), 34132, etc.)
I also have a folder full of images that are similarly formatted as such
"...imagesrand_name_52011.jpg". (They will be moved eventually to a webserver.) Each number in the column may or may not have a corresponding image. And the images may or may not have a corresponding number in the spreadsheet. Is there a way to generate a url automatically in a column that corresponds to the image with the matching number? And if it doesn't, just leave it blank?
View 4 Replies
View Related
Oct 24, 2013
Having a bit of trouble trying to get excel to pick up text in one sheet (sheet 2) and populate cells in another (sheet 1) if the row (row 1) labels and columns (column a) in both sheets match. hope that makes sense? I've tried googling this to no avail, i've also tried index-match however i keep getting errors.
View 3 Replies
View Related
Feb 18, 2014
I'm using the code below to compare columns on two sheets (1 column for each) and where there is a match, copy data from the 'Source' to 'Destination' sheet.
[Code] ...........
The code works fine, but I'd like to amend this, but I'm a little unsure about how to proceed.
The script currently compares column C on the 'Source' sheet with column D on the 'Destination' sheet and where a match is found copy column G from the 'Source' sheet and pastes this to column O on the 'Destination' sheet.
I'd still like to copy and paste the same columns, but I'd like to compare two columns from each sheet, so from the 'Source' sheet compare columns C and E to columns D and J on the 'Destination' sheet.
View 2 Replies
View Related
Jan 31, 2014
I am trying to match headers of different files and copy data.If the one of the header is not matched then a new column with the non matching header should be created and the column has to be copied
Ex: file1.xlsx has AA BB CC as header
file2.xlsx has AA BB DD EE as header
file3.xlsx should contain AA BB CC DD EE i.e data from file1.xlsx and file2.xlsx
I have more than 300 files how can I copy all the files to one excel file by matching headers ?
View 1 Replies
View Related
Jan 8, 2008
Does anybody have a code snippet to search and compare 2 sheets.
I want to read the value in a cell on the first sheet, then search the entire second sheet for a match, if it does match then copy the value from certain cells on the second sheet to the row on the first.
View 8 Replies
View Related
Mar 11, 2008
I have to write a macro that will copy the values of 'Items' in 'worksheet 2' to 'Items' in 'worksheet 1' ONLY when the 'Title' value is matching in both worksheets AND the value of ' Heading' in worksheet 2 is 'Entry1'.
Example:
Worksheet 1:
Title |Items
David
Bill
John
Worksheet 2:
Title |Items |Heading
David |A, B, C |Entry1
David |A, B |Entry2
Bill |A, B, C |Entry1
Bill |A, B, D |Entry2
John |G, H, J |Entry1
Gerry |G, H, R |Entry1
From the above data, I want the macro to compare 'Worksheet 1' with 'Worksheet 2' and only copy the 'Items' that have a matching 'Title' AND a 'Heading 1' value of 'Entry1'
So the final result should be:
Worksheet 1:
Title |Items
David |A, B, C
Bill |A, B, C
John |G, H, J
I have seen examples of compare used but not sure how to apply it to my case.
View 8 Replies
View Related
Apr 18, 2008
I have found the following code on this forum that has really worked well. This code matches the first two columns of two worksheets and inputs them into a third worksheet. But I need to be able to copy up to five more columns, a total of seven columns and have it input into the third worksheet. I am not sure what it means "For Each c In Sht1Rng". look at the code and advise me of what I need to do.
Sub FindMatches()
Dim Sht1Rng As Range
Dim Sht2Rng As Range
Set Sht1Rng = Worksheets("Data1").Range("A1", Worksheets("Data1").Range("A65536").End(xlUp))
Set Sht2Rng = Worksheets("Data2").Range("A1", Worksheets("Data2").Range("A65536").End(xlUp))
For Each c In Sht1Rng
Set d = Sht2Rng. Find(c.Value, LookIn:=xlValues)
If Not d Is Nothing Then
Worksheets("Results").Range("A65536").End(xlUp).Offset(1, 0).Value = c.Value
Worksheets("Results").Range("A65536").End(xlUp).Offset(0, 1).Value = c.Offset(0, 1).Value
Set d = Nothing
End If
Next c
End Sub
View 2 Replies
View Related
Dec 24, 2013
I have a set of sheets on a workbook that contain addresses, which are all combined on the first sheet (as you can see in the attached file). I would like to use a formula to compare the addresses on the Main sheet with the other sheets, then return in the D column of Main a colored cell indicating on which sheet the address was found on; different colors for different sheets. I tried to figure out some conditional formatting, but to no avail. Also, on occasion I have spreadsheets with 5 or 6 sheets containing these mixed addresses. Is there a solution that won't be limited to just a few sheets for comparison?
Excel Help.xlsx
View 8 Replies
View Related
Apr 24, 2009
Rylo thanks for your help on the other project. Here is step two for the same project.
Basically I'm going to list a step by step list of what needs to be done then attach some sample sets so you can see what I'm talking about.
Step 1 ........
View 12 Replies
View Related
May 21, 2008
I have some specific requirement and it would be great if I could do it through macro. Also attached a sample excel file for the requirement. I have raw data in a worksheet (WS1) that I receive daily from factory. There is another sheet in a different workbook (WS2) that has format specified on how to display this raw data. The order of raw data on WS1 remains same. The question is that how do I replace the format titles with raw data?
For example, in the attached excel sheet, sheet "data" has list of values. Sheet "format" says how to display data. Sheet "output" is output that I finally want to generate. The sheet "data" will always have data in the same order. I need to read values and replace the format title ( name, age etc) with corresponding values on "data" sheet.
View 5 Replies
View Related
May 10, 2009
I'm building a spreadsheet for a sporting franchise using a pull-down validation menu. Once the opposing team name is selected from the drop down menu, I would like it to display the opposing teams players and their statistics.
On the database worksheet, I have a web query pulling names and statistics from the web. Each player name has his team name in his corresponding row. What function can I use that will search for that team name and report it back to the display sheet? Once I have the player name there, I think I can figure the statistic portion... But I can't seem to figure out how to pull all of the players with the matching team name into my display sheet.
Example:
A1 on worksheet "A" contains a pull down menu with team names "Lightning", "Storm", "Magic", and "Thunder".
A16-A20 on worksheet "B" contains players from team "Lightning"
A21-A25 on worksheet "B" contains players from team "Storm"
A26-A30 on worksheet "B" contains players from team "Magic"
A31-A35 on worksheet "B" contains players from team "Thunder"
B16-B20 on worksheet "B" contains team name "Lightning"
B21-B25 on worksheet "B" contains team name "Storm"
B26-B30 on worksheet "B" contains team name "Magic"
B31-B35 on worksheet "B" contains team name "Thunder"
If I pull down "Lightning" on A1 (worksheet "A"), I want the following to display:..........................
View 4 Replies
View Related
