I've got a worksheet containing data similar to the following:

Name1 Name2
Mark Sharon
John Tracy
Mark Tracy
Mark Chloe
Paul Emma
Paul Stuart

What I am trying to do is, on a new worksheet, obtain each unique name from
Name1 and place it in column C. Beneath each unique name, I want to list the
value(s) from Name2 in the cells in column B.

The result should look something like:

Mark
sharon
Tracy
Chloe
John
Tracy
Paul
Emma
Stuart

Is it possible to do this in excel?

I think you need to add a Macro.

Best Regards,
Jay

"mwl" wrote in message
...
I've got a worksheet containing data similar to the following:

Name1 Name2
Mark Sharon
John Tracy
Mark Tracy
Mark Chloe
Paul Emma
Paul Stuart

What I am trying to do is, on a new worksheet, obtain each unique name
from
Name1 and place it in column C. Beneath each unique name, I want to list
the
value(s) from Name2 in the cells in column B.

The result should look something like:

Mark
sharon
Tracy
Chloe
John
Tracy
Paul
Emma
Stuart

Is it possible to do this in excel?

Thanks Jay, but any clues as to how I do this?

"Jayarama Vytla" wrote:

I think you need to add a Macro.

Best Regards,
Jay

"mwl" wrote in message
...
I've got a worksheet containing data similar to the following:

Name1 Name2
Mark Sharon
John Tracy
Mark Tracy
Mark Chloe
Paul Emma
Paul Stuart

What I am trying to do is, on a new worksheet, obtain each unique name
from
Name1 and place it in column C. Beneath each unique name, I want to list
the
value(s) from Name2 in the cells in column B.

The result should look something like:

Mark
sharon
Tracy
Chloe
John
Tracy
Paul
Emma
Stuart

Is it possible to do this in excel?

Hi,

It's not so simple macro. But I will do it for you tomorrow.

Best Regards,
Jay

"mwl" wrote in message
...
Thanks Jay, but any clues as to how I do this?

"Jayarama Vytla" wrote:

I think you need to add a Macro.

Best Regards,
Jay

"mwl" wrote in message
...
I've got a worksheet containing data similar to the following:

Name1 Name2
Mark Sharon
John Tracy
Mark Tracy
Mark Chloe
Paul Emma
Paul Stuart

What I am trying to do is, on a new worksheet, obtain each unique name
from
Name1 and place it in column C. Beneath each unique name, I want to
list
the
value(s) from Name2 in the cells in column B.

The result should look something like:

Mark
sharon
Tracy
Chloe
John
Tracy
Paul
Emma
Stuart

Is it possible to do this in excel?

Try this

Alt + f11 to open VB editor. Right click 'This Workbook' and insert module
and paste this on on the right and run it

Copies the date from sheet 1 to sheet 2 so change that to suit

Sub sonic()
Sheets("Sheet1").Activate
lastrow = Cells(Cells.Rows.Count, "A").End(xlUp).Row
Range("A1:A" & lastrow).Copy Destination:=Sheets("Sheet2").Range("B1")
Range("B1:B" & lastrow).Copy Destination:=Sheets("Sheet2").Range("A1")
Sheets("Sheet2").Activate
Columns("A:B").Sort Key1:=Range("B1"), Order1:=xlAscending
For x = 1 To lastrow
For y = x + 1 To lastrow
If Cells(x, 2) = Cells(y, 2) Then
Cells(y, 2).ClearContents
End If
Next
Next
End Sub

Mike

"mwl" wrote:

Thanks Jay, but any clues as to how I do this?

"Jayarama Vytla" wrote:

I think you need to add a Macro.

Best Regards,
Jay

"mwl" wrote in message
...
I've got a worksheet containing data similar to the following:

Name1 Name2
Mark Sharon
John Tracy
Mark Tracy
Mark Chloe
Paul Emma
Paul Stuart

What I am trying to do is, on a new worksheet, obtain each unique name
from
Name1 and place it in column C. Beneath each unique name, I want to list
the
value(s) from Name2 in the cells in column B.

The result should look something like:

Mark
sharon
Tracy
Chloe
John
Tracy
Paul
Emma
Stuart

Is it possible to do this in excel?

Thanks for the code Mike. Unfortunately this doesn't quite meet my needs.

I've tested this out with the following data:

A B
1 Mark Test
2 Mark Test 2
3 John Test

The result from your code provides the following:

A B
1 Test John
2 Test Mark
3 Test2

The result I'd like to get a

A B
1 John
2 Test
3 Mark
4 Test
5 Test2

Any ideas how to change you code to achieve this?

"Mike H" wrote:

Try this

Alt + f11 to open VB editor. Right click 'This Workbook' and insert module
and paste this on on the right and run it

Copies the date from sheet 1 to sheet 2 so change that to suit

Sub sonic()
Sheets("Sheet1").Activate
lastrow = Cells(Cells.Rows.Count, "A").End(xlUp).Row
Range("A1:A" & lastrow).Copy Destination:=Sheets("Sheet2").Range("B1")
Range("B1:B" & lastrow).Copy Destination:=Sheets("Sheet2").Range("A1")
Sheets("Sheet2").Activate
Columns("A:B").Sort Key1:=Range("B1"), Order1:=xlAscending
For x = 1 To lastrow
For y = x + 1 To lastrow
If Cells(x, 2) = Cells(y, 2) Then
Cells(y, 2).ClearContents
End If
Next
Next
End Sub

Mike

"mwl" wrote:

Thanks Jay, but any clues as to how I do this?

"Jayarama Vytla" wrote:

I think you need to add a Macro.

Best Regards,
Jay

"mwl" wrote in message
...
I've got a worksheet containing data similar to the following:

