Align Duplicate Cells?

[ladysemi]

I have 2 columns, both containing numbers.

I want duplicate numbers to be aligned.

Next to the unique numbers, I want a blank space.

How can I sort this properly and easily?

I already have Conditional Formatting applied that highlights the unmatched cells, but as you can see when unmatched cells occur it begins to off-set the two columns matching up.


See the picture link for a better look...

http://i48.tinypic.com/10rmj2a.jpg


Things worth mentioning...

I am 100% unfamiliar with a VBA/macros.

I'm sorting over 900 numbers.

There are some numbers in A that aren't in B and some numbers in B that aren't in A.


Any and all help appreciated...

 

[Sajan]

Hello,

Welcome to the forum.


Here is one approach.


To make the formulas easier to read, please give the number range in each coluumn a name. I would suggest something like "List1" for the data range in column1, and "List2" for the data range in column2.


Put the following formula in cell C2:

=IFERROR(IFERROR(INDEX(list1,MATCH(0,COUNTIF($C$1:C1,list1),0)),INDEX(list2,MATCH(0,COUNTIF($C$1:C1,list2),0))),"---")

enter with Ctrl + Shift + Enter (since it is an array formula)

Copy down to additional rows, till you get the "---"


Put the following formula in cell D2:

=IFERROR((VLOOKUP(C2,list1,1,FALSE)+ VLOOKUP(C2,list2,1,FALSE))/2, "")


Copy down to additional rows


That should do it. You should now have a view like you describe.


Credit for the first formula goes to Kaushik on this forum.


Cheers,

Sajan.

 

[ladysemi]

Thank you, very helpful information on this site. I will plug this in first thing tomorrow and let you know how it works!