google sheets
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):
This is my second sheet:
I want to join both sheets so they look like this (with merged cells):
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?
Use a combination of the ARRAYFORMULA, SUM and arithmetic operations
Here's the function to count all of the clicks by a 26 year old:
=ARRAYFORMULA(sum((A:A=26) * (C:C="true")))
There are 3 parts to this operation.
Essentially, true is being converted to 1 and false is being converted to 0. Boolean logic is done by using arithmetic operations.
An AND operation uses multiplication:
An OR operation uses a combination of the *SIGN function and addition:
Note: The sign function is necessary because of the way boolean addition works differently than arithmetic addition. Basically in boolean addition 1 + 1 = 1, in arithmetic addition 1 + 1 = 2. Obviously, arithmetic addition will mess up the count so you need to run the results of the addition operations through a sign function. The sign function returns 1 if the value is positive, 0 if the value is 0, and -1 if the value is negative.
Lets say you wanted to count the clicks for all users between age 20-25:
=ARRAYFORMULA(sum(sign((A:A=20) + (A:A=21) + (A:A=22) + (A:A=23) + (A:A=24) + (A:A=25)) * (C:C="true")))
Use a custom function.
function result(range) {
var splitCol = 1; // split on column B
var output2 = [];
for(var i=0, iLen=range.length; i<iLen; i++) {
var s = range[i][splitCol].split("\n");
for(var j=0, jLen=s.length; j<jLen; j++) {
var output1 = [];
for(var k=0, kLen=range[0].length; k<kLen; k++) {
if(k == splitCol) {
output1.push(s[j]);
} else {
output1.push(range[i][k]);
}
}
output2.push(output1);
}
}
return output2;
}
The script evaluates each row, and particularly the second column of each row (in JavaScript arrays are zero based, so column 2 corresponds to index 1 of the array). It splits the contents of that cell, into multiple values and uses the "\n" as delimiter (line feed). After that it adds the existing info to an array and only add the individual results, when it hits index 1 (k == 1). The newly prepared row is then added to another array, that's being returned to show the result.
data
result
I've created an example file for you: multi-line cells into new rows.
Best Answer
I would suggest using
dgetin 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):
The second column would have cells with the formula (changing the cells accordingly, with copy and paste):
You could also try a more direct approach, by using cell references in the "field" section, such as:
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.