Matching fields from 2 databases

**BG1983** · 09-12-2015, 10:59 PM

Hi, I am in the process of matching up fields from 1 database to fields in a 2nd database. Each database is unique in the sense that they name things differently, one has some fields for things that the other does not, etc. But, I am trying to think of the best way to get a good chunk of the matches done in bulk, then looking at everything individually. So, my question is: Is there a way to compare 2 different columns, and if a cell from column 1 contains the same words, or similar words as column 2 then match them together.

An example would be Database1 = Red Brick House Multi and DataBase2 = Red_House_2 Story - Probably not the best example, but hopefully you understand where I am going with it... I just need to find similar values within each comparison column and then have them matched up together.

I don't know if there is a way to do that or not, but, figured was worth asking the question. Or, if anyone has any suggestions regarding a different tactic, I would love to learn. Thanks for any help!

**Doc.AElstein** · 09-13-2015, 07:29 AM

Hi

Originally Posted by BG1983

..... So, my question is: Is there a way to compare 2 different columns, and if a cell from column 1 contains the same words, or similar words as column 2 then match them together.....

... sounds like something that is very do – able, especially with VBA code, if that is what you are asking. ( Probably a formula might do if the data size and sort was smaller )
. In any case, although you have explained well, . It would be much easier for someone trying to help if you give a clear picture ( Not IMAGES ) of what you have before and what you want after with reduced test data.

. So simply provide a clear before and after ... ( For example a "Before" and "After" sheet in an Excel File )

To summarise.

. The Before or Befores, should look just as it / they does before running of any macro.
.
. The After or Afters, should be hand filled by you so that it they / looks exactly as you want it to After running of any macro, based on the actual sample data in the Before / or Befores
. Make sure there is just enough data to demonstrate your need. Remember to desensitize the data if necessary, but use the typical Format type and choose your data carefully so that it can be used to test all possible scenarios.
...

Alan

P.s. some ways to provide that info: ( Most people use . 2a )
. 1 ) use the Forum Tools in my signature to produce screen shots we can copy to a spreadsheet ( NO IMAGES ! )
. 2 ) Post Files
. 2 a) To Attach a sample workbook:
View Pic
http://www.excelforum.com/members/da...ch-a-file.html
http://tinyurl.com/oenwprw
. 2 b) Send over a file sharing site, such as this free thing
Box Net,
https://www.box.com/
http://tinyurl.com/7chr7u8
. Remember to select Share and give the link / links they give..

. 3 ) - Up left in the Thread editor is a Table icon. Click that, create an appropriately sized table and fill it in. (To get the table icon up in the Reply window you may need to click on the “Go Advanced” Button next to the Reply Button)

. 4) Only as a very last resort, P.M. me and i will reply with my Email Addressee so you can send me a file
** To PM me, click on my name in the left hand margin when you are logged in, the rest should be obvious.

**BG1983** · 09-13-2015, 08:36 AM

Thanks for the reply! Thanks for the tip, I agree, I will be looking into getting providing that info to you tonight. Appreciate your response!

**HaHoBe** · 09-13-2015, 08:48 AM

Hi, BG1983,

