VBA Help

grover1973 · Jun 11, 2015

I need a vba for the below:

I have user names in one column and dates in another, user names repeat. This is dynamic list and user names might keep increasing

John 11/27/2014
Ash 11/28/2014
John 11/28/2014
Joey 12/01/2014
John 11/29/2014
Ash 12/15/2914

i want to change the table as below
John 11/27/2014 11/28/2014 11/29/2014
Ash 11/28/2014 12/15/2914
Joey 12/01/2014

Thanks for your help

JoeMo · Jun 11, 2015

This assumes your data are arranged in cols A & B as shown below. The data are reconfigured starting in D1.
Sheet1 <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:75px;" /><col style="width:64px;" /><col style="width:44px;" /><col style="width:225px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td><td >C</td><td >D</td><td >E</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td >Name</td><td >Date</td><td > </td><td >Name</td><td >Date(s)</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td >John</td><td style="text-align:right; ">11/27/2014</td><td > </td><td >John</td><td > 11/27/2014 11/28/2014 11/29/2014</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td >Ash</td><td style="text-align:right; ">11/28/2014</td><td > </td><td >Ash</td><td > 11/28/2014 12/15/2914</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td >John</td><td style="text-align:right; ">11/28/2014</td><td > </td><td >Joey</td><td style="text-align:right; "> 12/1/2014</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td >Joey</td><td style="text-align:right; ">12/1/2014</td><td > </td><td > </td><td > </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >6</td><td >John</td><td style="text-align:right; ">11/29/2014</td><td > </td><td > </td><td > </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >7</td><td >Ash</td><td style="text-align:right; ">12/15/2914</td><td > </td><td > </td><td > </td></tr></table> Excel tables to the web >> <a style ="font-family:Arial; font-size:9pt; color:#fcf507; background-color:#800040; font-weight:bold;" href="http://www.excel-jeanie-html.de/index.php?f=1" target="_blank"> Excel Jeanie HTML 4 </a>

Code:

Sub grover()
Dim R As Range, Vin As Variant, i As Long, j As Long, Dts As String
Set R = Range("A1:B" & Cells(Rows.Count, "B").End(xlUp).Row)
Vin = R.Value
Application.ScreenUpdating = False
R.Columns(1).AdvancedFilter Action:=xlFilterCopy, copytorange:=Range("D1"), unique:=True
Range("E1").Value = "Date(s)"
For i = 2 To Range("D" & Rows.Count).End(xlUp).Row
       For j = 2 To UBound(Vin, 1)
              If Range("D" & i).Value = Vin(j, 1) Then Dts = Dts & " " & Vin(j, 2)
       Next j
       Range("E" & i).Value = Dts
       Dts = ""
Next i
Columns("D:E").AutoFit
Application.ScreenUpdating = True
End Sub

grover1973 · Jun 11, 2015

JoeMo said:
This assumes your data are arranged in cols A & B as shown below. The data are reconfigured starting in D1.
Sheet1

* A B C D E
1 Name Date * Name Date(s)
2 John 11/27/2014 * John 11/27/2014 11/28/2014 11/29/2014
3 Ash 11/28/2014 * Ash 11/28/2014 12/15/2914
4 John 11/28/2014 * Joey 12/1/2014
5 Joey 12/1/2014 * * *
6 John 11/29/2014 * * *
7 Ash 12/15/2914 * * *

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

Excel tables to the web >> Excel Jeanie HTML 4

Code:

Sub grover() Dim R As Range, Vin As Variant, i As Long, j As Long, Dts As String Set R = Range("A1:B" & Cells(Rows.Count, "B").End(xlUp).Row) Vin = R.Value Application.ScreenUpdating = False R.Columns(1).AdvancedFilter Action:=xlFilterCopy, copytorange:=Range("D1"), unique:=True Range("E1").Value = "Date(s)" For i = 2 To Range("D" & Rows.Count).End(xlUp).Row For j = 2 To UBound(Vin, 1) If Range("D" & i).Value = Vin(j, 1) Then Dts = Dts & " " & Vin(j, 2) Next j Range("E" & i).Value = Dts Dts = "" Next i Columns("D:E").AutoFit Application.ScreenUpdating = True End Sub

Thanks JoeMo.......I actually needed the dates in different columns not in one.
Your help is greatly appreciated

grover1973 · Jun 12, 2015

grover1973 said:
Thanks JoeMo.......I actually needed the dates in different columns not in one.
Your help is greatly appreciated

Joe...........It did not take long for you to get the first VBA done, I am pretty sure the minor adjustment that i need will be a piece of cake for you. I wish i knew how to write macros.

I am thankful to you to helping me out.

Hope you can come up with changes. (One date in one column, next date in next column)

Regards,

JoeMo · Jun 12, 2015

grover1973 said:
Thanks JoeMo.......I actually needed the dates in different columns not in one.
Your help is greatly appreciated

