Combing lists

Not sure how to best describe this, but I have 2 lists, the first being unique communities, and the second being categories that apply to all communities. I was to organize my data so in one column I have the first community and the second column I have the 50 categories, then list the second community and the same 50 categories, etc. I know I can just do this manually, but it's a lot of data a feel like there has to be a more efficient way.

Community 1: Trade 1
Trade 2
Trade 3
Cummunity 2: Trade 1
Trade 2
Trade 3

Thanks,

Glenn,

Thanks! Yes, I actually would prefer the community in every row. I used your above formulas and it did the trick, without the repeating community. By data set is actually 175 community with 53 categories, so over 9000 rows when all is said and done, so if you could let me know the trick to getting the community to repeat that would be awesome. Thanks again!

If you did repeat the community down the column, you could always make it look like it only appears once using conditional formatting (i.e. use a white font if the current cell is the same as the previous cell).

I haven't looked at the other part of it in detail - I'll have a coffee and check back later to see if anyone else has submitted a reply for that.

Hope this helps.

Pete

Or, you could use this formula in F2:

=IF(ROWS($1:1)>(COUNTA(A:A)-1)*(COUNTA(B:B)-1),"",INDEX(A:A,INT((ROWS($1:1)-1)/(COUNTA(B:B)-1))+2))

then copy down as far as you need to.

Hope this helps.

Pete