Matching Cells And Replacing Data
Apr 14, 2009
We have a report with 2 different sheets. On sheet one there are 13,000 rows of data and columns A-H are filled with different data. column A is our item id and column G has a date that states when the item was sold last. Well, the date in column G is wrong for about 7800 rows and needs changed.
The second sheet in the file is the 7800 rows with just the item id in column A and the correct date in column B. We are looking for an easy way to replace the rows in the first sheet with the correct information from the rows in the second sheet without manually replacing it line by line.
I almost forgot to add in, that in both sheets, column A with the item ids are the same item ids on both those sheets. So column A on sheet one has 13000 rows and included in those 13000 are the 7800 item ids on sheet 2.
We tried using the VLookup function that is incorporated into excel but had absolutly no luck, mostly because we have never used this function before. Sheet one already has all the lines of sheet 2, its just that sheet one has the wrong dates for the item ids and sheet 2 has the correct dates. im trying to replace the dates on sheet one from the dates on sheet 2 matching on the item ids from both sheets......
View 2 Replies
ADVERTISEMENT
Jan 5, 2010
I have 4 Columns. Column A has a master list of inventory numbers(about 3000 of them). Column B has dollar figures associated with them. Column C has some inventory numbers in it(800 of them), all of which are also in the master list in Column A. Column D has dollar figures that are associated with the inventory numbers in Column C.
My question is how can I make it so that if an inventory number in Column C equals one in Column A that the dollar figure associated with the inventory number in Column C gets put into its own column on the same row as the inventory number it was equal to in Column A.
I've tried basic IF functions and I can't get it to work because the inventory numbers aren't lined up on the same row. So one may be on row 300 but the other maybe located in row 12. Maybe if a double sort was possible or if someone knows how to make the row in the IF statement a variable.
View 2 Replies
View Related
May 2, 2007
need to add same data to every other existing cell in the column, but not replace the data already in it, but to add to it. I've tried to google the answer and look here, but I probably use bad search terms.
For example, I need to add "QW" after each of these lines:
data1432
data9292
data3933
data3939
so it would look like this:
data1432QW
data9292QW
data3933QW
data3939QW
I have a few thousand rows of data, so wouldn't rather not do it manually cell by cell by typing :-)
View 9 Replies
View Related
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
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
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
View Related
Apr 11, 2003
I am doing a "text to columns" in a VBA script and each time it runs I get a "Do you wnat to replace contents of the destination cells" prompt. I will always say yes to this prompt. Is there something I can put into the code to avoid this prompt all together, again the answer will always be yes to the prompt.
View 3 Replies
View Related
Nov 30, 2011
I have been using this code below to replace any blank cells with the number 0 but recently I have encountered an error if the format of the cell is a string.
Code:
Range("A1").Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.SpecialCells(xlCellTypeBlanks).Value = "0"
Is there a way to change this so it looks at the length of the cell, if the length of the cell is 0 then set the value = 0?
View 6 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
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
Dec 3, 2013
I am having a set of data. Every month I need to filter for some values and replace data in some cells.
In the attached file I have given the data and also the filter conditions and what column I need to change.
creating a macro where in the filters and the changes to be made are in the table. The macro should use these conditions and make the changes.
View 2 Replies
View Related
Dec 11, 2013
how to replace a range of cells in excel with another range of cells for an entire workbook?
The top range being what I want to replace the Bottom Range with for the entire book
snapshot of the File is attached and the highlighting is to show the range and is not part of the workbook formatting.
View 7 Replies
View Related
Jan 20, 2013
My task is to combine two large databases into one spreadsheet by extending the number of columns. The data from each database is 90% matching based on an identification number, however occasionally there are additional rows or unmatched identification numbers that need to be kept for analysis.
When this happens, there needs to be a blank row inserted to represent the missing data in the rest of the corresponding row.
I am having trouble finding a quick way to do this because I have approximately 12,000 rows (and columns up to DV when combined).
for example:
p1
data
data
data
data
p2
data
data
data
data
[Code] .....
needs to become:
p1
data
data
data
data
[Code] .....
I am guessing I will need a macro of sorts, So far I have made one column that tell me if the ID's are matching or not (1 or 0) and if they are not matching (0) I manually insert the rest of the row that is missing or make space for the duplicate data (which needs to remain).
View 5 Replies
View Related
Feb 26, 2013
I'm making a vba macro that takes a workbook and splits and saves it as individual sheets. In this process I need to find all the referenced cells (ie =E5) and change them to their text or numeric value. A few things I've found on the form and tired with no success:
Selection is a column selected from the top to the last used cell (I know there is only 4 columns that have references)
VB:
Selection.Copy
Selection.PasteSpecial Paste = xlPasteValues
Also tried:
VB:
ActiveSheet.UsedRange = ActiveSheet.UsedRange.Value
I feel like one of these should be working. But even after the paste the values are still references.
Edit: An afterthought, if I were to select the whole sheet and do a copy/special paste would it replace all formula cells with their calculated value?
View 5 Replies
View Related
Apr 25, 2014
I'm trying to use the Worksheet_Change event to replace all cleared cells with a default formula. This allows users to override the default, or restore the default when they need to:
If Not Intersect(Target, Range("Data")) Is Nothing And Target.Value = "" Then
Range("DefaultFormula").Copy
ActiveSheet.Paste
ActiveSheet.Application.CutCopyMode = False
End If
Here are the results with various different regions selected:
Single cell selected and cleared -- works. Multiple cells selected and cleared -- doesn't work if the first area selected contains more than one cell. Otherwise, it works, no matter how many other areas are selected.
Example1: Selected range is (D2:E2, G2) in that order, it does not copy the formula into the cleared cells. Example2: Selected range is (G2, D2:E2) in that order, the code does copy the formula into all the cleared cells as desired.
The "IF" condition is not TRUE when the initial area has more than one cell.
View 6 Replies
View Related
Mar 8, 2014
I have data in sheet 1 like below
Custid loc city
123 us newyork
124 uk wales
876 in mumbai
Sheet 2
custid newcustomerid
123 756
124 394
Outputsheet:
Custid loc city
756 us newyork
394 uk wales
876 in mumbai
So it will check sheet1 custid with sheet2 custid. If both matches it will replace custid with newcustid.if cust id not exists it will load sheet1 recprds as usual.
View 1 Replies
View Related
Feb 17, 2010
What it does is to activate the desired worksheet, and for 3 different choices replace what's there with what I want to be there. I put this together a while back, and it's working fine, but I'm sure it can be cleaned up a bit. Any help would be appreciated.
View 9 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
Mar 7, 2012
I have a monthly report with over 2,000 entries per column and it is dynamic (number changes monthly). I'm trying to replace individual department names with "department" but keep certain ones the same. For instance- A,B,C,D stay the same but E-Z need to be changed to "department". I first coded an If then macro but was unable to get it to loop and after researching looping realized that it may not be the best option especially with large datasets. Is an advanced filter macro the best option where I filter NOT A,B,C,D and then change the results to department?
View 9 Replies
View Related
Feb 27, 2014
I have two tables, Table1 one has only customer codes in it, and I have Table2 with plenty of customer codes and those customer name, surname, age, location...
And I want to match and copy each of those customer codes in Table 1 all the information which is on Table2
Table 1:
50025
50026
50086
Table 2: Considering that name, age and location is each in separate cell
50025JohnSecond25Location 1
60085EmilyThird 20Location 2
45454Wilhelm Fourth35Location 3
10000RoseFifth 60Location 4
50086JohnySixth 45Location 5
65501JacobSeventh18Location 6
50026Jackie Eighth22Location 7
And the outcame should be that it finds the value from Table1 in Table2 and copy's information in Table 1 like this:
50025 JohnSecond25Location 1
50026 Jackie Eighth22Location 7
50086 Johny Sixth 45Location 5
View 2 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
Feb 20, 2009
It might be a simple question but ive been trying for over a week now and i just cant do it (probably cos my knowledge of VB is useless )
What i need to do is:
I have 2 worksheets: worksheet 1 Articles and worksheet 2 New Prices.
Whats common on both worksheets is Reference Nš (In the case of worksheet 1 its column E and in worksheet 2 its B), what i need to do is replace the price corresponding to reference nš in worksheet 2 (In column E) with the price of the corresponding reference number in worksheet 1 (in column N).
Its a basic find and replace, but its a list of over 17.000 articles so the best way is VB, so i look through your lists and helps, but i just cant do it.
View 9 Replies
View Related
Feb 21, 2014
When i want delete all my data on my sheet 'Export Freshbooks' and replace it whit new data it says.Data lost.PNG
So i click on ok then... but when i do this all my Formulas Are gone....
My Formulas must stay no matter what.
Test Version - Copy.xlsm
View 5 Replies
View Related
May 21, 2009
I have two sheets - sheet1 and sheet2. Everytime i run a macro in the sheet1and manually copy the rows from sheet1 {starting (A2:AI2)} and paste as values it in the sheet2 {starting (A2:AI2)}.
Then I ll delete the available datas in the sheet1 and will run a macro for different customer.
Again I need to repeat the same action (manually copy and paste the datas to sheet2) without replacing the existing data in the sheet1.
In simple I require a macro code where it copy the available information in sheet1 and paste it as values in the sheet2 without replacing the existing one.
View 14 Replies
View Related
Jan 10, 2014
Basically I've got a back end worksheet with a huge table full of data (hundreds of rows, tens of columns) containing the data for a load of different contracts (each contract is on a different row).
I have all the information I want displayed on a front sheet by means of very simple lookups which looks for one contract at a time to display that information. What I would like to do is to be able to alter that information on the front sheet which will then go back and lookup that entry in the big table and overwrite the old data with the new. Effectively I want the excel sheet to act as a user form, but without actually using a form (a requirement from the people I'm doing this for). I'm struggling to find out how to do this as I don't know which VB functions I would need to use.
Each contract has its own unique reference number, so really what I need is some sort of code that will look for that reference number in the back end table, then look for any differences between what's on the front sheet and on the back end, and then replace anything that's changed.
View 4 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
May 6, 2009
I am new to Excel macros and programming, and am running into a problem that I can't figure out how to solve. I have read through the forum and tried various examples, but I am still not getting the result I am looking for.
I have a spreadsheet that has a variable number of cells in-between blank rows (the data can be 1 to 6 rows). I need to sum up the rows of two columns and see if they equal 0 or if there is a difference. If there is a difference, I want to make the cell red. The exact problem I am having is I can't make it check the # of rows and run the formula based on that number of rows. I am attaching an example of my raw data followed by an example of what I am looking for as the end result.
View 5 Replies
View Related
May 5, 2009
Now that the calculations are working, with the press of a button, I need to be able to select a range of dates and copy all lines within the range to a seperate sheet with the desired name under the same headings they currently reside under. I have included some modified code that is being used in another spreadsheet that was created for me, but I do not pretent to understand all of it and I no longer work with the creator of the spreadsheet. How do I use a button to open the form for date selections and entering the name of the new sheet, and then use the start button on the form to begin the matching and copying to a new sheet? If there is an easier way I am all for that too.
View 14 Replies
View Related
Apr 11, 2008
im looking for a formula that will find matching dates in column a and then i want to see if column b has a matching time in it
View 11 Replies
View Related
May 19, 2009
I have 2 worksheets - main and match. In the main I have something like the following:.....
There are 200 entries in the main work sheet. In the match work sheet I have something like:......
I need to be able to flag if the entry in the match work sheet is already in the main worksheet by using the unique keyID. This flag will be in the match worksheet.
I am using the following array formula: {=IF(A3='[main.xls]Main File'!$A$8:$A$16,"y","n")} and was hoping that this would give me a Y in my flag column but this only shows a N. If I just do a straight if statement then I get a yes: =IF(A2='[main.xls]Main File'!$A$29,"y","n")
View 4 Replies
View Related