VBA - Grab Unique Values and Paste them x number of times

stwp86 · Dec 3, 2013

Hi Everyone . . .

Not really an Excel VBA expert, so I thought I would come try my luck here. Essentially, what I am trying to accomplish is this:

I have two columns of data that I would like to generate, a date range and an hour range. I have the following range of data in Sheet 1 Column B:

Date

11/11/2013

11/13/2013

11/12/2013

11/14/2013

11/12/2013

11/15/2013

11/11/2013

What I need to do is extract the unique values, and then paste them in Sheet 2 Column B 24 times each. In Column A of Sheet 2, I would like to Paste 0-23 (a set of 0-23 for every unique date). This is the desired output:

Hour	Date
0	11/11/2013
1	11/11/2013
2	11/11/2013
3	11/11/2013
4	11/11/2013
5	11/11/2013
6	11/11/2013
7	11/11/2013
8	11/11/2013
9	11/11/2013
10	11/11/2013
11	11/11/2013
12	11/11/2013
13	11/11/2013
14	11/11/2013
15	11/11/2013
16	11/11/2013
17	11/11/2013
18	11/11/2013
19	11/11/2013
20	11/11/2013
21	11/11/2013
22	11/11/2013
23	11/11/2013
0	11/12/2013
1	11/12/2013
...	...

Any thoughts on how I can make this happen?

Thanks for the help in advance!

Travis

Fazza · Dec 3, 2013

hi,

Here is a non-formula & non-code approach. Give the dates data (incl header) a normal defined name, say "Dates". Make a list called "Hours" with header Hours & liast 0, 1, 2, 3, etc

Now make a query table with SQL: "SELECT DISTINCT Dates.Date, Hours.Hour FROM Dates, Hours"

regards

stwp86 · Dec 3, 2013

Fazza said:
hi,

Here is a non-formula & non-code approach. Give the dates data (incl header) a normal defined name, say "Dates". Make a list called "Hours" with header Hours & liast 0, 1, 2, 3, etc

Now make a query table with SQL: "SELECT DISTINCT Dates.Date, Hours.Hour FROM Dates, Hours"

regards

Thanks, but that is not quite what I am trying to do here. . .

this is what I have thus far:

Dim uniqueDates As Integer
Dim Lastrow As Long
Lastrow = Range("B" & Rows.Count).End(xlUp).Row

uniqueDates = Evaluate("=SUM(IF(FREQUENCY(MATCH(Sheet1!$B$2:$B$" & Lastrow & ",Sheet1!$B$2:$B$" & Lastrow & ",0),MATCH(Sheet1!$B$2:$B$" & Lastrow & ",Sheet1!$B$2:$B$" & Lastrow & ",0))>0,1))")
Sheet2.Activate

Dim numberOfDates As Integer
Dim numberOfHours As Integer
Dim numberOfRows As Integer

numberOfRows = 2
numberOfDates = 1

Do While numberOfDates <= uniqueDates
Do While numberOfHours < 23
Range("A" & numberOfRows).Value = numberOfHours
numberOfHours = numberOfHours + 1
numberOfRows = numberOfRows + 1
Loop
numberOfDates = numberOfDates + 1
Loop

But it seems to only write out 0-23 1 time instead of looping through uniqueDates which should be 5 times

apo · Dec 3, 2013

Hi..

Try this..

Code:

Private Sub CommandButton1_Click()
Dim vArr, i As Long
Sheets("Sheet1").Cells(2, 2).Resize(Range("B" & Rows.Count).End(xlUp).Row - 1, 1).Sort [B1], 1
With CreateObject("scripting.dictionary")
            For Each it In Sheets("Sheet1").Columns(2).SpecialCells(2).Offset(1)
                x0 = .Item(it.Value)
            Next
            Z = .keys
        End With
     vArr = Array(0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24)
    For i = LBound(Z) To UBound(Z) - 1
    Sheets("Sheet2").Range("B" & Rows.Count).End(xlUp).Offset(1).Resize(24, 1) = Z(i)
    Sheets("Sheet2").Range("A" & Rows.Count).End(xlUp).Offset(1).Resize(24, 1) = Application.Transpose(vArr)
    Next i
