unique combinations in excel

Borrowing from southward's upload, if you would like a formula solutions as well ... array-enter this in a helper column C.

Formula:

Please Login or Register  to view this content.

If you aren’t familiar with array-entered formulas array enter means the formula must be committed from edit mode by simultaneously pressing and holding down Ctrl and Shift while hitting Enter.

Then this non-array in D:E

Formula:

Please Login or Register  to view this content.

Row\Col	A	B	C	D	E
1	Number	status
2	0	pay	1	0	pay
3	0	pay		1	pay
4	1	pay	3	2	pay
5	2	pay	4	0	free
6	0	free	5
7	0	pay

Got it. Thanks!!

Sure. The helper formula identifies the row number of the first occurrence of unique pairs "Number/status".

(A2=$A$2:$A$7) generates an array of TRUE/FALSE as does (B2=$B$2:$B$7). When multiplied they coerce the underlying numeric values of TRUE and FALSE .... 1 and 0. Select ((A2=$A$2:$A$7)*(B2=$B$2:$B$7)) in the formula bar and press the F9 function key on the upper keyboard.

You will see an array that looks like this. {1;1;0;0;0;1}. Reciprocating it looks like this. {1;1;#DIV/0!;#DIV/0!;#DIV/0!;1}.

MATCH ignores those errors and returns the row number of the 1st 1 (MATCH(1,{1;1;#DIV/0!;#DIV/0!;#DIV/0!;1},0)) it finds which in this case is 1.

If that match return equals the current row # of the range it returns that row #, else an "" empty string.

=IF(MATCH(1,1/((A2=$A$2:$A$7)*(B2=$B$2:$B$7)),0)=(ROW()-ROW($2:$2)+1),(ROW()-ROW($2:$2)+1),"").

The second formula sorts that resulting helper column rows ascending as you fill / copy downward with SMALL($C$2:$C$7,(ROW()-ROW($2:$2)+1)) and passes those row numbers to


INDEX(A$2:A$7,SMALL($C$2:$C$7,(ROW()-ROW($2:$2)+1))). Filled across A$2:A$7 becomes B$2:B$7.


Use the F9 function key to analyze nearly any formula, or the Evaluate Formula feature in the FORMULAS ribbon. These are among your best friends and teachers in Excel.

Has this helped?