Expanding User Referrals - Alternative Methods?

[JingoWingo1]

Hi all,

I have had to remove the real names for GDPR reasons, however, I have got 750 lines of customer data as follows:

1.PNG

Essentially, this data tells us information about clients that are on affiliate marketing programmes with us and gives us a rundown of how many customers they have referred to us.

For the purpose of this question, I have simplified the data.

What the columns mean:

Everything from A to J tells us about the user information, so the person that is signed up to our affiliate marketing scheme

K tells us how many users said person has referred

L gives us the emails of the users that they have referred

M gives us the User ID of the person that they have referred, alongside the date and time that they registered

What I want to do:

I want to be able to take each line and make it look like this:

What I am currently doing:

1.) I take one of the lines, from A to L, and I copy it onto Sheet 2

2.PNG

2.) I then select the relevant row in column L that relates to the user that I just copied across and click data > text to cells > delimit > comma

This then splits up all of the emails that said user has referred (all are in the format of being separated by commas), I then output in 'general'.

3.PNG

3.) I then highlight all of the separated emails and transpose, so that they go downwards.

4.PNG

5.PNG

4.) I then copy the data from the same row in Sheet 1 from column M, which contains the User ID and date of joining and paste it into Sheet 2

I am no longer able to attach any more images due to a cap, but essentially, I then text to columns again and delimit, I then bring down the User ID and the date information and drag it all down.

The data from A to K is also brought down, so that I can see in a simple table which users have been referred by who.

I essentially need to do this for 750 lines, is there any faster way?

[Bernie Deitrick]

You can use Power Query to quickly manage that. Here are the steps to use - if you have issues, share a sanitized version of your workbook, and I will add the PQ to it.

Select your data set, and convert it to a table.

Use Data / "From Table" (in the "Get & Transform" section)

That will open a new window that you will be working in.

Select the column with your comma delimited emails and use "Transform" / "Replace Values" and replace "comma""space" with just "comma" Where "comma" is the single character "," etc.

Then use "Split Column" delimited, by comma.

Select the new columns of split-out emails, and use "Unpivot Columns"

There will be 2 new columns - one with 'header' values and one with the emails. Delete the 'headers' column, and then drag the email column to the left to reposition it between the columns where it started.

Then choose File, / "Close and Load" and you are done.

Anytime you add new data to or delete old data from your table, simply use Data / Refresh All and a new table with all the added / deleted data properly handled will be created.

[alex5421]

this is pretty much helpful