Filter and Textjoin formula if two columns match a text value in a cell - excel 365 in a Mac

E

esmarques

New Member
Joined
Oct 27, 2020
Messages
34
Office Version
  1. 365
Platform
  1. MacOS
Hello, I am trying to find a way to filter column D if column H or column J match with text value in cell N1 or Q1, etc. and also to combine the text from columns I and K based on same criteria.
For example, on column Q, from cell Q3, the result would include, Khights, Wednesday Welcome(that match with colum H) and Bloco(that matches with column J). In column T it would show Bloco, Education and Kurling.
For the textjoin, On cell Y3 the results would be Nationaly, 2 Lead artists full time, Winter TBC

Any help is welcomed.
Thank you

Book4
ABCDEFGHIJKLMNOPQRSTUVWXY
1ReferenceDate [Happening]Arrangement ReferenceDescriptionManagerCustomerLocal AuthorityActivity Outcome (NPO)Activity Outcome NotesSub-Activity Outcome (NPO)Sub-Activity Outcome NotesNo Outcome detailsAct.1 - Young Artist DevAct.2 - Artist DevAct.3 - Children in/out of school
238274/1/24KNI001KnightsAct.1 - Young Artist Dev30 YP outstanding performanceAct.3 - Children in/out of schoolActivityNo of ActivitiesNotesActivityNo of ActivitiesNotesActivityNo of ActivitiesNotesActivityNo of ActivitiesNotes
33544/1/2425Monthly Meetingto be developed furtherMonthly Meeting1to be developed furtherKnights130 YP outstanding performanceBloco2 Education2nationaly, 2 Lead artists full time
421314/2/24166BlocoAct.2 - Artist Devreaching all goalsAct.1 - Young Artist Dev9 Extra trainning sessions 00 Wednesday Welcome1 0 Kurling1to be developed further
521324/2/24166BlocoAct.2 - Artist DevAct.1 - Young Artist Dev0 0 0 0 
626824/3/24392Wednesday WelcomeAct.1 - Young Artist DevAct.3 - Children in/out of schoolsummer progeramme developed0 0 0 0 
734044/3/24526EducationAct.3 - Children in/out of schoolnationalyAct.2 - Artist Dev
829354/4/24388KurlingAct.3 - Children in/out of schoolto be developed furtherAct.2 - Artist Dev
934044/10/24526EducationAct.3 - Children in/out of school2 Lead artists full timeAct.2 - Artist DevWinter TBC
Sheet1
Cell Formulas
RangeFormula
N3:N4N3=IFERROR(UNIQUE(FILTER($D:$D,$H:$H=0)),"None")
U3:U6,R3:R6,O3:O6,X3:X6O3=COUNTIF($D:$D,N3)
P3:P6P3=TEXTJOIN(", ",TRUE,IF((D:D=N3)*(I:I<>0),I:I,""))
Q3:Q4,W3:W4,T3Q3=IFERROR(UNIQUE(FILTER($D:$D,$H:$H=Q1)),"None")
S3:S6,Y3:Y6S3=TEXTJOIN(", ",TRUE,IF(($D:$D=Q3)*($I:$I<>0),$I:$I,""))
V3:V6V3=TEXTJOIN(", ",TRUE,IF((G:G=T3)*(L:L<>0),L:L,""))
Dynamic array formulas.
 

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.
Sort by date Sort by votes
How about
Fluff.xlsm
ABCDEFGHIJKLMNOPQRS
1ReferenceDate [Happening]Arrangement ReferenceDescriptionManagerCustomerLocal AuthorityActivity Outcome (NPO)Activity Outcome NotesSub-Activity Outcome (NPO)Sub-Activity Outcome NotesNo Outcome detailsAct.1 - Young Artist Dev
2382745383KNI001KnightsAct.1 - Young Artist Dev30 YP outstanding performanceAct.3 - Children in/out of schoolActivityNo of ActivitiesNotesActivityNo of ActivitiesNotes
33544538325Monthly Meetingto be developed furtherMonthly Meeting1to be developed furtherKnights130 YP outstanding performance
4213145384166BlocoAct.2 - Artist Devreaching all goalsAct.1 - Young Artist Dev9 Extra trainning sessions 0 Bloco2reaching all goals, 9 Extra trainning sessions
5213245384166BlocoAct.2 - Artist DevAct.1 - Young Artist Dev0 Wednesday Welcome1summer progeramme developed
6268245385392Wednesday WelcomeAct.1 - Young Artist DevAct.3 - Children in/out of schoolsummer progeramme developed0 0 
7340445385526EducationAct.3 - Children in/out of schoolnationalyAct.2 - Artist Dev
8293545386388KurlingAct.3 - Children in/out of schoolto be developed furtherAct.2 - Artist Dev
9340445392526EducationAct.3 - Children in/out of school2 Lead artists full timeAct.2 - Artist DevWinter TBC
Sheet5
Cell Formulas
RangeFormula
N3N3=UNIQUE(FILTER(D2:D1000,((H2:H1000="")+(J2:J1000=""))*(D2:D1000<>"")))
O3:O6,R3:R6O3=COUNTIF($D:$D,N3)
P3:P6,S3:S6P3=TEXTJOIN(", ",,UNIQUE(FILTER(CHOOSECOLS($I$2:$K$1000,1,3),$D$2:$D$1000=N3,"none")))
Q3:Q5Q3=UNIQUE(FILTER(D2:D1000,(H2:H1000=Q1)+(J2:J1000=Q1),"none"))
Dynamic array formulas.
 
