Excel compare, sort 10.000

hi

example

in the example above i've 3 examples of 10.000.  

The Z-Number at Cell A2 should be the same Number as the W-Number at cell C2
It should sort and compare that the same numbers are always in the same line
The displaynames also should sort with the numbers.
LVL 2
Mandy_Asked:
Who is Participating?
 
mark_harris231Commented:
Just noted that your original example indicated that the "_" appeared in the DisplayName associated with the W values, but you indicate it is associated with the Z values above.  My formula assumes it is with the W values.

Also, I grabbed the wrong cell formula.  Use this in Cell E2 instead and copy down:

=IFERROR(IF(AND(RIGHT(A2,LEN(A2)-1)=RIGHT(C2,LEN(C2)-1),RIGHT(B2,LEN(B2)-1)<>D2),"Mismatch",""),"")

See attached for an example of formula and results.
Mismatch.xlsx
0
 
KimputerCommented:
What should happen with the lines where Z and W numbers don't match up ? Delete ? Move to another sheet for review?
0
 
mark_harris231Commented:
Are you looking for a macro/button to perform the sort/validation?  I ask because the Excel Sort functionality should take care of the first part of your request.  Simply select these four columns and sort by Column A.

With regards to the comparison, this could be done in a number of ways and really depends on what you want the final result to look like.  Kimputer identified a couple of possibilities (i.e., delete or move non-matching rows).  You could also flag matches or non-matches in an additional column using a formula, or highlight non-matching cells using conditional formatting.

Flag non-matching cells (Enter in Cell E2 and copy down)
=IFERROR(IF(RIGHT(A2,LEN(A2)-1)<>RIGHT(C2,LEN(C2)-1),"No Match","Match"),"ERROR")

Highlight non-matching cells using Conditional Formatting
Select Column C
Conditional Formatting > New Rule > "Use a formula to determine which cells to format"
Enter =IF(RIGHT(A1,LEN(A1)-1)<>RIGHT(C1,LEN(C1)-1),TRUE,FALSE) in the formula bar
Select a desired format and click OK
0
 
Mandy_Author Commented:
For every Z-Number is matching a W-Number. What i want is to filter which Z Displayname
not match with W Displayname. We need to correct all Displaynames because a lot not matching.

Z-Displayname          W-Displayname not match but Numbers always the same
Z000001                   W000001
DER, NYC TEST          _KRU, NYC TEST

It should sort all rows which is not matching ( W-displaynames have a "_" as first letter but all other should be the same as the Z-Displayname if not sort out.
0
 
mark_harris231Commented:
Put this formula in Column E2 and copy down:

=IFERROR(IF(AND(RIGHT(A5,LEN(A5)-1)=RIGHT(C5,LEN(C5)-1),RIGHT(B5,LEN(B5)-1)<>D5),"Mismatch",""),"")

This will all rows where the SamAccountName fields match, but DisplayName fields do not.  The value, "Mismatch" will be placed in column E beside these rows, which you can then sort on.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.