Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Excel compare, sort 10.000

Posted on 2013-10-25
5
Medium Priority
?
271 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 37

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 1100 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

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

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

Stellar Exchange Toolkit: this 5 in 1 toolkit comes loaded with mega-software tool. Here’s an introduction to tools’ usage and advantages:
Mailbox Corruption is a nightmare every Exchange DBA wishes he never has. Recovering from it can be super-hectic if not entirely futile. And though techniques like the New-MailboxRepairRequest cmdlet have been designed to help with fixing minor corr…
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

886 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