TEXTJOIN IF ISBLANK

SkywardPalm

SkywardPalm

Board Regular
Joined
Oct 23, 2021
Messages
61
Office Version
  1. 365
Platform
  1. Windows
I am attempting to pull the categories that names are found in, and have it working all but for blank/missing names.

Excel Formula: 
=TEXTJOIN(", ",TRUE,UNIQUE(IF(ISNUMBER(SEARCH(A1,'Master'!C:C)),LEFT('Master'!A:A,4),"")))

The cell "A1" is blank, and I am trying to pull the results (first 4 left characters from Column A of Master sheet) for an exact blank match (names to find are in Column C of Master sheet). I tried TEXTJOIN with an IF(ISBLANK) but couldn't get the syntax figured out. The way I have it written currently, it pulls in four different sections along with the header, when there are only blanks in a single category.
 

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college
Sort by date Sort by votes
Can you post some sample data along with expected results.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.
 
Upvote 0
Fluff said:
Can you post some sample data along with expected results.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.
Click to expand...
In the initial post I was being a little vague with the references, but the user count changes so the blank would appear at the bottom of the list of users (which I have handled). And I am running tests so the sheet I am referencing is actually 'Master (2)'.
Master Template.xlsm
HIJKL
3Collection Templates Compiled
4Users FoundUserLocations Counted# of EntriesSum of Entries
512Aaron A0001831874
6Brandon B0001, 0002290
7Chris C0001252
8Daniel D000187
9Ellis E0001151
10Frank F0001, 000310
11Gabriel G0001235
12Hannah H0001299
13John J000177
14Kendrick K000193
15Lamont L0001129
16Marjorie M000169
17LOC, 0001, 0002, 000399
Validation
Named Ranges
NameRefers ToCells
'Master (2)'!_FilterDatabase='Master (2)'!$A$1:$AF$1875J5:J17


