Create search for cd database

[scopio]

I am new to Excel and would like some help in creating a database where I can list the artist in one column and their music title on another column. But then I would like to create a search for the artist where all his/her music would also appear in the result.

Is tis at all possible and if yes how?

 

[Peter_SSs]

Welcome to the MrExcel board!

If you use your Microsoft 365 it is easy. Here is a small demonstration.
Just enter your artist of interest in E2 and the results will appear in column F

scopio.xlsm
	A	B	C	D	E	F
1	Artist	Title			Artist	Titles
2	a	T1			c	T3
3	b	T2				T7
4	c	T3				T8
5	d	T4
6	a	T5
7	d	T6
8	c	T7
9	c	T8
10	d	T9
11	a	T10
12	e	T11
13	f	T12
14	b	T13
15
Sheet1

Cell Formulas
Range	Formula
F2:F4	F2	=FILTER(B2:B1000,A2:A1000=E2,"")
Dynamic array formulas.

 

[Peter_SSs]

If you want to make it easier you can set up cell E2 with a data Validation drop-down list to choose the artist.
Again, this is with MS365
Enter the formula shown in H2 to give you an alphabetical list of all artists.
Then set up cell E2 with Data Validation (it is on the Data ribbon tab) as shown below

scopio.xlsm
	A	B	C	D	E	F	G	H
1	Artist	Title			Artist	Titles		Artist List
2	d	T1			d	T1		a
3	c	T2				T4		b
4	b	T3				T6		c
5	d	T4				T9		d
6	a	T5						e
7	d	T6						f
8	c	T7
9	c	T8
10	d	T9
11	a	T10
12	e	T11
13	f	T12
14	b	T13
15
Sheet2

Cell Formulas
Range	Formula
F2:F5	F2	=FILTER(B2:B1000,A2:A1000=E2,"")
H2:H7	H2	=SORT(UNIQUE(FILTER(A2:A1000,A2:A1000<>"")))
Dynamic array formulas.

Cells with Data Validation
Cell	Allow	Criteria
E2	List	=H2#

The result allows you to choose the artist like this

 

[scopio]

Hi @Peter_SSs thank you for your quick response.
I have tried a small number of data and it works perfectly in MS 365 and in Excel Web App.

Should I have any queries I will post at a later date.

Many thanks

 

[Peter_SSs]

You're welcome. Thanks for the follow-up.

 

[scopio]

As I posted in post 4 above the method to sort the cd/music database suggested by @Peter_SSs worked perfectly in MS 365 and the Web Excel for which I am extremely grateful.
But it does not work in Excel 2007.
My friend who only has Excel 2007 would like the database to be able to work in his Excel version.
Is there any possibility of someone to give me instructions on how to be able to achieve the same results in Excel 2007 as in MS 365 version?

 

[greenwind007]

If you want to make it easier you can set up cell E2 with a data Validation drop-down list to choose the artist.
Again, this is with MS365
Enter the formula shown in H2 to give you an alphabetical list of all artists.
Then set up cell E2 with Data Validation (it is on the Data ribbon tab) as shown below

scopio.xlsm
	A	B	C	D	E	F	G	H
1	Artist	Title			Artist	Titles		Artist List
2	d	T1			d	T1		a
3	c	T2				T4		b
4	b	T3				T6		c
5	d	T4				T9		d
6	a	T5						e
7	d	T6						f
8	c	T7
9	c	T8
10	d	T9
11	a	T10
12	e	T11
13	f	T12
14	b	T13
15
Sheet2

Cell Formulas
Range	Formula
F2:F5	F2	=FILTER(B2:B1000,A2:A1000=E2,"")
H2:H7	H2	=SORT(UNIQUE(FILTER(A2:A1000,A2:A1000<>"")))
Dynamic array formulas.

Cells with Data Validation
Cell	Allow	Criteria
E2	List	=H2#

View attachment 51197

The result allows you to choose the artist like this

View attachment 51198

Hi Peter, if i have 10 sheets data with the same artist, tittle. How can i collect all information of an artist in every sheets into sumary sheet. Tks

 

[Joe4]

Hi Peter, if i have 10 sheets data with the same artist, tittle. How can i collect all information of an artist in every sheets into sumary sheet. Tks

As that is a completely different question (the original question did not have multiple sheets), you should post it to a new thread instead of posting to an old one.
That way it will appear in the "Unanswered threads" listing.