Formula to list each unique value in a list only once in a different column

[asavocch]

I have two columns of data, one is a 3 digit alphabetical code denoting a record type and the second is a 6 digit numeric code indicating a supplier. I need a formula that will list, in a 3rd column the unique records in the 2nd column according to the record type. I need to have the record types grouped together and the suppliers sorted ascending.

Ideally, the solution would work as shown with the formula producing the data in column 3: I don't want to use a macro for this, it needs to be a formula and I cannot perform any filtering. This is a fixed format worksheet and can't be changed. Please help.

Col1 Col2 Col3
RBT 121011 RBT121011
RBT 121011 RBT122011
RBT 122011 RBT122012
RBT 122012 PKI403011
PKI 403011 PKI 403012
PKI 403011 BLK403011
PKI 403012 BLK403012
PKI 403012 BLK403011
BLK 403011 BLK403012
BLK 403011 BLK701011
BLK 403012 BLK701012
BLK 701011
BLK 701011
BLK 701012

I'm currently using an INDEX($A3:$A$34,MATCH(TRUE,INDEX($A3:$A$34<>A1,0),0)) formula which only partially works. It cannot negotiate the values in the first column to group them and instead skips them even if they are a different record type.

 

[hiker95]

asavocch,

Welcome to the MrExcel forum.


Using a helper column B. The below will create a uqique sorted list in column C.

Excel Workbook
	A	B	C	D
1	RBT	121011	RBT121011	BLK403011
2	RBT	121011	RBT121011	BLK403012
3	RBT	122011	RBT122011	BLK701011
4	RBT	122012	RBT122012	BLK701012
5	PKI	403011	PKI403011	PKI403011
6	PKI	403011	PKI403011	PKI403012
7	PKI	403012	PKI403012	RBT121011
8	PKI	403012	PKI403012	RBT122011
9	BLK	403011	BLK403011	RBT122012
10	BLK	403011	BLK403011
11	BLK	403012	BLK403012
12	BLK	701011	BLK701011
13	BLK	701011	BLK701011
14	BLK	701012	BLK701012
15
Sheet1

The formula in cell C1 copied down to the end of your data in column B:

=A1&B1





The array formula in cell D1, confirmed with CTRL + SHIFT + ENTER (not just ENTER) copied down until the cell displays a blank cell:

=IF(ROWS($D$1:D1)>SUMPRODUCT(($C$1:$C$14<>"")/COUNTIF($C$1:$C$14,$C$1:$C$14&"")),"",INDEX($C$1:$C$14,MATCH(SMALL(IF(COUNTIF(OFFSET($C$1,0,0,ROW($C$1:$C$14)-ROW($C$1)+1,1),$C$1:$C$14)=1,COUNTIF($C$1:$C$14,"<"&$C$1:$C$14)),ROWS($D$1:D1)),IF(COUNTIF(OFFSET($C$1,0,0,ROW($C$1:$C$14)-ROW($C$1)+1,1),$C$1:$C$14)=1,COUNTIF($C$1:$C$14,"<"&$C$1:$C$14)),0)))

 

[hiker95]

asavocch,

Using a helper column C. The above will create a uqique sorted list in column D.

 

[MikeWx]

asavocch,

Your request was for a unique list of values, and the following array formula will create the list without a helper column

Sheet1

