Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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!!! |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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!!! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Sorting Validation | Excel Discussion (Misc queries) | |||
Help please - Data sorting | Excel Worksheet Functions | |||
Validation Issues | Excel Worksheet Functions | |||
I want to sort everything but my validation list | Excel Worksheet Functions | |||
Sorting protected worksheets | Excel Discussion (Misc queries) |