lookup value and fill with the one above it

[dgrosen]

Hi all. I need help with the following:
I have a calendar of events and need to generate a list from it. I need to lookup the value on the calendar and populate the table with the date above it.
<style type="text/css">
.tg {border-collapse:collapse;border-spacing:0;}
.tg td{font-family:Arial, sans-serif;font-size:14px;padding:10px 5px;border-style:solid;border-width:1px;overflow:hidden;word-break:normal;}
.tg th{font-family:Arial, sans-serif;font-size:14px;font-weight:normal;padding:10px 5px;border-style:solid;border-width:1px;overflow:hidden;word-break:normal;}
</style>
<table class="tg">
<tr>
<th class="tg-031e">1</th>
<th class="tg-031e">2</th>
<th class="tg-031e">3</th>
<th class="tg-031e">4</th>
<th class="tg-031e">5</th>
<th class="tg-031e">6</th>
<th class="tg-031e">7</th>
</tr>
<tr>
<td class="tg-031e">cm1</td>
<td class="tg-031e">cm1</td>
<td class="tg-031e">cm2</td>
<td class="tg-031e">cm2</td>
<td class="tg-031e">ab1</td>
<td class="tg-031e">ab1</td>
<td class="tg-031e">ab1</td>
</tr>
<tr>
<td class="tg-031e">8</td>
<td class="tg-031e">9</td>
<td class="tg-031e">10</td>
<td class="tg-031e">11</td>
<td class="tg-031e">12</td>
<td class="tg-031e">13</td>
<td class="tg-031e">14</td>
</tr>
<tr>
<td class="tg-031e">ab1</td>
<td class="tg-031e">cm1</td>
<td class="tg-031e">cm1</td>
<td class="tg-031e">cm2</td>
<td class="tg-031e">cm2</td>
<td class="tg-031e">ab1</td>
<td class="tg-031e"></td>
</tr>
</table>

I need to populate as follows
cm1 1
cm1 2
cm1 9
cm1 10
cm2 3
cm2 4
cm2 11
cm2 12 ...

Thanks in advance

 

[chicagocomputerclasses]

Do you provide the lookup values (event names to lookup) or does it have to generate the values from the calendar?
Do you always have 7 columns (7 days per row) in your calendar?

 

[dgrosen]

Thanks for you reply.

Yes, is always 7 columns one for each day of the week. The numbers represent the dates. Underneath each date there is a class I have to teach.
<style>
.demo {
border:1px solid #C0C0C0;
border-collapse:collapse;
padding:5px;
}
.demo th {
border:1px solid #C0C0C0;
padding:5px;
background:#F0F0F0;
}
.demo td {
border:1px solid #C0C0C0;
padding:5px;
}
</style>
<table class="demo">
<caption>Table 1</caption>
<thead>
<tr>
<th>MON</th>
<th>TUE</th>
<th>WED</th>
<th>THU</th>
<th>FRI</th>
<th>SAT</th>
<th>SUN</th>
</tr> </thead> <tbody> <tr>
<td> 1</td>
<td> 2</td>
<td> 3</td>
<td> 4</td>
<td> 5</td>
<td> 6</td>
<td> 7</td>
</tr>
<tr>
<td> CM1</td>
<td> CM2</td>
<td> CM3</td>
<td> CM4</td>
<td> AB1</td>
<td> AB2</td>
<td> AB3</td>
</tr>
<tr>
<td> 8</td>
<td> 9</td>
<td>10</td>
<td>11 </td>
<td>12 </td>
<td>13 </td>
<td> 14</td>
</tr>
<tr>
<td>AB4 </td>
<td> CM5</td>
<td> CM6</td>
<td> CM7</td>
<td>CM8 </td>
<td> AB5</td>
<td> </td>
</tr>
</tbody>
</table>

In another sheet I have the class names in one column and I want to autopopulate with the dates right next to it. It would be nice if the formula/code could look up for the class names and sort them automatically but that can be more complicated. I will be very happy just by autopopulating the dates

CM1
CM2
CM3
CM4
CM5
CM6
CM7
CM8
AB1
AB2
AB3
AB4
AB5

Thanks so very much

 

[chicagocomputerclasses]

Not sure if you can do this with a formula, but here is a macro that will do this for you.
Simply change the range in this macro to your calendar range. Do not include headers. So if we had to assume your sample table in your post above was starting in cell A1, the range would be A2:G5.
Once you run the macro it will output the results 1 row below your calendar.

Sub CalendarResults()
Dim myCalendarRange As Range


' set the range to your calendar range
Set myCalendarRange = Range("A2:G5")
cls = myCalendarRange.Columns.Count
rws = myCalendarRange.Rows.Count


d = myCalendarRange.Column
r = myCalendarRange.Row
temp = ""


For v = myCalendarRange.Row + 1 To myCalendarRange.Row + rws - 1 Step 2
temp = temp & ";" & Join(Application.Transpose(Application.Transpose(Range(Cells(v, myCalendarRange.Column), Cells(v, myCalendarRange.Column + cls - 1)))), ";")
Next v


myarr = Split(temp, ";")
temp2 = myarr(0)
For Each word In myarr
        If InStr(";" & temp2 & ";", ";" & word & ";") > 0 Then
        
        Else
        temp2 = temp2 & ";" & word
        End If
Next word
temp2 = Right(temp2, Len(temp2) - 1)
mynextarr = Split(temp2, ";")


wrrow = myCalendarRange.Row + rws + 1
For Each uword In mynextarr


    For i = myCalendarRange.Row + 1 To myCalendarRange.Row + rws - 1 Step 2
        For x = myCalendarRange.Column To myCalendarRange.Column + cls - 1
            If uword = Cells(i, x) Then
              Cells(wrrow, myCalendarRange.Column) = uword
              Cells(wrrow, myCalendarRange.Column + 1) = Cells(i, x).Offset(-1, 0)
              wrrow = wrrow + 1
            End If
        Next x
    
    Next i
Next uword


End Sub

 

[chicagocomputerclasses]

As an alternative you can use this macro. In this scenario you will first highlight the range, range being the same as I mentioned in my previous post, so no headers. Then just run the macro.

Sub CalendarResults()
Dim myCalendarRange As Range


Set myCalendarRange = Selection
cls = myCalendarRange.Columns.Count
rws = myCalendarRange.Rows.Count


d = myCalendarRange.Column
r = myCalendarRange.Row
temp = ""


For v = myCalendarRange.Row + 1 To myCalendarRange.Row + rws - 1 Step 2
temp = temp & ";" & Join(Application.Transpose(Application.Transpose(Range(Cells(v, myCalendarRange.Column), Cells(v, myCalendarRange.Column + cls - 1)))), ";")
Next v


myarr = Split(temp, ";")
temp2 = myarr(0)
For Each word In myarr
        If InStr(";" & temp2 & ";", ";" & word & ";") > 0 Then
        
        Else
        temp2 = temp2 & ";" & word
        End If
Next word
temp2 = Right(temp2, Len(temp2) - 1)
mynextarr = Split(temp2, ";")


wrrow = myCalendarRange.Row + rws + 1
For Each uword In mynextarr


    For i = myCalendarRange.Row + 1 To myCalendarRange.Row + rws - 1 Step 2
        For x = myCalendarRange.Column To myCalendarRange.Column + cls - 1
            If uword = Cells(i, x) Then
              Cells(wrrow, myCalendarRange.Column) = uword
              Cells(wrrow, myCalendarRange.Column + 1) = Cells(i, x).Offset(-1, 0)
              wrrow = wrrow + 1
            End If
        Next x
    
    Next i
Next uword


End Sub