Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old January 22nd 07, 09:13 AM posted to microsoft.public.excel.programming
ap ap is offline
external usenet poster
 
First recorded activity by ExcelBanter: Jan 2007
Posts: 3
Default Complex comparison of Columns of Data: Extracting unique records after comparison on 4 levels

Dear all,

as a relative VBA newbie I seem to have gotten into something a little
bit over my head :S

I have two lists on two different sheets, which I need to compare on
multiple levels. Usually I can manage by browsing this group and
creatively combine some of the previous solutions. However, atm I am at
a loss...

I have (as an example) on the first row of Sheet1:
Identifier (col A) b c d
VS0067157023 5 2424 2425
VS0067157024 5 2431 2433

Sheet2:
Identifier (col A) b c d
VS0067157023 5 2424 2425
VS0067157024 6 2431 2433
VS0067157055 6 2400 2433

Now comes the hard part (sorry if my english is not perfect...) I need
to compare this with the data on Sheet2:

- if there is an exact match on all four levels (A to D) in any row on
Sheet2 nothing happens
- if there is a mismatch on any level -- the record with the
identifier VS0067157024 needs to be written to Sheet3
- This example record is on row 2 on Sheet1, but might not be on row 2
on Sheet2 but on any row on Sheet2
- After is have done this, I also need to write the unique records from
Sheet2 to a Sheet4 (in this example the VS0067157055 record)

Two things I do know:
- on both sheets there are no empty cells in the list of Identifiers.
(Except after the last one ofc...)
- the data has the same format on both sheets, i.e. the same columns on
both sheets.

Any help would be greatly and utterly appreciated!

H.


  #2   Report Post  
Old January 22nd 07, 01:40 PM posted to microsoft.public.excel.programming
ap ap is offline
external usenet poster
 
First recorded activity by ExcelBanter: Jan 2007
Posts: 3
Default Complex comparison of Columns of Data: Extracting unique records a

Dear John,

thank you very much for you reply! After trying out your code, i
noticed that i was getting only the exact matches between the two
sheets. However, i was looking for all the records that have no exact
match.
Ofc this can be corrected with a simple insertion of the Else statement
in your very clear code. (See below).

Two remaining questions:

How do i get the records on Sheet2 that are unique to that sheet?
("VS0067157055 6 2400 2433" in the example)
Should i just copy paste this in a main2 and reverse the Sheetnames ?

Do you have a 'trick' or way to make the code less bulky?
(Though it is true that this was very easy to understand, and thus very
helpful and didactically apt.)


This is the code that i have now, which does the trick:

Sub main()
Dim myCheck1 As String
Dim myCheck2 As String
Dim myCheck3 As String
Dim myCheck4 As String
Dim lastCell1 As Long
Dim lastCell2 As Long
Dim lastCell3 As Long
Dim myRow As Long
Dim newRow As Long
Dim myCol As Long


lastCell1 = Sheets("sheet1").Cells(Rows.Count, "A").End(xlUp).Row
lastCell2 = Sheets("sheet2").Cells(Rows.Count, "A").End(xlUp).Row
lastCell3 = Sheets("sheet3").Cells(Rows.Count, "A").End(xlUp).Row


myRow = 1 'change this to the first row containing data
myCol = 1 ' change this to the first column containing data
newRow = 1


For myRow = 1 To lastCell1
myCheck1 = Sheets("sheet1").Cells(myRow, myCol)
myCheck2 = Sheets("sheet1").Cells(myRow, myCol + 1)
myCheck3 = Sheets("sheet1").Cells(myRow, myCol + 2)
myCheck4 = Sheets("sheet1").Cells(myRow, myCol + 3)
'MsgBox myCheck1 & ", " & myCheck2 & ", " & myCheck3 & ", " & myCheck4
For newRow = 1 To lastCell2
If myCheck1 = Sheets("sheet2").Cells(newRow, myCol) Then
If myCheck2 = Sheets("sheet2").Cells(newRow, myCol + 1) Then
If myCheck3 = Sheets("sheet2").Cells(newRow, myCol + 2) Then
If myCheck4 = Sheets("sheet2").Cells(newRow, myCol + 3)
Then
'Sheets("sheet3").Cells(lastrow + 1, 1) = myCheck1
'Sheets("sheet3").Cells(lastrow + 1, 2) = myCheck2
'Sheets("sheet3").Cells(lastrow + 1, 3) = myCheck3
'Sheets("sheet3").Cells(lastrow + 1, 4) = myCheck4
'Nothing Should happen if all 4 Checks match, but if
they don't...
'
Else
Sheets("sheet3").Cells(lastCell + 1, 1) = myCheck1
Sheets("sheet3").Cells(lastCell + 1, 2) = myCheck2
Sheets("sheet3").Cells(lastCell + 1, 3) = myCheck3
Sheets("sheet3").Cells(lastCell + 1, 4) = myCheck4

End If
Else
Sheets("sheet3").Cells(lastCell + 1, 1) = myCheck1
Sheets("sheet3").Cells(lastCell + 1, 2) = myCheck2
Sheets("sheet3").Cells(lastCell + 1, 3) = myCheck3
Sheets("sheet3").Cells(lastCell + 1, 4) = myCheck4

End If

