Running into issues with IF(ISNUMBER(SEARCH

MikeJHHT

New Member
Joined
Jan 8, 2020
Messages
39
Office Version
  1. 365
Platform
  1. Windows
The source code for this is in S2
I'm trying to nest an IF(ISNUMBER(SEARCH statement and keep getting errors.
=IF(ISNUMBER(SEARCH(D2,D1)),P2,K2,IF(ISNUMBER(SEARCH(D2,D3)),"",O2))
What im trying to say is
If D2 is the same as D1 then P2 otherwise K2, If D2 is the same as D3 do nothing, otherwise O2,

For a better longer explanation, at the nd of each of my concatenates in column K and column P. There needs to be an END( which means F3 to execute the concatenate), that is hurting me, the END can only be used at end of each truck number and no where in the middle, so i removed it from every concatenate and placed it in O2,(HIGHLIGHTED IN RED)
My thought it that this formula will determine when a new truck number will begin and place the END on the previous truck number
In this example D2 to D16 is all the same customer and If you look in Column A, they are all the same truck number.
For every first customer, K2 will begin the concatenate. If its the only customer, only K2 will be used for the customer in D2
but... if the customer has multiple lines, each additional line will come from P...P3&p4&p5&p6 etc once the last P is used... P16, O2 will be used (end)


magic test 2 no end.xlsm
ABDHIJKLMNOPQRSTUVWXYZAA
1TRUCK #SO'SCUSTOMERBEGINNING OF TRUCK SETUPADDITION TO SAME TRUCK #GROUPING CODE BY COL DTRUCKCODE
2750J432121006*4 ARCHITECTURAL FIREPLACESSTART010;200714;750;01;Y;25;S;J43212;T1;16:30;ENDS;J43212;T1;16:30; #VALUE!750010;200714;750;01;Y;25;S;J43212;T1;16:30;;S;J66459;T1;16:30;;S;J66880;T1;16:30;;S;J67979;T1;16:30;;S;J68218;T1;16:30;;S;J69785;T1;16:30;;S;J69795;T1;16:30;;S;J69796;T1;16:30;;S;J69797;T1;16:30;;S;J69798;T1;16:30;;S;J69806;T1;16:30;;S;J69807;T1;16:30;;S;J70985;T1;16:30;;S;J71001;T1;16:30;;S;J71123;T1;16:30;
3750J664591006*4 ARCHITECTURAL FIREPLACES010;200714;750;01;Y;25;S;J66459;T1;16:30;S;J66459;T1;16:30;S;J66459;T1;16:30;751010;200711;751;01;Y;25;S;J68210;T1;16:30;END;S;J73315;T1;16:30;END
4750J668801006*4 ARCHITECTURAL FIREPLACES010;200714;750;01;Y;25;S;J66880;T1;16:30;S;J66880;T1;16:30;S;J66880;T1;16:30;
5750J679791006*4 ARCHITECTURAL FIREPLACES010;200714;750;01;Y;25;S;J67979;T1;16:30;S;J67979;T1;16:30;S;J67979;T1;16:30;
6750J682181006*4 ARCHITECTURAL FIREPLACES 010;200714;750;01;Y;25;S;J68218;T1;16:30;S;J68218;T1;16:30;S;J68218;T1;16:30;
7750J697851006*4 ARCHITECTURAL FIREPLACES010;200714;750;01;Y;25;S;J69785;T1;16:30;S;J69785;T1;16:30;S;J69785;T1;16:30;
8750J697951006*4 ARCHITECTURAL FIREPLACES010;200714;750;01;Y;25;S;J69795;T1;16:30;S;J69795;T1;16:30;S;J69795;T1;16:30;
9750J697961006*4 ARCHITECTURAL FIREPLACES010;200714;750;01;Y;25;S;J69796;T1;16:30;S;J69796;T1;16:30;S;J69796;T1;16:30;
10750J697971006*4 ARCHITECTURAL FIREPLACES010;200714;750;01;Y;25;S;J69797;T1;16:30;S;J69797;T1;16:30;S;J69797;T1;16:30;
11750J697981006*4 ARCHITECTURAL FIREPLACES010;200714;750;01;Y;25;S;J69798;T1;16:30;S;J69798;T1;16:30;S;J69798;T1;16:30;
12750J698061006*4 ARCHITECTURAL FIREPLACES010;200714;750;01;Y;25;S;J69806;T1;16:30;S;J69806;T1;16:30;S;J69806;T1;16:30;
13750J698071006*4 ARCHITECTURAL FIREPLACES010;200714;750;01;Y;25;S;J69807;T1;16:30;S;J69807;T1;16:30;S;J69807;T1;16:30;
14750J709851006*4 ARCHITECTURAL FIREPLACES010;200714;750;01;Y;25;S;J70985;T1;16:30;S;J70985;T1;16:30;S;J70985;T1;16:30;
15750J710011006*4 ARCHITECTURAL FIREPLACES010;200714;750;01;Y;25;S;J71001;T1;16:30;S;J71001;T1;16:30;S;J71001;T1;16:30;
16750J711231006*4 ARCHITECTURAL FIREPLACES010;200714;750;01;Y;25;S;J71123;T1;16:30;S;J71123;T1;16:30;S;J71123;T1;16:30;
17751J73367A2371*3 HEARTHSIDE FIREPLACE & PATIO010;200714;751;01;Y;25;S;J73367;T1;16:30;S;J73367;T1;16:30;010;200714;751;01;Y;25;S;J73367;T1;16:30;751010;200714;751;01;Y;25;S;J73367;T1;16:30;;S;J73476;T1;16:30;
18751J73476A2371*3 HEARTHSIDE FIREPLACE & PATIO010;200714;751;01;Y;25;S;J73476;T1;16:30;S;J73476;T1;16:30;S;J73476;T1;16:30;
19752J62974A80648 FRANKLIN FIREPLACE010;200714;752;01;Y;25;S;J62974;T1;16:30;S;J62974;T1;16:30;010;200714;752;01;Y;25;S;J62974;T1;16:30;752010;200714;752;01;Y;25;S;J62974;T1;16:30;;S;J66513;T1;16:30;
20752J66513A80648 FRANKLIN FIREPLACE010;200714;752;01;Y;25;S;J66513;T1;16:30;S;J66513;T1;16:30;S;J66513;T1;16:30;
21010;200714;;01;Y;25;S;;T1;16:30;S;;T1;16:30;S;;T1;16:30;
22010;200714;;01;Y;25;S;;T1;16:30;S;;T1;16:30;010;200714;;01;Y;25;S;;T1;16:30;
23010;200714;;01;Y;25;S;;T1;16:30;S;;T1;16:30;010;200714;;01;Y;25;S;;T1;16:30;
24010;200714;;01;Y;25;S;;T1;16:30;S;;T1;16:30;010;200714;;01;Y;25;S;;T1;16:30;
25010;200714;;01;Y;25;S;;T1;16:30;S;;T1;16:30;010;200714;;01;Y;25;S;;T1;16:30;
26010;200714;;01;Y;25;S;;T1;16:30;S;;T1;16:30;010;200714;;01;Y;25;S;;T1;16:30;
27010;200714;;01;Y;25;S;;T1;16:30;S;;T1;16:30;010;200714;;01;Y;25;S;;T1;16:30;
28010;200714;;01;Y;25;S;;T1;16:30;S;;T1;16:30;010;200714;;01;Y;25;S;;T1;16:30;
WILL CALLS
Cell Formulas
RangeFormula
O2O2="END"
K2:K28K2="010;"&TEXT(TODAY()+1,"yymmdd")&";"&A2&";01;Y;25;S;"&B2&";T1;16:30;"
P2:P28P2="S;"&B2&";T1;16:30;"
S2S2=IF(ISNUMBER(SEARCH(D2,D1)),P2,K2)*(IF(ISNUMBER(SEARCH(D2,D3)),"",O2))
S3:S28S3=IF(ISNUMBER(SEARCH(D3,D2)),P3,K3)
 

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK
How about
=IF(ISNUMBER(SEARCH(D2,D1)),P2,K2)&IF(ISNUMBER(SEARCH(D2,D3)),"","END")
 
Upvote 0
Thankyou so much, that worked exactly how you wrote it.
But i ran into an issue i didn't see until now..
If it possible to combine this statement in with Column K and P, Having it in S works for Column S

SO column K or P can determine where the End can be?

I apologize, I have 2 options here, either rework K and P, or have the person who created my macro remake the macro.

Id like to see if the formula could be adapted to K and P
 
Upvote 0
Bearing in mind I have absolutely no idea what you are trying to do, or why. I have no idea if it can be adapted.
 
Upvote 0
completely understood, ive been learning VBA extremely slowly, i managed to somewhat decipher the Macro another member on here had created for me, and i believe i got it to work, I still need to remember when starting an inquiry, no one knows what im talking about, i seem to forget that often.

Thanks again so much for your help, that formula you gave me worked perfect
 
Upvote 0
You're welcome & thanks for the feedback.
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,256
Members
448,558
Latest member
aivin

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