Hi,

A user has presented a challenge that I'm wondering if it's possible.

The spreadsheet has columns of cells each containing several model numbers,
and the values are separated by commas and spaces in each cell. (eg. 8890,
x3340, mx750)

To make things more confusing, each model is colored differently depending
on the distributor, and there are several different colors in some cells. In
total, about six different colors are used (green, red, blue orange, gray,
purple).

Is there a way I can create a "SUM count" based on how many individual
strings are in a range of cells?

How about how many strings exist of each color for a range?

Am I asking the impossible?

I know there's some options for sorting by color, etc. in Excel 2007, I
don't know if that helps, though.
(http://msdn.microsoft.com/en-us/library/cc952296.aspx)

I have Excel 2007 and older versions if that helps

Thanks to everyone who tries to figure this out.

Ben

To get a count of the number of strings in each cell, you just need to
count the number of commas and add one. This formula will do that:

=LEN(A1)-LEN(SUBSTITUTE(A1,",",""))+1

It assumes that your model numbers are in cell A1 - adjust to suit,
then you can copy down.

As for counting how many strings exist of each colour for a range, you
would need a macro to do that.

Hope this helps.

Pete


On Dec 17, 6:11*pm, Ben in CA
wrote:
Hi,

A user has presented a challenge that I'm wondering if it's possible.

The spreadsheet has columns of cells each containing several model numbers,
and the values are separated by commas and spaces in each cell. (eg. 8890,
x3340, mx750)

To make things more confusing, each model is colored differently depending
on the distributor, and there are several different colors in some cells. In
total, about six different colors are used (green, red, blue orange, gray,
purple).

Is there a way I can create a "SUM count" based on how many individual
strings are in a range of cells?

How about how many strings exist of each color for a range?

Am I asking the impossible?

I know there's some options for sorting by color, etc. in Excel 2007, I
don't know if that helps, though.
(http://msdn.microsoft.com/en-us/library/cc952296.aspx)

I have Excel 2007 and older versions if that helps

Thanks to everyone who tries to figure this out.

Ben

Thanks for that Pete_UK!

Does anyone know macro would be required?

Ben

"Pete_UK" wrote:

To get a count of the number of strings in each cell, you just need to
count the number of commas and add one. This formula will do that:

=LEN(A1)-LEN(SUBSTITUTE(A1,",",""))+1

It assumes that your model numbers are in cell A1 - adjust to suit,
then you can copy down.

As for counting how many strings exist of each colour for a range, you
would need a macro to do that.

Hope this helps.

Pete


On Dec 17, 6:11 pm, Ben in CA
wrote:
Hi,

A user has presented a challenge that I'm wondering if it's possible.

The spreadsheet has columns of cells each containing several model numbers,
and the values are separated by commas and spaces in each cell. (eg. 8890,
x3340, mx750)

To make things more confusing, each model is colored differently depending
on the distributor, and there are several different colors in some cells. In
total, about six different colors are used (green, red, blue orange, gray,
purple).

Is there a way I can create a "SUM count" based on how many individual
strings are in a range of cells?

How about how many strings exist of each color for a range?

Am I asking the impossible?

I know there's some options for sorting by color, etc. in Excel 2007, I
don't know if that helps, though.
(http://msdn.microsoft.com/en-us/library/cc952296.aspx)

I have Excel 2007 and older versions if that helps

Thanks to everyone who tries to figure this out.

Ben

You're welcome, Ben.

Can't help on the second part.

Pete

On Dec 17, 7:10*pm, Ben in CA
wrote:
Thanks for that Pete_UK!

Does anyone know macro would be required?

Ben

And if the cell is empty:
=LEN(A1)-LEN(SUBSTITUTE(A1,",",""))+(A1<"")



Pete_UK wrote:

To get a count of the number of strings in each cell, you just need to
count the number of commas and add one. This formula will do that:

=LEN(A1)-LEN(SUBSTITUTE(A1,",",""))+1

It assumes that your model numbers are in cell A1 - adjust to suit,
then you can copy down.

As for counting how many strings exist of each colour for a range, you
would need a macro to do that.

Hope this helps.

Pete

On Dec 17, 6:11 pm, Ben in CA
wrote:
Hi,

A user has presented a challenge that I'm wondering if it's possible.

The spreadsheet has columns of cells each containing several model numbers,
and the values are separated by commas and spaces in each cell. (eg. 8890,
x3340, mx750)

To make things more confusing, each model is colored differently depending
on the distributor, and there are several different colors in some cells. In
total, about six different colors are used (green, red, blue orange, gray,
purple).

Is there a way I can create a "SUM count" based on how many individual
strings are in a range of cells?

How about how many strings exist of each color for a range?

Am I asking the impossible?

I know there's some options for sorting by color, etc. in Excel 2007, I
don't know if that helps, though.
(http://msdn.microsoft.com/en-us/library/cc952296.aspx)

I have Excel 2007 and older versions if that helps

Thanks to everyone who tries to figure this out.

Ben

--

Dave Peterson

I haven't got this figured out yet.

I'd be open to using a complex macro if required - if anyone has any ideas.

Thanks,

Ben

"Ben in CA" wrote:

Hi,

A user has presented a challenge that I'm wondering if it's possible.

The spreadsheet has columns of cells each containing several model numbers,
and the values are separated by commas and spaces in each cell. (eg. 8890,
x3340, mx750)

To make things more confusing, each model is colored differently depending
on the distributor, and there are several different colors in some cells. In
total, about six different colors are used (green, red, blue orange, gray,
purple).

Is there a way I can create a "SUM count" based on how many individual
strings are in a range of cells?

How about how many strings exist of each color for a range?

Am I asking the impossible?

I know there's some options for sorting by color, etc. in Excel 2007, I
don't know if that helps, though.
(http://msdn.microsoft.com/en-us/library/cc952296.aspx)

I have Excel 2007 and older versions if that helps

Thanks to everyone who tries to figure this out.

Ben

Ben,

You could place the following two functions in a module (same place you
would typically store a macro. Then, you could use the functions in your
workbook the same as you use Excel's fundtions. For example, if you wanted
a count of all the strings separated by commas found in A1:D50 and a count
of all the unique font colors appearing in the same range,
=ModelCount(a1:d50)
would give the number of strings and
=ColorCount(a1:d50)
would give the count of how many different colors were used in the same
range.

'---------------------------------------------------

Function ModelCount(myRange As Range) As Integer
Dim rngCell As Range
Dim intCount As Integer

intCount = 0
For Each rngCell In myRange
If Len(rngCell.Text 0) Then
intCount = intCount + UBound(Split(rngCell.Text, ",")) + 1
End If
Next rngCell

ModelCount = intCount

End Function



Function ColorCount(theRange As Range) As Long
Dim rgnCell As Range
Dim txtColor As Variant

Set clrDict = CreateObject("Scripting.Dictionary")

For Each rngCell In theRange
If Len(rngCell.Text 0) Then
For x = 1 To Len(rngCell.Text)
txtColor = rngCell.Characters(x, x).Font.Color
If Len(txtColor) 0 Then
If Not clrDict.Exists(txtColor) Then
clrDict.Add txtColor, txtColor
End If
End If
Next x
End If
Next rngCell

ColorCount = clrDict.Count

Set clrDict = Nothing

End Function


'--------------------------------------------------

Steve Yandl



"Ben in CA" wrote in message
...
I haven't got this figured out yet.

I'd be open to using a complex macro if required - if anyone has any
ideas.

Thanks,

Ben

"Ben in CA" wrote:

Hi,

A user has presented a challenge that I'm wondering if it's possible.

The spreadsheet has columns of cells each containing several model
numbers,
and the values are separated by commas and spaces in each cell. (eg.
8890,
x3340, mx750)

To make things more confusing, each model is colored differently
depending
on the distributor, and there are several different colors in some cells.
In
total, about six different colors are used (green, red, blue orange,
gray,
purple).

Is there a way I can create a "SUM count" based on how many individual
strings are in a range of cells?

How about how many strings exist of each color for a range?

Am I asking the impossible?

I know there's some options for sorting by color, etc. in Excel 2007, I
don't know if that helps, though.
(http://msdn.microsoft.com/en-us/library/cc952296.aspx)

I have Excel 2007 and older versions if that helps

Thanks to everyone who tries to figure this out.

Ben

Hi Steve,

That's phenomenal! The first part is exactly what I wanted.

As for the color counting - how could this be setup so that it lists how
many green, how many blue, how many red, etc.

So that I could put
=CountColorRed'Items'!(F4:M137)
=CountColorBlue'Items'!(F4:M137)
=CountColorGreen'Items'!(F4:M137)
=CountColorOrange'Items'!(F4:M137)
=CountColorYellow'Items'!(F4:M137)
=CountColorPurple'Items'!(F4:M137)
=CountColorGrey'Items'!(F4:M137)

Is there a way that these can be combined?

Thank you for your time!

Ben

"Steve Yandl" wrote:

Ben,

You could place the following two functions in a module (same place you
would typically store a macro. Then, you could use the functions in your
workbook the same as you use Excel's fundtions. For example, if you wanted
a count of all the strings separated by commas found in A1:D50 and a count
of all the unique font colors appearing in the same range,
=ModelCount(a1:d50)
would give the number of strings and
=ColorCount(a1:d50)
would give the count of how many different colors were used in the same
range.

'---------------------------------------------------

Function ModelCount(myRange As Range) As Integer
Dim rngCell As Range
Dim intCount As Integer

intCount = 0
For Each rngCell In myRange
If Len(rngCell.Text 0) Then
intCount = intCount + UBound(Split(rngCell.Text, ",")) + 1
End If
Next rngCell

ModelCount = intCount

End Function



Function ColorCount(theRange As Range) As Long
Dim rgnCell As Range
Dim txtColor As Variant

Set clrDict = CreateObject("Scripting.Dictionary")

For Each rngCell In theRange
If Len(rngCell.Text 0) Then
For x = 1 To Len(rngCell.Text)
txtColor = rngCell.Characters(x, x).Font.Color
If Len(txtColor) 0 Then
If Not clrDict.Exists(txtColor) Then
clrDict.Add txtColor, txtColor
End If
End If
Next x
End If
Next rngCell

ColorCount = clrDict.Count

Set clrDict = Nothing

End Function


'--------------------------------------------------

Steve Yandl



"Ben in CA" wrote in message
...
I haven't got this figured out yet.

I'd be open to using a complex macro if required - if anyone has any
ideas.

Thanks,

Ben

"Ben in CA" wrote:

Hi,

A user has presented a challenge that I'm wondering if it's possible.

The spreadsheet has columns of cells each containing several model
numbers,
and the values are separated by commas and spaces in each cell. (eg.
8890,
x3340, mx750)

To make things more confusing, each model is colored differently
depending
on the distributor, and there are several different colors in some cells.
In
total, about six different colors are used (green, red, blue orange,
gray,
purple).

Is there a way I can create a "SUM count" based on how many individual
strings are in a range of cells?

How about how many strings exist of each color for a range?

Am I asking the impossible?

I know there's some options for sorting by color, etc. in Excel 2007, I
don't know if that helps, though.
(http://msdn.microsoft.com/en-us/library/cc952296.aspx)

I have Excel 2007 and older versions if that helps

Thanks to everyone who tries to figure this out.

Ben

Ben,

It would be a bit more complex to count instances of different colors but
can be done. I'll see if I can find a little time to work on it in the next
couple of days.

Steve


"Ben in CA" wrote in message
...
Hi Steve,

That's phenomenal! The first part is exactly what I wanted.

As for the color counting - how could this be setup so that it lists how
many green, how many blue, how many red, etc.

So that I could put
=CountColorRed'Items'!(F4:M137)
=CountColorBlue'Items'!(F4:M137)
=CountColorGreen'Items'!(F4:M137)
=CountColorOrange'Items'!(F4:M137)
=CountColorYellow'Items'!(F4:M137)
=CountColorPurple'Items'!(F4:M137)
=CountColorGrey'Items'!(F4:M137)

Is there a way that these can be combined?

Thank you for your time!

Ben

"Steve Yandl" wrote:

Ben,

You could place the following two functions in a module (same place you
would typically store a macro. Then, you could use the functions in your
workbook the same as you use Excel's fundtions. For example, if you
wanted
a count of all the strings separated by commas found in A1:D50 and a
count
of all the unique font colors appearing in the same range,
=ModelCount(a1:d50)
would give the number of strings and
=ColorCount(a1:d50)
would give the count of how many different colors were used in the same
range.

'---------------------------------------------------

Function ModelCount(myRange As Range) As Integer
Dim rngCell As Range
Dim intCount As Integer

intCount = 0
For Each rngCell In myRange
If Len(rngCell.Text 0) Then
intCount = intCount + UBound(Split(rngCell.Text, ",")) + 1
End If
Next rngCell

ModelCount = intCount

End Function



Function ColorCount(theRange As Range) As Long
Dim rgnCell As Range
Dim txtColor As Variant

Set clrDict = CreateObject("Scripting.Dictionary")

For Each rngCell In theRange
If Len(rngCell.Text 0) Then
For x = 1 To Len(rngCell.Text)
txtColor = rngCell.Characters(x, x).Font.Color
If Len(txtColor) 0 Then
If Not clrDict.Exists(txtColor) Then
clrDict.Add txtColor, txtColor
End If
End If
Next x
End If
Next rngCell

ColorCount = clrDict.Count

Set clrDict = Nothing

End Function


'--------------------------------------------------

Steve Yandl



"Ben in CA" wrote in message
...
I haven't got this figured out yet.

I'd be open to using a complex macro if required - if anyone has any
ideas.

Thanks,

Ben

"Ben in CA" wrote:

Hi,

A user has presented a challenge that I'm wondering if it's possible.

The spreadsheet has columns of cells each containing several model
numbers,
and the values are separated by commas and spaces in each cell. (eg.
8890,
x3340, mx750)

To make things more confusing, each model is colored differently
depending
on the distributor, and there are several different colors in some
cells.
In
total, about six different colors are used (green, red, blue orange,
gray,
purple).

Is there a way I can create a "SUM count" based on how many individual
strings are in a range of cells?

How about how many strings exist of each color for a range?

Am I asking the impossible?

I know there's some options for sorting by color, etc. in Excel 2007,
I
don't know if that helps, though.
(http://msdn.microsoft.com/en-us/library/cc952296.aspx)

I have Excel 2007 and older versions if that helps

Thanks to everyone who tries to figure this out.

Ben

Thanks a lot Steve!

Ben

"Steve Yandl" wrote:

Ben,

It would be a bit more complex to count instances of different colors but
can be done. I'll see if I can find a little time to work on it in the next
couple of days.

Steve


"Ben in CA" wrote in message
...
Hi Steve,

That's phenomenal! The first part is exactly what I wanted.

As for the color counting - how could this be setup so that it lists how
many green, how many blue, how many red, etc.

So that I could put
=CountColorRed'Items'!(F4:M137)
=CountColorBlue'Items'!(F4:M137)
=CountColorGreen'Items'!(F4:M137)
=CountColorOrange'Items'!(F4:M137)
=CountColorYellow'Items'!(F4:M137)
=CountColorPurple'Items'!(F4:M137)
=CountColorGrey'Items'!(F4:M137)

Is there a way that these can be combined?

Thank you for your time!

Ben

"Steve Yandl" wrote:

Ben,

You could place the following two functions in a module (same place you
would typically store a macro. Then, you could use the functions in your
workbook the same as you use Excel's fundtions. For example, if you
wanted
a count of all the strings separated by commas found in A1:D50 and a
count
of all the unique font colors appearing in the same range,
=ModelCount(a1:d50)
would give the number of strings and
=ColorCount(a1:d50)
would give the count of how many different colors were used in the same
range.

'---------------------------------------------------

Function ModelCount(myRange As Range) As Integer
Dim rngCell As Range
Dim intCount As Integer

intCount = 0
For Each rngCell In myRange
If Len(rngCell.Text 0) Then
intCount = intCount + UBound(Split(rngCell.Text, ",")) + 1
End If
Next rngCell

ModelCount = intCount

End Function



Function ColorCount(theRange As Range) As Long
Dim rgnCell As Range
Dim txtColor As Variant

Set clrDict = CreateObject("Scripting.Dictionary")

For Each rngCell In theRange
If Len(rngCell.Text 0) Then
For x = 1 To Len(rngCell.Text)
txtColor = rngCell.Characters(x, x).Font.Color
If Len(txtColor) 0 Then
If Not clrDict.Exists(txtColor) Then
clrDict.Add txtColor, txtColor
End If
End If
Next x
End If
Next rngCell

ColorCount = clrDict.Count

Set clrDict = Nothing

End Function


'--------------------------------------------------

Steve Yandl



"Ben in CA" wrote in message
...
I haven't got this figured out yet.

I'd be open to using a complex macro if required - if anyone has any
ideas.

Thanks,

Ben

"Ben in CA" wrote:

Hi,

A user has presented a challenge that I'm wondering if it's possible.

The spreadsheet has columns of cells each containing several model
numbers,
and the values are separated by commas and spaces in each cell. (eg.
8890,
x3340, mx750)

To make things more confusing, each model is colored differently
depending
on the distributor, and there are several different colors in some
cells.
In
total, about six different colors are used (green, red, blue orange,
gray,
purple).

Is there a way I can create a "SUM count" based on how many individual
strings are in a range of cells?

How about how many strings exist of each color for a range?

Am I asking the impossible?

I know there's some options for sorting by color, etc. in Excel 2007,
I
don't know if that helps, though.
(http://msdn.microsoft.com/en-us/library/cc952296.aspx)

I have Excel 2007 and older versions if that helps

Thanks to everyone who tries to figure this out.

Ben