Arrayformula generating duplicates in Google Sheets

Question

I seem to have an arrayformula which is generating duplicates for me.

I'll show you the code in column D and then explain the problem:

=arrayformula(
if (C1225:C$1684="A",A1225:A$1684, 
if (C1225:C$1684="B",B1225:B$1684,
if (C1225:C$1684="AB",{A1225:A$1684, B1225:B$1684},""))))

If A or B, it should take the content of A or B and put it in D. So I want cell E to be blank UNLESS the content in C was "AB" - only then do I want two cells populated with the data from A and B.

At the moment it's putting out A twice.

For No One|No Reply|ab|For No One|No Reply
Across The Universe (Let It Be Naked...)|The End|a|Across The Universe (Let It Be Naked...)|Across The Universe (Let It Be Naked...)
All You Need Is Love|Twist And Shout|b|Twist And Shout|Twist And Shout

So the first row is OK, but the second two are generating unwanted duplicates.

Karl_S Karl_S · Accepted Answer · 2017-08-14T13:30:31

The problem you are experiencing is because the ARRAYFORMULA is making all responses an Array of 2 items. Since the end result has this size, all results must have the same size.

Try this, changing the ranges to match your needs as well as the 10:

=arrayformula(
if (C2:C$11="A",{A2:A$11, transpose(split(rept(", ", 10), ",", TRUE))}, 
if (C2:C$11="B",{B2:B$11, transpose(split(rept(", ", 10), ",", TRUE))},
if (C2:C$11="AB",{A2:A$11, B2:B$11},))))

The rept(", ", 10) portion creates a text string which is ", " repeated 10 times, or ", , , , , , , , , , "

Split() then splits this into an entry for each comma, removing the comma, so a series of spaces in this case. The TRUE tells Split to do this for every occurrence of the comma, so it becomes:

split(", , , , , , , , , , ", ",", TRUE)

I then use transpose () to change this into rows instead of columns. This needs to be the same number of rows as the other items in the array I am creating using the brackets so I basically get: {A2:A$10, [Make_Blank_Entries_For_Each_Row]}

So if I did my math correctly, you should use:

=arrayformula(
if (C1225:C$1684="A",{A1225:A$1684, transpose(split(rept(", ", 460), ",", TRUE))}, 
if (C1225:C$11="B",{B1225:B$1684, transpose(split(rept(", ", 460), ",", TRUE))},
if (C1225:C$11="AB",{A1225:A$1684, B2:B$11},))))

Arrayformula generating duplicates in Google Sheets

1 Answers