+ Reply to Thread
Results 1 to 11 of 11

Can I turn this database into something useful?...

  1. #1
    Registered User
    Join Date
    06-28-2013
    Location
    wales
    MS-Off Ver
    excel 2007
    Posts
    6

    Can I turn this database into something useful?...

    Hi all here I'm new here so please take it easy on me

    I have this spreadsheet:

    EXCEL.jpg

    It has been exported from a certain CRM system and I will admit myself it is a mess.

    Is there any way to tidy this up at all and putting all the data present into 1 single row for each contact.

    I've thought of a couple of possible ways of doing it but have not had any success as of yet, so wanted to try to pick somebody else's brains.

    Any advice is very much appreciated.

    Thanks
    Paul

  2. #2
    Forum Guru (RIP) Marcol's Avatar
    Join Date
    12-23-2009
    Location
    Fife, Scotland
    MS-Off Ver
    Excel '97 & 2003/7
    Posts
    7,216

    Re: Can I turn this database into something useful?...

    Post the workbook, not a picture of it ...

    Attach a sample workbook. Make sure there is just enough data to make it clear what is needed. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are demonstrated, mock them up manually if needed. Remember to desensitize the data.

    Click on GO ADVANCED and use the paperclip icon to open the upload window.

    View Pic

  3. #3
    Registered User
    Join Date
    06-28-2013
    Location
    wales
    MS-Off Ver
    excel 2007
    Posts
    6

    Re: Can I turn this database into something useful?...

    Sorry.

    I have now found another possible problem in that contacts with several contact numbers and e-mail addresses need to be duplicated at first, but first of all it would be nice if I could just get down to a more manageable spreadsheet.

    Here is my before and after worksheet.

    Thanks again.
    Attached Files Attached Files

  4. #4
    Forum Expert newdoverman's Avatar
    Join Date
    02-07-2013
    Location
    Port Dover, Ontario, Canada
    MS-Off Ver
    2010
    Posts
    10,330

    Re: Can I turn this database into something useful?...

    A relational database might be better suited to handle data like this.
    <---------If you like someone's answer, click the star to the left of one of their posts to give them a reputation point for that answer.
    Ron W

  5. #5
    Forum Guru (RIP) Marcol's Avatar
    Join Date
    12-23-2009
    Location
    Fife, Scotland
    MS-Off Ver
    Excel '97 & 2003/7
    Posts
    7,216

    Re: Can I turn this database into something useful?...

    Perhaps I have gone headlong down the wrong road, but here it is for what it's worth ...

    Your "Before" sheet doesn't have some of the values expected in your "After" sheet ...

    I have colour filled the headers where the formulae are similar.

    In M2 there is an CSE array, this must be confirmed with Ctrl+Shift+Enter before dragging down. (ORGANISATION_ID)

    Maybe this will help towards a final solution ...
    Attached Files Attached Files
    If you need any more information, please feel free to ask.

    However,If this takes care of your needs, please select Thread Tools from menu above and set this topic to SOLVED. It helps everybody! ....

    Also
    اس کی مدد کرتا ہے اگر
    شکریہ کہنے کے لئے سٹار کلک کریں
    If you are satisfied by any members response to your problem please consider using the small Star icon bottom left of their post to show your appreciation.

  6. #6
    Registered User
    Join Date
    06-28-2013
    Location
    wales
    MS-Off Ver
    excel 2007
    Posts
    6

    Re: Can I turn this database into something useful?...

    Hello,

    Sorry, after checking the organisation IDs in the before and after sheets I see what you mean. They should match, they do in my original spreadsheet but this was just a mock up and I forgot to change the organisation IDs.

    What I basically want to do is take out the duplicate contacts but also somehow pick up all the data from each row that has the same contact ID in and put it into the 1 row.

    Pesky CRM exports!

  7. #7
    Forum Guru Kyle123's Avatar
    Join Date
    03-10-2010
    Location
    Leeds
    MS-Off Ver
    365 Win 11
    Posts
    7,238

    Re: Can I turn this database into something useful?...

    It would be a lot easier to to query the data from your crm that you actually want

  8. #8
    Registered User
    Join Date
    06-28-2013
    Location
    wales
    MS-Off Ver
    excel 2007
    Posts
    6

    Re: Can I turn this database into something useful?...

    The CRM would sadly only allow XML export.

    Any other ideas?

  9. #9
    Forum Guru Kyle123's Avatar
    Join Date
    03-10-2010
    Location
    Leeds
    MS-Off Ver
    365 Win 11
    Posts
    7,238

    Re: Can I turn this database into something useful?...

    So where are you getting it from then?

  10. #10
    Registered User
    Join Date
    06-28-2013
    Location
    wales
    MS-Off Ver
    excel 2007
    Posts
    6

    Re: Can I turn this database into something useful?...

    This is the XML file for Contacts in Excel format.

  11. #11
    Registered User
    Join Date
    06-28-2013
    Location
    wales
    MS-Off Ver
    excel 2007
    Posts
    6

    Re: Can I turn this database into something useful?...

    Any ideas? I'm struggling on where to go next.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1