this is a duplicate post and as such does not comply with Rule 5 of our forum rules (http://www.excelforum.com/access-tab...databases.html). Please only post your question in only one subforum According to your question this should be in Access.

Ciao,
Holger

**Doc.AElstein** · 09-13-2015, 09:16 AM

@ HaHoBe
. Danke für den hinweise

@ BG1983,

Originally Posted by BG1983

.... I agree, I will be looking into getting providing that info to you tonight......!

. I cannot help you with Access. I do not have Access nor any experience with that Software. If you wish to take this further as an Excel VBA problem, please reply in the other Thread mentioned by HaHoBe and reference this thread
http://www.excelforum.com/excel-prog...databases.html
and ask a Moderator to close and lock That Thread over at the Access Sub Forum.
. Please note what HaHoBe said

Alan

**BG1983** · 09-13-2015, 09:25 AM

@ HaHoBe

I realized that my question should be in VBA after posting in the access area so that is why it is in two separate places. This is not a direct "database" question as all of the information was dumped to excel. I am in need of help from people with VB experience greater than mine to help me find a solution. I would prefer that the other thread be closed. Not this one. Is that possible?

**Doc.AElstein** · 09-13-2015, 09:35 AM

Originally Posted by BG1983

..... I would prefer that the other thread be closed. Not this one. Is that possible?

.. see my Post #5.
. To get a message to a moderator go into a Post of yours in that Thread at Access Forum, Hit the Report Post and a Window comes up for you to pass a message

**BG1983** · 09-24-2015, 02:28 PM

Following up on this... Attached is some sample data. On the left is the values I have. On the right (in bold) are the values I am trying to match up. I can sort through them quickly to see if they do in fact match, so mismatches are not really a problem... I am just trying to match column A to column B. Column A has around 4,000 values while Column B has around 2000. They do not all match up, I need to find the ones that can be matched up and am trying to find a way to get the majority of these done in a big chunk.. fuzzy match add in didn't for me. If the below attachment doesnt work, let me know. Thanks!

Capture.PNG Capture.PNG

**Doc.AElstein** · 09-24-2015, 02:53 PM

Originally Posted by BG1983

... If the below attachment doesnt work, let me know. ......

It sort of did work... but check out again what i said about not posting images. Images are a pain for us to get data from... check out all i said in Post #2

Alan

**BG1983** · 09-24-2015, 04:03 PM

Excel 2013

_Row\^Col	A	B	E	F
1	Example Data		Desired Matching
2	Dismissal Fee Approval Received*	CT_DISMISS_FEE_APPROVAL_RECEIV	Dismissal Fee Approval Received*	CT_DISMISS_FEE_APPROVAL_RECEIV
3	Dismissal Fee Approval Requested*	AMD_COMPLAINT1_PREPARED	Dismissal Fee Approval Requested*	CT_DISMISS_FEE_APPROVAL_REQUEST
4	Instructs to Close/Bill/Dismiss Rcvd*	CT_DIS_PRE_JUD_NOTES	Instructs to Close/Bill/Dismiss Rcvd*	CT_DIS_INSTR_TO_CLOSE_DT
5	Motion to Vacate Filed*	CT_DIS_NOT_SENT_DT	Motion to Vacate Filed*	CT_DIS_MOTION_VAC_FILED_DT
6	Motion to Vacate Prepared*	RE_EV_LOCKOUT_RCVD_DT	Motion to Vacate Prepared*	CT_DIS_MOTION_VAC_PREP_DT
7	Motion to Vacate Sent for Filing*	AMD_COMPLAINT2_NEEDED	Motion to Vacate Sent for Filing*	CT_DIS_MOTION_VAC_SENT_DT
8	Notice of Dismissal Filed*	CT_DIS_INSTR_TO_CLOSE_DT	Notice of Dismissal Filed*	CT_DIS_NOT_FILED_DT
9	Notice of Dismissal Prepared*	NOFI_LOSS_MIT_PLAN_FILED	Notice of Dismissal Prepared*	CT_DIS_NOT_PREP_DT
10	Notice of Dismissal Sent for Filing*	jfigs_hear_date	Notice of Dismissal Sent for Filing*	CT_DIS_NOT_SENT_DT
11	Order Vacating Entered*	CT_DIS_NOT_PREP_DT	Order Vacating Entered*	CT_DIS_MOTION_VAC_ENTERED_DT
12	Post-Judgment Vacate Notes*	jfigs_filing_fee	Post-Judgment Vacate Notes*	CT_DIS_POST_JUD_NOTES
13	Pre-Judgment Dismissal Notes*	CT_FHA_NOH_DEADLINE_DT	Pre-Judgment Dismissal Notes*	CT_DIS_PRE_JUD_NOTES
14	1st Legal Action	CT_DIS_MOTION_VAC_SENT_DT	1st Legal Action	CT_FHA_NOH_DEADLINE_DT
15	Amended Complaint1 Needed	CMPLNT_SENT	Amended Complaint1 Needed	AMD_COMPLAINT1_NEEDED
16	Amended Complaint1 Prepared	CT_DISMISS_FEE_APPROVAL_REQUEST	Amended Complaint1 Prepared	AMD_COMPLAINT1_PREPARED
17	Amended Complaint1 Reason	CT_DIS_NOT_FILED_DT	Amended Complaint1 Reason	AMD_COMPLAINT1_REASON
18	Amended Complaint2 Needed	CMPLNT_Received	Amended Complaint2 Needed	AMD_COMPLAINT2_NEEDED
19	Complaint Prepared	CMPLNT_PEPARED	Complaint Prepared	CMPLNT_PEPARED
20	Complaint Sent	AMD_COMPLAINT1_REASON	Complaint Sent	CMPLNT_SENT
21	Complaint Received	CT_DIS_MOTION_VAC_PREP_DT	Complaint Received	CMPLNT_Received
22	Lockout Date Notification Rcvd	CT_DIS_MOTION_VAC_FILED_DT	Lockout Date Notification Rcvd	RE_EV_LOCKOUT_RCVD_DT
23	Loss Mit Plan Entered	CT_DIS_MOTION_VAC_ENTERED_DT	Loss Mit Plan Entered	NOFI_LOSS_MIT_PLAN_ENTERED
24	Loss Mit Plan Filed	jfigs_client_item5_name2	Loss Mit Plan Filed	NOFI_LOSS_MIT_PLAN_FILED
25	NOFI Resolved	NOFI_LOSS_MIT_PLAN_ENTERED	NOFI Resolved	jfigs_client_item5_name2
26	NOFI Status	AMD_COMPLAINT1_NEEDED	NOFI Status	jfigs_filing_fee
27	NOFI/Lmit Approved - Denied	CT_DIS_POST_JUD_NOTES	NOFI/Lmit Approved - Denied	jfigs_hear_date
28	NOFI/Lmit Program Requested		NOFI/Lmit Program Requested
29	NOH Cert Mail Sent		NOH Cert Mail Sent
30	NOH Place of Filing		NOH Place of Filing
31	NOH Posted Date		NOH Posted Date
32	NOH Time		NOH Time
33	NOTFI Served Date		NOTFI Served Date
34	Notice of Appearance filed*		Notice of Appearance filed*
35	Notice of Appearance prep*		Notice of Appearance prep*
36	Notice of Appearance sent*		Notice of Appearance sent*

Sheet: Sheet1

**Doc.AElstein** · 09-24-2015, 06:09 PM

Hi BG1983,
_ Good to see you have got Rory’s Tool in your hand and have it up
_ I can work well with that, and get the stuff out of it.
_
_ The actual data is a bit different than your data in Post # 8.
_
_ So just clarify a bit in words please exactly what you want. I do see that Column E and F are your Output or After...
_
_ But
_
_ 1 ) Clarify where you want your output.
_
_ Best is still ideally two screenshots a before and after. I am guessing here that columns E and F are the After, sort of. And the actula before would be a sheet with just Columns A and B. That may all seem obvious to you, but remember i see it for the first time. – Maybe you actually want Columns E And F to replace A an B or maybe you want Columns E and F to be put in a second sheet in columns A and B ?
_
_ 2 ) The last 3 rows in your column F look a bit suspect, - I mean i am not clear how they fit in

