Index function to search a string and retain multiple words with reference to another table

J

jajatidev

Board Regular
Joined
Jul 29, 2016
Messages
78
Office Version
  1. 365
Platform
  1. Windows
Hi,
I'm in need of assistance with the Index Function to search for words within a string with a reference from another sheet. match the values and retain them.

Table 1 contains the following words;
RRDC
RRMF
RROM
SCAL
XBOP
EOL2
INCO

Table 2 contains the word strings;
Order Status
BLCA BLKD C2AP COM-GLA NoMP REL SCAL XBOP
BLCA BLKD C2AP COM-GLA NoMP REL SCAL XBOP
BLCA BLKD C2AP COM-GLA NoMP REL SCAL XBOP
BLCA BLKD C2AP COM-GLA NoMP REL SCAL XBOP
BLCA BLKD C2AP CFSL COM-ATP COM-FDL COM-GLA NoMP REL XBOP
BLCA BLKD C2AP CFSL COM-ATP COM-FDL COM-GLA NoMP REL XBOP
BLCA BLKD C2AP CFSL COM-ATP COM-FDL COM-GLA NoMP REL XBOP
BLKD C2AP COM-GLA EOL0 EOL2 NoMP REL SCAL
BLCA BLKD C2AP COM-GLA EOL2 NoMP REL SCAL
BLCA BLKD C2AP COM-GLA EOL2 NoMP REL SCAL
BLKD C2AP COM-GLA EOL0 EOL2 NoMP REL SCAL
BLKD C2AP COM-GLA EOL0 EOL2 NoMP REL SCAL
BLCA C2AP EOL2 NoMP REL SCAL
BLKD C2AP COM-GLA EOL2 NoMP REL SCAL
BLCA C2AP CFSL COM-GLA NoMP REL XBOP
BLCA BLKD C2AP CFSL COM-ATP COM-FDL COM-GLA NoMP REL XBOP
C2AP COM-GLA INC-VBEP-LDDAT INC-VBEP-MBDAT INC-VBEP-TDDAT INC-VBEP-WADAT INCO NoMP REL
BLCA BLKD C2AP COM-GLA NoMP REL SCAL
BLCA BLKD C2AP COM-GLA NoMP REL RRDC SCAL
BLCA C2AP CFSL COM-GLA NoMP REL XBOP
BLCA BLKD C2AP COM-GLA NoMP PRSE REL RRDC
BLCA BLKD C2AP COM-GLA EOL1 NoMP REL RRDC
BLCA C2AP COM-GLA EOL1 NoMP REL RRDC
C2AP INC-VBAP-CEPOK INCO MDID NoMP REL RROM SCAL
BLCA C2AP EOL2 INC-VBAP-CEPOK INCO MDID NoMP REL RROM SCAL
BLCA BLKD C2AP COM-GLA NoMP REL RRDC SCAL
INC-VBAP-CEPOK INC-VBAP-NETWR INCO NoMP PRSE REL
BLKD C2AP COM-GLA INC-VBAP-ROUTE INCO NoMP REL
BLKD C2AP COM-GLA EOL0 EOL2 NoMP REL SCAL

The formula [=IFERROR(INDEX(CodeList,MATCH(1,COUNTIF(A9,"*"&CodeList&"*"),0)),"")] I'm using is not giving the desired result.

Therefore, I need assistance with the formula where it should search the string and retain all the words recognized from Table 1.

I am looking forward to hearing from you.

Thanks,
Dev
 
Sort by date Sort by votes
Fluff said:
Ok, how about
Excel Formula: 
=LET(x,TEXTSPLIT(C2," "),TEXTJOIN(" ",,XLOOKUP(x,$A$2:$A$8,$A$2:$A$8,"")))
Click to expand...
Thanks, Fluff. The solution works as expected.
Now onto the 2nd half of the work.

Complete Delivery - N
Each line item within the order is treated independently. So if one line item in the order has a block then the remaining lines can be shipped if they don't have blocks.

Complete Delivery - Y
The order is treated as a whole. If a line item has a block then it needs to be removed for the order to ship.

The BlockCode column provides information on whether the line item is blocked or not.

The logic I'm looking for is;
If the order is Complete Delivery - N and the order line has a block then under the OrderIsBlocked column I need the comment "OrderLineIsBlocked"
Example: Order No# 0113987750 has 3 lines of which 1 is blocked. "OrderLineIsBlocked" should appear against the line while for the other two lines the field under OrderIsBlocked is blank.

