Extracting first letter

[RIZVI]

Hi All,

I am using the following VBA to extract first letters from SKU list. All is fine except when couple of SKUs have matching starting letters, then I get duplicate results. Like say there are items like Chicken Crispy, Chicken Chilly then in both cases i get CC.

Is there a way that ONLY in such conflicing cases ,I get different results say like CCr and CCh instead of CC in both cases.

VBA that I m using is geiven below:

Function GetFirstLetters(rng As Range) As String
'Update 20140325
Dim arr
Dim I As Long
arr = VBA.Split(rng, " ")
If IsArray(arr) Then
For I = LBound(arr) To UBound(arr)
GetFirstLetters = GetFirstLetters & Left(arr(I), 1)
Next I
Else
GetFirstLetters = Left(arr, 1)
End If
End Function

 

[bobsan42]

Thos function only extracts letters from one string. It doesn't know if you have others which may become duplicates.
And even if you add another letter you are still bound to have duplicates e.g. chicken chilly and chicken chunks.
Give it some more thinking and try to clarify exactly what you're trying to achieve.

 

[RIZVI]

Thos function only extracts letters from one string. It doesn't know if you have others which may become duplicates.
And even if you add another letter you are still bound to have duplicates e.g. chicken chilly and chicken chunks.
Give it some more thinking and try to clarify exactly what you're trying to achieve.

Thanks for the response.

Currently SKUs with second letter also matchig is very few. If I can resolve the first letters duplicates, a major headache will be solved. In situations where second letter is also matchig I will have to manually change it or think of chaning the names of items so that second letter is not conflicting. As items with second letters matching are just few I think that will be OK.

RGDS,

Rizvi.M.H.

 

[Joe4]

The problem is that your current code is just a function - it just takes input and creates output. It does not look at any other data that you might have. It is very similar to most other native basic Excel functions.

To do what you want, you would need to make some substantial changes, possibly creating more functions and/or procedures to check for duplicates and decide what to do with them. You will probably need to add looping to handle those duplicates. In any event, it would require a bit more work.

If you do not have very many, it might be easier to simply use Conditional Formatting to highlight the duplicates, and then manually fix those.

 

[RIZVI]

The problem is that your current code is just a function - it just takes input and creates output. It does not look at any other data that you might have. It is very similar to most other native basic Excel functions.

To do what you want, you would need to make some substantial changes, possibly creating more functions and/or procedures to check for duplicates and decide what to do with them. You will probably need to add looping to handle those duplicates. In any event, it would require a bit more work.

If you do not have very many, it might be easier to simply use Conditional Formatting to highlight the duplicates, and then manually fix th

 

[RIZVI]

Currently I am doing same; using conditional formatting. I was hoping if someone can suggest a way by which at least first matching letters can be resolved.


RGDS,

Rizvi.M.H.

 

[Joe4]

Currently I am doing same; using conditional formatting. I was hoping if someone can suggest a way by which at least first matching letters can be resolved.


RGDS,

Rizvi.M.H.

It is possible, but it is by no means a trivial task.

How are you currently using your function? Are you using it right on the spreadsheet, or is it being called by other VBA code?

Can you post a small sample of data and what the formula is currently returning?
It would be helpful to see what columns/rows these two things reside in.

 

[RIZVI]

It is possible, but it is by no means a trivial task.

How are you currently using your function? Are you using it right on the spreadsheet, or is it being called by other VBA code?

Can you post a small sample of data and what the formula is currently returning?
It would be helpful to see what columns/rows these two things reside in.

I am using right on spreadsheet.

RGDS,

Rizvi.M.H.

 

[Joe4]

I cannot think of a way to do what you want using the same format you are now, that would dynamically also consider all other entries with just that single function right on the sheet.
The way that I would do it would be to use VBA to populate column A. It would make use of your function, but also have to do checking of all other values too.

Before I go down that path, are you open to that kind of solution, where you will need to run a VBA procedure in order to populate column A?

 

[RIZVI]

I cannot think of a way to do what you want using the same format you are now, that would dynamically also consider all other entries with just that single function right on the sheet.
The way that I would do it would be to use VBA to populate column A. It would make use of your function, but also have to do checking of all other values too.

Before I go down that path, are you open to that kind of solution, where you will need to run a VBA procedure in order to populate column A?

Of course I am open to it and look forward to the solution.

RGDS,

Rizvi.M.H.