End Sub

<b>Sheet1</b><br /><br /><table border="1" cellspacing="0" cellpadding="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:102px;" /><col style="width:157px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td></tr><tr style="height:20px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td > </td><td style="color:#333333; font-weight:bold; font-family:Verdana; font-size:10pt; ">Date</td></tr><tr style="height:20px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td > </td><td style="color:#333333; font-family:Verdana; font-size:10pt; text-align:left; ">11/11/2013</td></tr><tr style="height:20px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td > </td><td style="color:#333333; font-family:Verdana; font-size:10pt; text-align:left; ">11/11/2013</td></tr><tr style="height:33px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td > </td><td style="color:#333333; font-family:Verdana; font-size:10pt; text-align:left; ">11/13/2013</td></tr><tr style="height:20px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td > </td><td style="color:#333333; font-family:Verdana; font-size:10pt; text-align:left; ">11/12/2013</td></tr><tr style="height:33px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >6</td><td > </td><td style="color:#333333; font-family:Verdana; font-size:10pt; text-align:left; ">11/14/2013</td></tr><tr style="height:33px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >7</td><td > </td><td style="color:#333333; font-family:Verdana; font-size:10pt; text-align:left; ">11/14/2013</td></tr><tr style="height:20px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >8</td><td > </td><td style="color:#333333; font-family:Verdana; font-size:10pt; text-align:left; ">11/12/2013</td></tr><tr style="height:33px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >9</td><td > </td><td style="color:#333333; font-family:Verdana; font-size:10pt; text-align:left; ">11/15/2013</td></tr><tr style="height:20px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >10</td><td > </td><td style="color:#333333; font-family:Verdana; font-size:10pt; text-align:left; ">11/11/2013</td></tr></table> <br />Excel tables to the web - Excel Jeanie Html 4

<b>Sheet2</b><br /><br /><table border="1" cellspacing="0" cellpadding="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:64px;" /><col style="width:114px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td >Hour</td><td >Date</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td style="text-align:right; ">0</td><td style="text-align:right; ">11/11/2013</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td style="text-align:right; ">1</td><td style="text-align:right; ">11/11/2013</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td style="text-align:right; ">2</td><td style="text-align:right; ">11/11/2013</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td style="text-align:right; ">3</td><td style="text-align:right; ">11/11/2013</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >6</td><td style="text-align:right; ">4</td><td style="text-align:right; ">11/11/2013</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >7</td><td style="text-align:right; ">5</td><td style="text-align:right; ">11/11/2013</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >8</td><td style="text-align:right; ">6</td><td style="text-align:right; ">11/11/2013</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >9</td><td style="text-align:right; ">7</td><td style="text-align:right; ">11/11/2013</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >10</td><td style="text-align:right; ">8</td><td style="text-align:right; ">11/11/2013</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >11</td><td style="text-align:right; ">9</td><td style="text-align:right; ">11/11/2013</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >12</td><td style="text-align:right; ">10</td><td style="text-align:right; ">11/11/2013</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >13</td><td style="text-align:right; ">11</td><td style="text-align:right; ">11/11/2013</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >14</td><td style="text-align:right; ">12</td><td style="text-align:right; ">11/11/2013</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >15</td><td style="text-align:right; ">13</td><td style="text-align:right; ">11/11/2013</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >16</td><td style="text-align:right; ">14</td><td style="text-align:right; ">11/11/2013</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >17</td><td style="text-align:right; ">15</td><td style="text-align:right; ">11/11/2013</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >18</td><td style="text-align:right; ">16</td><td style="text-align:right; ">11/11/2013</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >19</td><td style="text-align:right; ">17</td><td style="text-align:right; ">11/11/2013</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >20</td><td style="text-align:right; ">18</td><td style="text-align:right; ">11/11/2013</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >21</td><td style="text-align:right; ">19</td><td style="text-align:right; ">11/11/2013</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >22</td><td style="text-align:right; ">20</td><td style="text-align:right; ">11/11/2013</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >23</td><td style="text-align:right; ">21</td><td style="text-align:right; ">11/11/2013</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >24</td><td style="text-align:right; ">22</td><td style="text-align:right; ">11/11/2013</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >25</td><td style="text-align:right; ">23</td><td style="text-align:right; ">11/11/2013</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >26</td><td style="text-align:right; ">0</td><td style="text-align:right; ">11/12/2013</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >27</td><td style="text-align:right; ">1</td><td style="text-align:right; ">11/12/2013</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >28</td><td style="text-align:right; ">2</td><td style="text-align:right; ">11/12/2013</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >29</td><td style="text-align:right; ">3</td><td style="text-align:right; ">11/12/2013</td></tr></table> <br />Excel tables to the web - Excel Jeanie Html 4

