Re: Comparing Data in different formats

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

Hi Charlotte

Take your data from the AS400 and covert it first. With the data in
column A, enter in B1
=TEXT(A1,"dd-mm-yy")
and copy down as far as required.
Copy this column and Paste Special>Values to "fix" the data.
Now you will have data you can compare with your other list.

--
Regards

Roger Govier


"Charlotte Howard" <CharlotteHoward@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in
message news:44C4E630-41D9-4787-A13C-C17D3EB06B1C@xxxxxxxxxxxxxxxx
Hello,

I'm having some difficulty in comparing the two lists below. Both
sets of
data come from different databases, the first an AS/400, the second a
SQL
database.

sort code Custom
20/11/1981 201181
93-82-70 938270
95-03-66 950366
07/01/2016 070116

The AS/400 mainly stored the 6 digit code as XX-XX-XX, but sometimes
it was
reformatted as a date field - hence 20-11-81 became 20/11/1981
How can I convert these to a 6 digit string sop that I can compare
thousands
of records against our new custom database?
I have tried Text-to-Columns, forcing General, but the date fields
come in
screwy, and the - fromatting is preserved!

Help!!
charlotte




.

Relevant Pages

  • Re: Getting a list of list permissions for WSS
    ... this on a backup copy of the database so you're not hitting the live DB. ... Now when you run the script and refresh the SPS Event List which this ... my departmental Event Lists roll-up flawlessly to a single Portal list. ...
    (microsoft.public.sharepoint.portalserver)
  • Re: The Sorrows of Record Cataloguing
    ... but the database ... needed was a list so that I wouldn't buy records or CDs over again. ... to see on the shelves and were in alpha-order by composer. ... recordings, there is an entirely different set of lists -- five lists, ...
    (rec.music.classical.recordings)
  • Re: Are you one of the EIGHT MILLION who will be arrested when Bush declares martial law in October?
    ... Called "Main Core," the database's origins date back to the 1980s when ... "There exists a database ... of Americans, who, often for the slightest and most trivial reason, ... called someone on those lists, you get added to the master list. ...
    (alt.politics)
  • www.CeBeans.com - new program listings - Dec 17 2007
    ... This program is a database of 24 Christmas/Holdiay tips for a safe season. ... This program is a database of 24 cooking email lists that you can subscribe ... The object of this game is to use your taser gun to shoot down the birds ... Free man every round after five. ...
    (microsoft.public.pocketpc)
  • Re: File Auditing - Fast DB import and data manipulation
    ... the files into the database is not the best way forward. ... on either system (you'll need to download gunzip from gnuwin32 ... The databases I've dealt with (Oracle, SQL Server, Access, Postgres) ... So, I need some way to compare todays file, to yesterdays and see ...
    (comp.lang.ruby)