mercredi 26 juin 2019

ArrayFormula - If cell contains match, combine other cells with TEXTJOIN

I have a Google Sheet that contains names of characters, together with corresponding values for group name, "selected" and attack power. It looks like this:

Sheet1

| NAME     | GROUP NAME | SELECTED  | ATTACK POWER |
|:---------|:-----------|----------:|-------------:|
| guile    | Team Red   |         1 |          333 | 
|----------|------------|-----------|--------------|
| blanka   | Team Red   |         1 |           50 |
|----------|------------|-----------|--------------|
| sagat    | Team Red   |           |          500 |
|----------|------------|-----------|--------------|
| ruy      | Team Blue  |         1 |          450 |
|----------|------------|-----------|--------------|
| vega     | Team Blue  |         2 |          150 |

Sheet2

In my second sheet I have two columns. Group name, which contains names of each team from Sheet1 and names, which contains my current ArrayFormula:

=ARRAYFORMULA(TEXTJOIN(CHAR(10); 1; REPT('Sheet1'!A:A; 1*('Sheet1'!B:B=A2))))

Using this formula I can combine all characters into one cell (with textjoin, repeated with row breaks) based on the value in Group name. Result looks like the following:

| GROUP NAME | NAME                      |
|:-----------|:--------------------------|
| Team Red   | guile                     |         
|            | blanka                    |
|            | sagat                     |
|------------|---------------------------|
| Team Blue  | ruy                       |
|            | vega                      |
|------------|---------------------------|

The problem is that I only want to combine the characters with have a selected value of 1. End-result should instead look like this:

| GROUP NAME | NAME                      |
|:-----------|:--------------------------|
| Team Red   | guile                     |         
|            | blanka                    |
|------------|---------------------------|
| Team Blue  | ruy                       |
|------------|---------------------------|

I tried the following setup using a IF-statement, but it just returns a string of FALSE:

=ARRAYFORMULA(TEXTJOIN(CHAR(10); 1; REPT(IF('Sheet1'!C:C="1";'Sheet1'!A:A); 1*('Sheet1'!B:B=A2))))

Can this be one?

Aucun commentaire:

Enregistrer un commentaire