1

I have the need to combine different excel sheets of data into one single master file.

I have one master file with e-mail addresses and several excel-files from our CRM with data in different columns.

Is it possible to create some sort of pivot where I use emailadress as a key and just add the columns I want to combine? As an example

Row 1: [email protected] John
Row 2: [email protected] Doe

Need for result

Row: [email protected] John Doe

One of the files is the master. I only want to enrich the columns in that file, if the same email address is found in the other files and only for the columns I have created in the master file.

A little simplified as I have tens of thousands of rows.

Improve this question
3
  • Can you make your question title clearer? Do you mean to say 'How to combine non-unified data in Excel based on row ID?"
    – KAE
    Commented Sep 12, 2018 at 12:19
  • Sorry, if unclear. I have about 10 sheets, all with the same columns, but not data in alla columns. In any of the 10 sheets I can have the same emailadress (but I don't know). Also, if the same emailaddress excist in one sheet it can have data in different columns than in any of the other sheet (but never different data in the same column). I also have one destination sheet with about 11000 rows (unique emailaddresses) I wan't to keep and use as the key, to fetch data from the other sheets, when it matches.
    – Per
    Commented Sep 12, 2018 at 12:27
  • I'd recommend to attach all Excel files to a MS Access database (or import data into any DBMS database available for you) and to obtain the result you need by one query. Alternatively you can do it by macro (VBA sub) - it may be simple enough...
    – Akina
    Commented Sep 12, 2018 at 12:49

1 Answer 1

Reset to default
0

Sorry cannot comment so have to post here.

By my understanding, you have a master file that contains the email addresses, and several other CRM dumps that contain user names as well as email addresses.

Correct if my assumption is wrong, because I see there is no way to "match" email addresses if they are not present in both files.

Now, if my assumption is correct, what you need is to grab every name from CRM dumps that matches the email address in master file. This can be done in a few ways:

  1. VBA

I'll write down the algorithm here with pseudo code:

For each email in master_file Dim concatedName as String For each name in CRM_dump If email_of_name = email Then String = String + name + " " End If Next name Dump concatedName into the neighboring cell Next email

  1. Function

TBH this would be a complicated function because we have no idea how many matches there are. I have a reference for you:

http://eimagine.com/how-to-return-multiple-match-values-in-excel-using-index-match-or-vlookup/

  1. Power Query

Found this one might be of use to you:

https://stackoverflow.com/questions/32767117/pivot-in-excel-without-aggregation-to-show-text-not-numbers

Improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .