vba code to copy unique data, transpose it and get count of the unique data

[breilly00]

I have a schedule of games for a sports league in sheet1 with dates in column “A” and remaining schedule data in the other columns. I need to take the dates and find just the unique dates and count the unique dates and paste them into a different sheet. The dates need to be transposed into column headers and the number of unique dates is needed to format the borders of where the dates were pasted. Also, the number of dates will change as new seasons are played.

I can copy the entire date column to a temporary sheet and use the range.remove duplicates to get the unique dates, get the number of unique dates by Cells count function and then using the range command by selecting the first cell and the count of unique dates to paste the dates in the new sheet with a transpose of true.

Is there a way that I can do this without the ‘gerry-rigging’ of using a temporary sheet. I need to take unique dates from a column of dates move them to rows in a new sheet and get the count to use when I format the columns.

 

[Fluff]

How about

Sub breilly00()
   Dim Cl As Range
   
   With CreateObject("scripting.dictionary")
      For Each Cl In Range("A2", Range("A" & Rows.Count).End(xlUp))
         .Item(Cl.Value) = Empty
      Next Cl
      Sheets("Sheet6").Range("B1").Resize(, .Count).Value2 = .Keys
   End With
End Sub

 

[breilly00]

wow, that dictionary is something. However, my results are off.

My dates in column A are from 5/7/2019 repeating to 6/27/2019 with 5 rows for each of the individual dates (like below)

5/7/2019

5/7/2019

5/7/2019

5/7/2019

5/7/2019

5/14/2019

5/14/2019

5/14/2019

5/14/2019

5/14/2019

<colgroup><col width="72" style="width:54pt"> </colgroup><tbody>
</tbody>

the results that I get back are

5/7/19

5/8/19

5/9/19

5/10/19

5/11/19

5/12/19

5/13/19

5/14/19

5/15/19

5/16/19

5/17/19

<tbody>
</tbody>

I need the results to be 5/7/19, 5/14/19, etc. until the last date. Tried changing the macro around that you gave me and things only got worse.
Will you provide me with direction that I need to take?

In the mean time, I am going to look at that 'scripting dictionary' object to learn what it is doing. Seems like a great tool.

 

[Fluff]

With data like

Excel 2013/2016
	A
2	07/05/2019
3	07/05/2019
4	07/05/2019
5	07/05/2019
6	07/05/2019
7	14/05/2019
8	14/05/2019
9	14/05/2019
10	14/05/2019
11	14/05/2019
12	21/05/2019
13	21/05/2019
14	21/05/2019
15	21/05/2019
16	21/05/2019
17	28/05/2019
18	28/05/2019
19	28/05/2019
20	28/05/2019
21	28/05/2019
Dates

I get

Excel 2013/2016
	A	B	C	D	E
1		07/05/2019	14/05/2019	21/05/2019	28/05/2019
Sheet6

Check that those other dates don't appear in column A

 

[breilly00]

Sticking my tail between my legs.

Now I run it again, 3 times, and the correct dates show up. Problem must have been "PIBCAKB" ------- Problem Is Between Chair And Key Board

Fluff, UR the greatest. My hat is off to you for the quick and ACCURATE response to my 'newbee' question.

 

[Fluff]

You're welcome & thanks for the feedback