Upvote 0
Fluff said:
How about
Fluff.xlsm
ABCDEFGHIJKLMNOPQRS
1ReferenceDate [Happening]Arrangement ReferenceDescriptionManagerCustomerLocal AuthorityActivity Outcome (NPO)Activity Outcome NotesSub-Activity Outcome (NPO)Sub-Activity Outcome NotesNo Outcome detailsAct.1 - Young Artist Dev
2382745383KNI001KnightsAct.1 - Young Artist Dev30 YP outstanding performanceAct.3 - Children in/out of schoolActivityNo of ActivitiesNotesActivityNo of ActivitiesNotes
33544538325Monthly Meetingto be developed furtherMonthly Meeting1to be developed furtherKnights130 YP outstanding performance
4213145384166BlocoAct.2 - Artist Devreaching all goalsAct.1 - Young Artist Dev9 Extra trainning sessions 0 Bloco2reaching all goals, 9 Extra trainning sessions
5213245384166BlocoAct.2 - Artist DevAct.1 - Young Artist Dev0 Wednesday Welcome1summer progeramme developed
6268245385392Wednesday WelcomeAct.1 - Young Artist DevAct.3 - Children in/out of schoolsummer progeramme developed0 0 
7340445385526EducationAct.3 - Children in/out of schoolnationalyAct.2 - Artist Dev
8293545386388KurlingAct.3 - Children in/out of schoolto be developed furtherAct.2 - Artist Dev
9340445392526EducationAct.3 - Children in/out of school2 Lead artists full timeAct.2 - Artist DevWinter TBC
Sheet5
Cell Formulas
RangeFormula
N3N3=UNIQUE(FILTER(D2:D1000,((H2:H1000="")+(J2:J1000=""))*(D2:D1000<>"")))
O3:O6,R3:R6O3=COUNTIF($D:$D,N3)
P3:P6,S3:S6P3=TEXTJOIN(", ",,UNIQUE(FILTER(CHOOSECOLS($I$2:$K$1000,1,3),$D$2:$D$1000=N3,"none")))
Q3:Q5Q3=UNIQUE(FILTER(D2:D1000,(H2:H1000=Q1)+(J2:J1000=Q1),"none"))
Dynamic array formulas.
Click to expand...


This is amazing, thank you very much.
There is just one formula that still not giving the result wanted which is the formula on cell N3. In this updated table I want to filter column D to show only if there is no info in any rolls of column I and K.
So in the updated table, column N would only display Khights (even so Bloco on line 4 doesn't have info in columns I or K, but it has on line 5, so it woudn't display on column D)
Thank you

