Counting and Then Notating a Color Pattern Within a Row

[AustinMaly]

After much digging, I don't know if what I'm looking for is possible or not. Its a reach for sure.

I am working on a knitting project, and it would be really helpful if I could get Excel to count the patterns in each for me. So far I have cells being conditionally formatted whether they are blank or if they contain an "X".

What i would like to know is: Can Excel count the Pattern and then notate the number of cells that have that specific color? Or is there an easier way to Flash Fill the pattern?

This is what i have as a base for my pattern:

Here are a couple options of what I'm HOPING achieve. I have tried to Flash Fill / Auto-Fill many of my rows as depicted for "Possible Option 3", but there are so many rows, and the patterns get kind of crazy, that its going to take me hours just to do this.

I hope this makes sense!

 

[ouvay]

Hi,
I'm sure this is possible .. but for ease of pain would you be willing to share your excel? feel free to simplify it or remove the conditional formatting.

 

[AustinMaly]

Hi,
I'm sure this is possible .. but for ease of pain would you be willing to share your excel? feel free to simplify it or remove the conditional formatting.

Would sharing it via Google Sheets work okay? If not, I'll figures something out.

 

[ouvay]

Fast & Simple File Transfers

Free transfer of up to 6 GB of photos, videos and documents. Send large files via email or a link to share. No registration, no ads, just simple file sharing!

filetransfer.io

should be fairly easy

 

[ouvay]

Fast & Simple File Transfers

Free transfer of up to 6 GB of photos, videos and documents. Send large files via email or a link to share. No registration, no ads, just simple file sharing!

filetransfer.io

should be fairly easy

Works the same as

WeTransfer - Send Large Files & Share Photos Online - Up to 2GB Free

WeTransfer is the simplest way to send your files around the world

wetransfer.com

 

[Peter_SSs]

Would sharing it via Google Sheets work okay?

The best way is to use XL2BB as I have done below. That way you do not eliminate possible helpers who choose not to download files or are prevented from doing so by workplace restrictions and your data (& expected results) are immediately visible directly in your post, similar to posting images like you did but with the advantage we can copy from it.

I did not really understand your "possible options" image as the results did not seem to correlate with the data. However, would you consider a vba approach?
This is my interpretation of your request.

Sub CountBlocks()
  Dim rRow As Range, rA As Range, rConst As Range
  Dim lr As Long, i As Long, FirstResultCol As Long
  
  Const FirstRow As Long = 3              '<- Adjust to suit
  Const ColumnsToCheck As String = "A:T"  '<- Adjust to suit
  
  lr = Range(ColumnsToCheck).Find(What:="*", LookIn:=xlValues, SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
  FirstResultCol = Range(ColumnsToCheck).Column + Range(ColumnsToCheck).Columns.Count + 1
  Application.ScreenUpdating = False
  For Each rRow In Range(Replace(ColumnsToCheck, ":", FirstRow & ":") & lr).Rows
    i = 0
    Set rConst = Nothing
    On Error Resume Next
    Set rConst = rRow.SpecialCells(xlConstants)
    On Error GoTo 0
    If Not rConst Is Nothing Then
      For Each rA In rConst.Areas
        Cells(rRow.Row, FirstResultCol + i).Value = rA.Count
        i = i + 1
      Next rA
    End If
  Next rRow
  Application.ScreenUpdating = True
End Sub

My original sample data is in columns A:T and the code has produced the results shown in columns V onwards.

AustinMaly.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF
1
2
3	x	x	x	x	x	x	x				x	x	x		x	x	x		x	x		7	3	3	2
4
5				x	x	x					x	x	x		x	x	x					3	3	3
6		x		x		x		x		x		x		x		x		x		x		1	1	1	1	1	1	1	1	1	1
7
Patterns

 

[AustinMaly]

I did not really understand your "possible options" image as the results did not seem to correlate with the data. However, would you consider a vba approach?
This is my interpretation of your request.

I think that this is really close. I will have to work on using XLBB2 to upload a snippet. I think the option

Thank you for your reply. I will work on installing the XL2BB.

I did run this VBA and it is actually pretty close to what I'm looking for! Thank you so much!

I think the interpretation didn't make sense, because I was also hoping that it would count out the blank cell also.

 

[Peter_SSs]

... I was also hoping that it would count out the blank cell also.

Ah, now I understand those options better.

See if this version is closer.

Sub CountBlocks_v2()
  Dim rRow As Range, rA As Range, rConst As Range, rBlanks As Range
  Dim lr As Long, i As Long, FirstResultCol As Long
  Dim bFirstIsBlank As Boolean
  
  Const FirstRow As Long = 3              '<- Adjust to suit
  Const ColumnsToCheck As String = "A:T"  '<- Adjust to suit
  
  lr = Range(ColumnsToCheck).Find(What:="*", LookIn:=xlValues, SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
  FirstResultCol = Range(ColumnsToCheck).Column + Range(ColumnsToCheck).Columns.Count + 1
  Application.ScreenUpdating = False
  For Each rRow In Range(Replace(ColumnsToCheck, ":", FirstRow & ":") & lr).Rows
    bFirstIsBlank = IsEmpty(rRow.Cells(1).Value)
    Set rConst = Nothing
    Set rBlanks = Nothing
    On Error Resume Next
    Set rConst = rRow.SpecialCells(xlConstants)
    Set rBlanks = rRow.SpecialCells(xlBlanks)
    On Error GoTo 0
    If Not rConst Is Nothing Then
      i = 0
      For Each rA In rConst.Areas
        Cells(rRow.Row, FirstResultCol + i - bFirstIsBlank).Value = rA.Count
        i = i + 2
      Next rA
    End If
    If Not rBlanks Is Nothing Then
      i = 1
      For Each rA In rBlanks.Areas
        Cells(rRow.Row, FirstResultCol + i + bFirstIsBlank).Value = rA.Count
        i = i + 2
      Next rA
    End If
  Next rRow
  Application.ScreenUpdating = True
End Sub

Sample data and results

AustinMaly.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH	AI	AJ	AK	AL	AM	AN	AO	AP
1
2
3	x	x	x	x	x	x	x				x	x	x		x	x	x		x	x		7	3	3	1	3	1	2
4																						20
5				x	x	x					x	x	x		x	x	x					3	3	4	3	1	3	3
6		x		x		x		x		x		x		x		x		x		x		1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1
7	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x		20
Patterns

 

[AustinMaly]

This worked! You are a gosh darn genius! Thank you so much! VBA has been my weakness!

 

[Peter_SSs]

You're welcome. Thanks for the follow-up.