<TABLE style="BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; FONT-FAMILY: Calibri,Arial; FONT-SIZE: 11pt" border=1 cellSpacing=0 cellPadding=0><COLGROUP><COL style="WIDTH: 30px; FONT-WEIGHT: bold"><COL style="WIDTH: 72px"></COLGROUP><TBODY><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt; FONT-WEIGHT: bold"><TD> </TD><TD>C</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">3</TD><TD>RBT121011</TD></TR></TBODY></TABLE>
<TABLE style="BORDER-BOTTOM-STYLE: groove; BORDER-BOTTOM-COLOR: #00ff00; BORDER-RIGHT-STYLE: groove; BACKGROUND-COLOR: #fffcf9; BORDER-TOP-COLOR: #00ff00; FONT-FAMILY: Arial; BORDER-TOP-STYLE: groove; COLOR: #000000; BORDER-RIGHT-COLOR: #00ff00; FONT-SIZE: 10pt; BORDER-LEFT-STYLE: groove; BORDER-LEFT-COLOR: #00ff00"><TBODY><TR><TD>Spreadsheet Formulas</TD></TR><TR><TD><TABLE style="FONT-FAMILY: Arial; FONT-SIZE: 9pt" border=1 cellSpacing=0 cellPadding=2><TBODY><TR style="BACKGROUND-COLOR: #cacaca; FONT-SIZE: 10pt"><TD>Cell</TD><TD>Formula</TD></TR><TR><TD>C3</TD><TD>{=INDEX($A$3:$A$34&$B$3:$B$34,SMALL(IF(MATCH($A$3:$A$34&$B$3:$B$34,$A$3:$A$34&$B$3:$B$34,0)=ROW(INDIRECT("1:"&ROWS($A$3:$A$34))),MATCH($A$3:$A$34&$B$3:$B$34,$A$3:$A$34&$B$3:$B$34,0),""),ROW(A1)))}</TD></TR></TBODY></TABLE></TD></TR><TR><TD>Formula Array:
Produce enclosing { } by entering
formula with CTRL+SHIFT+ENTER!</TD></TR></TBODY></TABLE>

Excel tables to the web >> http://www.excel-jeanie-html.de/index.php?f=1" target="_blank"> Excel Jeanie HTML 4


After copying it down in Column C, it will give #NUM! errors after the last unique value. If you have Excel 2007, you can convert the errors to empty cells by putting the formula inside the IFERROR function like this

Sheet1

<TABLE style="BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; FONT-FAMILY: Calibri,Arial; FONT-SIZE: 11pt" border=1 cellSpacing=0 cellPadding=0><COLGROUP><COL style="WIDTH: 30px; FONT-WEIGHT: bold"><COL style="WIDTH: 72px"></COLGROUP><TBODY><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt; FONT-WEIGHT: bold"><TD> </TD><TD>C</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">3</TD><TD>RBT121011</TD></TR></TBODY></TABLE>
<TABLE style="BORDER-BOTTOM-STYLE: groove; BORDER-BOTTOM-COLOR: #00ff00; BORDER-RIGHT-STYLE: groove; BACKGROUND-COLOR: #fffcf9; BORDER-TOP-COLOR: #00ff00; FONT-FAMILY: Arial; BORDER-TOP-STYLE: groove; COLOR: #000000; BORDER-RIGHT-COLOR: #00ff00; FONT-SIZE: 10pt; BORDER-LEFT-STYLE: groove; BORDER-LEFT-COLOR: #00ff00"><TBODY><TR><TD>Spreadsheet Formulas</TD></TR><TR><TD><TABLE style="FONT-FAMILY: Arial; FONT-SIZE: 9pt" border=1 cellSpacing=0 cellPadding=2><TBODY><TR style="BACKGROUND-COLOR: #cacaca; FONT-SIZE: 10pt"><TD>Cell</TD><TD>Formula</TD></TR><TR><TD>C3</TD><TD>{=IFERROR(INDEX($A$3:$A$34&$B$3:$B$34,SMALL(IF(MATCH($A$3:$A$34&$B$3:$B$34,$A$3:$A$34&$B$3:$B$34,0)=ROW(INDIRECT("1:"&ROWS($A$3:$A$34))),MATCH($A$3:$A$34&$B$3:$B$34,$A$3:$A$34&$B$3:$B$34,0),""),ROW(A1))),"")}</TD></TR></TBODY></TABLE></TD></TR><TR><TD>Formula Array:
Produce enclosing { } by entering
formula with CTRL+SHIFT+ENTER!</TD></TR></TBODY></TABLE>

Excel tables to the web >> http://www.excel-jeanie-html.de/index.php?f=1" target="_blank"> Excel Jeanie HTML 4