is there any way to sort my data in excel from

IP address Username
10.10.10.1 A
10.10.10.1 B
10.10.10.1 C
10.10.9.2 D
10.10.9.3 E
10.10.9.3 F

TO

IP address Username
10.10.10.1 A,B,C
10.10.9.2 D
10.10.9.3 E,F

initially,all the IP and username are in difference cells.Finally, if the
usernames are matched in the ip, they will be regroup into one cell as i
mentioned above.

hi
not with the standard excel sort. what you want would require consolidating
data and deleteing row. that would require a macro.
the below macro works on your sample data. backup your data before runing
the macro.
Sub shiftup()
Dim r As Range
Dim ro As Range
Set r = Range("A2")
Do While Not IsEmpty(r)
Set ro = r.Offset(1, 0)
If r.Value = ro.Value Then
r.Offset(0, 1).Value = r.Offset(0, 1).Value &", " & r.Offset(1, 1).Value
r.Offset(1, 0).EntireRow.Delete shift:=xlUp
Set ro = r.Offset(1, 0)
Else
Set r = ro
End If
Loop
End Sub

regards
FSt1

"kokhong" wrote:

is there any way to sort my data in excel from

IP address Username
10.10.10.1 A
10.10.10.1 B
10.10.10.1 C
10.10.9.2 D
10.10.9.3 E
10.10.9.3 F

TO

IP address Username
10.10.10.1 A,B,C
10.10.9.2 D
10.10.9.3 E,F

initially,all the IP and username are in difference cells.Finally, if the
usernames are matched in the ip, they will be regroup into one cell as i
mentioned above.

soli, FSt1, may i know where should i run this script?or can you write down
the step to run this...im really noob to this...thanks you.

"FSt1" wrote:

hi
not with the standard excel sort. what you want would require consolidating
data and deleteing row. that would require a macro.
the below macro works on your sample data. backup your data before runing
the macro.
Sub shiftup()
Dim r As Range
Dim ro As Range
Set r = Range("A2")
Do While Not IsEmpty(r)
Set ro = r.Offset(1, 0)
If r.Value = ro.Value Then
r.Offset(0, 1).Value = r.Offset(0, 1).Value &", " & r.Offset(1, 1).Value
r.Offset(1, 0).EntireRow.Delete shift:=xlUp
Set ro = r.Offset(1, 0)
Else
Set r = ro
End If
Loop
End Sub

regards
FSt1

"kokhong" wrote:

is there any way to sort my data in excel from

IP address Username
10.10.10.1 A
10.10.10.1 B
10.10.10.1 C
10.10.9.2 D
10.10.9.3 E
10.10.9.3 F

TO

IP address Username
10.10.10.1 A,B,C
10.10.9.2 D
10.10.9.3 E,F

initially,all the IP and username are in difference cells.Finally, if the
usernames are matched in the ip, they will be regroup into one cell as i
mentioned above.

hi
sorry for no instuctions.
press Alt +F11. this will bring up the vb editor. in the project window(far
left) expand your project(file). if the code window(far right) in dark, on
the toolbar, click insertmodule. paste the code i wrote for you in to the
molule. close the vb editor.
after backing up your file(just in case), on the menu bar click....
toolsmacromacros;
when the macro dialog comes up, click the macro name then the run buttton.
post back here if problems.

Regards
FSt1

"kokhong" wrote:

soli, FSt1, may i know where should i run this script?or can you write down
the step to run this...im really noob to this...thanks you.

"FSt1" wrote:

hi
not with the standard excel sort. what you want would require consolidating
data and deleteing row. that would require a macro.
the below macro works on your sample data. backup your data before runing
the macro.
Sub shiftup()
Dim r As Range
Dim ro As Range
Set r = Range("A2")
Do While Not IsEmpty(r)
Set ro = r.Offset(1, 0)
If r.Value = ro.Value Then
r.Offset(0, 1).Value = r.Offset(0, 1).Value &", " & r.Offset(1, 1).Value
r.Offset(1, 0).EntireRow.Delete shift:=xlUp
Set ro = r.Offset(1, 0)
Else
Set r = ro
End If
Loop
End Sub

regards
FSt1

"kokhong" wrote:

