A concatenate formula to create a list of all and any possible combinations of rows in excel (OSX 2008)
Posted on 2010-08-31
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!