Returning a list with constants based on adjacent cell

Hey guys, I have some data and would like to make a list with spacers/constants at certain intervals, determined by what is in the cells adjacent to the data. Best way to see what I want is to open the spreadsheet I think. If anyone has any ideas its much appreciated. Thanks!

List problem.xlsx

Maybe I should have posted this in the Formulas and Functions forum but I know I'll get yelled at if I post it again there.....

Do you want the break after every color (Column C)? How do I determine "Some Repeating Text?: Is it the first row? Is it all the text found until the first change in color?

Thank you to both of your for your replies.

dflak, I want the break after every color, as determined by the strings in column C ("Yellow","Brown", etc.), not the cell fill color. "Some", "Repeating", and "Text" are just three cells which I want to repeat after each break.

• Likely, two of them will be empty strings and one may have some text in it. I don't mind it you reference those cells or hardcode them into the formula, I can figure it out either way.
• Assume for my purposes that it will always be exactly three cells that I want to repeat. No more, no less.

FDibbins, my sample is very close. The actual data is much longer, but it's all just text strings. Some further clarifications:

• All of the values in column B ("Banana","SchoolBus","Lemons", etc.) are unique
• The values in column C repeat often
• Ideally the three cells in column D are not necessarily unique, to accommodate blank cells. But if you can write a formula that doesn't fit that, I can just use spaces to make it work (i.e. " "," "," ")

Let me know if I can clarify anything else and thanks again!

Try this: a lot of hard coding in it.

Sub CopyData()
Dim cl As Range
Dim sh As Worksheet
Dim MyRange As String
Dim RowNum As Long
Dim CurColor As String


MyRange = ("$C$5:$C$15")
RowNum = 5
Set sh = Sheets("Sheet1")
CurColor = ""


For Each cl In sh.Range(MyRange)
    If cl.Value = CurColor Or RowNum = 5 Then
        ' Repeat the item
        sh.Cells(RowNum, 6) = cl.Offset(0, -1).Value
        CurColor = cl.Value
        RowNum = RowNum + 1
    Else
        sh.Range("$D$5:$D$7").Copy sh.Cells(RowNum, 6)
        RowNum = RowNum + 3
        sh.Cells(RowNum, 6) = cl.Offset(0, -1).Value
        RowNum = RowNum + 1
        CurColor = cl.Value
    End If
Next

Hi dflak, I was expecting a formula of some sort. Can you walk me through how to put what you wrote to work - I'm not familiar with VBA at all.

I hope this helps:

Option Explicit ' This means I must declare my variables. It's a good idea to start every macro this way.

Sub CopyData()
Dim cl As Range         ' This is a "pointer" it points to one cell after another in a range
Dim sh As Worksheet     ' I'm defining this as an alias for the worksheet name so I don't have to type a lot.
Dim MyRange As String   ' This is the range of data to inspect
Dim RowNum As Long      ' A row counter
Dim CurColor As String  ' A string to hold the current color name so the program can "remember" it.

' Initalize variables.
MyRange = ("$C$5:$C$15")
RowNum = 5
Set sh = Sheets("Sheet1")   ' Now I don't have to type Sheets("Sheet1") all the time.
CurColor = ""               ' It probably defualts to this, but I like to set everythign explicitly

' I'm going to slide the "pointer" down the range one cell at a time.
For Each cl In sh.Range(MyRange)
    ' If I'm on the same color or if it's the first color
    If cl.Value = CurColor Or RowNum = 5 Then
        ' Put the item (zero rows up or down and one row left of the pointer into the cell
        sh.Cells(RowNum, 6) = cl.Offset(0, -1).Value  ' We use .Value because cl is actually a cell location
        ' increcment the row counter
        RowNum = RowNum + 1
    Else ' There is a change in color
        ' Copy in the boilerplate text
        sh.Range("$D$5:$D$7").Copy sh.Cells(RowNum, 6)
        ' increment the row counter by three since we copied in 3 rows of text
        RowNum = RowNum + 3
        ' Put the item into the cell
        sh.Cells(RowNum, 6) = cl.Offset(0, -1).Value
        ' Increment the row counter
        RowNum = RowNum + 1
    End If
    ' Remember the color - in the original code I had this in both parts of the if statement
    CurColor = cl.Value
Next

End Sub

Ok just for anyone else wondering I figured out how to do it with a formula. As based on the original spreadsheet I uploaded with results in H5:H27...

Set the first cell (H5) equal to the first result. The set all the rest to

{=IF(H5=$D$7,INDEX($B$5:$B$15, MATCH(0,COUNTIF($H$4:H5, $B$5:$B$15), 0)),IF(H5=$D$6,$D$7,IF(H5=$D$5,$D$6,IF(INDEX($C$5:$C$15,MATCH(INDEX($B$5:$B$15, MATCH(0,COUNTIF($H$4:H5, $B$5:$B$15), 0)),$B$5:$B$15,0))=IFNA(INDEX($C$5:$C$15,MATCH(H5,$B$5:$B$15,0)),0),IFNA(INDEX($B$5:$B$15, MATCH(0,COUNTIF($H$4:H5, $B$5:$B$15), 0)),""),$D$5))))}

It's just the kind of convoluted unnecessary avoidance of VBA that I love!

I usually prefer a non-VBA solution myself. Even when I use VBA, I try to get "basic" Excel to do most of the heavy lifting.