is there any way to sort my data in excel from

IP address Username
10.10.10.1 A
10.10.10.1 B
10.10.10.1 C
10.10.9.2 D
10.10.9.3 E
10.10.9.3 F

TO

IP address Username
10.10.10.1 A,B,C
10.10.9.2 D
10.10.9.3 E,F

initially,all the IP and username are in difference cells.Finally, if the
usernames are matched in the ip, they will be regroup into one cell as i
mentioned above.

FSt1, your script is running perfectly for my excel file..thanks you
"FSt1" wrote:

hi
sorry for no instuctions.
press Alt +F11. this will bring up the vb editor. in the project window(far
left) expand your project(file). if the code window(far right) in dark, on
the toolbar, click insertmodule. paste the code i wrote for you in to the
molule. close the vb editor.
after backing up your file(just in case), on the menu bar click....
toolsmacromacros;
when the macro dialog comes up, click the macro name then the run buttton.
post back here if problems.

Regards
FSt1

"kokhong" wrote:

soli, FSt1, may i know where should i run this script?or can you write down
the step to run this...im really noob to this...thanks you.

"FSt1" wrote:

hi
not with the standard excel sort. what you want would require consolidating
data and deleteing row. that would require a macro.
the below macro works on your sample data. backup your data before runing
the macro.
Sub shiftup()
Dim r As Range
Dim ro As Range
Set r = Range("A2")
Do While Not IsEmpty(r)
Set ro = r.Offset(1, 0)
If r.Value = ro.Value Then
r.Offset(0, 1).Value = r.Offset(0, 1).Value &", " & r.Offset(1, 1).Value
r.Offset(1, 0).EntireRow.Delete shift:=xlUp
Set ro = r.Offset(1, 0)
Else
Set r = ro
End If
Loop
End Sub

regards
FSt1

"kokhong" wrote:

is there any way to sort my data in excel from

IP address Username
10.10.10.1 A
10.10.10.1 B
10.10.10.1 C
10.10.9.2 D
10.10.9.3 E
10.10.9.3 F

TO

IP address Username
10.10.10.1 A,B,C
10.10.9.2 D
10.10.9.3 E,F

initially,all the IP and username are in difference cells.Finally, if the
usernames are matched in the ip, they will be regroup into one cell as i
mentioned above.

hi
you are welcome. and thanks for the feedback

Regards
FSt1

"kokhong" wrote:


FSt1, your script is running perfectly for my excel file..thanks you
"FSt1" wrote:

hi
sorry for no instuctions.
press Alt +F11. this will bring up the vb editor. in the project window(far
left) expand your project(file). if the code window(far right) in dark, on
the toolbar, click insertmodule. paste the code i wrote for you in to the
molule. close the vb editor.
after backing up your file(just in case), on the menu bar click....
toolsmacromacros;
when the macro dialog comes up, click the macro name then the run buttton.
post back here if problems.

Regards
FSt1

"kokhong" wrote:

soli, FSt1, may i know where should i run this script?or can you write down
the step to run this...im really noob to this...thanks you.

"FSt1" wrote:

hi
not with the standard excel sort. what you want would require consolidating
data and deleteing row. that would require a macro.
the below macro works on your sample data. backup your data before runing
the macro.
Sub shiftup()
Dim r As Range
Dim ro As Range
Set r = Range("A2")
Do While Not IsEmpty(r)
Set ro = r.Offset(1, 0)
If r.Value = ro.Value Then
r.Offset(0, 1).Value = r.Offset(0, 1).Value &", " & r.Offset(1, 1).Value
r.Offset(1, 0).EntireRow.Delete shift:=xlUp
Set ro = r.Offset(1, 0)
Else
Set r = ro
End If
Loop
End Sub

regards
FSt1

"kokhong" wrote:

is there any way to sort my data in excel from

IP address Username
10.10.10.1 A
10.10.10.1 B
10.10.10.1 C
10.10.9.2 D
10.10.9.3 E
10.10.9.3 F

TO

IP address Username
10.10.10.1 A,B,C
10.10.9.2 D
10.10.9.3 E,F

initially,all the IP and username are in difference cells.Finally, if the
usernames are matched in the ip, they will be regroup into one cell as i
mentioned above.