Hi, I am trying to match financial report data from a Reynolds & Reynolds
business system to data in excel. I need to sort the new data to match an
existing list of accounts. i.e - In a company department there are 90
accounts in the chart of accounts. In reports generated only some of these
will have data, but the accounts without data are omitted from the report so
the fields won't match up when the reports are extracted in excel format.
I think I need to create a template with the list of account No.s in colum
A, but I can't sort the new data(which contains the account no.) to match by
Account No. Is this possible??

Hi Jack

Lets assume your Sheet with the Reports layout you want is called RR and
the extracted list is on a sheet called XL
with Account codes in column A and data in column B.
Then in sheet RR cell B1 enter

=IF(ISERROR(VLOOKUP(A1,XL!$A$1:$B$90,2,0)),0,VLOOK UP(A1,XL!$A$1:$B$90,2,0))
copy down column B to row 90

Change sheet names and range references to suit. Note if your sheet name
has spaces, then it must be enclosed in single quotes e.g. 'Sheet
1'!$A$1:$B$90

--
Regards

Roger Govier


"Jack C" wrote in message
...
Hi, I am trying to match financial report data from a Reynolds &
Reynolds
business system to data in excel. I need to sort the new data to match
an
existing list of accounts. i.e - In a company department there are 90
accounts in the chart of accounts. In reports generated only some of
these
will have data, but the accounts without data are omitted from the
report so
the fields won't match up when the reports are extracted in excel
format.
I think I need to create a template with the list of account No.s in
colum
A, but I can't sort the new data(which contains the account no.) to
match by
Account No. Is this possible??

Many Thanks Roger
Jack

"Jack C" wrote:

Hi, I am trying to match financial report data from a Reynolds & Reynolds
business system to data in excel. I need to sort the new data to match an
existing list of accounts. i.e - In a company department there are 90
accounts in the chart of accounts. In reports generated only some of these
will have data, but the accounts without data are omitted from the report so
the fields won't match up when the reports are extracted in excel format.
I think I need to create a template with the list of account No.s in colum
A, but I can't sort the new data(which contains the account no.) to match by
Account No. Is this possible??

Hi Roger,
I've tried your suggestion and it works a treat on the some fields not
others. The sheet I'm working on has 234 rows I've changed the formula
accordingly. The first 91 work ok the rest show error #N/A. Coincidently the
error fields have positive values in the XL sheet and the fields that worked
ok have negative or 0 value. I've chenged the data value to test if the value
is causing the error however it makes no difference.
Do you have any suggestions


"Roger Govier" wrote:

Hi Jack

Lets assume your Sheet with the Reports layout you want is called RR and
the extracted list is on a sheet called XL
with Account codes in column A and data in column B.
Then in sheet RR cell B1 enter

=IF(ISERROR(VLOOKUP(A1,XL!$A$1:$B$90,2,0)),0,VLOOK UP(A1,XL!$A$1:$B$90,2,0))
copy down column B to row 90

Change sheet names and range references to suit. Note if your sheet name
has spaces, then it must be enclosed in single quotes e.g. 'Sheet
1'!$A$1:$B$90

--
Regards

Roger Govier


"Jack C" wrote in message
...
Hi, I am trying to match financial report data from a Reynolds &
Reynolds
business system to data in excel. I need to sort the new data to match
an
existing list of accounts. i.e - In a company department there are 90
accounts in the chart of accounts. In reports generated only some of
these
will have data, but the accounts without data are omitted from the
report so
the fields won't match up when the reports are extracted in excel
format.
I think I need to create a template with the list of account No.s in
colum
A, but I can't sort the new data(which contains the account no.) to
match by
Account No. Is this possible??

Hi Jack
Post the formula as you have it modified and give an example row of data
from each sheet.
Alternatively, you may mail me directly with a copy of your sheet and I
will taker a look for you.
Remove NOSPAM from my address to send direct.

--
Regards

Roger Govier


"Jack C" wrote in message
...
Hi Roger,
I've tried your suggestion and it works a treat on the some fields not
others. The sheet I'm working on has 234 rows I've changed the formula
accordingly. The first 91 work ok the rest show error #N/A.
Coincidently the
error fields have positive values in the XL sheet and the fields that
worked
ok have negative or 0 value. I've chenged the data value to test if
the value
is causing the error however it makes no difference.
Do you have any suggestions


"Roger Govier" wrote:

Hi Jack

Lets assume your Sheet with the Reports layout you want is called RR
and
the extracted list is on a sheet called XL
with Account codes in column A and data in column B.
Then in sheet RR cell B1 enter

=IF(ISERROR(VLOOKUP(A1,XL!$A$1:$B$90,2,0)),0,VLOOK UP(A1,XL!$A$1:$B$90,2,0))
copy down column B to row 90

Change sheet names and range references to suit. Note if your sheet
name
has spaces, then it must be enclosed in single quotes e.g. 'Sheet
1'!$A$1:$B$90

--
Regards

Roger Govier


"Jack C" wrote in message
...
Hi, I am trying to match financial report data from a Reynolds &
Reynolds
business system to data in excel. I need to sort the new data to
match
an
existing list of accounts. i.e - In a company department there are
90
accounts in the chart of accounts. In reports generated only some
of
these
will have data, but the accounts without data are omitted from the
report so
the fields won't match up when the reports are extracted in excel
format.
I think I need to create a template with the list of account No.s
in
colum
A, but I can't sort the new data(which contains the account no.) to
match by
Account No. Is this possible??