search criteria like small(if(isnumber(find.. with any code

**adeel1** · 09-29-2019, 07:23 AM

Hi All

i have attached the sheet with made up sample.
in sheet 4 there is column C and E is the data which i received and i want result of column D and F as i mentioned with reference to subject of this post!

column C and E is linked with sheet 3 columns, there is mainly two types of packages here "PRO" and "Control" in column E if its not mention which one package it is than it will look off column D cell to determined that this will pro or control. means E2 has only "freedom" it will look to D2 to determent for its Pro or control , its pro as per D2 & correct result is in F2!

if sheet 2 columns D is empty than "sms only" will appear along with all data if it has any of cell e mail address than it will not appear.

Also posted: https://www.mrexcel.com/forum/excel-...ml#post5348812

Adeel

**jindon** · 09-29-2019, 07:31 PM

If Col.F in Sheet4 is what you want, there are too many irregular results, e.g

Row 13:
"data mini/data lite" in Col.E while "PRO - Data Mini;PRO - DataLite;SMS Only"
Row 19
"voice 2" in Col.E while "PRO - All Voice 2;SMS Only"

etc.etc.
No way to do this unless you clarify the rules in detail.

**adeel1** · 09-30-2019, 02:11 AM

Hi sir , Thanks for your help
these are correct result infect, any back slash (/) means this cell has 2 or 3 Bolton from column B in it if there is no "/" means its has single Bolton and "/" will turn into ";" in results columns.
Data mini turn into "PRO - Data Mini" from B25 after search and it determine from D13 that it pro-600, if D13 has control 600 than it will be "control- Data Mini" . than back slash turn into ; and
same rule apply for data lite. it come from B23 after search, data lite turn into "PRO - DataLite" from B23 and it determine from D13 that it pro-600, if D13 has control 600 than it will be "control- Data Lite"
"sms only" joins because sheet 2 cell D13 has no e mail address ,if it has email address than "sms only" will not come!

Adeel

**jindon** · 09-30-2019, 02:38 AM

Originally Posted by adeel1

these are correct result infectl

If you say so, impossible.
I know single/multiple "Bolton" when ; is found, but you gave no explanation why:
"data lite" becomes DataLite
"voice 2" becomes "All Voice 2".

**adeel1** · 09-30-2019, 03:02 AM

Hi sir,
"data lite" & "voice 2" is the close match with these in column B or you can say its means this is it that why its become this,
i received data from different channel in sheet which i have to look up in column B which i have to corrected it manually if this is hard to manage than i am ready to compromised on these.

Adeel

**jindon** · 09-30-2019, 03:15 AM

Then as I said, it is impossible.
I don't know where the data in col.E come from but you will need to correct the Col.E data first to match your result.

**adeel1** · 09-30-2019, 03:22 AM

no issue sir , as i say i am ready to compromise if any code give max results or little change in column E i can manage to correct it manually! or with any helper/additional column, please
Adeel

**jindon** · 09-30-2019, 03:37 AM

Use in cell like
=GetBoltons(C2,E2,Sheet2!D2)

Please Login or Register  to view this content.

**adeel1** · 09-30-2019, 04:41 AM

thnx for your code , please review file word "data" is repeating in results! yellow highlighted cells

Adeel

**jindon** · 09-30-2019, 04:56 AM

Is this a last change that might occur or if you have more cases, upload any combination of the data.
I don't want to do it one by one.

**adeel1** · 09-30-2019, 12:46 PM

Hi sir sorry for late reply

I can understand it is very hard to change code again & again that why I made example in F, these are correct results, some of points, which I observed for corrections as under.
Pro should be in capital letter “PRO”
Word “Data” is repeating in most cells.
In PRO voice (row 48 to 53, these are correct) Bolton there is only one dash and in control voice (row 85 to 90) there is two dashes. in column B. in result column these are missing. Word “All” is also Missing row 110 to row 121 & row 32,34,35
Row 26, 27, correct result is “PRO - Data Liberty Ace” with sms only
Row 28, 29 correct result is “PRO – DataLite” with sms only
Row 37 correct result is “PRO - Data Liberty”
Row 43 to 45 spelling mismatched
Row 47 correct result is “Control Data-Liberty Century”
Row 47 to 70 word “data control” is additional word
Row 71 correct result is “Control Data-Liberty Plus”
Row 73 to 76 there should no space in word “VoiceNet”

Most cells taking spell/character from column E as it is, spell/character should be column F because these are correct result I made these manually, row 43 to 45.
Please give me one more chance after this or if you don’t mind to apply exact case in column H to avoid this, as these are correct of column F, please

Adeel

**adeel1** · 09-30-2019, 12:49 PM

if you think some of option cannot be apply or will not get correct result i am ready to compromise on those!

Adeel

**adeel1** · 10-01-2019, 04:16 AM

Hi sir

i will be greatfull if you help me more on this! please
Adeel

**jindon** · 10-01-2019, 04:20 AM

Found discrepancy, so will post later.

**jindon** · 10-01-2019, 10:22 AM

1st question
why
row 8
"Pro 600" "data liberty/data freedom" becomes "PRO - Data Liberty;PRO - Data Freedom;SMS Only"

row 26
"Pro-600" "data liberty" becomes "PRO - Data Liberty Ace;SMS Only"

row 38
"Pro-600" "Pro data liberty" becomes "PRO - Data Liberty Ace;SMS Only"

**adeel1** · 10-01-2019, 11:11 AM

Hi sir

My apology i mistakenly put there, its all "PRO - Data Liberty"

another correction cells E104 & E105 actually control instead of pro but results are correctly placed in F104 and F105!

Adeel

**jindon** · 10-01-2019, 08:41 PM

I need to see EXACT CORRECT results to test with.

If any wrong data, that means nothing.

**adeel1** · 10-02-2019, 02:34 AM

Hi sir

i corrected the data!

Adeel

**jindon** · 10-02-2019, 08:34 AM

Row 45
"Control 300" "Pro SMS Ace" becomes "PRO - SMS Ace;SMS Only"
Need you explanation.

This is only for "PRO".

Please Login or Register  to view this content.

**adeel1** · 10-02-2019, 12:41 PM

thnx you so much sir this is perfectly worked outclass!you are brilliant!

About Row 45

this is typo mistake it is "pro 300" in C45! sorry and "SMS only" is not disappearing when column D sheet2 cells are no empty!

Adeel

**jindon** · 10-02-2019, 10:41 PM

Explain the difference in the result of "Data Liberty".

Sometimes "Data Liberty", and "Data-Liberty" sometimes.

**adeel1** · 10-03-2019, 02:22 AM

Hi sir
could you help me in which row you find this i review file i haven't look,please
adeel

**jindon** · 10-03-2019, 02:30 AM

See row 72

**adeel1** · 10-03-2019, 03:58 AM

this is "data liberty century" becomes "Control Data-Liberty Century;SMS Only" or "Liberty century" into "Control Data-Liberty Century;SMS Only"

simple "data liberty" will come up with "Control - Data Liberty" or liberty also come up with "Control - Data Liberty"

Adeel

**jindon** · 10-03-2019, 05:22 AM

Another one and getting tired of this...

row 108, 109, 110 "Voice 6"

Simply impossible.

**adeel1** · 10-03-2019, 06:00 AM

can we go with like "All voice 6" if not no issue than i will manage manually!

Adeel

**jindon** · 10-03-2019, 06:16 AM

I only work from the result that you uploaded, so if you don't review your workbook and correct everything.
I mean if you don't update your workbook, I will not do it.

**adeel1** · 10-04-2019, 10:06 AM

very sorry for very late reply , i was in hospital with my father!
i will not ask you again for change, please help

Adeel

**jindon** · 10-05-2019, 12:14 AM

Listen carefully.

The code I posted in #19 is not a completed one due to the row 45. I thought it would be different.

As I'm not your "typo" finder, I will never write the rest of the code until I see the PERFECT COMPLETE workbook to be tested.
You are comparing the result with EXACT function, so it MUST BE EXACT CORRECT desired result.

After that, I can tell you if I can do it or not, when I find any logical contradiction again.

Otherwise, please ask someone else.

**adeel1** · 10-05-2019, 10:11 AM

i corrected and uploaded the reviewed file!

Adeel

**jindon** · 10-05-2019, 10:59 PM

Still typo and other things in both Col.E & F...

Please Login or Register  to view this content.

**adeel1** · 10-07-2019, 01:36 AM

Thank you so much sir for your time and help and patience!
your knowledge , skill and experience is out standing , i have always great wishes for you! stay blessed with happy life!

Adeel

search criteria like small(if(isnumber(find.. with any code

LinkBack

Thread Tools

Rate This Thread

Display

search criteria like small(if(isnumber(find.. with any code

Re: search criteria like small(if(isnumber(find.. with any code

Re: search criteria like small(if(isnumber(find.. with any code

Re: search criteria like small(if(isnumber(find.. with any code

Re: search criteria like small(if(isnumber(find.. with any code

Re: search criteria like small(if(isnumber(find.. with any code

Re: search criteria like small(if(isnumber(find.. with any code

Re: search criteria like small(if(isnumber(find.. with any code

Re: search criteria like small(if(isnumber(find.. with any code

Re: search criteria like small(if(isnumber(find.. with any code

Re: search criteria like small(if(isnumber(find.. with any code

Re: search criteria like small(if(isnumber(find.. with any code

Re: search criteria like small(if(isnumber(find.. with any code

Re: search criteria like small(if(isnumber(find.. with any code

Re: search criteria like small(if(isnumber(find.. with any code

Re: search criteria like small(if(isnumber(find.. with any code

Re: search criteria like small(if(isnumber(find.. with any code

Re: search criteria like small(if(isnumber(find.. with any code

Re: search criteria like small(if(isnumber(find.. with any code

Re: search criteria like small(if(isnumber(find.. with any code

Re: search criteria like small(if(isnumber(find.. with any code

Re: search criteria like small(if(isnumber(find.. with any code

Re: search criteria like small(if(isnumber(find.. with any code

Re: search criteria like small(if(isnumber(find.. with any code

Re: search criteria like small(if(isnumber(find.. with any code

Re: search criteria like small(if(isnumber(find.. with any code

Re: search criteria like small(if(isnumber(find.. with any code

Re: search criteria like small(if(isnumber(find.. with any code

Re: search criteria like small(if(isnumber(find.. with any code

Re: search criteria like small(if(isnumber(find.. with any code

Re: search criteria like small(if(isnumber(find.. with any code

Re: search criteria like small(if(isnumber(find.. with any code

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Search & Find with AutoFilter code for Multiple Criteria Solved!

[SOLVED] Can ISNUMBER + SEARCH function find values NOT in a cell

[SOLVED] Find-Search-2 Columns-Find / Search Multiple Criteria & Add or Sum Totals

[SOLVED] If/IsNumber multiple search criteria. Does syntax accept "OR"?

[SOLVED] Having trouble with =IF(ISNUMBER(SEARCH... formula to find keywords from a cell.

Apache Open Office 4.0.1(=SUM(ISNUMBER(MATCH(B1:F1,SMALL(B2:F2+{1:-1}))))

[SOLVED] Multiple Criteria Isnumber Search Array (with Attachment Example)

Bookmarks

Bookmarks

Posting Permissions