Count the similar string of many cells

[IceFrogBG]

I have some cells have data (string type) and it is have some letter is same. Everyone here
please give me a method to get the similar letter.
Example : I have some string : SV38A1,SV38A2,SV38A3,SV38B1,SV38B2,SV38C1,SV38D1
in this case I have to get
Series = "SV38" all data have "SV38"
Model 1 = "SV38A" 3 data have "SV38A"
Model 2 = "SV38B"
Model 3 = "SV38C"
Model 4 = "SV38D"
Hope receive a idea from you.
Thanks so much.

 

[NARAYANK991]

Hi ,

It would help if you could upload a workbook with real-life data ; alternatively , you can answer the following questions :

1. Are all the text strings of the same length ?

2. Is the length for all text strings fixed at 6 characters ?

3. Is a series defined by the maximum sub-string which is common to all text strings ?

4. Is a model defined as a combination of a series and one additional unique character ?

5. Is the digit in the right-most position irrelevant ?

Narayan

 

[IceFrogBG]

Hi ,

It would help if you could upload a workbook with real-life data ; alternatively , you can answer the following questions :

1. Are all the text strings of the same length ?

2. Is the length for all text strings fixed at 6 characters ?

3. Is a series defined by the maximum sub-string which is common to all text strings ?

4. Is a model defined as a combination of a series and one additional unique character ?

5. Is the digit in the right-most position irrelevant ?

Narayan

Hi Narayan,
these string above is a example.
1.all text string is not same length.
2.So length can not fix 6 letter
3. Yes Series is max sub_string which is common of all text string
4. Ex SMD600 Acoustic 1,SMD600 Acoustic 2,SMD600 Acoustic 3,SDM600 LCD 1,SDM600 LCD 2
=> series : SDM600
Model 1 : SDM600 Acoustic
Model 2 : SDM600 LCD
5. the digit in the right-most position don't take care.
Please give me a idea, it can done or not?

 

[NARAYANK991]

Hi ,

It may be possible using formulae , provided helper columns are acceptable.

It will be possible using VBA provided that is acceptable.

Either way , is it possible to upload a workbook with as much data as you can give , with as much variety as you can include ?

Narayan

 

[IceFrogBG]

Hi ,

It may be possible using formulae , provided helper columns are acceptable.

It will be possible using VBA provided that is acceptable.

Either way , is it possible to upload a workbook with as much data as you can give , with as much variety as you can include ?

Narayan

Hi Narayan
I want to ma VBA project (can use with different case).
So can you have me make a sub/function can solved this master
Example :
Function GetSeries(Range of sheet)
Range of sheet content all text string which need to compare.
the value return is series and model etc...

 

[NARAYANK991]

Hi ,

Please upload a workbook with adequate data in it.

Narayan

 

[IceFrogBG]

Please check it Narayan.
Thanks

 

[NARAYANK991]

Hi ,

See the attached file ; there are two separate UDFs , one named GetSeries , and the other named GetModels.

The GetSeries UDF is a simple one , to be entered in a single cell.

The GetModels UDF returns an array of values , and therefore should be array entered in a range of cells. Thus , if your input data is in the range A1 through H1 , which is a range of 8 cells , then for the GetModels UDF , select a range of 8 cells , and then array enter the UDF , using the CTRL SHIFT ENTER combination.

If your input data is a row vector , as in A1:H1 , then the GetModels UDF should also be entered as a row vector ; thus , select a range such as K1:Z1 and then array enter =GetModels(A1:H1)

If you wish to populate a column range with the output of this UDF , you will have to array enter =TRANSPOSE(GetModels(A1:H1))

The GetModels UDF will populate cells with the model numbers and leave the remaining cells blank ; however , if the range over which it has been entered exceeds the number of cells in the input data range , the remaining cells beyond this range will be populated with the #N/A error value.

Thus , if the input data range is A1:H1 , selecting the range K1:R1 (8 cells) and array entering =GetModels(A1:H1) will populate the relevant cells in this range with the model numbers , and leave the remaining cells blank.

However , if for the same input data range , selecting the range K1:Z1 (16 cells) and array entering the same formula , will populate the range K1:R1 with the identified model numbers , and leave the remaining cells blank. The cells S1:Z1 will all be populated with the #N/A error value.

Narayan

 

[IceFrogBG]

Hi Narayan,
Thank you so much for your support.
I will try to understand your solution.