Linking up data from 2 different sheets with a unique ID

[CC4581]

Hi all,

I am looking for a way to link up data from 2 different sheets so all the data is correctly organised. The sheets were exported from a program we used to export all information from a CRM application in anticipation of importing the data into a bespoke CRM system currently being developed.

In one of the sheets, we have all the contact information (Name, Company, Telephone, Email etc.) and in the other we have notes which are what the sales team use to keep notes on each client (things like a favourite football team).

We need to be able to import all of the client information, and these notes into the new system. The system has been designed to accommodate this, but the only issue we've encountered is that in having the information in 2 different sheets, we need a way to be able to ensure all of this information is imported together. The only information that is identical in both of these sheets is a Contact ID which is what the currently used CRM system assigns to each unique contact.

I have attached an example Excel sheet (with fake data) with 3 tabs. The first two are an example of how the data is currently as of export in both sheets (Contacts and Notes) The third is how we need the information to appear so it is ready for import.

Obviously the example is somewhat rough (doesn't include nearly all the fields we will require such as phone number etc.) but I just wanted to give you an idea of what we need and how it needs to go together. The example is also an .xlsx file, but the ones we're working with and will be importing are actually .csv

If you need any more info from me, please let me know!

[Pete_UK]

You can merge these together in Excel to produce the 3rd sheet, and then you can export that as a .csv file in order to import it into your new CRM system.

Can you guarantee that you will have at least one note for each person listed in the Contact sheet?

Also, do you not need the Contact ID in the third sheet, or will the new CRM allocate its own when you import the data?

Pete

[CC4581]

Hi Pete, thanks for your reply.

The issue with merging them is that we will be using the company name as an identifier for what is a new entry. So when importing the data, it will follow the example shown where each note has its own row, but no other fields other than "note date" will be filled in until it reaches the next company. Then the system will be able to create a separate entry for that company and import all notes relevant to that company and so on. That's my basic understanding on how the import will work based on what I have seen; I am not a developer so I do not know the in depth details of it, I have simply been tasked with getting the data in the requested format. Unless I have misunderstood your idea on merging the sheets?

The Contact ID will not be necessary once the import occurs, as mentioned above, the system will determine what is a new entry by checking to see if there is any data in the Company field. However, it is the only unique piece of data that links the sheets together. It also identifies unique clients, and not companies.

The reason a semi-automated solution of organising this data is needed is because each member of the sales team has their own unique database, and each one can have anywhere from 500-8,000 clients in..

[Pete_UK]

I've come up with a solution for you, but it is getting late here so I'll post it in the morning with a full description and explanations.

Pete

[CC4581]

Hi Pete,

That will be brilliant, thanks!

[AliGW]

Please don't quote whole posts, especially when you are responding to the one immediately preceding your own - it's just clutter. It's OK to quote if you are responding to a post out of sequence, but limit quoted content to a few relevant lines that makes clear to whom and what you are responding. Thanks!

For normal conversational replies, try using the QUICK REPLY box below.

[Pete_UK]

It is important to pick up ALL the companies, including those that do not have any comments recorded for them, so in the attached file I have amended your Contact sheet by adding two new companies (shown in red) for which there is no comment in the Notes sheet. I have put this formula in cell E2:

=IF(D2="","-",IF(COUNTIF(Notes!$A:$A,D2)=0,1,COUNTIF(Notes!$A:$A,D2)))

which counts the number of entries in the Notes sheet for that company, or returns 1 if there are no entries, so this represents the number of records that you will need to generate in the Merged sheet. I have put zero in cell F1 (important) and this formula in F2:

=IF(E2="-","",E2+F1)

and this gives a cumulative count of the records. Both these formulae can be copied down to beyond the bottom of your data - I've copied to row 9 in the example, as can be seen by the hyphens in column E.

In the Notes sheet I have added this formula to cell E2:

=IF(A2="","-",A2&"_"&COUNTIF(A$2:A2,A2))

and again copied down to beyond the data. This generates a unique reference for each ContactID by adding a sequential number (and underscore) to the end.

In the Merged sheet I have used this helper formula in cell A2:

=IF(ROWS($1:1)>MAX(Contact!$F:$F),"-",MATCH(ROWS($1:1)-1,Contact!$F:$F)+1)

and when this is copied down it generates the row number in the Contact sheet where the record can be found. The row numbers are duplicated where there are comments for a particular record, so you will see three 2's, then two 3's and so on in the example file. Again, copy this down until you start to get hyphens. All the helper columns are coloured blue, as this one is - when you have generated the merged file and are ready to produce the .csv file for the import, you can hide this column so that it does not appear in the .csv file.

You can bring across the appropriate Company by using this formula in B2:

=IF(OR($A2="-",$A2=""),"",IF($A2=$A1,"",INDEX(Contact!A:A,$A2)))

and this will produce blanks for those records which are duplicated. The formula can also be copied into C2:D2 and copied down, to bring the other data fields from the Contact sheet. Cell E2 uses this formula:

=IF(OR($A2="-",$A2=""),"",IFERROR(INDEX(Notes!C:C,MATCH(INDEX(Contact!$D:$D,$A2)&"_"&COUNTIF($A$2:$A2,$A2),Notes!$E:$E,0)),""))

and this can be copied into cell F2, and these bring the comment fields from the Notes sheet. Column E needs to be formatted as a date. All the formulae in A2:F2 should be copied down until you get hyphens in column A.

As mentioned earlier, to prepare the .csv file you can hide column A and then use File | Save As on that sheet. Alternatively, you can fix all the values and then delete column A, and then save the sheet as a .csv file.

Hope this helps.

Pete

[CC4581]

Hi Pete,

That's wonderful, thank you so much for your hard work on this. The process described works perfectly once I have adapted it to the fields I have and has helped avoid a long manual process of ordering the data!

[Pete_UK]

Glad to hear it worked for you.

If that takes care of your original question, please take a moment to select Thread Tools from the menu above your first post and mark this thread as SOLVED.

Also, since you are relatively new to the forum, you might like to know that you can directly thank those who have helped you by clicking on the small "star" icon located in the lower left corner of a post that you have found to be helpful (not just in this thread - for any post that has helped you). This also adds to the reputation of the poster (the small green bars in the poster's profile).

Pete