Compare 4 columns in an Excel sheet

3D Design Volume Model
1

Hello world 

I need your help please!! I'm going to explain the problem and my part of the code and if someone can help me it would be great.

I have an Excel sheet with 4 columns:

column A = user (user1,user2,user3......)
column B = functionalities ( fonc1, fonc2, fonc3.....)

column C = user (user1,user2,user3......)
column D = functionalities ( fonc1, fonc2, fonc3.....)
I want to compare in the same sheet the columns A, B and C, D at the same time knowing that columns C and D are the new version of the data of A and B and that the users of column C can change order and functionality 

the result as you will see in my Excel file where there is the column of users (column F) and the fonctionnalitées_supprimées (column G) and the nouvelles_fonctionnalitées (column H)
When I run my code it doesn't show me the users only the new and removed features and not all of them.
The second problem is that when the user rows (columns A) are different from the user (column C) the code does not execute

Here is the excel file on the auel I work 
and here is my code

Private Sub CommandButton2_Click()
  Dim rngCell As Range
    For Each rngCell In Range("B2:B2000")
        If WorksheetFunction.CountIf(Range("D2:D2000"), rngCell) = 0 Then 
            Range("G" & Rows.Count). End(xlUp). Offset(1) = rngCell
        End If 
    Next
    For Each rngCell In Range("D2:D2000")
        If WorksheetFunction.CountIf(Range("B2:B2000"), rngCell) = 0 Then 
            Range("H" & Rows.Count). End(xlUp). Offset(1) = rngCell
        End If 
    Next
End Sub


compare.xlsm
2

Hi, You explain to us the content, the result you get, not the one you want.

Describe precisely the desired result and we will try to help you.

2 Likes
3

Hello

1) when I run my code it doesn't show me the users only the new and removed features and not all of them. Do you want in column F the list of all users? Column A and column C.

2) The second problem is that when the user rows (columns A) are different from the users (column C) the code doesn't  execute Do you have an example?

On the other hand, to do this, I strongly advise you to use variable arrays, it will go much faster!

See here:

http://silkyroad.developpez.com/vba/tableaux/

 

4

Thank you for your thoughts  , in fact  after the comparison of the 4 columns the result I get does not show me the users it only shows the removed features or the new features.

What I would like to do is that after the comparison I would have the list of users according to the features  for example:

In the first two columns the user1 has the 2 feature  but in the other two columns it no longer has this feature 

So in the results I would like to have the name of user  and the feature either in the deleted_fonctionnalite  column if it is removed  or in the New_fonctionnalite column if it is added.

 

Thank you in advance for you:)

5

Hello

Same thought as last week:

Lynkoa's vocation is to deal with CAD even if we can sometimes use excel or other solutions but I don't have the impression that this is your case.

Please use dedicated excel forums for this kind of question.

Kind regards

2 Likes