Else
Sheets("sheet3").Cells(lastCell + 1, 1) = myCheck1
Sheets("sheet3").Cells(lastCell + 1, 2) = myCheck2
Sheets("sheet3").Cells(lastCell + 1, 3) = myCheck3
Sheets("sheet3").Cells(lastCell + 1, 4) = myCheck4


End If

Else
Sheets("sheet3").Cells(lastCell + 1, 1) = myCheck1
Sheets("sheet3").Cells(lastCell + 1, 2) = myCheck2
Sheets("sheet3").Cells(lastCell + 1, 3) = myCheck3
Sheets("sheet3").Cells(lastCell + 1, 4) = myCheck4

End If
Next
Next


End Sub


John Bundy (remove) wrote:
Caught an error so as not to confuse, here is all of it, if you notice i
named my variable wrong at the bottom, and didn't put lastcell3 in a place
that would increment properly.
We all learn!!

Sub main()
Dim myCheck1 As String
Dim myCheck2 As String
Dim myCheck3 As String
Dim myCheck4 As String
Dim lastCell1 As Long
Dim lastCell2 As Long
Dim lastCell3 As Long
Dim myRow As Long
Dim newRow As Long
Dim myCol As Long

lastCell1 = Sheets("sheet1").Cells(Rows.Count, "A").End(xlUp).Row
lastCell2 = Sheets("sheet2").Cells(Rows.Count, "A").End(xlUp).Row


myRow = 1 'change this to the first row containing data
myCol = 1 ' change this to the first column containing data
newRow = 1

For myRow = 1 To lastCell1
myCheck1 = Sheets("sheet1").Cells(myRow, myCol)
myCheck2 = Sheets("sheet1").Cells(myRow, myCol + 1)
myCheck3 = Sheets("sheet1").Cells(myRow, myCol + 2)
myCheck4 = Sheets("sheet1").Cells(myRow, myCol + 3)
'MsgBox myCheck1 & ", " & myCheck2 & ", " & myCheck3 & ", " & myCheck4
For newRow = 1 To lastCell2
lastCell3 = Sheets("sheet3").Cells(Rows.Count, "A").End(xlUp).Row
If myCheck1 = Sheets("sheet2").Cells(newRow, myCol) Then
If myCheck2 = Sheets("sheet2").Cells(newRow, myCol + 1) Then
If myCheck3 = Sheets("sheet2").Cells(newRow, myCol + 2) Then
If myCheck4 = Sheets("sheet2").Cells(newRow, myCol + 3) Then
Sheets("sheet3").Cells(lastCell3 + 1, 1) = myCheck1
Sheets("sheet3").Cells(lastCell3 + 1, 2) = myCheck2
Sheets("sheet3").Cells(lastCell3 + 1, 3) = myCheck3
Sheets("sheet3").Cells(lastCell3 + 1, 4) = myCheck4
End If
End If
End If
End If
Next
Next


End Sub

--
-John Northwest11
Please rate when your question is answered to help us and others know what
is helpful.


"ap" wrote:

Dear all,

as a relative VBA newbie I seem to have gotten into something a little
bit over my head :S

I have two lists on two different sheets, which I need to compare on
multiple levels. Usually I can manage by browsing this group and
creatively combine some of the previous solutions. However, atm I am at
a loss...

I have (as an example) on the first row of Sheet1:
Identifier (col A) b c d
VS0067157023 5 2424 2425
VS0067157024 5 2431 2433

Sheet2:
Identifier (col A) b c d
VS0067157023 5 2424 2425
VS0067157024 6 2431 2433
VS0067157055 6 2400 2433

Now comes the hard part (sorry if my english is not perfect...) I need
to compare this with the data on Sheet2:

- if there is an exact match on all four levels (A to D) in any row on
Sheet2 nothing happens
- if there is a mismatch on any level -- the record with the
identifier VS0067157024 needs to be written to Sheet3
- This example record is on row 2 on Sheet1, but might not be on row 2
on Sheet2 but on any row on Sheet2
- After is have done this, I also need to write the unique records from
Sheet2 to a Sheet4 (in this example the VS0067157055 record)

Two things I do know:
- on both sheets there are no empty cells in the list of Identifiers.
(Except after the last one ofc...)
- the data has the same format on both sheets, i.e. the same columns on
both sheets.

Any help would be greatly and utterly appreciated!

H.



  #3   Report Post  
Old January 23rd 07, 10:12 AM posted to microsoft.public.excel.programming
ap ap is offline
external usenet poster
 
First recorded activity by ExcelBanter: Jan 2007
Posts: 3
Default Extracting unique record

Works like a charm, even though i dont understand the code completely.
How come if you use < and this condition is met for col A, but not for
col B the record stil gets written to page 3?
Sorry, this is a very, very basic question, but there is a major error
in my thinking here...



Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Comparison of Columns Rob Excel Discussion (Misc queries) 3 January 28th 10 12:02 PM
Matching Data Columns for comparison John Excel Discussion (Misc queries) 1 January 8th 07 06:24 AM
Comparison of records between two spreadsheets Carolyn at VW Excel Worksheet Functions 5 June 27th 06 12:13 AM
Comparison between 2 columns ? nisha Excel Programming 1 June 9th 04 08:20 PM
Comparison between 2 columns ? nisha Excel Programming 0 June 9th 04 07:21 PM


All times are GMT +1. The time now is 05:50 PM.

Powered by vBulletin® Copyright ©2000 - 2021, Jelsoft Enterprises Ltd.
Copyright 2004-2021 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017