Compare The Two Lists And Then Delete The Cleared Checks Off The Original List

Mar 18, 2008

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


ADVERTISEMENT

Compare Number Lists On Different Sheets And Delete Duplicates

Mar 26, 2011

Need 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.

View 9 Replies View Related

VLookup To Compare Two Lists And Add Difference To One List

Sep 1, 2013

I am trying to compare two lists and extract from one to add back to the source. However neither are alphabetically.

List 1
accessories
belt
boots
cardigan
coat
glasses
gloves
hat

List 2
tshirt
tshirt
jumper
jumper
jumper
tshirt
shoes
cardigan

So i would the vlookup to add automatically back to list 1 tshirt, jumper and shoes.

List 1 would now look like this

accessories
belt
boots
cardigan
coat
glasses
gloves
hat

tshirt
jumper
shoes

View 3 Replies View Related

Delete The Original File

Jun 21, 2006

I am writing a macro that copies a pdf from a temporary directory to a permanent directory. After the copy is complete, I want to delete the original file. Looking in Visual Basic Help, it appears that I need to create a FileSystemObject, but I have no idea what that means or how to do it. sDirectory is a string variable of the path of the file and sBatch is a string variable of the name of file. This isn't working.

Set fs = CreateObject("Scripting.FileSystemObject")
sDeleteFile = (sDirectory & sBatch)

Set a = fs.CreateTextFile(sDeleteFile, True)
a.DeleteFile.sDeleteFile

View 4 Replies View Related

Delete Both Original And Duplicate Rows?

May 25, 2014

My worksheet that will have duplicate data inserted into it. Once inserted I need to delete the new duplicate row and the row it duplicated. The attachment is a copy of what the data looks like with the first tab showing what it will look like with inserted data and the second tab is what I want it to look like at the end. I will not need the deleted data again so it does not need to be on a new sheet, that is just for an example. The name of the tabs will also be different so I want to be able to run it on any worksheet. This is the code I am using to find and delete the duplicate but I can't get it to delete the original. I used "First, MI, Last, and DOB" because those are the ones that won't be duplicated where others will. This is a list of about 15,000 entries. There should never be an incident where there is triplicate data but I can't say for sure.

[Code] .....

Attached File : Practice.xlsx‎

View 2 Replies View Related

Delete Original Workbook After Save As

Mar 21, 2008

I don't believe this is possible but I've been wrong before.. Is it at all possible to Kill the current workbook using VBA? I " saved as" in another location and do not wish to keep the current workbook.

View 3 Replies View Related

Save CSV As New Copy & Delete Original

Apr 20, 2008

I was wondering if it is possible to have a macro running that will automatically save a .csv file to specific location and close the file. I would like to have the macro running so that as the files are opened they will be automatically saved to this location.

View 2 Replies View Related

Move Files To Another Folder Delete Original

Sep 8, 2009

i would like to be able to move files from one folder to another then delete the original files.
I dont think im passsing the variables to the functions corectly.

View 8 Replies View Related

Delete Duplicate Rows :: Including The Original

Jan 15, 2010

I have a large worksheet with over 8,000 rows and about 5 columns. In several instances, I have several rows that are all exactly the same. It could be 2, 3, 4 or even more rows that are duplicate to one another.

I would like a macro that deletes ALL rows that are duplicates, including the original.

For example, let's say, rows 3, 4 & 5 are all exactly the same, I don't want to just delete rows 4 & 5, I want to delete row 3 as well!

View 9 Replies View Related

Compare Two Lists

Jun 9, 2009

I have two separate lists, one in Column A and the other in Column B. Both lists are roughly 2,000 rows long. For the most part, the two lists are identical, but not quite.

I'm trying to determine what data is present in Column A that is absent from Column B, and vice versa. That's easy enough to do with COUNTIF (at least that's what I've tried), but to complicate matters, duplicate entries appear in both column A and column B.