_ So try and make everything as clears and obvious as possible please.

Alan

**BG1983** · 09-24-2015, 06:34 PM

Very handy tool! Yes, the data is different because I did not save the first example so went in and pulled out other examples.

Yes, the actual data before is only columns A and B. Column A would be Data Fields from Company 1 and B is Data Fields from Company 2. The two companies are merging and only company 1's data tracking software will be kept, so all data from company 2 needs to be moved over to company 1's system. The problem is that each company has different ways to track things so there are many fields that the other does not have. I am just trying to find the common ground between the two as a good base to start at.

They both are in the same business genre so the tracking and field names are very similar but not exact... its just a mater of how the person setting up the companies data tracking software named things. Since there are so many fields its tough to give you a full scope of things. I am not sure what you are referring to regarding the last 3 rows in column F? Perhaps the wording? In this type of business jfigs is also known as Judgment Figures, so many companies will name the fields JFIGS as shorthand and to save space as many fields are limited to a certain number of characters.

Hope this clarifies a little..

**Doc.AElstein** · 09-24-2015, 07:10 PM

_ 1)

Originally Posted by BG1983

... Since there are so many fields its tough to give you a full scope of things. I am not sure what you are referring to regarding the last 3 rows in column F? Perhaps the wording? In this type of business jfigs is also known as Judgment Figures, so many companies will name the fields JFIGS as shorthand and to save space as many fields are limited to a certain number of characters......