Master Template.xlsm
ABC
1LOCDeviceREC
20001-Section 1Hannah H036
30001-Section 1Frank F
40001-Section 1Frank F
50001-Section 1Hannah H068
60001-Section 1Kendrick K043
70001-Section 1Marjorie M022
80001-Section 1Kendrick K042
90001-Section 1Frank F
100001-Section 1Frank F
110001-Section 1Frank F
120001-Section 1Frank F
130001-Section 1Frank F
140001-Section 1Kendrick K018
150001-Section 1Ellis E100
160001-Section 1Ellis E137
170001-Section 1Hannah H091
180001-Section 1Frank F
190001-Section 1Frank F
200001-Section 1Hannah H026
210001-Section 1Hannah H028
220001-Section 1Hannah H128
230001-Section 1Hannah H129
240001-Section 1Hannah H139
250001-Section 1Hannah H017
260001-Section 1Hannah H116
270001-Section 1Hannah H111
280001-Section 1Hannah H299
290001-Section 1Kendrick K029
300001-Section 1Daniel D076
310001-Section 1Chris C227
320001-Section 1Gabriel G 006
330001-Section 1Gabriel G 013
340001-Section 1Hannah H046
350001-Section 1Hannah H274
360001-Section 1Kendrick K001
370001-Section 1Chris C228
380001-Section 1Chris C230
390001-Section 1Chris C206
400001-Section 1Kendrick K013
410001-Section 1Hannah H125
420001-Section 1Hannah H015
430001-Section 1Hannah H126
440001-Section 1Brandon B025
450001-Section 1Brandon B009
460001-Section 1Gabriel G 096
470001-Section 1Brandon B008
480001-Section 1Chris C144
490001-Section 1Brandon B036
500001-Section 1Brandon B018
510001-Section 1Chris C145
520001-Section 1Brandon B012
530001-Section 1Brandon B017
540001-Section 1Chris C068
550001-Section 1Brandon B121
560001-Section 1Gabriel G 129
570001-Section 1Chris C054
580001-Section 1Chris C108
590001-Section 1Hannah H276
600001-Section 1Chris C053
610001-Section 1Hannah H137
620001-Section 1Kendrick K074
630001-Section 1Brandon B022
640001-Section 1Kendrick K034
650001-Section 1Brandon B194
660001-Section 1Marjorie M053
670001-Section 1Hannah H084
680001-Section 1Brandon B137
690001-Section 1Kendrick K015
700001-Section 1Lamont L016
710001-Section 1Gabriel G 002
720001-Section 1Brandon B136
730001-Section 1Brandon B111
740001-Section 1Brandon B139
750001-Section 1Daniel D020
760001-Section 1Lamont L055
770001-Section 1Gabriel G 001
780001-Section 1Chris C240
790001-Section 1Brandon B016
800001-Section 1Marjorie M054
810001-Section 1Ellis E056
820001-Section 1Hannah H100
830001-Section 1Hannah H102
840001-Section 1Hannah H099
850001-Section 1Brandon B141
860001-Section 1Lamont L018
870001-Section 1Gabriel G 086
880001-Section 1Marjorie M015
890001-Section 1Chris C174
900001-Section 1Brandon B095
910001-Section 1Kendrick K054
920001-Section 1Brandon B114
930001-Section 1Hannah H202
940001-Section 1Gabriel G 136
950001-Section 1Hannah H141
960001-Section 1Gabriel G 213
970001-Section 1Brandon B187
980001-Section 1Daniel D052
990001-Section 1Hannah H069
1000001-Section 1Ellis E043
1010001-Section 1Chris C078
1020001-Section 1Daniel D036
1030001-Section 1John J016
1040001-Section 1John J015
1050001-Section 1Lamont L126
1060001-Section 1Hannah H277
1070001-Section 1Chris C109
1080001-Section 1Aaron A046
1090001-Section 1Hannah H169
1100001-Section 1Marjorie M042
1110001-Section 1Hannah H067
1120001-Section 1Brandon B174
1130001-Section 1John J066
1140001-Section 1John J064
1150001-Section 1Brandon B075
1160001-Section 1Aaron A045
1170001-Section 1Lamont L021
1180001-Section 1John J062
1190001-Section 1John J061
1200001-Section 1Hannah H144
1210001-Section 1Kendrick K035
1220001-Section 1Marjorie M041
1230001-Section 1Chris C083
1240001-Section 1Gabriel G 018
1250001-Section 1Hannah H270
1260001-Section 1Daniel D053
1270001-Section 1Hannah H142
1280001-Section 1Gabriel G 063
1290001-Section 1Lamont L097
1300001-Section 1Marjorie M033
1310001-Section 1Lamont L036
1320001-Section 1Brandon B113
1330001-Section 1Hannah H193
1340001-Section 1John J058
1350001-Section 1Marjorie M055
1360001-Section 1Marjorie M025
1370001-Section 1John J063
1380001-Section 1Hannah H271
1390001-Section 1Hannah H278
1400001-Section 1Hannah H140
1410001-Section 1Lamont L038
1420001-Section 1Hannah H040
1430001-Section 1Kendrick K079
1440001-Section 1Chris C051
1450001-Section 1Brandon B124
1460001-Section 1Kendrick K085
1470001-Section 1Hannah H200
1480001-Section 1Hannah H201
1490001-Section 1Kendrick K023
1500001-Section 1Brandon B196
1510001-Section 1Lamont L049
1520001-Section 1John J065
1530001-Section 1Chris C166
1540001-Section 1Lamont L115
1550001-Section 1Hannah H004
1560001-Section 1Chris C111
1570001-Section 1Brandon B024
1580001-Section 1Lamont L051
1590001-Section 1Brandon B035
1600001-Section 1Brandon B063
1610001-Section 1Hannah H070
1620001-Section 1Gabriel G 016
1630001-Section 1Hannah H023
1640001-Section 1Marjorie M032
1650001-Section 1Marjorie M051
1660001-Section 1Ellis E006
1670001-Section 1Hannah H252
1680001-Section 1Brandon B112
1690001-Section 1Hannah H082
1700001-Section 1John J018
1710001-Section 1Brandon B033
1720001-Section 1Kendrick K063
1730001-Section 1Brandon B054
1740001-Section 1Hannah H012
1750001-Section 1Hannah H272
1760001-Section 1Ellis E015
1770001-Section 1Brandon B004
1780001-Section 1Marjorie M018
1790001-Section 1Brandon B104
1800001-Section 1Gabriel G 137
1810001-Section 1Lamont L096
1820001-Section 1Hannah H248
1830001-Section 1Marjorie M043
1840001-Section 1Aaron A047
1850001-Section 1Daniel D065
1860001-Section 1Hannah H183
1870001-Section 1Kendrick K033
1880001-Section 1Chris C092
1890001-Section 1Marjorie M028
1900001-Section 1Hannah H212
1910001-Section 1Hannah H048
1920001-Section 1Lamont L116
1930001-Section 1Hannah H146
1940001-Section 1Kendrick K075
1950001-Section 1Lamont L079
1960001-Section 1Aaron A054
1970001-Section 1Hannah H216
1980001-Section 1Gabriel G 003
1990001-Section 1Lamont L124
2000001-Section 1Marjorie M058
2010001-Section 1Hannah H098
2020001-Section 1Hannah H222
2030001-Section 1Lamont L015
2040001-Section 1Chris C143
2050001-Section 1Daniel D087
2060001-Section 1Ellis E055
2070001-Section 1Hannah H245
2080001-Section 1Lamont L118
2090001-Section 1Brandon B064
2100001-Section 1Hannah H096
2110001-Section 1John J074
2120001-Section 1Chris C128
2130001-Section 1Marjorie M048
2140001-Section 1Lamont L027
2150001-Section 1Brandon B115
2160001-Section 1Kendrick K002
2170001-Section 1Marjorie M027
2180001-Section 1Hannah H263
2190001-Section 1Brandon B140
2200001-Section 1Kendrick K032
2210001-Section 1Hannah H280
2220001-Section 1Hannah H220
2230001-Section 1Ellis E003
2240001-Section 1Ellis E002
2250001-Section 1Aaron A006
2260001-Section 1Chris C084
2270001-Section 1John J059
2280001-Section 1Ellis E091
2290001-Section 1Marjorie M020
2300001-Section 1Ellis E013
2310001-Section 1Ellis E141
2320001-Section 1Chris C046
2330001-Section 1Brandon B123
2340001-Section 1Daniel D058
2350001-Section 1Hannah H138
2360001-Section 1Chris C178
2370001-Section 1Daniel D073
2380001-Section 1John J060
2390001-Section 1Ellis E122
2400001-Section 1Kendrick K053
2410001-Section 1Lamont L119
2420001-Section 1Daniel D042
2430001-Section 1Daniel D040
2440001-Section 1Daniel D033
2450001-Section 1Aaron A031
2460001-Section 1Brandon B011
2470001-Section 1Marjorie M026
2480001-Section 1Chris C244
2490001-Section 1Brandon B093
2500001-Section 1Brandon B143
2510001-Section 1Lamont L109
2520001-Section 1Lamont L058
2530001-Section 1Aaron A044
2540001-Section 1Hannah H199
2550001-Section 1Gabriel G 212
2560001-Section 1Chris C204
2570001-Section 1Chris C019
2580001-Section 1Brandon B021
2590001-Section 1Marjorie M063
2600001-Section 1Brandon B053
2610001-Section 1Gabriel G 150
2620001-Section 1Gabriel G 202
2630001-Section 1Marjorie M012
2640001-Section 1Lamont L089
2650001-Section 1Aaron A042
2660001-Section 1Lamont L077
2670001-Section 1Chris C085
2680001-Section 1John J017
2690001-Section 1Hannah H265
2700001-Section 1Chris C239
2710001-Section 1Daniel D062
2720001-Section 1Gabriel G 185
2730001-Section 1Brandon B078
2740001-Section 1Brandon B205
2750001-Section 1Gabriel G 205
2760001-Section 1Chris C129
2770001-Section 1Hannah H167
2780001-Section 1Daniel D054
2790001-Section 1Hannah H215
2800001-Section 1Brandon B097
2810001-Section 1Chris C029
2820001-Section 1Daniel D032
2830001-Section 1Daniel D083
2840001-Section 1Lamont L104
2850001-Section 1Daniel D017
2860001-Section 1Ellis E050
2870001-Section 1Brandon B037
2880001-Section 1Brandon B200
2890001-Section 1Brandon B201
2900001-Section 1Hannah H152
2910001-Section 1Lamont L078
2920001-Section 1Gabriel G 199
2930001-Section 1Hannah H211
2940001-Section 1Lamont L106
2950001-Section 1Hannah H075
2960001-Section 1Ellis E016
2970001-Section 1Lamont L117
2980001-Section 1Kendrick K051
2990001-Section 1Ellis E054
3000001-Section 1Hannah H050
3010001-Section 1Hannah H155
3020001-Section 1Chris C251
3030001-Section 1Marjorie M031
3040001-Section 1Daniel D051
3050001-Section 1Ellis E058
3060001-Section 1Gabriel G 113
3070001-Section 1Hannah H101
3080001-Section 1Hannah H282
3090001-Section 1Brandon B128
3100001-Section 1Brandon B122
3110001-Section 1Hannah H171
3120001-Section 1Gabriel G 085
3130001-Section 1Chris C153
3140001-Section 1Ellis E017
3150001-Section 1Lamont L110
3160001-Section 1Gabriel G 198
3170001-Section 1Lamont L029
3180001-Section 1Aaron A011
3190001-Section 1Marjorie M010
3200001-Section 1Hannah H161
3210001-Section 1Lamont L120
3220001-Section 1Hannah H164
3230001-Section 1Gabriel G 149
3240001-Section 1Hannah H095
3250001-Section 1Hannah H218
3260001-Section 1Brandon B032
3270001-Section 1Kendrick K038
3280001-Section 1Chris C176
3290001-Section 1Marjorie M019
3300001-Section 1Daniel D050
3310001-Section 1Gabriel G 062
3320001-Section 1Daniel D064
3330001-Section 1Hannah H076
3340001-Section 1Chris C125
3350001-Section 1Lamont L112
3360001-Section 1Marjorie M052
3370001-Section 1Marjorie M024
3380001-Section 1Hannah H066
3390001-Section 1Lamont L093
3400001-Section 1Gabriel G 220
3410001-Section 1Brandon B130
3420001-Section 1Marjorie M016
3430001-Section 1Hannah H219
3440001-Section 1Aaron A081
3450001-Section 1Gabriel G 197
3460001-Section 1Hannah H254
3470001-Section 1Chris C107
3480001-Section 1Chris C163
3490001-Section 1Ellis E062
3500001-Section 1Chris C156
3510001-Section 1Lamont L088
3520001-Section 1Lamont L108
3530001-Section 1Brandon B145
3540001-Section 1Hannah H273
3550001-Section 1Brandon B185
3560001-Section 1Lamont L105
3570001-Section 1Hannah H232
3580001-Section 1Brandon B173
3590001-Section 1Lamont L107
3600001-Section 1Chris C081
3610001-Section 1Hannah H250
3620001-Section 1Chris C154
3630001-Section 1Chris C052
3640001-Section 1Chris C045
3650001-Section 1Brandon B107
3660001-Section 1Brandon B166
3670001-Section 1Chris C115
3680001-Section 1Gabriel G 211
3690001-Section 1Hannah H279
3700001-Section 1Aaron A068
3710001-Section 1Marjorie M005
3720001-Section 1Daniel D024
3730001-Section 1Chris C202
3740001-Section 1Kendrick K059
3750001-Section 1Ellis E026
3760001-Section 1Gabriel G 093
3770001-Section 1Hannah H083
3780001-Section 1Hannah H042
3790001-Section 1Brandon B094
3800001-Section 1Ellis E088
3810001-Section 1Gabriel G 025
3820001-Section 1Lamont L020
3830001-Section 1Lamont L087
3840001-Section 1Lamont L076
3850001-Section 1Brandon B092
3860001-Section 1Brandon B120
3870001-Section 1Kendrick K040
3880001-Section 1Brandon B156
3890001-Section 1Ellis E134
3900001-Section 1Kendrick K064
3910001-Section 1Marjorie M064
3920001-Section 1Marjorie M023
3930001-Section 1Gabriel G 092
3940001-Section 1Brandon B100
3950001-Section 1Brandon B186
3960001-Section 1Aaron A021
3970001-Section 1Aaron A042
3980001-Section 1Daniel D069
3990001-Section 1Lamont L111
4000001-Section 1Brandon B182
4010001-Section 1Hannah H071
4020001-Section 1Gabriel G 210
4030001-Section 1Brandon B023
4040001-Section 1Kendrick K055
4050001-Section 1Gabriel G 049
4060001-Section 1Daniel D038
4070001-Section 1Gabriel G 076
4080001-Section 1Gabriel G 121
4090001-Section 1Kendrick K022
4100001-Section 1Lamont L019
4110001-Section 1Brandon B215
4120001-Section 1Daniel D037
4130001-Section 1Hannah H030
4140001-Section 1Hannah H093
4150001-Section 1Lamont L054
4160001-Section 1Hannah H209
4170001-Section 1Chris C122
4180001-Section 1Daniel D063
4190001-Section 1Brandon B005
4200001-Section 1Chris C074
4210001-Section 1Chris C086
4220001-Section 1Brandon B056
4230001-Section 1Gabriel G 048
4240001-Section 1Hannah H231
4250001-Section 1Hannah H029
4260001-Section 1Kendrick K071
Master (2)

