3

Say I have list of users and their info. Each user can have multiple accounts, and the "paid" status of each of those accounts may be "no" or "yes". A user may have one or two paid accounts.

My first sheet looks like this (fake data):

enter image description here

This is my second sheet:

enter image description here

I want to join both sheets so they look like this (with merged cells):

enter image description here

Here I have listed only two accounts per user, but there may be more accounts or less accounts than that.

How do I accomplish this?

  • If instead of merged cells in columns A,B,C you wanted linebreaks in columns D and F, the task could be easily done by Filter and Join commands. But merging requires a script, specifically mergeVertically method. – user79865 May 5 '15 at 5:59
  • @Yes: a quick-and-dirty alternative is to use Pivot Table as an intermediate step :) – Rubén May 7 '15 at 23:55
2

I would suggest using dget in the first sheet, if the number of users is defined, and doesn't grow automatically.

Assuming the second sheet is Sheet2, I would add 2 columns to the first sheet.

The first column would have cells with the formula (changing the cells accordingly, with copy and paste):

=dget(Sheet2!$A:$C,"PAID ACCOUNT",{"USER ID";$A2})

The second column would have cells with the formula (changing the cells accordingly, with copy and paste):

=dget(Sheet2!$A:$C,"ACCOUNT ID",{"USER ID";$A2})

You could also try a more direct approach, by using cell references in the "field" section, such as:

=dget(Sheet2!$A:$C,Sheet2!B$1,{"USER ID";$A2})

After this, you would only need to copy and paste the formula in all the cells that require importing.

If the list grows or is fed from a form, then this solution would not apply.

1

One alternative is to join both tables using formulas, then use Pivot Table and Copy and paste values.

To join both tables using formulas, in the second sheet add the following formulas assuming that your first sheet name is Sheet1:
In D2

=INDEX(Sheet1!B:B,MATCH(A2,Sheet1!A:A,0)  

In E2

=INDEX(Sheet1!C:C,MATCH(A2,Sheet1!A:A,0)  

Then fill down to the last row of the range with values.

Insert a Pivot Table and set the columns as row headers. Copy and paste the values of the Pivot Table to another sheet.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.