_ OK I understand. But
_ 1 a) if any other shorthand comes to mind you should post and i can allow for that.
( I am a bit computer illiterate, jus learning by answering These sort of threads, so any obvious to you shorthand may not be known to me.)
_ 1b) I still do not see a Judgment Figures in column E alongside where you have jfigs in column F

_ 2 ) I am still unclear where you want the final result to go, but that is not too important, typically I generate the result table internally and then at the end paste out in one go, so that can easily be changes later. I will stick it where ever i feel like for now unless you tell me different.

**Doc.AElstein** · 09-24-2015, 07:22 PM

Hi.....

I just editted this bit as it "fell off" my original Post # 13

_ 1b) I still do not see a Judgment Figures in column E alongside where you have jfigs in column F

**BG1983** · 09-24-2015, 07:44 PM

I will send over more sample data when I get home in a few hours. But, the output can go to column c (output will be the correlating match from column a) as to the judgment figures it may be called jfigs in either column... or jdmnt figs... or any variation the person felt like using.... but, ive noticed when names are shortened some vowels are usually removed... so hearing becomes hrng and judgment may be jdmnt... motion becomes mtn.... amend becomes amd...

**Doc.AElstein** · 09-24-2015, 07:46 PM

Originally Posted by BG1983

I will send over more sample data when I get home in a few hours. .....

OK , I will take a look tomorrow
Alan
Baveria
Germany

**BG1983** · 09-24-2015, 07:47 PM

And to clarify, not all of the data has a match.. company a will have many fields that column b does not... and vica versa.. I am just trying to find the fields that both companies share

**Doc.AElstein** · 09-25-2015, 01:22 AM

Hi,

Originally Posted by BG1983

... ... box site looks like it requires a credit card to get the free trial. ........

1 ) Please try again the
Box Net,
https://www.box.com/
http://tinyurl.com/7chr7u8
_ ........ see post #2
_ .. as far as I know it is and was always free and easy to use. Many members use it. And have been for ages and it has never cost a penny. I have used Box a lot recently , latest yesterday

.. As always you have to spend a bit of time going through the registration to get at the Free one. Like everyone else they try to get you first to try a different one that costs, maybe offering a free trial period. Normal practice.
_ ..Just google and check a bit more.
: - I just checked quickly, - , had to search a bit to get past all there adds for the trial period of stuff that costs But eventually i found this:
https://app.box.com/signup/personal
Attachment 421194

. looks still free. I will add that link now to the ones I suggestYou need the 10GB Personal option that is the only free one.
. If it really does cost now, that would be useful info you should consider sharing in , for example the Water Colors sub forum
http://www.excelforum.com/the-water-cooler/

. There are others, DropBox is another. There is a Google Thing. I did not like that, they did over do their advertisements a bit too much, and as always with Google stuff, you get loads of crap downloaded with which it spies on you. Drop Box was / is my second favourite but a lot of extra crap comes with that also.
https://www.dropbox.com/de/

( Remember to select share if you use a File sharing site ) and give ( Post in the Thread ) the link they give.

_ .........................

Originally Posted by BG1983

Hey, so I was going to send over some sample data that is 250 rows or so... by 5 columns... ...........

2) I prefer to work on limited test data. I appreciate you want a good spread to get a better idea that it will work. But it makes developing a Code a lot harder. I have a lot more tedious work to do. I am keen to help and learn using an interesting real life example. But I am not here to do a lot of tedious work. Try to reduce the Sample data, picking carefully that it demonstrate all possible scenarios. I will have a look at all data you send, but may reduce it to make it more manageable initially. Might be better if you do that, having a better idea of most representative ..

_.......................

Originally Posted by BG1983

...... .. I will save it as non-macro enabled as I am sure that is the main concern regarding sending excel docs on here. ...

_ .. It is always more sensible to send as non-macro enabled as people sensibly are not wary about opening macro enabled ones, although it should always ask when you open if you want to keep the disabled initially to be on the safe side. But I have seen here that half the attached files are .xlsm. Does NOT seem to be an issue here.

Alan

P.s. Have dropped my Email address per PM, if you still have problems getting a File across

**BG1983** · 09-25-2015, 09:55 AM

I sent over a larger sample for you to take a look at - there are many differences between the two as duplicates within 1 column are few and far between so this should give you a better understanding. Again, many of the fields will not be able to be matched, I am just trying to get a good bulk of the ones that do out of the way first. Thanks again!