Master Template.xlsm
ABC
17760003-Section 3
17770003-Section 3
17780003-Section 3
17790003-Section 3
17800003-Section 3
17810003-Section 3
17820003-Section 3
17830003-Section 3
17840003-Section 3
17850003-Section 3
17860003-Section 3
17870003-Section 3
17880003-Section 3
17890003-Section 3
17900003-Section 3
17910003-Section 3
17920003-Section 3
17930003-Section 3
17940003-Section 3
17950003-Section 3
17960003-Section 3
17970003-Section 3
17980003-Section 3
17990003-Section 3
18000003-Section 3
18010003-Section 3
18020003-Section 3
18030003-Section 3
18040003-Section 3
18050003-Section 3
18060003-Section 3
18070003-Section 3
18080003-Section 3
18090003-Section 3
18100003-Section 3
18110003-Section 3
18120003-Section 3
18130003-Section 3
18140003-Section 3
18150003-Section 3
18160003-Section 3
18170003-Section 3
18180003-Section 3
18190003-Section 3
18200003-Section 3
18210003-Section 3
18220003-Section 3
18230003-Section 3
18240003-Section 3
18250003-Section 3
18260003-Section 3
18270003-Section 3
18280003-Section 3
18290003-Section 3
18300003-Section 3
18310003-Section 3
18320003-Section 3
18330003-Section 3
18340003-Section 3Frank F
18350003-Section 3
18360003-Section 3
18370003-Section 3
18380003-Section 3
18390003-Section 3
18400003-Section 3
18410003-Section 3
18420003-Section 3
18430003-Section 3
18440003-Section 3
18450003-Section 3
Master (2)
 
