Could someone please help me with this?

Idiocracy

New Member
Joined
Jun 23, 2021
Messages
3
Office Version
  1. 365
Platform
  1. Windows
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
 

Attachments

  • Screenshot (2).png
    Screenshot (2).png
    204.2 KB · Views: 8

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
11,555
Welcome to the MrExcel forum!

Try:

Book2 (version 1).xlsb
MNOPQ
1IngredientMeatDairy
2AGroceryCF
3BGroceryGJ
4CMeatHK
5DVegetablesO
6EVegetablesP
7FDairy
8GMeat
9HMeatVegetablesGrocery
10IGroceryDA
11JDairyEB
12KDairyLI
13LVegetablesMN
14MVegetables
15NGrocery
16OMeat
17PMeat
18#N/A#N/A
19#N/A#N/A
20#N/A#N/A
21#N/A#N/A
22
Sheet5
Cell Formulas
RangeFormula
P2:P6P2=SORT(FILTER(M2:M25,IFERROR(N2:N25=P1,0)))
Q2:Q4Q2=SORT(FILTER(M2:M25,IFERROR(N2:N25=Q1,0)))
P10:P13P10=SORT(FILTER(M2:M25,IFERROR(N2:N25=P9,0)))
Q10:Q13Q10=SORT(FILTER(M2:M25,IFERROR(N2:N25=Q9,0)))
M18:N21M18=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

MrExcel MVP
Joined
Aug 18, 2015
Messages
11,555
This will work on older versions of Excel, but it doesn't sort:

Book2 (version 1).xlsb
MNOPQ
1IngredientMeatDairy
2AGroceryCF
3BGroceryGJ
4CMeatHK
5DVegetablesO 
6EVegetablesP 
7FDairy  
8GMeat
9HMeatVegetablesGrocery
10IGroceryDA
11JDairyEB
12KDairyLI
13LVegetablesMN
14MVegetables  
15NGrocery  
16OMeat  
17PMeat
18#N/A#N/A
19#N/A#N/A
20#N/A#N/A
21#N/A#N/A
22
Sheet5
Cell Formulas
RangeFormula
P2:Q7P2=IFERROR(INDEX($M:$M,AGGREGATE(15,6,ROW($M$2:$M$22)/($N$2:$N$22=P$1),ROWS(P$2:P2))),"")
P10:Q16P10=IFERROR(INDEX($M:$M,AGGREGATE(15,6,ROW($M$2:$M$22)/($N$2:$N$22=P$9),ROWS(P$10:P10))),"")
M18:N21M18=NA()
 

Idiocracy

New Member
Joined
Jun 23, 2021
Messages
3
Office Version
  1. 365
Platform
  1. Windows
Welcome to the MrExcel forum!

Try:

Book2 (version 1).xlsb
MNOPQ
1IngredientMeatDairy
2AGroceryCF
3BGroceryGJ
4CMeatHK
5DVegetablesO
6EVegetablesP
7FDairy
8GMeat
9HMeatVegetablesGrocery
10IGroceryDA
11JDairyEB
12KDairyLI
13LVegetablesMN
14MVegetables
15NGrocery
16OMeat
17PMeat
18#N/A#N/A
19#N/A#N/A
20#N/A#N/A
21#N/A#N/A
22
Sheet5
Cell Formulas
RangeFormula
P2:P6P2=SORT(FILTER(M2:M25,IFERROR(N2:N25=P1,0)))
Q2:Q4Q2=SORT(FILTER(M2:M25,IFERROR(N2:N25=Q1,0)))
P10:P13P10=SORT(FILTER(M2:M25,IFERROR(N2:N25=P9,0)))
Q10:Q13Q10=SORT(FILTER(M2:M25,IFERROR(N2:N25=Q9,0)))
M18:N21M18=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

New Member
Joined
Jun 23, 2021
Messages
3
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

Book2 (version 1).xlsb
MNOPQ
1IngredientMeatDairy
2AGroceryCF
3BGroceryGJ
4CMeatHK
5DVegetablesO 
6EVegetablesP 
7FDairy  
8GMeat
9HMeatVegetablesGrocery
10IGroceryDA
11JDairyEB
12KDairyLI
13LVegetablesMN
14MVegetables  
15NGrocery  
16OMeat  
17PMeat
18#N/A#N/A
19#N/A#N/A
20#N/A#N/A
21#N/A#N/A
22
Sheet5
Cell Formulas
RangeFormula
P2:Q7P2=IFERROR(INDEX($M:$M,AGGREGATE(15,6,ROW($M$2:$M$22)/($N$2:$N$22=P$1),ROWS(P$2:P2))),"")
P10:Q16P10=IFERROR(INDEX($M:$M,AGGREGATE(15,6,ROW($M$2:$M$22)/($N$2:$N$22=P$9),ROWS(P$10:P10))),"")
M18:N21M18=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.
 

Forum statistics

Threads
1,144,695
Messages
5,725,807
Members
422,642
Latest member
praveenlal

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top