That's the kind of information that should be included in your OP - saves time for both of us.

Here's a revision:

Code:

Sub grover()
Dim R As Range, Vin As Variant, i As Long, j As Long, Dts As String, k As Long
Set R = Range("A1:B" & Cells(Rows.Count, "B").End(xlUp).Row)
Vin = R.Value
Application.ScreenUpdating = False
R.Columns(1).AdvancedFilter Action:=xlFilterCopy, copytorange:=Range("D1"), unique:=True
Range("E1").Value = "Date(s)"
For i = 2 To Range("D" & Rows.Count).End(xlUp).Row
       For j = 2 To UBound(Vin, 1)
              If Range("D" & i).Value = Vin(j, 1) Then Dts = Dts & " " & Vin(j, 2)
       Next j
       For k = LBound(Split(Dts, " ")) To UBound(Split(Dts, " ")) - 1
        Range("E" & i).Offset(0, k).Value = Split(Dts, " ")(k + 1)
    Next k
       Dts = ""
Next i
Range("D1").CurrentRegion.EntireColumn.AutoFit
Application.ScreenUpdating = True
End Sub

grover1973 · Jun 12, 2015

JoeMo said:

That's the kind of information that should be included in your OP - saves time for both of us.

Here's a revision:

Code:

Sub grover()
Dim R As Range, Vin As Variant, i As Long, j As Long, Dts As String, k As Long
Set R = Range("A1:B" & Cells(Rows.Count, "B").End(xlUp).Row)
Vin = R.Value
Application.ScreenUpdating = False
R.Columns(1).AdvancedFilter Action:=xlFilterCopy, copytorange:=Range("D1"), unique:=True
Range("E1").Value = "Date(s)"
For i = 2 To Range("D" & Rows.Count).End(xlUp).Row
       For j = 2 To UBound(Vin, 1)
              If Range("D" & i).Value = Vin(j, 1) Then Dts = Dts & " " & Vin(j, 2)
       Next j
       For k = LBound(Split(Dts, " ")) To UBound(Split(Dts, " ")) - 1
        Range("E" & i).Offset(0, k).Value = Split(Dts, " ")(k + 1)
    Next k
       Dts = ""
Next i
Range("D1").CurrentRegion.EntireColumn.AutoFit
Application.ScreenUpdating = True
End Sub

My apologies, i should have mentioned it initially.

Many thanks for helping me out, appreciate it!

JoeMo · Jun 12, 2015

grover1973 said:
My apologies, i should have mentioned it initially.

Many thanks for helping me out, appreciate it!

You are welcome - thanks for the reply.

grover1973 · Jun 16, 2015

JoeMo said:
You are welcome - thanks for the reply.

Joe...........Sorry but a change just came up and I was thinking if you can help me please.

Your VBA worked perfect but I am now required to arranbge that based on Months.
This is how it is now

Employee

Date(s)

Absence

Occurrence

John

11/27/2014

11/28/2014

3/16/2015

3/17/2015

3/18/2015

2

5

Joey

11/12/2014

11/13/2014

12/24/2014

5/25/2015

3

4

William

11/14/2014

2/2/2015

2

David

11/28/2014

1/30/2015

2

Angela

11/19/2014

2/23/2015

3/18/2015

3/27/2015

4

Eva

11/17/2014

11/18/2014

12/29/2014

12/30/2014

1/2/2015

3

5

John

11/21/2014

12/1/2014

1/13/2015

1/14/2015

3

4

Joey

11/3/2014

11/4/2014

1/30/2015

3/6/2015

4/8/2015

5/1/2015

5

6

William

11/5/2014

1/14/2015

2/3/2015

4/6/2015

4/7/2015

5/22/2015

5

6

David

11/17/2014

11/18/2014

11/19/2014

11/20/2014

1/12/2015

3/2/2015

3/3/2015

3/9/2015

3/10/2015

3/11/2015

4

10

What I need is the absences and Occurences based on Months

If you can please help me here?

Regards,

VBA Help

grover1973

New Member

Excel Facts

JoeMo

MrExcel MVP

grover1973

New Member

grover1973

New Member

JoeMo

MrExcel MVP

grover1973

New Member

JoeMo

MrExcel MVP

grover1973

New Member

Similar threads

Forum statistics

Share this page

*	A	B	C	D	E
1	Name	Date	*	Name	Date(s)
2	John	11/27/2014	*	John	11/27/2014 11/28/2014 11/29/2014
3	Ash	11/28/2014	*	Ash	11/28/2014 12/15/2914
4	John	11/28/2014	*	Joey	12/1/2014
5	Joey	12/1/2014	*	*	*
6	John	11/29/2014	*	*	*
7	Ash	12/15/2914	*	*	*

VBA Help

New Member

Excel Facts

MrExcel MVP

New Member

New Member

MrExcel MVP

New Member

MrExcel MVP

New Member

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