Solved

Excel compare, sort 10.000

Posted on 2013-10-25
5
259 Views
Last Modified: 2013-10-25
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.
0
Comment
Question by:Mandy_
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
5 Comments
 
LVL 36

Expert Comment

by:Kimputer
ID: 39600409
What should happen with the lines where Z and W numbers don't match up ? Delete ? Move to another sheet for review?
0
 
LVL 10

Expert Comment

by:mark_harris231
ID: 39600863
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
 
LVL 2

Author Comment

by:Mandy_
ID: 39601359
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
 
LVL 10

Expert Comment

by:mark_harris231
ID: 39601398
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
 
LVL 10

Accepted Solution

by:
mark_harris231 earned 275 total points
ID: 39601420
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

Featured Post

MS Dynamics Made Instantly Simpler

Make Your Microsoft Dynamics Investment Count  & Drastically Decrease Training Time by Providing Intuitive Step-By-Step WalkThru Tutorials.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Unified and professional email signatures help maintain a consistent company brand image to the outside world. This article shows how to create an email signature in Exchange Server 2010 using a transport rule and how to overcome native limitations …
This article will help to fix the below errors for MS Exchange Server 2013 I. Certificate error "name on the security certificate is invalid or does not match the name of the site" II. Out of Office not working III. Make Internal URLs and Externa…
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…
Suggested Courses

632 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question