If the order is Complete Delivery - Y and one of the order lines are blocked then under the OrderIsBlocked column I need the comment "OrderIsBlocked"
Example: Order No# 0110433907 has 2 lines with blocks. Since its a Delivery Complete - Y order all lines regardless will capture the comment "OrderIsBlocked"

Book1
BCDEFGH
1SalesOrderNoProductNoOrderQtyCompleteDeliveryUserStatusBlockCodeOrderIsBlocked
201129353801NR73A-K1H1NC2AP INC-VBAP-CEPOK INCO MDID NoMP RELINCO
30113430548A1A30A-KGJ1NBLCA C2AP EOL2 NoMP REL SCALEOL2 SCAL
401134937101NR73A-K1H1NC2AP INC-VBAP-CEPOK INCO MDID NoMP RELINCO
50113512547A1A30A-KGJ1NC2AP EOL2 NoMP REL SCALEOL2 SCAL
601135438135RA32F-KGJ1NC2AP MDID NoMP REL RRDCRRDC
70113561139A1A30A-KGJ1NEOL2 MDID NoMP REL SCALEOL2 SCAL
80113705782A1A77A-KGJ10NC2AP EOL2 MDID NoMP REL SCALEOL2 SCAL
90113725103N7L37A1NC2AP INC-VBAP-CEPOK INCO MDID NoMP RELINCO
1001139009462Z600F-KGJ1NC2AP NoMP REL SCALSCAL
1101139338933JA05AN-1501NC2AP EOL2 NoMP RELEOL2
120113969730A1A56A-KGJ1NC2AP EOL2 MDID NoMP REL SCALEOL2 SCAL
1301139877505RA32F-KGJ1NCOM-GLA NoMP REL
1401140021502Z613F-KGJ2NC2AP NoMP REL SCALSCAL
1501140021505RA32F-KGJ1NCOM-GLA NoMP REL
1601140034522Z600F-KGJ5NC2AP NoMP REL SCALSCAL
1701140051802Z600F-KGJ1NC2AP NoMP REL SCALSCAL
180114016353A1A77A-KGJ1NC2AP EOL2 MDID NoMP REL SCALEOL2 SCAL
1901101134333GZ25A-KGJ1YBLKD C2AP COM-GLA NoMP REL SCALSCAL
200110113433K5L35A1YBLKD C2AP CFSL COM-ATP COM-FDL COM-GLA NoMP REL RRDCRRDC
2101101134333GZ25A-KGJ1YBLKD C2AP COM-GLA NoMP REL SCALSCAL
220110113433K5L35A1YBLKD C2AP CFSL COM-ATP COM-FDL COM-GLA NoMP REL RRDCRRDC
230110113433Y3D03A1YBLKD C2AP CFSL COM-ATP COM-FDL COM-GLA NoMP REL
240110113433Z7C07A1YBLKD C2AP CFSL COM-ATP COM-FDL COM-GLA NoMP REL
2501101134333PZ35A-KGJ1YC2AP CFSL COM-FDL COM-GLA COM-PDD NoMP PREL REL XBOPXBOP
260110113433D7P27A1YC2AP CFSL COM-FDL COM-GLA COM-PDD NoMP REL
2701101134333PZ35A-KGJ1YC2AP CFSL COM-FDL COM-GLA COM-PDD NoMP PREL REL XBOPXBOP
280110113433D7P27A1YC2AP CFSL COM-FDL COM-GLA COM-PDD NoMP REL
2901104339073GZ25A-KGJ1YBLCA BLKD C2AP COM-GLA NoMP REL SCAL XBOPSCAL XBOP
300110433907K5L35A1YBLCA BLKD C2AP CFSL COM-ATP COM-FDL COM-GLA NoMP REL
310110433907Y3D03A1YBLCA BLKD C2AP CFSL COM-ATP COM-FDL COM-GLA NoMP REL
320110433907Z7C07A1YBLCA BLKD C2AP CFSL COM-ATP COM-FDL COM-GLA NoMP REL
3301104339073GZ25A-KGJ1YBLCA BLKD C2AP COM-GLA NoMP REL SCAL XBOPSCAL XBOP
Sheet1
 
Upvote 0

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.
As this is a totally different question, it needs a new thread. Thanks
 
Upvote 0
You must log in or register to reply here.

Similar threads

J
  • Solved
Excel Functions: Condition Based
Replies
1
Views
295
kevin9999
K
J
  • Solved
Need assistance with Power Query
2
Replies
11
Views
660
smozgur
smozgur

Forum statistics

Threads
1,223,912
Messages
6,175,340
Members
452,637
Latest member
Ezio2866

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