Book4
ABCDEFGHIJKLMNOPQRSTUVWXY
1ReferenceDate [Happening]Arrangement ReferenceDescriptionManagerCustomerLocal AuthorityActivity Outcome (NPO)Activity Outcome NotesSub-Activity Outcome (NPO)Sub-Activity Outcome NotesNo Outcome detailsAct.1 - Young Artist DevAct.2 - Artist DevAct.3 - Children in/out of school
238274/1/24KNI001Knights30 YP outstanding performanceActivityNo of ActivitiesNotesActivityNo of ActivitiesNotesActivityNo of ActivitiesNotesActivityNo of ActivitiesNotes
33544/1/2425Monthly Meetingto be developed furtherAct.1 - Young Artist DevKnights130 YP outstanding performanceMonthly Meeting1to be developed furtherBloco2 Wednesday Welcome1summer progeramme developed
421314/2/24166BlocoMonthly Meeting1to be developed furtherBloco2 Education2 Education2nationaly, 2 Lead artists full time
521324/2/24166BlocoAct.2 - Artist DevAct.1 - Young Artist DevBloco2 Wednesday Welcome1 Kurling1 Kurling1to be developed further
626824/3/24392Wednesday WelcomeAct.1 - Young Artist DevAct.3 - Children in/out of schoolsummer progeramme developed0 0 0 0 
734044/3/24526EducationAct.3 - Children in/out of schoolnationalyAct.2 - Artist Dev
829354/4/24388KurlingAct.3 - Children in/out of schoolto be developed furtherAct.2 - Artist Dev
934044/10/24526EducationAct.3 - Children in/out of school2 Lead artists full timeAct.2 - Artist DevWinter TBC
Sheet1
Cell Formulas
RangeFormula
N3:N5N3=UNIQUE(FILTER($D$2:$D$1000,(($H$2:$H$1000="")+($J$2:$J$1000=""))*($D$2:$D$1000<>"")))
U3:U6,R3:R6,O3:O6,X3:X6O3=COUNTIF($D:$D,N3)
P3,S3,V3,Y3P3=TEXTJOIN(", ",,UNIQUE(FILTER(CHOOSECOLS($I$2:$K$1000,1,3),$D$2:$D$1000=N3,"none")))
Q3:Q5,W3:W5,T3:T5Q3=UNIQUE(FILTER($D$2:$D$1000,($H$2:$H$1000=Q1)+($J$2:$J$1000=Q1),"none"))
Y4:Y6,S4:S6Y4=TEXTJOIN(", ",TRUE,IF(($D:$D=W4)*($I:$I<>0),$I:$I,""))
P4:P6P4=TEXTJOIN(", ",TRUE,IF((D:D=N4)*(I:I<>0),I:I,""))
V4:V6V4=TEXTJOIN(", ",TRUE,IF((G:G=T4)*(L:L<>0),L:L,""))
Dynamic array formulas.
 
Upvote 0
Fluff said:
You originally said cols H & J, so which is it?
Click to expand...
My apologies, it is H & J
So in the updated table, column N would only display Khights (even so Bloco on line 4 doesn't have info in columns H or J, but it has on line 5, so it woudn't display on column D)
 
Upvote 0
How about
Excel Formula: 
=LET(f,FILTER($D$2:$J$1000,$D$2:$D$1000<>""),d,INDEX(f,,1),FILTER(d,ISNA(XMATCH(d,SORT(UNIQUE(FILTER(d,(INDEX(f,,5)<>"")+(INDEX(f,,7)<>"")))),,2)),"none"))
 
Upvote 0
Solution
Fluff said:
How about
Excel Formula: 
=LET(f,FILTER($D$2:$J$1000,$D$2:$D$1000<>""),d,INDEX(f,,1),FILTER(d,ISNA(XMATCH(d,SORT(UNIQUE(FILTER(d,(INDEX(f,,5)<>"")+(INDEX(f,,7)<>"")))),,2)),"none"))
Click to expand...
Amazing, thank you very much
 
Upvote 0
You're welcome & thanks for the feedback.
 
Upvote 0
You must log in or register to reply here.

Similar threads

E
  • Question Question
Exel Filter formula with multiple criterias
Replies
2
Views
277
Fluff
Fluff
Georgiboy
  • Solved
Unique, Textjoin, Filter all in one formula
Replies
7
Views
5K
Fluff
Fluff
Asbestos_Jen
  • Question Question
Compare multiple parameters of samples to reference values (Excel 2007)
Replies
3
Views
360
Asbestos_Jen
Asbestos_Jen
J
  • Solved
Excel formula to find matches in two columns
Replies
8
Views
469
smozgur
smozgur
B
  • Solved
Excel Formula Text Twist
Replies
5
Views
345
Fluff
Fluff

Forum statistics

Threads
1,223,912
Messages
6,175,344
Members
452,638
Latest member
Oluwabukunmi

Share this page

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
Back
Top