comparing data without a unique identifier

[Linda]

I've also posted this message under Excel Programming.

I am attempting to compare 2 Excel worksheets (one from another agency) and
there is no unique identifier. Each has a specific grant number; one may be
OH W-13-L-1, the other W13L1. In addition, the vendor name, although
similar, is not usually exactly the same. such as Hunter, J vs. Hunter
Estate.

I receive the other agency's worksheet via excel and download mine from
Access.

I could use either program to compare, if I knew what to do! Any suggestions?

Thanks!

 

[John W. Vinson]

I've also posted this message under Excel Programming.

I am attempting to compare 2 Excel worksheets (one from another agency) and
there is no unique identifier. Each has a specific grant number; one may be
OH W-13-L-1, the other W13L1. In addition, the vendor name, although
similar, is not usually exactly the same. such as Hunter, J vs. Hunter
Estate.

I receive the other agency's worksheet via excel and download mine from
Access.

I could use either program to compare, if I knew what to do! Any suggestions?

Thanks!

So you want to... what? Compare two fields which contain arbitrary,
unpredictable extra characters? What constitutes "the same" and what
constitutes "different"? Is there *ANY* consistancy in either spreadsheet?

You can trim out the hyphens (if they are irrelevant) by comparing

Replace([GrantNumber], "-", "")

to the hyphenless grantnumber, but that won't solve the OH tacked on the
front.

 

[John W. Vinson]

I've also posted this message under Excel Programming.

I am attempting to compare 2 Excel worksheets (one from another agency) and
there is no unique identifier. Each has a specific grant number; one may be
OH W-13-L-1, the other W13L1. In addition, the vendor name, although
similar, is not usually exactly the same. such as Hunter, J vs. Hunter
Estate.

I receive the other agency's worksheet via excel and download mine from
Access.

I could use either program to compare, if I knew what to do! Any suggestions?

Thanks!

So you want to... what? Compare two fields which contain arbitrary,
unpredictable extra characters? What constitutes "the same" and what
constitutes "different"? Is there *ANY* consistancy in either spreadsheet?

You can trim out the hyphens (if they are irrelevant) by comparing

Replace([GrantNumber], "-", "")

to the hyphenless grantnumber, but that won't solve the OH tacked on the
front.

 

[kc-mass]

I once did an application for a firm that received invoicing data from 5
disparate systems most of which allowed free form entry for Customer name.
They probably had 1500 customers with 8000 variations on the names. They
needed to resolve the data to one name each.

The application was basically a form with three list boxes.
The middle list was what were the "Unresolved names".
The left list was "Official Customer Names". The right list was "Customer
Name Variations".

Initially every name in the data was loaded into the unresolved table and
thus loaded to the "Unresolved" list.

To the left of the center list was a button that would take the currently
selected "name" in the central list and move it to the Customer name table,
Assign a Customer ID to it, remove it from the Unresolved table and update
the lists.

To the right of the central list was a button that when pressed would move
the Name selected in the central list to the Name Variation table, assigning
to it the customer ID matching the Customer Name selected in the left list.

It sounded like a lot of work to me but one person in the firm resolved them
all in less than three days. New unresolved names were then fewer than 100
a month and there were 10s of thousands of invoices carrying these names.

Some times the human is better at seeing matches in strings that don't
really match at all. The application really only gave them an easy way to
see and sort them.


Kevin

 

[kc-mass]

I once did an application for a firm that received invoicing data from 5
disparate systems most of which allowed free form entry for Customer name.
They probably had 1500 customers with 8000 variations on the names. They
needed to resolve the data to one name each.

The application was basically a form with three list boxes.
The middle list was what were the "Unresolved names".
The left list was "Official Customer Names". The right list was "Customer
Name Variations".

Initially every name in the data was loaded into the unresolved table and
thus loaded to the "Unresolved" list.

To the left of the center list was a button that would take the currently
selected "name" in the central list and move it to the Customer name table,
Assign a Customer ID to it, remove it from the Unresolved table and update
the lists.

To the right of the central list was a button that when pressed would move
the Name selected in the central list to the Name Variation table, assigning
to it the customer ID matching the Customer Name selected in the left list.

It sounded like a lot of work to me but one person in the firm resolved them
all in less than three days. New unresolved names were then fewer than 100
a month and there were 10s of thousands of invoices carrying these names.

Some times the human is better at seeing matches in strings that don't
really match at all. The application really only gave them an easy way to
see and sort them.


Kevin

 

[John W. Vinson]

Some times the human is better at seeing matches in strings that don't
really match at all. The application really only gave them an easy way to
see and sort them.

Absolutely! Nothing beats a well designed USB [1] interface.



[1] Using Someone's Brain

 

[John W. Vinson]

Some times the human is better at seeing matches in strings that don't
really match at all. The application really only gave them an easy way to
see and sort them.

Absolutely! Nothing beats a well designed USB [1] interface.



[1] Using Someone's Brain

 

[Piet Linden]

Some times the human is better at seeing matches in strings that don't
really match at all.  The application really only gave them an easy way to
see and sort them.

Absolutely! Nothing beats a well designed USB [1] interface.

[1] Using Someone's Brain

Hmm... but where do you plug it in?!!!

 

[Piet Linden]

Some times the human is better at seeing matches in strings that don't
really match at all.  The application really only gave them an easy way to
see and sort them.

Absolutely! Nothing beats a well designed USB [1] interface.

[1] Using Someone's Brain

Hmm... but where do you plug it in?!!!

 

[John W. Vinson]

Some times the human is better at seeing matches in strings that don't
really match at all.  The application really only gave them an easy way to
see and sort them.

Absolutely! Nothing beats a well designed USB [1] interface.

[1] Using Someone's Brain

Hmm... but where do you plug it in?!!!

The latest models come equipped with either cellphones or earbuds... <g>

 

[John W. Vinson]

Some times the human is better at seeing matches in strings that don't
really match at all.  The application really only gave them an easy way to
see and sort them.

Absolutely! Nothing beats a well designed USB [1] interface.

[1] Using Someone's Brain

Hmm... but where do you plug it in?!!!

The latest models come equipped with either cellphones or earbuds... <g>

 

[Piet Linden]

Some times the human is better at seeing matches in strings that don't
really match at all.  The application really only gave them an easyway to
see and sort them.
Absolutely! Nothing beats a well designed USB [1] interface.
[1] Using Someone's Brain

Hmm... but where do you plug it in?!!!

The latest models come equipped with either cellphones or earbuds... <g>

oh, so THAT's how you get all that information out... just suck it out
through the ear bud... wow... I must be living in the stone age!!!
Makes me feel like Rip Van Winkle....

 

[Piet Linden]

Some times the human is better at seeing matches in strings that don't
really match at all.  The application really only gave them an easyway to
see and sort them.
Absolutely! Nothing beats a well designed USB [1] interface.
[1] Using Someone's Brain

Hmm... but where do you plug it in?!!!

The latest models come equipped with either cellphones or earbuds... <g>

oh, so THAT's how you get all that information out... just suck it out
through the ear bud... wow... I must be living in the stone age!!!
Makes me feel like Rip Van Winkle....