For example, in column A, the word "Electric" appears 13 times...but in column B, it only appears 8. I need to know these differences in a quick and easily readable way (remember, the spreadsheet is roughly 2,000 rows).

View 5 Replies View Related

Compare 2 Lists - 6 Columns

Mar 20, 2012

I need to compare 2 lists (each list has 3 columns [first name, last name and email address).

I am trying to compare columns A(incl. First name), B(incl. Last name) and C(incl. email address) to columns D, E and F.

D, E, and F are also First name, Last name and e-mail address respectively.

I need to compare A, B, and C to D, E, and F and find duplicates and highlight them.

View 1 Replies View Related

Compare Innventory Lists

Jan 9, 2009

Every Monday, our database sends me a list in excel, with a snapshot of our inventory.

Here is (simplified) how it looks like

part#, desc, color, units
001 Camry Black 13
003 Toyota Green 2
009 Honda Yellow 17

and so on for about 8k rows.

Every Monday, the list is different. we sold some items, so the "units" in stock are less, we run out of some items, so that part# is not in the list,
we acquired some new items, with a different part#, and new amount of units, some part#s have more units in stock.

I need to send to our boss, a spreadsheet with a list of all part#s in stock every Monday, and the last 4 weeks, how many we sold of each in the last 7 days, so we can try to get rid of inventory that is getting obsolete.

It would be easy to do, if not for the fact of the "new" items that come in, and the items that we run out of, and are not in the list anymore. With 8k items, to to the corrections by hand, it gets old very quickly

View 10 Replies View Related

Compare Lists Of Data

Jun 5, 2007

I need to search a column of numbers in Excel (A) and compare it to another column (B). I need to find all of the numbers that apprear in column A but not in column B. Can this be done?

View 4 Replies View Related

Compare Values Between 2 Lists

Nov 26, 2007

how to compare the value in one cell on sheet "Block #1" to a list of values on sheet "Data". Specifically I want to be able to look at the value in a cell (which will be a date) and then compare it to a list of company holidays on another sheet in the workbook and if the date is a company holiday it will display the word "Holiday" in another cell I specify on "Block #1" I am doing this to automate generation of a calendar which will have 13 worksheets one for each of 13 4-week blocks. I can get the calendars generated, but can't seem to be able to get it to compare the values of each date to the list of holidays.

View 3 Replies View Related

Compare 2 Lists & Return Details

Nov 22, 2006

I have a list of two rows of over 20,000 items. I would like to know what data has been added to the second row from the first row and what has been removed.

View 4 Replies View Related

Compare Lists Flag Differences

Jan 9, 2007

I am currently trying to work out what the best way would be to search between two sheets and find out where the differences are. IO have sheet 1 and 2 which both have account numbers and details on. I searching off the first column on both sheets and trying to identify which records exist on sheet 1 but not on sheet 2. The code I have so far is

Sub check()
data_sheet = "Sheet1"
target_sheet = "sheet2"
rowcn = 2
Do
If Sheets(data_sheet). Cells(rowcn, 1) <> Sheets(target_sheet).Cells(rowcn, 1) Then
Rows(rowcn).Select
Selection.Font.ColorIndex = 3
End If
rowcn = rowcn + 1
Loop While Sheets(data_sheet).Cells(rowcn, 1) > 0
End Sub

This currently works 50%. Only problem is that this code is not independant to each list, it simply looks at the same cell reference on both sheets and check whether the value is there. It doesnt actually go down the whole list and checks whether it is there.

View 5 Replies View Related

Compare Lists & Highlight Duplicates

Jul 3, 2007

I receive a daily spreadsheet which contains (amongst other information) a column of postcodes. I'd like to run a macro which compares these postcodes with a list of other postcodes, and then to colour highlight each instance of that post code in the daily spreadhseet.

I have been able to find and modify a macro but that searches for one post code at a time and then adds a comments box to the cell.

Instead of copying that piece of code and editing it for the several hundred post codes I need to "look up" against, I'd like to know how to create a list of post codes in the macro, so that the macro searches the first post code in the lookup list, finds and highlights each cell instance, then moves onto the next post code in the lookup list, until the end of the lookup list. I would like this as the editing of the lookup list would be much easier than editing paragraphs of code.

View 9 Replies View Related

Compare 2 Lists & Return Non Dupilcates

Nov 25, 2007

i have a list in column "a" in sheet 1 and a list in sheet 2 column "b" range from row 1 to 50

i would kike to get the data from sheet1 that is not in sheet 2 and copy it in to sheet 3

View 6 Replies View Related

Highlight Entire Row When Cell Selected Without Losing Original Formats And Color Of Original Row

Sep 5, 2012

The problem is when I highlight a row with some color the original color of the row is gone, so I tried this code, and again, it's removing the original format and color for the row This is the code from McGimpsey & Associates : Excel : Highlight row with background colors

Code:
PrivateSub Worksheet_SelectionChange(ByVal Target As Excel.Range)
Const cnNUMCOLS AsLong=256
Const cnHIGHLIGHTCOLOR AsLong=36'default lt. yellow
Static rOld As Range
Static nColorIndices(1To cnNUMCOLS)AsLong
Dim i AsLong
IfNot rOld IsNothingThen'Restore color indices

[code].....

How can I retain the range's historical color so that when I deselect the row it reverts properly?

View 4 Replies View Related

Compare Two Similar Lists To Determine What Is Missing?

Jan 29, 2014

is there a formula I could use to compare two similar lists to determine what is missing, for example an old list of 200 lines and another of 275 lines, with a first column containing unique ID code.

View 1 Replies View Related

How To Compare And Combine Data From New And Old Product Lists

Mar 19, 2014

I have two spreadsheets full of products one of new products and the other of old products. I want to filter out the old ones that are discontinued but keep the ones that are still there. The old list has data I want to preserve for the new listings that would be duplicates from the old ones. I want to delete the new listings that already exist in the old list. I'm not sure exactly how to do it. I tried to delete dups based on product id or name but I'm afraid it's deleting entries I want to keep. Both lists contain over 5000 products each. I need to compare two lists and filter out the old products then work on removing the dupes.

View 3 Replies View Related

Compare Two Email Lists And Output Non-matching

Nov 20, 2008

I am a newbie and need your expert help please.

I have two email lists. For example they are:

List 1:

a@spam.com
b@spam.com
c@spam.com

List 2:

abcdef@spam.com
b@spam.com
c@spam.com

In list 1 the non-matching address is a@spam.com. namely it is not common to both lists. I want to automatically go through the lists and output firstly a new list of the non-matches from column 1 and also a new list of the non-common items from list two.

So in this case the result would be that somewhere there is a new list from list 1 that has "a@spam.com" and secondly there is a new list from list 2 that has "abcdef@spam.com."

Obvioulsy the real list is more complicated and longer but the principle will be the same.

View 10 Replies View Related

Compare And Display Similarities/Differences In Two Lists.

Jul 24, 2009

I have searched some of the other forums but have not found anything specifically related to what I'm trying to do.. An example is attached. Basically just want to compare data between two different lists and then in two additional columns (Differences & Similarities) display that corresponding data.

View 3 Replies View Related

Compare Two Lists And Output Unmatched Items

Apr 17, 2013

I have two lists that I have compare to compare.

Expected result: Mark items matched in next to them, output unmatched items in column "J"

I started the code below. It properly marks the correct items, but I have trouble outputting them in Column "J"

Sub BNKREC()
Counter = 0
For i = 3 To 9
For J = 3 To 6
If Cells(i, "B").Value = Cells(J, "F").Value And Cells(i, "C").Value = Cells(J, "G").Value Then

[Code] .....

List 1:
Date Check# Amount
03/02/2013 100 $1,000.00
03/03/2013 101 $1,045.25
03/05/2013 102 $280.56
03/06/2013 103 $456.31
03/08/2013 104 $250.55
03/15/2013 105 $2,456.12
03/25/2013 106 $844.76

List 2:
Date Check# Amount
03/02/2013 100 $1,000.00
03/05/2013 102 $280.56
03/08/2013 104 $250.55
03/25/2013 106 $844.76

Expected result:

Un-Matched List:
03/03/2013 101 $1,045.25
03/06/2013 103 $456.31
03/15/2013 105 $2,456.12

View 5 Replies View Related

Compare Two Lists & Save Duplicates To New Sheet

Jan 11, 2008

The Master list:
Sheet1, Column C has unique numbers, i.e, 0608-211-1093-2.

(can have thousands of entries)

The Comparison list:
Sheet2, Column C will have duplicates of some of Sheet1, Column C numbers.

(Sheet2, Column C will have the numbers but other columns will have different
data than Sheet1 and may have a few hundred entries or less)

I need a macro that will that will compare the two sheets for duplicates based on
Column C and then write the entire row of Sheet1 with the duplicate number to a new Sheet3.

View 9 Replies View Related

Visual Basic - Compare Two Lists And Report On The Differences?

Mar 29, 2012

I have 2 workbooks and I must list the differences between them. Here is an example of what I need to do -

Workbook 1

Column1Column2DUE0.044433NHF0.068691CBA0.059029RMD0.051539CSL0.047868WOW0.047744

Workbook 2

Column1Column2DUE0.044433NHF0.068691CBA8AGI5WOW0.047744

The report to look like this

Column1Column2StatusCBA8modifiedRMD0.051539deletedCSL0.047868deletedAGI5added

View 2 Replies View Related

Excel 2003 :: Compare Two Lists Of Names On Same Spreadsheet

Apr 18, 2012

In Excel 2003 I am trying to compare two lists of names on the same spreadsheet to determine which names are different between the two columns of names. What is the easiest way to do that?

View 1 Replies View Related

Macro That Compare Items In Two Seperate Lists In A Spreadsheet

Jan 20, 2009

I am trying to create code for a Macro that will compare items in two seperate lists in a spreadsheet, and eliminate all entries that do not appear in both lists.

View 9 Replies View Related

Compare 2 Lists On 2 Sheets And Output Non Duplicates To A Third Sheet

Feb 19, 2009

I need a macro to compare the values in column b across 2 sheets and output the rows that do not have duplicate values in column b to a third sheet?

View 3 Replies View Related

Sort Unique, Then Copy From Original List

Mar 1, 2004

I have a CSV spreadsheet full of data: many rows and many columns. I want to sort based on values in some column. Then, I want to divide my large worksheet into smaller worksheets based on these sorted column values. For instance, if column 4 was 20,000 items long and contained 6 unique values I would want to create 6 smaller spreadsheets. Each spreadsheet would still have a column 4. Now, however, all the column 4 values would be the same for a particular spreadsheet. It would be nice to have a pop-up window query me for which column to use to divide the data with, instead of hardcoding a value in (like 4).

I am trying to crank this CSV file through MATLAB and it is just too big. My PC has 1GB memory and it still craps out. I am guessing that a dozen or so smaller files will be easier for MATLAB to digest. Any help is appreciated!

For the record, I am doing the following to sort a column, then copy it to a scratch worksheet and then determine all the unique values. I have hardcoded in column lengths and would really like to know how to make these variable expressions:

Sub divide_and_conquer()

Dim lngSheet As Long
Dim strName As String

Range("A1:HR778").Sort Key1:=Range("D2"), Order1:=xlAscending, Key2:= _
Range("B2"), Order2:=xlAscending, Key3:=Range("E2"), Order3:=xlAscending _
, Header:=xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:= _
xlTopToBottom, DataOption1:=xlSortNormal, DataOption2:=xlSortNormal, _.............

View 9 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved