R
rhoneyman
We are moving to a new accounting system. I'm attempting to test the quality
of the uploaded data making sure the new account codes have been mapped
correctly.
We have a table mapping the old chart of account segments to the new. In
the old system, all segments are built into a single, 12 digit expression. I
use left()
function to isolate the segments in the old chart of accounts. This is the
first section of vlookup. The mapping table is the second section. This
always gives me #n/a as the result.
If I highlight the mapping table and press F2-enter on each cell, the #n/a
goes away. However, when I compare the result to the data dump from the new
system, I continue to some sort of formatting issue. This issue goes away if
I do a copy paste-value of the column derived using the left() function, but
only after going through the F2-enter routine on each of the hundreds of
cells.
There has to be a better approach. I've tried changing everything to text,
to general, whatever. Nothing works except to stress my carpal tunnel
syndrome.
Example:
Old Parsed New
1000-000-000 1000 10100
What had started with 1000 in my old system needs to start with 10100 in my
new. The mapping table ties 1000 to 10100. The left() function returns 1000
for any records starting with 1000. The Vlookup function is supposed to take
the Parsed number resulting from use of the left() function and return the
New number. But for #n/a, it works as advertised.
Any ideas? Thanks.
of the uploaded data making sure the new account codes have been mapped
correctly.
We have a table mapping the old chart of account segments to the new. In
the old system, all segments are built into a single, 12 digit expression. I
use left()
function to isolate the segments in the old chart of accounts. This is the
first section of vlookup. The mapping table is the second section. This
always gives me #n/a as the result.
If I highlight the mapping table and press F2-enter on each cell, the #n/a
goes away. However, when I compare the result to the data dump from the new
system, I continue to some sort of formatting issue. This issue goes away if
I do a copy paste-value of the column derived using the left() function, but
only after going through the F2-enter routine on each of the hundreds of
cells.
There has to be a better approach. I've tried changing everything to text,
to general, whatever. Nothing works except to stress my carpal tunnel
syndrome.
Example:
Old Parsed New
1000-000-000 1000 10100
What had started with 1000 in my old system needs to start with 10100 in my
new. The mapping table ties 1000 to 10100. The left() function returns 1000
for any records starting with 1000. The Vlookup function is supposed to take
the Parsed number resulting from use of the left() function and return the
New number. But for #n/a, it works as advertised.
Any ideas? Thanks.