Return Multiple Unique Values for Horiontal Table

**sclost** · 01-03-2012, 02:09 PM

Hello,

I am a little stumped and could use some helpful expertise.

I am trying to create a table that looks up "Customer #" from a "DATA" sheet and returns any "Username" associated with that "Customer #."

The returned results need to be in a horizontal layout so that they can be quickly and easily imported into a different program.

I have attached a sample of what I am looking to do. Basically I want the DATA in the DATA worksheet to be summarized as shown in the SUMMARY tab.

I am using Microsoft Excel 2010.

**Bob Phillips** · 01-03-2012, 02:19 PM

Try this array formula

=IF(ISERROR(SMALL(IF(DATA!$A$2:$A$11=SUMMARY!$A2,ROW(DATA!$A$2:$A$11)),COLUMN(A1))),"",
INDEX(DATA!$B$2:$B$11,SMALL(IF(DATA!$A$2:$A$11=SUMMARY!$A2,ROW(DATA!$A$2:$A$11)),COLUMN(A1))))

**Haseeb A** · 01-03-2012, 02:46 PM

B2, copy across & down

=IFERROR(INDEX(DATA!$B:$B,SMALL(IF(DATA!$A$2:$A$25=$A2,ROW(DATA!$A$2:$A$25)),COLUMNS($B2:B2))),"")

If you need only unique

=IFERROR(INDEX(DATA!$B$2:$B$25,MATCH(1,IF(DATA!$A$2:$A$25=$A2,IF(ISNA(MATCH(DATA!$B$2:$B$25,$A2:A2,0 )),1)),0)),"")

Both are Confirmed with CTRL+SHIFT+ENTER

**WHER** · 01-03-2012, 05:10 PM

One more approach, see attachment.

**sclost** · 01-09-2012, 09:30 AM

Thanks! I ended up using the formula approach in the Summary.xls file WHER uploaded. I would have used the Macro, but my knowledge of editing and working with Macros is rather limited.

Thanks again everyone!