Functions

  • Thread starter Thread starter Sanjib
  • Start date Start date
S

Sanjib

I have 10 names in sheet1, 15 names in sheet2 out of which 4 are same as
sheet 1.
Now I want to display the names of remaining 6 persons of sheet1 and 11
persons name of sheet2 in sheet3
 
Are the 10 names on Sheet1 and the 15 names on Sheet2 all the names in their
respective columns, or are you picking 10 and 15 names from a subset of a
longer list of names?

Are there any gaps in the name lists (that is, are the lists contiguous, or
can there be missing "names" in between the names that are there)?

Can the names be placed on Sheet3 in any order, or does one list have to
come before the other (and, if so, which is first)?

Rick
 
Rick Rothstein (MVP - VB) said:
Are the 10 names on Sheet1 and the 15 names on Sheet2 all the names in their
respective columns, or are you picking 10 and 15 names from a subset of a
longer list of names?

Are there any gaps in the name lists (that is, are the lists contiguous, or
can there be missing "names" in between the names that are there)?

Can the names be placed on Sheet3 in any order, or does one list have to
come before the other (and, if so, which is first)?

Rick




10 & 15 Names are in their respective columns
Click to expand...
No gaps in the name list
It could be placed in any order in sheet3
 
10 & 15 Names are in their respective columns
No gaps in the name list
It could be placed in any order in sheet3
 
10 & 15 Names are in their respective columns
No gaps in the name list
It could be placed in any order in sheet3
Click to expand...

Give the following macro a try. Where indicated, adjust the Worksheet names
and the Range references (the "A1:A" parts) for each worksheet to match your
actual worksheets names, columns and starting row for the lists.

Rick

Sub CopyNames()
Dim X As Long
Dim Cel As Range
Dim WS1range As Range
Dim WS2range As Range
Dim LongerList As Range
Dim ShorterList As Range
Dim WS3 As Worksheet
Dim EmptyCell As Long
' **** Adjust references to match your worksheet ****
With Worksheets("Sheet1")
Set WS1range = .Range("A1:A" & .Cells(Rows.Count, "A").End(xlUp).Row)
End With
With Worksheets("Sheet2")
Set WS2range = .Range("A1:A" & .Cells(Rows.Count, "A").End(xlUp).Row)
End With
Set WS3 = Worksheets("Sheet3")
' ***************************************************
If WS1range.Count > WS2range.Count Then
Set LongerList = WS1range
Set ShorterList = WS2range
Else
Set LongerList = WS2range
Set ShorterList = WS1range
End If
LongerList.Copy Destination:=WS3.Range("A1")
For Each Cel In ShorterList
EmptyCell = WS3.Cells(Rows.Count, "A").End(xlUp).Row + 1
If WS3.Range("A1:A" & EmptyCell).Find(Cel.Text) Is Nothing Then
WS3.Range("A" & EmptyCell).Value = Cel.Text
End If
Next
End Sub
 
Rick Rothstein (MVP - VB) said:
Give the following macro a try. Where indicated, adjust the Worksheet names
and the Range references (the "A1:A" parts) for each worksheet to match your
actual worksheets names, columns and starting row for the lists.

Rick

Sub CopyNames()
Dim X As Long
Dim Cel As Range
Dim WS1range As Range
Dim WS2range As Range
Dim LongerList As Range
Dim ShorterList As Range
Dim WS3 As Worksheet
Dim EmptyCell As Long
' **** Adjust references to match your worksheet ****
With Worksheets("Sheet1")
Set WS1range = .Range("A1:A" & .Cells(Rows.Count, "A").End(xlUp).Row)
End With
With Worksheets("Sheet2")
Set WS2range = .Range("A1:A" & .Cells(Rows.Count, "A").End(xlUp).Row)
End With
Set WS3 = Worksheets("Sheet3")
' ***************************************************
If WS1range.Count > WS2range.Count Then
Set LongerList = WS1range
Set ShorterList = WS2range
Else
Set LongerList = WS2range
Set ShorterList = WS1range
End If
LongerList.Copy Destination:=WS3.Range("A1")
For Each Cel In ShorterList
EmptyCell = WS3.Cells(Rows.Count, "A").End(xlUp).Row + 1
If WS3.Range("A1:A" & EmptyCell).Find(Cel.Text) Is Nothing Then
WS3.Range("A" & EmptyCell).Value = Cel.Text
End If
Next
End Sub

Cant we make it with any FUNCTION
Click to expand...
i would b thankful
 
Back
Top