# Running into issues with IF(ISNUMBER(SEARCH

#### MikeJHHT

##### New Member
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

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.
=IF(ISNUMBER(SEARCH(D2,D1)),P2,K2)&IF(ISNUMBER(SEARCH(D2,D3)),"","END")

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

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.

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

You're welcome & thanks for the feedback.

Replies
1
Views
859
Replies
0
Views
259
Replies
12
Views
266
Replies
4
Views
680
Replies
1
Views
468

1,206,971
Messages
6,075,924
Members
446,170
Latest member
zzzz02

### 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.

### Which adblocker are you using?

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

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