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.
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},))))