Solved

# A concatenate formula to create a list of all and any possible combinations of rows in excel (OSX 2008)

Posted on 2010-08-31

Hello,

I have looked into every nook and simply cannot find the exit here, so if anybody can, please provide some insight.. (Mac OSX user - MS Excel 2008 (no VBA)

I have a list of content categories (string) from different sources (index) and my task is to create a list for a mapping document that will concatenate (or not) each row from each source in all and any combination(s) (there are about 1000).

This formula is the closest that I have found (assumes 5 columns (not rows, but I can transpose them):

=$A$1 & IF(MOD(ROW(),2)>=1,"+" & B$1,"")& IF(MOD(ROW(),4)>=2,"+" & C$1,"")& IF(MOD(ROW(),8)>=4,"+" & D$1,"")& IF(MOD(ROW(),16)>=8,"+" & E$1,"")

It looks as though the iterations (n^y) are programmed manually, although I could conceivably see these intervals being read form a list elsewhere.

I will need to keep the association between each category and its source index (maybe some kind of delimiter within another) and also apply a new index to each resulting group (unique to group).

I hope that's enough information?

Many thanks in advance!

AF