"Cleaning" Microsoft Excel worksheets

Hi all,

I have a question for you Microsoft Excel experts out there;

I have two different Excel files (both holding similar data).
Ex:
File 1:
2031 - Name - Address - Location

File 2:
2031 - Name - Address - Location
2034 - Name - Address - Location

I would like a way to find items from File 1 in File 2, and then subsequently be left with only those that are not found in File 2.

The result; A new worksheet only holding the line:
2034 - Name - Address - Location

Hope someone here can assist me in finding a solution for this.

Sincerely,

Geir Andersen
LVL 6
geir_andersenSystems engineerAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

XGISCommented:
Hello Geir
Please check this link..it is a stepwise process that may help you resolve this problem
cheers
AaronRemove extraneous values from 2nd file
0
geir_andersenSystems engineerAuthor Commented:
Hi Aaron,

This seems to apply only if the values are found in the same cell in both workbooks.
In my case it may exist in any location, in a column.

Ex:
File 1;
Index{A} - Name{B} - Address{C} - Location{D}

2031{A1} - Test1{B1} - Test1{C1} - Test1{D1}
2034 {A2} - Test2{B2} - Test2{C2} - Test2{D2}

File 2;
Index{A} - Name{B} - Address{C} - Location{D}

2031{A12} - Test1{B12} - Test1{C12} - Test1{D12}

[Workbook1.xls]Sheet1!A1 does not necessarily match [Workbook2.xls]Sheet1!A1, but can be found in any of the cells in column A in Workbook2.

The column A (Index), is present in both workbooks and is the search key.

I'm not very good at explaining, but I hope this is somewhat understandable.


-Geir
0
KimputerCommented:
Try this VBA code ? It asks for a file1 and a file2
It runs through all cells from column a in file1, and if found in columna in file2, it will delete that row in file2, continue till all values in colA file1 are done. Copy sheet from file2 to a new sheet.
Of course, it means the header is also deleted, hope you don't mind.

Sub test()

Dim wb1, wb2, wb3 As Excel.Workbook

Set wb3 = Workbooks.Add
NewWorkbook = ActiveWorkbook.Name
myfile1 = Application.GetOpenFilename
Set wb1 = Workbooks.Open(myfile1)


Dim arng As Range
Range("A1").Select
Range(Selection, Selection.End(xlDown)).Select
Set arng = Selection.CurrentRegion
arngcount = arng.Rows.Count

myfile2 = Application.GetOpenFilename
Set wb2 = Workbooks.Open(myfile2)


Dim brng As Range
Range("A1").Select
Range(Selection, Selection.End(xlDown)).Select
Set brng = Selection.CurrentRegion
brngcount = brng.Rows.Count

For i = 1 To arngcount
    Found = 0
    For j = 1 To brngcount
        If wb1.Sheets(1).Cells(i, 1).Value = wb2.Sheets(1).Cells(j, 1).Value Then
            Found = 1
            Exit For
        End If
    Next
    If Found = 1 Then
         wb2.Sheets(1).Rows(j).Delete
    End If
Next

wb2.Sheets(1).Cells.Copy
wb3.Sheets(1).Paste
End Sub

Open in new window

0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

geir_andersenSystems engineerAuthor Commented:
@Kimputer:
I tried running this code, but every time it stops with the error message 1004...
0
KimputerCommented:
Is it possible you provide the two files ?
Of use the Step In macro (or F8 when you have to code in Excel's VBA view), see which line gives this error?
0
geir_andersenSystems engineerAuthor Commented:
Sorry for taking time to reply..
It seems to stop on this line:
Range("A1").Select

Unfortunately I can't provide the files, as they contain sensitive data...

I won't be working with the files again, until tomorrow morning, but I'll have another look at them then.

Thank you for your patience!

-Geir
0
KimputerCommented:
Slight code change. BUT, if it doesn't work, is it still possible to leave column A the way it is, but fill in the name address etc with "garbage" (just copy and paste "abc" to the whole range)

Sub test()

Dim wb1, wb2, wb3 As Excel.Workbook

Set wb3 = Workbooks.Add
NewWorkbook = ActiveWorkbook.Name
myfile1 = Application.GetOpenFilename
Set wb1 = Workbooks.Open(myfile1)


Dim arng As Range
wb1.Activate
wb1.Sheets(1).Range("A1").Select
Range(Selection, Selection.End(xlDown)).Select
Set arng = Selection.CurrentRegion
arngcount = arng.Rows.Count

myfile2 = Application.GetOpenFilename
Set wb2 = Workbooks.Open(myfile2)


Dim brng As Range
wb2.Activate
wb2.Sheets(1).Range("A1").Select
Range(Selection, Selection.End(xlDown)).Select
Set brng = Selection.CurrentRegion
brngcount = brng.Rows.Count

For i = 1 To arngcount
    Found = 0
    For j = 1 To brngcount
        If wb1.Sheets(1).Cells(i, 1).Value = wb2.Sheets(1).Cells(j, 1).Value Then
            Found = 1
            Exit For
        End If
    Next
    If Found = 1 Then
         wb2.Sheets(1).Rows(j).Delete
    End If
Next

wb2.Sheets(1).Cells.Copy
wb3.Sheets(1).Paste
End Sub

Open in new window

0
geir_andersenSystems engineerAuthor Commented:
It's still stopping in the early stages.
This time it's stopping on this line:
Range(Selection, Selection.End(xlDown)).Select

The two files are rather large, 2505 rows in one and 3395 in the other.
The 2505 rows should be matched against the 3395 rows in the second file.

-Geir
0
KimputerCommented:
Slight code change again.

Sub test()

Dim wb1, wb2, wb3 As Excel.Workbook

Set wb3 = Workbooks.Add
NewWorkbook = ActiveWorkbook.Name
myfile1 = Application.GetOpenFilename
Set wb1 = Workbooks.Open(myfile1)


Dim arng As Range
wb1.Activate
wb1.Sheets(1).Range("A1").Select
wb1.Sheets(1).Range(Selection, Selection.End(xlDown)).Select
Set arng = Selection.CurrentRegion
arngcount = arng.Rows.Count

myfile2 = Application.GetOpenFilename
Set wb2 = Workbooks.Open(myfile2)


Dim brng As Range
wb2.Activate
wb2.Sheets(1).Range("A1").Select
wb2.Sheets(1).Range(Selection, Selection.End(xlDown)).Select
Set brng = Selection.CurrentRegion
brngcount = brng.Rows.Count

For i = 1 To arngcount
    Found = 0
    For j = 1 To brngcount
        If wb1.Sheets(1).Cells(i, 1).Value = wb2.Sheets(1).Cells(j, 1).Value Then
            Found = 1
            Exit For
        End If
    Next
    If Found = 1 Then
         wb2.Sheets(1).Rows(j).Delete
    End If
Next

wb2.Sheets(1).Cells.Copy
wb3.Sheets(1).Paste
End Sub

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
geir_andersenSystems engineerAuthor Commented:
Thanks for your patience Kimputer, I'm running some tests now and it's not stopping this far.

My description of the columns was just as an example, and the actual files have columns with different names and there are a lot more of them.

I will of course let you know how it turns out.

-Geir
0
geir_andersenSystems engineerAuthor Commented:
@Kimputer:
Thank you for helping me sort this! As far as I can see, this has resulted in leaving me with only the unique values in a new spreadsheet.

-Geir
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Office

From novice to tech pro — start learning today.