**Doc.AElstein** · 09-25-2015, 11:01 AM

Hi Brent,
_ There was no File attatched to your Email
Alan

**jindon** · 09-25-2015, 12:05 PM

Here's just a sample for the data provided, not perfect though.

So, if this is not good enough, just ignore it.

**BG1983** · 09-25-2015, 12:16 PM

Unfortunately I cannot download here - can you send the code over so I can give it a shot?

**jindon** · 09-25-2015, 12:23 PM

Assuming data in col.A & B and Heading in row1.
My result appears in col.C

Please Login or Register  to view this content.

**BG1983** · 09-25-2015, 12:33 PM

Hmm, Put Company 1 data in column A, Company 2 Data in column B and ran - got error on line

If .test(temp) Then

**jindon** · 09-25-2015, 12:35 PM

Are you testing the data you provided in post #10?

http://www.excelforum.com/excel-prog...ml#post4199178

It runs fine here.

**BG1983** · 09-25-2015, 12:40 PM

Yes, but I did not include columns E and F. (Column F is the result I want so I cannot include it in my data. Only columns A and B.

**jindon** · 09-25-2015, 12:42 PM

Very strange.

It runs fine here, so I can not help you more.

**BG1983** · 09-25-2015, 12:45 PM

Okay, I got it to work for the sample data, tried it on a larger sample and got the error for the line I referenced above

**jindon** · 09-25-2015, 12:47 PM

If it runs, what do you think about the result?

If you think it is OK, I need to see more data.

**BG1983** · 09-25-2015, 01:23 PM

Yes, it matched up well with that data. Just need to be able to use it for A LOT more data, that may change... so it needs to be able to work on new data too...

https://app.box.com/s/fcmk4lgdfbznvqouwdztlx6h2yisrp0f

**jindon** · 09-25-2015, 01:30 PM

See if this is good enough

Please Login or Register  to view this content.

**BG1983** · 09-25-2015, 01:41 PM

Works pretty well.. will probably get me about 30% of the matches which is a great start and is what I am looking for. I appreciate your help!

**jindon** · 09-25-2015, 01:54 PM

You are welcome.

**Doc.AElstein** · 09-25-2015, 05:56 PM

Originally Posted by BG1983

Works pretty well.. will probably get me about 30% of the matches which is a great start and is what I am looking for. ...

Hi Brennt,
_ i leave you in then in jindon hands with this one.
_ I just took a look at his stuff.
_ It is in another world to me. I haven’t a clue!
Alan

**BG1983** · 09-27-2015, 04:39 PM

Hmm, so I gave this a try against the full amount of data and its freezing. Any suggestions..?

**jindon** · 09-27-2015, 07:05 PM

Originally Posted by BG1983

Hmm, so I gave this a try against the full amount of data and its freezing. Any suggestions..?

Due to the heavy process, it would.

Please Login or Register  to view this content.

Matching fields from 2 databases

Re: Matching fields from 2 databases

Re: Matching fields from 2 databases

Re: Matching fields from 2 databases

Re: Matching fields from 2 databases

Re: Matching fields from 2 databases

Re: Matching fields from 2 databases

Re: Matching fields from 2 databases

Re: Matching fields from 2 databases

Re: Matching fields from 2 databases

Re: Matching fields from 2 databases

Re: Matching fields from 2 databases

Re: Matching fields from 2 databases

Re: Matching fields from 2 databases

Re: Matching fields from 2 databases

Re: Matching fields from 2 databases

Re: Matching fields from 2 databases

Re: Matching fields from 2 databases

Re: Matching fields from 2 databases

Re: Matching fields from 2 databases

Re: Matching fields from 2 databases

Re: Matching fields from 2 databases

Re: Matching fields from 2 databases

Re: Matching fields from 2 databases

Re: Matching fields from 2 databases

Re: Matching fields from 2 databases

Re: Matching fields from 2 databases

Re: Matching fields from 2 databases

Re: Matching fields from 2 databases

Re: Matching fields from 2 databases

Re: Matching fields from 2 databases

Re: Matching fields from 2 databases

Re: Matching fields from 2 databases

Re: Matching fields from 2 databases

Thread Information

Users Browsing this Thread

Similar Threads

Bookmarks

Bookmarks

Posting Permissions