Could someone please help me with this?

[Idiocracy]

I am a community nurse trying to make a database to help my diabetic patients in the community. I have done a decent job so far of searching the message boards and google for this project, but I have hit a wall and I do not know what I should even be searching for to complete this part of the project.

I took a screen shot of the problem I am facing

Column M & N are generated from another list (they are the ingredients for several meals all jumbled together)

I want Column M & N to populate into Column P & Q as shown (I created these columns via cut and paste to show what the end goal is because I do a poor job explaining things like this)

If it is possible I would like each section to get sorted alphabetically, and not to include #NUM! which represent nothing from columns M & N?

I would like to do this without a macro and have it update automatically if possible. Some of the patients that I plan to give copies to are not too computer savvy.

Any help is greatly appreciated,
Keith

 

[Eric W]

Welcome to the MrExcel forum!

Try:

Book2 (version 1).xlsb
	M	N	O	P	Q
1	Ingredient			Meat	Dairy
2	A	Grocery		C	F
3	B	Grocery		G	J
4	C	Meat		H	K
5	D	Vegetables		O
6	E	Vegetables		P
7	F	Dairy
8	G	Meat
9	H	Meat		Vegetables	Grocery
10	I	Grocery		D	A
11	J	Dairy		E	B
12	K	Dairy		L	I
13	L	Vegetables		M	N
14	M	Vegetables
15	N	Grocery
16	O	Meat
17	P	Meat
18	#N/A	#N/A
19	#N/A	#N/A
20	#N/A	#N/A
21	#N/A	#N/A
22
Sheet5

Cell Formulas
Range	Formula
P2:P6	P2	=SORT(FILTER(M2:M25,IFERROR(N2:N25=P1,0)))
Q2:Q4	Q2	=SORT(FILTER(M2:M25,IFERROR(N2:N25=Q1,0)))
P10:P13	P10	=SORT(FILTER(M2:M25,IFERROR(N2:N25=P9,0)))
Q10:Q13	Q10	=SORT(FILTER(M2:M25,IFERROR(N2:N25=Q9,0)))
M18:N21	M18	=NA()
Dynamic array formulas.

Edit: It just occurred to me that although you have Excel 365, which has the new FILTER and SORT functions, if you give this to other people, can we expect they'll have Excel 365 too, or would you want something that would work with older versions?

 

[Eric W]

This will work on older versions of Excel, but it doesn't sort:

Book2 (version 1).xlsb
	M	N	O	P	Q
1	Ingredient			Meat	Dairy
2	A	Grocery		C	F
3	B	Grocery		G	J
4	C	Meat		H	K
5	D	Vegetables		O
6	E	Vegetables		P
7	F	Dairy
8	G	Meat
9	H	Meat		Vegetables	Grocery
10	I	Grocery		D	A
11	J	Dairy		E	B
12	K	Dairy		L	I
13	L	Vegetables		M	N
14	M	Vegetables
15	N	Grocery
16	O	Meat
17	P	Meat
18	#N/A	#N/A
19	#N/A	#N/A
20	#N/A	#N/A
21	#N/A	#N/A
22
Sheet5

Cell Formulas
Range	Formula
P2:Q7	P2	=IFERROR(INDEX($M:$M,AGGREGATE(15,6,ROW($M$2:$M$22)/($N$2:$N$22=P$1),ROWS(P$2:P2))),"")
P10:Q16	P10	=IFERROR(INDEX($M:$M,AGGREGATE(15,6,ROW($M$2:$M$22)/($N$2:$N$22=P$9),ROWS(P$10:P10))),"")
M18:N21	M18	=NA()

 

[Idiocracy]

Welcome to the MrExcel forum!

Try:

Book2 (version 1).xlsb
	M	N	O	P	Q
1	Ingredient			Meat	Dairy
2	A	Grocery		C	F
3	B	Grocery		G	J
4	C	Meat		H	K
5	D	Vegetables		O
6	E	Vegetables		P
7	F	Dairy
8	G	Meat
9	H	Meat		Vegetables	Grocery
10	I	Grocery		D	A
11	J	Dairy		E	B
12	K	Dairy		L	I
13	L	Vegetables		M	N
14	M	Vegetables
15	N	Grocery
16	O	Meat
17	P	Meat
18	#N/A	#N/A
19	#N/A	#N/A
20	#N/A	#N/A
21	#N/A	#N/A
22
Sheet5

Cell Formulas
Range	Formula
P2:P6	P2	=SORT(FILTER(M2:M25,IFERROR(N2:N25=P1,0)))
Q2:Q4	Q2	=SORT(FILTER(M2:M25,IFERROR(N2:N25=Q1,0)))
P10:P13	P10	=SORT(FILTER(M2:M25,IFERROR(N2:N25=P9,0)))
Q10:Q13	Q10	=SORT(FILTER(M2:M25,IFERROR(N2:N25=Q9,0)))
M18:N21	M18	=NA()
Dynamic array formulas.

Edit: It just occurred to me that although you have Excel 365, which has the new FILTER and SORT functions, if you give this to other people, can we expect they'll have Excel 365 too, or would you want something that would work with older versions?

Wow that worked great, thank you so much.

Many of my patients are low income, I would guess many of them would have older versions of excel.
Would setting this up for an older version be difficult?

 

[Idiocracy]

This will work on older versions of Excel, but it doesn't sort:

Book2 (version 1).xlsb
	M	N	O	P	Q
1	Ingredient			Meat	Dairy
2	A	Grocery		C	F
3	B	Grocery		G	J
4	C	Meat		H	K
5	D	Vegetables		O
6	E	Vegetables		P
7	F	Dairy
8	G	Meat
9	H	Meat		Vegetables	Grocery
10	I	Grocery		D	A
11	J	Dairy		E	B
12	K	Dairy		L	I
13	L	Vegetables		M	N
14	M	Vegetables
15	N	Grocery
16	O	Meat
17	P	Meat
18	#N/A	#N/A
19	#N/A	#N/A
20	#N/A	#N/A
21	#N/A	#N/A
22
Sheet5

Cell Formulas
Range	Formula
P2:Q7	P2	=IFERROR(INDEX($M:$M,AGGREGATE(15,6,ROW($M$2:$M$22)/($N$2:$N$22=P$1),ROWS(P$2:P2))),"")
P10:Q16	P10	=IFERROR(INDEX($M:$M,AGGREGATE(15,6,ROW($M$2:$M$22)/($N$2:$N$22=P$9),ROWS(P$10:P10))),"")
M18:N21	M18	=NA()

Excellent, thank you again!

I will just create a new tab and include this coding in the event the person has an older version.

 

[spartacruz]

This will work on older versions of Excel, but it doesn't sort:

Hi Eric.
Sorry for interrupting.

Could you please have a moment to see this thread? Sort & Group Specific Cell Value

Thanks

 

[Eric W]

Idiocracy, glad I could help!