I cannot figure how to sort column with data in them.

My problem is somewhat unique. I have two sets of data, each set
containing the same 12 column names and each set on a different
worksheet. One set are sales figures the company headquarters uses to
reconcile against the other set which is from the point-of-sale store.
Each set contains well over 5500 lines, each representing a sale.

The column I am trying to sort the information by is a column titled
"Auth Num" and is basically a receipt number and is the only truly
unique identifier from each sale. The other columns are not truly
unique as there are many sales on the same date, by the same company,
same company identifier, same location, same amount, etc. and cannot
easily be sorted and used individually to identify each sale.

Now here's the problem.

The data set for column "Auth Num" which the company headquarters has
contains the entire reciept number which is 9 digits. The other data
set from the company store is the same receipt number but only contains
the LAST 6 digits of the receipt. That is the headquarter's data would
look like

438568238

whereas the data set from the company store would look like

568238

which is the receipt number but only the last six digits.

When I do a Data/Sort I first sort by Date, then by fuel amount, then
by Auth Num. This is the best way I can get the data into any sort of
comparable format. When I try to sort by that method and then compare
worksheets the a few receipt numbers are usually off by a line or two,
i.e. a particular transaction will be on line 264 on one worksheet but
will be on line 266 on the other worksheet.

Since the worksheets are out of sync by only a line or two I can still
compare but it's long and time-consuming. I have to look at each
transaction and see if the total matches from one set of data to
another. The transactions are usually in an order where both
worksheets are off but only by a line or two. If they are in decent
order I can go down about 100 transactions to see if they are still in
decent order but if they don't I usually have to delete a transaction
to get both lists into sync, i.e. the same transaction on the same line
number.

Is there any way I can delete the first three numbers for the receipt
for the headquarter's data? Is there any way I could sort that column
from right to left? If I could accurately sort by Auth Num that would
practically do the job itself, after all that's what Excel is for
right?




--
OfficeNDN

Hi
My quick solution
Create another column alongside the 9 digit receipt no (I assume it is a
number and not text) and if your receipt no was in B4 then the formula to put
in C4 is
=((B4/100000)-TRUNC((B4/1000000)*100000

This will remove the excess
Now you can combine and sort. (In the second set I suggest you create a
similar column and copy the existing numbers so that you actually sort on the
new column.

This is probably not the most elegant solution but it does work.
--
Don C


"OfficeNDN" wrote:


I cannot figure how to sort column with data in them.

My problem is somewhat unique. I have two sets of data, each set
containing the same 12 column names and each set on a different
worksheet. One set are sales figures the company headquarters uses to
reconcile against the other set which is from the point-of-sale store.
Each set contains well over 5500 lines, each representing a sale.

The column I am trying to sort the information by is a column titled
"Auth Num" and is basically a receipt number and is the only truly
unique identifier from each sale. The other columns are not truly
unique as there are many sales on the same date, by the same company,
same company identifier, same location, same amount, etc. and cannot
easily be sorted and used individually to identify each sale.

Now here's the problem.

The data set for column "Auth Num" which the company headquarters has
contains the entire reciept number which is 9 digits. The other data
set from the company store is the same receipt number but only contains
the LAST 6 digits of the receipt. That is the headquarter's data would
look like

438568238

whereas the data set from the company store would look like

568238

which is the receipt number but only the last six digits.

When I do a Data/Sort I first sort by Date, then by fuel amount, then
by Auth Num. This is the best way I can get the data into any sort of
comparable format. When I try to sort by that method and then compare
worksheets the a few receipt numbers are usually off by a line or two,
i.e. a particular transaction will be on line 264 on one worksheet but
will be on line 266 on the other worksheet.

Since the worksheets are out of sync by only a line or two I can still
compare but it's long and time-consuming. I have to look at each
transaction and see if the total matches from one set of data to
another. The transactions are usually in an order where both
worksheets are off but only by a line or two. If they are in decent
order I can go down about 100 transactions to see if they are still in
decent order but if they don't I usually have to delete a transaction
to get both lists into sync, i.e. the same transaction on the same line
number.

Is there any way I can delete the first three numbers for the receipt
for the headquarter's data? Is there any way I could sort that column
from right to left? If I could accurately sort by Auth Num that would
practically do the job itself, after all that's what Excel is for
right?




--
OfficeNDN

Hi OfficeNDN,

Insert a helper column next to your 9 digit number. Say the 9 digit
column starts in B2 then in C2(which is your helper column) put
this formula =RIGHT(B2,6) and copy down as far as needed.
You now have two identical columns to sort on.

HTH
Martin