Solved

Excel compare, sort 10.000

Posted on 2013-10-25
5
244 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_
  • 3
5 Comments
 
LVL 35

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

Is Your AD Toolbox Looking More Like a Toybox?

Managing Active Directory can get complicated.  Often, the native tools for managing AD are just not up to the task.  The largest Active Directory installations in the world have relied on one tool to manage their day-to-day administration tasks: Hyena. Start your trial today.

Question has a verified solution.

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

Lotus Notes – formerly IBM Notes – is an email client application, while IBM Domino (earlier Lotus Domino) is an email server. The client possesses a set of features that are even more advanced as compared to that of Outlook. Likewise, IBM Domino is…
This article explains in simple steps how to renew expiring Exchange Server Internal Transport Certificate.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
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.

911 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

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now