stwp86 · Dec 4, 2013

apo said:
Hi..

Try this..

Code:

Private Sub CommandButton1_Click() Dim vArr, i As Long Sheets("Sheet1").Cells(2, 2).Resize(Range("B" & Rows.Count).End(xlUp).Row - 1, 1).Sort [B1], 1 With CreateObject("scripting.dictionary") For Each it In Sheets("Sheet1").Columns(2).SpecialCells(2).Offset(1) x0 = .Item(it.Value) Next Z = .keys End With vArr = Array(0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24) For i = LBound(Z) To UBound(Z) - 1 Sheets("Sheet2").Range("B" & Rows.Count).End(xlUp).Offset(1).Resize(24, 1) = Z(i) Sheets("Sheet2").Range("A" & Rows.Count).End(xlUp).Offset(1).Resize(24, 1) = Application.Transpose(vArr) Next i End Sub

Sheet1

* A B
1 * Date
2 * 11/11/2013
3 * 11/11/2013
4 * 11/13/2013
5 * 11/12/2013
6 * 11/14/2013
7 * 11/14/2013
8 * 11/12/2013
9 * 11/15/2013
10 * 11/11/2013

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:102px;"><col style="width:157px;"></colgroup><tbody>
</tbody>

Excel tables to the web - Excel Jeanie Html 4

Sheet2

* A B
1 Hour Date
2 0 11/11/2013
3 1 11/11/2013
4 2 11/11/2013
5 3 11/11/2013
6 4 11/11/2013
7 5 11/11/2013
8 6 11/11/2013
9 7 11/11/2013
10 8 11/11/2013
11 9 11/11/2013
12 10 11/11/2013
13 11 11/11/2013
14 12 11/11/2013
15 13 11/11/2013
16 14 11/11/2013
17 15 11/11/2013
18 16 11/11/2013
19 17 11/11/2013
20 18 11/11/2013
21 19 11/11/2013
22 20 11/11/2013
23 21 11/11/2013
24 22 11/11/2013
25 23 11/11/2013
26 0 11/12/2013
27 1 11/12/2013
28 2 11/12/2013
29 3 11/12/2013

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:64px;"><col style="width:114px;"></colgroup><tbody>
</tbody>

Excel tables to the web - Excel Jeanie Html 4

Perfect!

Thanks for the help . . .

Travis

Fazza · Dec 4, 2013

stwp86 said:
Thanks, but that is not quite what I am trying to do here. . .

Please explain how it doesn't do what you want. It seems to me to do exactly what you asked for.

regards

Fazza · Dec 4, 2013

hi, Travis

I see where I was wrong. You wanted a VBA solution.

regards

VBA - Grab Unique Values and Paste them x number of times

stwp86

New Member

Excel Facts

Fazza

MrExcel MVP

stwp86

New Member

apo

Well-known Member

stwp86

New Member

Fazza

MrExcel MVP

Fazza

MrExcel MVP

Similar threads

Forum statistics

Share this page

VBA - Grab Unique Values and Paste them x number of times

stwp86

New Member

Excel Facts

Fazza

MrExcel MVP

stwp86

New Member

apo

Well-known Member

stwp86

New Member

Fazza

MrExcel MVP

Fazza

MrExcel MVP

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock