Show a unique list of items based on Cell value

**gorgon777** · 05-10-2013, 10:41 AM

I have a table imported from Access as follows:
CITY..........SURNAME........VALUE
London..........Jones.............30
London..........Aria................20
Newcastle......Aria................10
London..........Hendry............50
Newcastle......Harrison..........50
Manchester....Gio.................30

I would like to display a unique list of SURNAME based on CITY.
For Example:
If A2 in a spreadsheet says "London"
Show dynamically in Cell B5, B6 and B7 horizontally, the values "Jones", "Aria" and "Hendry"

If A2 in a spreadsheet says "Newcastle"
Show dynamically in Cell B5 and B6 horizontally, the values "Aria" and "Harrison"

If A2 in a spreadsheet says "Manchester"
Show dynamically in Cell B7 horizontally, the values "Gio"

If A2 in a spreadsheet says "TEST"
Show dynamically in Cell B5 horizontally, nothing.

How can I achieve this in Excel 2010? Any help is greatly appreciated.

**ChemistB** · 05-10-2013, 11:03 AM

I put your imported data in Sheet2 and your results in Sheet1
In Sheet2 col D, I added a helper column (this can be hidden if you like)
In D2
=IF(A2=Sheet1!$A$2,D1+1,D1) copied down
Sheet1 A2 has your city name (I used data validation and a dropdown)
in Sheet1 B2 copied down
=IFERROR(INDEX(Sheet2!$B$2:$B$7,MATCH(ROW(A1),Sheet2!$D$2:$D$7,0)),"")
Does that work for you?

**loginjmor** · 05-10-2013, 11:21 AM

Hi -

Attached is a file that does what you specified. I had to add a couple of helper columns.

Hope this helps.

**gorgon777** · 05-10-2013, 11:33 AM

Originally Posted by ChemistB

I put your imported data in Sheet2 and your results in Sheet1
In Sheet2 col D, I added a helper column (this can be hidden if you like)
In D2
=IF(A2=Sheet1!$A$2,D1+1,D1) copied down
Sheet1 A2 has your city name (I used data validation and a dropdown)
in Sheet1 B2 copied down
=IFERROR(INDEX(Sheet2!$B$2:$B$7,MATCH(ROW(A1),Sheet2!$D$2:$D$7,0)),"")
Does that work for you?

This is very close to what I want. However i've realised ive specified the wrong cells. I actually want them to show across cell B2, to C2, D2, E2 and so on. I've highlighted this area on sheet1(attached).

loginjmor - Your example is also what I wanted

But i actually wanted the results to display horizontally(like B2, C2, D2 and so on).

Is there a way to display them horizontally across the sheet?

**Marcol** · 05-10-2013, 11:58 AM

Try this workbook

Sheet2 Column H extracts the Unique City Names.

Sheet1 B2 is a CSE array, confirm with Ctrl+Shift+Enter before dragging across

No helpers required.

**gorgon777** · 05-10-2013, 12:18 PM

Originally Posted by Marcol

Try this workbook

Sheet2 Column H extracts the Unique City Names.

Sheet1 B2 is a CSE array, confirm with Ctrl+Shift+Enter before dragging across

No helpers required.

This is perfect, thank you and everyone for your suggestions, its made me realise the INDEX() and OR() methods which are pretty useful!

This query can be marked as solved