Possible Combinations/Permutations Question

[pikachu]

I've got a question with regards to combinations, which is: I need to create combinations (permutations) for the content in two columns, but I only want combinations specific to unique items in Column A (and the matching data in Column B). Here's an example:

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]1234[/TD]
[TD]ABC0[/TD]
[/TR]
[TR]
[TD]1234[/TD]
[TD]ABC1[/TD]
[/TR]
[TR]
[TD]1234[/TD]
[TD]ABC2[/TD]
[/TR]
[TR]
[TD]1234[/TD]
[TD]ABC3[/TD]
[/TR]
[TR]
[TD]5678[/TD]
[TD]XYZ1[/TD]
[/TR]
[TR]
[TD]5678[/TD]
[TD]XYZ2[/TD]
[/TR]
</tbody>[/TABLE]

I want to pull so I get the following:

1234 ABC0
1234 ABC1
1234 ABC2
1234 ABC3
5678 XYZ1
5678 XYZ2

And not:

1234 ABC0
1234 ABC1
1234 ABC2
1234 ABC3
1234 XYZ1
1234 XYZ2
5678 ABC0
5678 ABC1
5678 ABC2 ...

I have thousands of rows of info, so it would be too time-consuming to break out all the unique values in column A. The formula I'm using is:

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]=IF(ROW()-ROW($F$2)+1>COUNTA(A:A)*COUNTA(B:B),"",INDEX(A:A,INT((ROW()-ROW($F$2))/COUNTA(B:B)+1))&INDEX(B:B,MOD(ROW()-ROW($F$2),COUNTA(B:B))+1))[/TD]
[/TR]
</tbody>[/TABLE]


... As always, thanks! You fine people are lifesavers. :D

 

[Special-K99]

Does the value in column B ever repeat, ie are there duplicate values in column B?

 

[Special-K99]

Isn't this just simply...

in C1
=A1

in D1
=B1
and copy down

in C2
=IF(A2=A1,A1,A2)
and copy down

 

[pikachu]

Yeah, I have to apologize. I read this over just now and realize how stupid my original question sounded. Here's what I'm looking for. Consider the following table:

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]IT 123
[/TD]
[TD]IT 123[/TD]
[/TR]
[TR]
[TD]IT 456[/TD]
[TD]IT 456[/TD]
[/TR]
[TR]
[TD]IT 789[/TD]
[TD]IT 789[/TD]
[/TR]
[TR]
[TD]EM 135[/TD]
[TD]EM135[/TD]
[/TR]
[TR]
[TD]EM 246[/TD]
[TD]EM 246[/TD]
[/TR]
[TR]
[TD]EM 357[/TD]
[TD]EM 357[/TD]
[/TR]
</tbody>[/TABLE]

Okay. I want to do the same thing I described earlier for this table. I should have 9 total combinations each for "IT" items and "EM" items (rather than the 36 possible combinations for everything). What's the best way to do this?

Hopefully that makes more sense than me literally asking to make a copy of two columns, hahah. Thanks!

 

[pikachu]

Bump!

 

[Roderick_E]

I still don't understand the question, sorry

 

[shg]

The two columns in post #4 are identical but for a missing space in B4.