Hi. The attached example represents what I am looking for.
Array 1: List of names
Array 2: List of activities
Result I want: I want every name to be associated to every single activity. So 5 names and 3 activities would result in an array that is 2*15.
Is there a formula based approach to doing this?
Thank you
1) I moved the lists into columns A and B to eliminate blank cells
2) I created two dynamic named ranges, NameList and StepList. Check the Ctrl-F3 Name Wizard to see the formulas used. You can add to the column A and B lists and these named ranges will expand themselves automatically.
3) I've added formulas in D2 and E2, then copied down a ways. These formulas will create a new list for you based on the number of items in the two lists.
D2: =IFERROR(INDEX(NameList,INT((ROW()+1)/(COUNTA(StepList))-1)+1),"")
E2: =IF(D2="", "", INDEX(StepList, IF(MOD(ROW()-1,COUNTA(StepList))=0, COUNTA(StepList), MOD(ROW()-1,COUNTA(StepList)))))
_________________
Microsoft MVP 2010 - Excel
Visit: Jerry Beaucaire's Excel Files & Macros
If you've been given good help, use theicon below to give reputation feedback, it is appreciated.
Always put your code between code tags. [CODE] your code here [/CODE]
?None of us is as good as all of us? - Ray Kroc
?Actually, I *am* a rocket scientist.? - JB (little ones count!)
Thank you very much.
You had a question? Post the question here along with a sample doc that demonstrates the problem.
Attached is the problem I am having with the original formula. When I add to the Data column, the formula produces a result that I can't fix.
I've put slightly different formulae in G and H - these seem to work.
Hope ths helps.
Pete
Thanks Pete, very nice!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks