Comparing the columns in excel

Microsoft Office excel 2003 step by step
December 30, 2009 at 22:17:59
Specs: Windows XP
Hear is the scenario, I have got two columns (column A, column B) in excel sheet with filenames. I need to compare column A with column B and column B with column C and past the result in column C and column D subsequently.


First case: If I find filename "text.txt" in column A and column B that entry should be moved from column A and pasted in column C so that at the end I have column A and column B that have no identical entries and column C that contain all duplicate entries found in A and B.

Second case: in the second case column B need to be compare with column C, If I find filename "text.txt" in column B and column C that entry should be moved from column B and pasted in column D so that at the end I have column B and column C that have no identical entries and column D that contain all duplicate entries found in B and C.

Help would be much appreciated.


See More: Comparing the columns in excel

Report •


#1
December 31, 2009 at 06:42:35
If I understand what you are asking for...

First case:

If text.txt is found in A and B, it's going to get removed from A and placed in C. That pretty straight forward

Second case:

Since Column C now contains items that were originally found in A and B, isn't every item in Column C going to match something in Column B? In the end, won't Columns C and D be identical?


Report •

#2
December 31, 2009 at 07:49:36
I populated Column A with a list of items.

I populated Column B with a different list of items but made sure that many of the items were duplicated in Column A.

When I ran this code, it found the matching items in A & B, deleted them from Column A and put them in Column C. It then found the matching items in B & C, deleted them from Column B and put them in Column D.

As suspected, Column C and D are identical. The only way they wouldn't be is if there was already data in Column C or D prior to running the code. Since that wasn't mentioned in your OP, I don't know if they did.

"I have got two columns (column A, column B) in excel sheet with filenames."

The code would need to be modified if there is existing data in Columns C & D.

Sub CompareAB()
'Determine length of Columns A & B
 lastA = Range("A" & Rows.Count).End(xlUp).Row
 lastB = Range("B" & Rows.Count).End(xlUp).Row
'Find B in A and move it to C
 For A_Row = lastA To 1 Step -1
  With Range("B1:B" & lastB)
   If Not .Find(Range("A" & A_Row)) Is Nothing Then
    C_Row = C_Row + 1
     Range("C" & C_Row) = Range("A" & A_Row)
     Range("A" & A_Row).Delete shift:=xlUp
   End If
  End With
 Next
'Determine length of Column C
 lastC = Range("C" & Rows.Count).End(xlUp).Row
'Find C in B and move it to D
 For B_Row = lastB To 1 Step -1
  With Range("C1:C" & lastC)
   If Not .Find(Range("B" & B_Row)) Is Nothing Then
    D_Row = D_Row + 1
    Range("D" & D_Row) = Range("B" & B_Row)
    Range("B" & B_Row).Delete shift:=xlUp
   End If
  End With
 Next
End Sub


Report •

Related Solutions


Ask Question