I have several (Child) sheets of data which I need to compare to a (Parent) master sheet. If a cell in column "A" on a child sheets matches a cell in column "A" on the parent sheet, I need the row on the Parent sheet deleted so in the end, only the rows with no Child matches are left on the Parent sheet.
View 4 RepliesI have 1 workbook, with 3 sheets. Sheet1 (EVER) has 3000+ rows and 12 columns of customer information. This sheet is for all customers who have ever placed an order. Sheet2 (06-07) has 1500+ rows and 12 columns of customer information. This sheet has all customers who have placed an order in the last 2 years. Sheet3 has 1 row, which consists of the column titles (12 columns) that are on Sheet1 and Sheet2.
I need to put all customers that are on Sheet1, but not on Sheet2 in Sheet3. I have tried VLookup; advanced Filter and a number of codes in the last 3 days and have not been able to figure this out.
Basically I have two sets of data. One will be new each week. I'd like to use the non-changing data as a base to compare new data to. The formula would need to match multiple values, including a 'time between', and then return whether a minimum rate has been met.
SampleRateExamine.xlsx
I have one sheet that have +/-5000 data in 4 table ... all table is fill with just only 2 column, first column is Code Number and second column is Quantity.
There are many duplicates of different entries in Column 1st in all table range and I need to add up their quantities to new table in new sheet...
This is for example:
(Sheet1 name="INPUT") (table1=A:B) (table2=E:F) (table3=I:J)
A B E F I J
AB123 100 ABC123 20 AA1 10
AC278 50 BCD547 30 AA2 10
AB123 30 FGE988 30 AB3 10
DE586 120 ABC123 50 AA2 10
RE158 80 BCD547 120 AA1 10
DE586 50 ABC123 70 AD4 10
Then I want to use macro vba to create new sheet(Summary Report),copy,paste,sort and sum all duplicate...
It should look like this
(Sheet1 name="Summary_Report")
A B E F I J
AB123 130 ABC123 140 AA1 20
AC278 50 BCD547 150 AA2 20
DE586 170 FGE988 30 AB3 10
RE158 80 AD4 10
I have multiple sheet in excel.All the excel sheet has a table with formula.Now i want to delete all formula in my all excel sheet at one time without delete/loosing my values.
View 4 Replies View RelatedI am trying to write some code that will compare three cells on a row and if they match then it will delete the row.
Column U-has Y, N entered
Column V-has Y, N entered
Column O-has 1, 2, entered (some cells may have the fill color set to red)
I need the macro to look at Column U and Column V and for example if the cell U2 has a N, and V2 has a Y then the macro would look at O2 and it there is a 1 with the fill color set to red the macro will delete that row.
The spreadsheet will vary on how many rows it contains. It can contain up to 5,000 rows and the macro would need to go through all the rows.
Attached is a sample data set. If all the positive and matching negative entries are located and removed, there are 4 remaining entries. The only pertinent columns are A through J, the columns after that are where my fiddling is occurring.
First you have to highlight all the data (including the control columns to the right). Then, my macro sorts the data by the absolute value (found in column O), allowing the formulas in N to then properly mark all negative entries with an "X and all the ones that now have a matching positive entry directly below it with an "X".
Then my macro hides all the fields with an "X" in column N. There is a macro called "CLEANSE" that does this, but only hides the rows. I keep messing up when I try to delete them entirely.
Can someone correct my macro so it will properly delete the rows with an "X" in the N column, or suggest a better macro that will scan the E column and flag matching positive/negative numbers and delete them without my control columns N and O?
i need a macro that will compare the p'folios in sheet "Data to Sheet Workings, and then list the p;folios which is in sheet Data but not in workings in Sheet "New", so in the example below this would be ETSTSA ...
View 9 Replies View RelatedI have a spreadsheet with 4 columns.
I would like to Compare Column A with Column B, if Column A contains text or numbers anywhere in Column B, then remove the corresponding rows not just in Column B, but Column C and D as well for that row.
This compares cells in every sheet to a master sheet "AllUnits" and deletes all the matched rows on the Master. I modified it slightly to also delete the matched row on the source sheet as well (looking for rows that are not matched on any sheet). This works to a point, but I have to re-run it many times since when the source row is deleted, the code actually skips to the next row. I tried to reset the source variable with
View 2 Replies View RelatedI have two sheets with about 7000 records in each. They both have two columns only. One is the master list. I want both of the files to be compared and then all the duplicate entries to be deleted from both files.
Ultimately I want to have only the entries in the master sheet that are not in the other sheet.
I have a data output from citrix that looks like the top half of the attached file. Each ID has 2 rows of data; open orders & recommended orders. Is there a macro that can sum up the two rows of data on to the first row (the one with ID and description in Collumn A & B) and delete the row afterwards, leaving only one row of data (format can be seen in the bottom half of the attached file). The original citrix report has hundreds of product IDs, which may vary from month to month. Original Format.xls
View 2 Replies View RelatedI an using Excel 2003. In that i am having 2 columns (A&B). I need to take each value from Column A and search that cell value in Column B. If it is not found in Column B then delete the cell data in Column A. Then take the next cell data in Column A. Find operation and delete if not found. Do it for entire rows in column A. Then repeat that for Column B also. So my final result will be the same cell value in Column A and B. I need VBA code.
View 2 Replies View RelatedI an using Excel 2003. In that i am having 2 columns (A&B). I need to take each value from Column A and search that cell value in Column B. If it is found in Column B then delete the cell data in Column A. Then take the next cell data in Column A. Find operation and delete if found. Do it for entire rows in column A. Then repeat that for Column B also. So my final result contains uncommon cell value in Column A and B. I need VBA code.
View 3 Replies View RelatedFind the attached : example.xlsx
My requirement is compare two cells a1 & a2 if found true highlight a2,compare a2 & a3 if found same highlight a3 or compare a3 and a4 and repeat the same for the entire sheet.
I have column A that is 1,500 records.
I have column B with 40,000 records.
Among the 40,000 records in column B, duplicates of ALL 1,500 records from column A exist.
My question is: How do I find WHERE the 1,500 dupes are and how can I delete JUST those records?
I am fitting 2 sets of data to match eachother. I want to have as much data as possible but only for the same dates of each dataset.
I have 4 coloumns.
Dataset 1: A is date B is data
Dataset 2: C is date D is data
I want to compare A vs B one row at a time. If they are not similar it should delete and shift up the values in A+B or C+D according to the lowest value (date) of A vs B.
Remember that if the macro just deleted and shifted up it should compare the same (undeleted) value against the new value that just shifted up in the other coloumn.
VB code to carry out the following task?
Look at name in cell BC2, check the name in cell BA2 and if is the same delete the name in BA2 and BC2.
If is different then leave both cells as they are and move onto checking BC3 against BA3
carry this loop thru BC2 : BC40
I have a column of dates (B2:B1500) that I want to compare to a single date (A1).
If the dates in Column B are older than the date in A1 I want to delete the entire row.
I have a large data file. In column A is 8760 entries (every hour of every day of the year). Columns D & E are linked, Column D is the time and date whilst Column E is a parameter assocaied with that time and date.
There should also be 8760 entries in these columns, however there are some missing data lines in columns D & E. I would therefore like to move the rows in columns D & E so they match with the entries in column A. Can anyone help? I have a headache as i have over 70 files to complete this operation on.
I have I workbook that contains 2 sheets. The first sheet "Business Objects" is the master list. The second sheet "Gene" contains similar data but is incomplete. There is a unique identifer for both sheets and that is item ID. what I would like to do is look at the Item ID column on sheet1 compare it to SS# on sheet two and copy any rows from sheet1 to the end of the sheet in sheet 2. I have found in my searches on this forum a bit of code that identifies the ones in sheet1 that are not in Sheet2 and highlights them red(which is not neccessary for me, but I am struggling to figure out how to take that and paste it to sheet2.
View 3 Replies View RelatedNeed to find a column of numbers from one worksheet and delete them from another.
Example
To be deleted is below:
83221
83223
83232
83233
83236
to be deleted from is below:
83221
83223
83226
83227
83228
83229
83230
83232
83233
83234
83235
83236
Another issues is that the worksheet that needs to have the numbers deleted from has 3 columns total so the whole row would need deleting, or if its easier I could replace the numbers with 0 and then sort and delete.
I've got two workbooks, Workbook1 with a list in column a and Workbook2 with a list in column F. I want to compare the cells in these columns and delete the entire row in Workbook2 if there is a match.
View 3 Replies View RelatedI have date on Sheet 1 and Sheet 2
what I want to do is take column A from Sheet 1 and compare to Sheet 2 column A if it's one Sheet 2 then on Sheet 1 column C and D post the results...
ie
Sheet 1
A B C D
ADDYY 64
Sheet 2
ADDYY4644454654
so then Sheet 1 would look like this
A B C D
ADDYY 64 ADDYY4644454654
how to assign values into arrays. I need to compare two sheet (sh1 & sh2), range a2:c20 using array function and get the duplicate values in sheet3.
I am trying to create a way to easily maintain a list of outstanding checks. I am able to import cleared checks into the spread sheet but cannot find a way to compare the two lists and then delete the cleared checks off the original list.
View 2 Replies View RelatedI would like a macro to find the columns named "apple" and "peach" and delete them. These would always be in row 1 but would always be in different column letters which is why I want the macro to simply find these columns by their name and not by their column letter.
And yes, I do mean the entire column altogether, shifting entire columns to the left. Wipe it off the face of the earth
I have the following code to compare two columns and delete adjacent rows if 1 is greater than or equal the other...
Sub LastReceipt_GT_Confirmed()
Dim intLstRow As Integer
For intLstRow = Range("E" & Rows.Count).End(xlUp).Row To 2 Step -1
With Range("E" & intLstRow)
If .Value > .Offset(0, 1).Value Then .EntireRow.Delete
End With
Next intLstRow
For intLstRow = Range("E" & Rows.Count).End(xlUp).Row To 2 Step -1 .............
Example, I have a sheet named DATA1, I want to add a new worksheet, copy a certain range from the DATA1 sheet to the new sheet and rename it the same name DATA1 and delete the old DATA1 sheet.
Also, the sheetname will be unknown, the macro must get the sheet name first.
[data] ...
I have three sheet. named Sheet1, Sheet2 and Sheet3.
Sheet1 being the master sheet. What I want to achieve is:
I want to search for Name in sheet1 with the list of names in Sheet2,
If the name is found, then compare the Age in Sheet1 and Sheet2. If the age matches make an entry in Sheet3 "matched" else "didnt match".
If the name itself is not found on sheet2 , make an entry saying not found.
I am not sure what would be the best approach to solve the problem(formula/VB)...