If a Cell Contains any one of an array of data, then X. If it contains one of a different array of data, then Y.

RockNGoalStar

New Member
Joined
Nov 4, 2022
Messages
5
Office Version
  1. 365
Platform
  1. MacOS
Hi All,

I could not get the XL2BB to work... maybe it's cos I am on a Mac?

Anyway, I was hoping someone can help me, and I've taken a screenshot to help me explain it...

Screenshot 2022-11-04 at 16.49.35.png



I have two sales-reps, Adam and Dave. Each of them are assigned a list of Postcode areas; Adam's are in column V, Dave's are in column W.

The postcodes for our individual customers are in column T - as you can see there is a number on there as well, which is not in the rep's lists of postcodes. So if it says TA1, TA3, TA27 in column T, then they would all be covered by the postcode are TA. So some sort of partial text match...?

I would like to be able to add the owner of the Account in column U, ADAM or DAVE, based on whether or not one of their assigned postcode areas appears in the cells in column T.

I really hope that makes sense :)

Many, many thanks in advance for any ideas - I am a bit of an Excel numpty, but I have tried Googling the topic for a while before posting here...
 

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.
Using XL 365 the following formula should work: in U3
Excel Formula:
=LET(cPC,T3,XList,$V$3:$V$21,YList,$W$3:$W$21,splcPC,MID(cPC,SEQUENCE(LEN(cPC)),1),AcPC, TEXTJOIN("",TRUE,IF(CODE(splcPC)<58,"",splcPC)),IF(COUNTIF(XList,AcPC)>0,"X",IF(COUNTIF(YList,AcPC)>0,"Y","")))
Then copy down

If you need to adapt the formula to your layout:
-cPC is the cell with the raw postcode (T3, in my example)
-XList and YList are the addresses of the two lists (V3:V21 and W3:W21 in my example)

The formula will remove from the original postcodes any and all the numbers; so L2A34 will be trated as LA and LA2b will be trated as LAb (and thus will not match with LA)
 

Attachments

  • RnGS_Immagine 2022-11-04 192006.jpg
    RnGS_Immagine 2022-11-04 192006.jpg
    28.3 KB · Views: 4
Upvote 0
MrExcelPlayground13.xlsx
ABCD
1AdamDave
2TA1AdamTANW
3NW10DaveLASW
4LA9AdamWAEH
5SW5DaveLSW
6WA4AdamHPLS
7SW6Dave
8EH3Dave
9LS16Adam
10W3Dave
11XX12Not Covered
12HP13Adam
13LS16Adam
Sheet19
Cell Formulas
RangeFormula
B2:B13B2=IF(NOT(ISNA(XMATCH(LEFT(A2,MIN(IFERROR(SEARCH(SEQUENCE(10,1,0),A2),999))-1),$C$2:$C$6,0))),$C$1,IF(NOT(ISNA(XMATCH(LEFT(A2,MIN(IFERROR(SEARCH(SEQUENCE(10,1,0),A2),999))-1),$D$2:$D$6,0))),$D$1,"Not Covered"))
 
Upvote 0
Another option
Fluff.xlsm
ABCD
1AdamDave
2TA1AdamTANW
3NW10DaveLASW
4LA9AdamWAEH
5SW5DaveLSW
6WA4AdamHPLS
7SW6Dave
8EH3Dave
9LS16Adam
10W3Dave
11XX12?
12HP13Adam
13LS16Adam
14
Data
Cell Formulas
RangeFormula
B2:B13B2=LET(a,TEXTBEFORE(A2,SEQUENCE(,10,0)),IF(COUNTIFS(C:C,a),$C$1,IF(COUNTIFS(D:D,a),$D$1,"?")))
 
Upvote 0
Solution
Glad we could help & thanks for the feedback.
 
Upvote 0

Forum statistics

Threads
1,213,539
Messages
6,114,221
Members
448,554
Latest member
Gleisner2

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