validation and sorting

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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?
 
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
 
Trevor Shuttleworth said:
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
Click to expand...
Thank you!!! That was exactly what I was hoping for!!!
 
Back
Top