data analysis/comparison

  • Thread starter Thread starter SteveDB1
  • Start date Start date
S

SteveDB1

Morning all.
I have a speadsheet with data on it, and I'm setting it up to place in a
database (DB)-- I've removed all of the formatting, etc... just leaving the
raw data. WEll, as "raw" as Excel will allow. Oh, I'm using Excel 2007.
My understanding is that the DB is an MS Sql database.
My reason for coming here is that after I'd placed the data into the DB, I
went to verify it was all in there, and correctly ordered, because the data
is alphabetically arranged.
When I copied the data back out into my spreadsheet, I set up a series of if
equations to verify, and the majority gave false responses. I went back to
look to see if I did something wrong, and found that the bulk of the data
that the DB had arranged got "mis-arranged." I.e., the contents of one column
did not match the alignment it had with the others columns it had when I
placed it in there.
It appears it all got "jumbled up," and ignored what I'd done configuring it.
Now as I write this I'm thinking that this is really a sql db programming
issue, and not an excel matter.
But my goal for posting is to match the data to ensure that it can be
correctly matched with the source data.
I know about the match function-- it gives the row number where the matching
data piece is located. I don't believe this is what I actually want.
I'm presently doing an if function, and it gives me a true or false
statement-- I'm using "ok" for my true, and "no match" for my false. I've got
way too many falses and need to correct those.
What else can I use to see just how badly it was "jumbled?"
Thank you.
 
If comparing to a theoretically identical layout of the same data, I
always just use AND since it is easy to type quick and gives basic
true/false response.
=AND(A1=Sheet2!A1)

Copy where needed.
 
G'day Steve

Are you importing the excel data into an AccessDB.....? (Assumed)

Before you do the import.

In your Spreadsheet

Sort the data into whatever order that you want it to appear.
Insert a new column into 'Column A'.
Place the number 1 in the first row of data. (Assume A1)
In second row = A1+1 and copy down as required

This will number every row of your data.

Importing into AccessDB

Don't forget to remove any row/column heading.
After importing into the table, before making any changes to the recordset
setup.
Create a Unique ID Key field, set to Primary, AutoNumber & No Duplicates.

The AutoNumber sequence should match the recordset with your spreadsheet, by
that I mean the first row of data of spreadsheet that is numbered 1 should
match the autonumber generated by Access.

Do a visual cross check between the spreadsheet and the table to ascertain
if the data has imported in the manner to which you require and that the
matching numbers correspond through the recordset.

If you are happy that the import is good, you can then delete the number
column from the DB table that you created on your spreadsheet, keeping the
Unique key ID field, this will ensure the recordsets integrity.

HTH
Mark.
 
Good point.

About half the time I do this sort of check, it has multiple arguments
and needs AND. Maybe I just carry it over from that.
 
Back
Top