Upvote 0
As you haven't supplied the expected result, this is a guess
Excel Formula: 
=TEXTJOIN(", ",TRUE,UNIQUE(FILTER(LEFT('Master (2)'!A:A,4),'Master (2)'!C:C="")))
 
Upvote 1
Solution
Fluff said:
As you haven't supplied the expected result, this is a guess
Excel Formula: 
=TEXTJOIN(", ",TRUE,UNIQUE(FILTER(LEFT('Master (2)'!A:A,4),'Master (2)'!C:C="")))
Click to expand...
That worked perfectly, sorry I forgot to mention that it was pulling in LOC, 0001, 0002, and 0003 when there were only blank entries in section 0003. Thanks for your quick responses! Always appreciated
 
Upvote 0
Glad to help & thanks for the feedback.
 
Upvote 0
You must log in or register to reply here.

Similar threads

B
  • Solved
Ignore Blank values not working correctly using TEXTJOIN function
Replies
5
Views
414
Bombdiggity
B
M
  • Question Question
Returning Values Based on Referent Cells (Avoiding Redundant Values)
Replies
5
Views
507
Fluff
Fluff
B
  • Question Question
Suggestions for Building a Historical Cost Database by Project
Replies
0
Views
98
babyjwhale
B
A
  • Question Question
Dynamically add new rows via VBA when using IFERROR TEXTJOIN and FILTER formulas from Table?
Replies
0
Views
538
AlfredDB
A
D
  • Question Question
Multiple conditions
Replies
8
Views
662
awoohaw
awoohaw

Forum statistics

Threads
1,224,823
Messages
6,181,176
Members
453,021
Latest member
Justyna P

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