Name1 Name2
Mark Sharon
John Tracy
Mark Tracy
Mark Chloe
Paul Emma
Paul Stuart

What I am trying to do is, on a new worksheet, obtain each unique name
from
Name1 and place it in column C. Beneath each unique name, I want to list
the
value(s) from Name2 in the cells in column B.

The result should look something like:

Mark
sharon
Tracy
Chloe
John
Tracy
Paul
Emma
Stuart

Is it possible to do this in excel?

Hi,

Test it agin with this

Sub sonic()
Sheets("Sheet1").Activate
lastrow = Cells(Cells.Rows.Count, "A").End(xlUp).Row
Range("A1:A" & lastrow).Copy Destination:=Sheets("Sheet2").Range("B1")
Range("B1:B" & lastrow).Copy Destination:=Sheets("Sheet2").Range("A1")
Sheets("Sheet2").Activate
Columns("A:B").Sort Key1:=Range("B1"), Order1:=xlAscending
For x = 1 To lastrow
For y = x + 1 To lastrow
If Cells(x, 2) = Cells(y, 2) Then
Cells(y, 2).ClearContents
End If
Next
Next
lastrow = Cells(Cells.Rows.Count, "B").End(xlUp).Row
Set myrange = Range("B2:B" & lastrow)
For Each c In myrange
If c.Offset(-1, 0) < "" Then
c.Insert Shift:=xlDown
End If
Next
Range("A1").Insert Shift:=xlDown
lastrow = Cells(Cells.Rows.Count, "A").End(xlUp).Row
Set myrange = Range("A2:A" & lastrow)
For Each c In myrange
If c.Offset(, 1) < "" Then
c.Insert Shift:=xlDown
End If
Next
End Sub


Mike

"mwl" wrote:

Thanks for the code Mike. Unfortunately this doesn't quite meet my needs.

I've tested this out with the following data:

A B
1 Mark Test
2 Mark Test 2
3 John Test

The result from your code provides the following:

A B
1 Test John
2 Test Mark
3 Test2

The result I'd like to get a

A B
1 John
2 Test
3 Mark
4 Test
5 Test2

Any ideas how to change you code to achieve this?

"Mike H" wrote:

Try this

Alt + f11 to open VB editor. Right click 'This Workbook' and insert module
and paste this on on the right and run it

Copies the date from sheet 1 to sheet 2 so change that to suit

Sub sonic()
Sheets("Sheet1").Activate
lastrow = Cells(Cells.Rows.Count, "A").End(xlUp).Row
Range("A1:A" & lastrow).Copy Destination:=Sheets("Sheet2").Range("B1")
Range("B1:B" & lastrow).Copy Destination:=Sheets("Sheet2").Range("A1")
Sheets("Sheet2").Activate
Columns("A:B").Sort Key1:=Range("B1"), Order1:=xlAscending
For x = 1 To lastrow
For y = x + 1 To lastrow
If Cells(x, 2) = Cells(y, 2) Then
Cells(y, 2).ClearContents
End If
Next
Next
End Sub

Mike

"mwl" wrote:

Thanks Jay, but any clues as to how I do this?

"Jayarama Vytla" wrote:

I think you need to add a Macro.

Best Regards,
Jay

"mwl" wrote in message
...
I've got a worksheet containing data similar to the following:

Name1 Name2
Mark Sharon
John Tracy
Mark Tracy
Mark Chloe
Paul Emma
Paul Stuart

What I am trying to do is, on a new worksheet, obtain each unique name
from
Name1 and place it in column C. Beneath each unique name, I want to list
the
value(s) from Name2 in the cells in column B.

The result should look something like:

Mark
sharon
Tracy
Chloe
John
Tracy
Paul
Emma
Stuart

Is it possible to do this in excel?

try this one
assumes first names are in A column, surnames are in B
the result will be stored in C

select all yr first names in A column and run the macro:

Sub listeczka()
Dim cell As Range
Dim cel As Range
Dim counter As Integer
Dim ile As Integer

Range("C:D").ClearContents
counter = 0
ile = 0

For Each cell In Selection

If Application.WorksheetFunction.CountIf(Range("C:C") , cell) = 0
Then
ile = 0
For Each cel In Selection
If cel = cell Then
counter = counter + 1
If
Application.WorksheetFunction.CountIf(Range("C:C") , cel) = 0 Then
Cells(counter, 3) = cel
ile = ile + 1
Cells(counter + ile, 3) = cel.Offset(0, 1)
Else:
Cells(counter + ile, 3) = cel.Offset(0, 1)
End If
Else: GoTo next_cel
End If
next_cel:
Next cel
Else: GoTo next_cell
End If

counter = counter -
Application.WorksheetFunction.CountIf(Range("C:C") , cell) + 2

next_cell:
Next cell


End Sub

HIH

yet another one
will bold and underline first names
HIH


Sub listeczka()
Dim cell As Range
Dim cel As Range
Dim counter As Integer
Dim ile As Integer

Range("C:C").Clear
counter = 0
ile = 0

For Each cell In Selection

If Application.WorksheetFunction.CountIf(Range("C:C") , cell) = 0
Then
ile = 0
For Each cel In Selection
If cel = cell Then
counter = counter + 1
If
Application.WorksheetFunction.CountIf(Range("C:C") , cel) = 0 Then
With Cells(counter, 3)
.Value = cel
.Font.Bold = True
.Font.Underline = True
End With
ile = ile + 1
Cells(counter + ile, 3) = cel.Offset(0, 1)
Else:
Cells(counter + ile, 3) = cel.Offset(0, 1)
End If
Else: GoTo next_cel
End If
next_cel:
Next cel
Else: GoTo next_cell
End If

counter = counter -
Application.WorksheetFunction.CountIf(Range("C:C") , cell) + 2

next_cell:
Next cell


End Sub