Solved

Excel compare, sort 10.000

Posted on 2013-10-25
5
252 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

Free Webinar: AWS Backup & DR

Join our upcoming webinar with experts from AWS, CloudBerry Lab, and the Town of Edgartown IT to discuss best practices for simplifying online backup management and cutting costs.

Question has a verified solution.

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

Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
Read this checklist to learn more about the 15 things you should never include in an email signature.
how to add IIS SMTP to handle application/Scanner relays into office 365.
This video demonstrates how to sync Microsoft Exchange Public Folders with smartphones using CodeTwo Exchange Sync and Exchange ActiveSync. To learn more about CodeTwo Exchange Sync and download the free trial, go to: http://www.codetwo.com/excha…

756 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