validation and sorting
 

I have two sets of receipts numbers. They are the same receipt only they
have a different amount of digits. The first set would have a receipt number
like
241625879
while the second set has
625879

so you can see it's the same receipt but the second set has only the last 6
digits while the first set has all 9 digits. As it is now when I sort the
receipt numbers don't match up at all so I cannot compare the two lists. The
accounting software at work does this on it's own and cannot be changed. I
need to compare the two lists to see if any list is missing a receipt or has
one too many.

So I figure I need to limit a cell to only accept the last 6 digits of the
first set which has all 9 digits then I can sort both lists then compare side
by side. How can I do this? Or is there any other way to accomplish
comparing the two lists? How I get to be able to accomplish this goal is
unimportant just that I compare the two lists.

Or does anyone know of any functions that will compare two sets of
dissimilar data and look for similarities?

validation and sorting
 

Add a "Helper Column" and put the following formula in row 2 and drag down

=IF(LEN(A2)6,--RIGHT(A2,6),A2)

which assumes your receipt number is in column A and starts in row 2

Now sort on the Helper Column

Regards

Trevor


"OfficeNDN" wrote in message
...
I have two sets of receipts numbers. They are the same receipt only they
have a different amount of digits. The first set would have a receipt
number
like
241625879
while the second set has
625879

so you can see it's the same receipt but the second set has only the last
6
digits while the first set has all 9 digits. As it is now when I sort the
receipt numbers don't match up at all so I cannot compare the two lists.
The
accounting software at work does this on it's own and cannot be changed.
I
need to compare the two lists to see if any list is missing a receipt or
has
one too many.

So I figure I need to limit a cell to only accept the last 6 digits of the
first set which has all 9 digits then I can sort both lists then compare
side
by side. How can I do this? Or is there any other way to accomplish
comparing the two lists? How I get to be able to accomplish this goal is
unimportant just that I compare the two lists.

Or does anyone know of any functions that will compare two sets of
dissimilar data and look for similarities?

validation and sorting
 

"Trevor Shuttleworth" wrote:

Add a "Helper Column" and put the following formula in row 2 and drag down

=IF(LEN(A2)6,--RIGHT(A2,6),A2)

which assumes your receipt number is in column A and starts in row 2

Now sort on the Helper Column

Regards

Trevor


Thank you!!! That was exactly what I was hoping for!!!

validation and sorting
 

You're welcome. Thanks for the feedback


"OfficeNDN" wrote in message
...


"Trevor Shuttleworth" wrote:

Add a "Helper Column" and put the following formula in row 2 and drag
down

=IF(LEN(A2)6,--RIGHT(A2,6),A2)

which assumes your receipt number is in column A and starts in row 2

Now sort on the Helper Column

Regards

Trevor


Thank you!!! That was exactly what I was hoping for!!!