VLOOKUP Insert & Copy

T

Ty

I have several postings. All of the answers solved my problem. Here
is another problem that I can't resolve with just VLOOKUP. VLOOKUP
only grabs the first line of data from the other sheet.

I'm trying to use the same VB script from my first post(down below).
I need to look at Sheet #2 in comparison to Sheet #1. Whenever col
1:sheet2 has matching data, then sheet #1 need to INSERT ROW and copy
sheet2:column 2:cell data to sheet1:column2 plus sheet2:column1:cell
data to sheet1:column1. All changes will be made on Sheet #1 after
viewing Sheet #2.

More detail:
col 1 in both sheets will have the same type of data. Example: last 4
SSN.

sheet1
col A
2255
3322
1134
8844

col B
blank

Sheet2
col A
2255
2255
2255

col B
Ty
Lincoln
Tony



Sub Duplicates()
'
' NOTE: You must select the first cell in the column and
' make sure that the column is sorted before running this macro
'
ScreenUpdating = False
FirstItem = ActiveCell.Value
SecondItem = ActiveCell.Offset(1, 0).Value
Offsetcount = 1
Do While ActiveCell <> ""
If FirstItem = SecondItem Then
ActiveCell.Offset(Offsetcount, 0).Interior.Color = RGB(255, 0,
0)
ActiveCell.Offset(Offsetcount - 1, 0).Interior.Color = RGB
(255, 0, 0)
Offsetcount = Offsetcount + 1
SecondItem = ActiveCell.Offset(Offsetcount, 0).Value
Else
ActiveCell.Offset(Offsetcount, 0).Select
FirstItem = ActiveCell.Value
SecondItem = ActiveCell.Offset(1, 0).Value
Offsetcount = 1
End If
Loop
ScreenUpdating = True
End Sub

I understand this might not be clear the first time around to the
reader. If not, please ask questions. Thanks in advance.
 
J

Joel

The code below assumes theree is a header row. It is much quicker to add
data to empty rows at the end of the worksheet then to insert rows in the
middle of a worksheet. The code adds duplicate items from sheet 2 to sheet 1
at the end of sheet 1. Then sorts sheets 1 by column A. finally the code
highlights the duplicate rows in sheet 1.

Sub Duplicates()
'
' NOTE: The macro assumes there is a header in the both worksheets
' The macro starts at row 2 and sort data automatically
'
ScreenUpdating = False

'get first empty row of sheet1
With Sheets("Sheet1")
LastRow = .Range("A" & Rows.Count).End(xlUp).Row
NewRow = LastRow + 1
End With

'find matching rows in sheet 2
With Sheets("Sheet2")
RowCount = 2
Do While .Range("A" & RowCount) <> ""
ID = .Range("A" & RowCount)
Employee = .Range("B" & RowCount)
'compare - look for ID in Sheet 1
With Sheets("Sheet1")
Set c = .Columns("A").Find(what:=ID, _
LookIn:=xlValues, lookat:=xlWhole)
If Not c Is Nothing Then
'add to end of sheet 1
.Range("A" & NewRow) = ID
.Range("B" & NewRow) = Employee
NewRow = NewRow + 1
End If
End With

RowCount = RowCount + 1
Loop
End With

'sort and highlight data
RowCount = 2
With Sheets("Sheet1")
LastRow = .Range("A" & Rows.Count).End(xlUp).Row
'sort data by column A
.Rows("1:" & LastRow).Sort _
header:=xlYes, _
Key1:=.Range("A1"), _
order1:=xlascendiing

Do While .Range("A" & RowCount) <> ""
Set FirstItem = .Range("A" & RowCount)
Set SecondItem = .Range("A" & (RowCount + 1))
If FirstItem.Value = SecondItem.Value Then
FirstItem.Interior.Color = RGB(255, 0, 0)
SecondItem.Interior.Color = RGB(255, 0, 0)
End If

RowCount = RowCount + 1
Loop
End With
ScreenUpdating = True

End Sub
 
T

Ty

The code below assumes theree is a header row.  It is much quicker to add
data to empty rows at the end of the worksheet then to insert rows in the
middle of a worksheet.  The code adds duplicate items from sheet 2 to sheet 1
at the end of sheet 1.  Then sorts sheets 1 by column  A.  finally the code
highlights the duplicate rows in sheet 1.

Sub Duplicates()
   '
   ' NOTE: The macro assumes there is a header in the both worksheets
   '       The macro starts at row 2 and sort data automatically
   '
   ScreenUpdating = False

   'get first empty row of sheet1
   With Sheets("Sheet1")
      LastRow = .Range("A" & Rows.Count).End(xlUp).Row
      NewRow = LastRow + 1
   End With

   'find matching rows in sheet 2
   With Sheets("Sheet2")
      RowCount = 2
      Do While .Range("A" & RowCount) <> ""
         ID = .Range("A" & RowCount)
         Employee = .Range("B" & RowCount)
         'compare - look for ID in Sheet 1
         With Sheets("Sheet1")
            Set c = .Columns("A").Find(what:=ID, _
               LookIn:=xlValues, lookat:=xlWhole)
            If Not c Is Nothing Then
               'add to end of sheet 1
               .Range("A" & NewRow) = ID
               .Range("B" & NewRow) = Employee
               NewRow = NewRow + 1
            End If
         End With

         RowCount = RowCount + 1
      Loop
   End With

   'sort and highlight data
   RowCount = 2
   With Sheets("Sheet1")
      LastRow = .Range("A" & Rows.Count).End(xlUp).Row
      'sort data by column A
      .Rows("1:" & LastRow).Sort _
         header:=xlYes, _
         Key1:=.Range("A1"), _
         order1:=xlascendiing

      Do While .Range("A" & RowCount) <> ""
         Set FirstItem = .Range("A" & RowCount)
         Set SecondItem = .Range("A" & (RowCount + 1))
         If FirstItem.Value = SecondItem.Value Then
            FirstItem.Interior.Color = RGB(255, 0, 0)
            SecondItem.Interior.Color = RGB(255, 0, 0)
         End If

         RowCount = RowCount + 1
      Loop
   End With
   ScreenUpdating = True

End Sub













- Show quoted text -
Click to expand...

I'm stepping through the above with F8 and I have a Watch on Employee
and ID. I can see the value changing from what is on Sheet2 but it is
not adding anything at the end of Sheet 1.
 
T

Ty

I'm stepping through the above with F8 and I have a Watch on Employee
and ID.  I can see the value changing from what is on Sheet2 but it is
not adding anything at the end of Sheet 1.- Hide quoted text -

- Show quoted text -
Click to expand...

My mistake. I forgot to match my Sheet2;colA with sheet1:colA. Now,
I have something to work with and I'm having a hard time figuring out
how to modify this code to resolve my problem. It's working but I
have 3 problems

1. it errors out when it tries to do the sort. But I figured that
out. 2 i's in the "xlascendiing". It sorts it and colors but it
still has the original row with the id from up top with a blank cell
in column B and the other 7-10 columns of data to the right. This
leads into problem #2.

.Rows("1:" & LastRow).Sort _
header:=xlYes, _
Key1:=.Range("A1"), _
order1:=xlascendiing

2. I don't mind adding at the end of the current data but I have
about 7-10 columns of data on Sheet1 to the right of each id such as
NAME, Department, Dpt Number...etc. Since it is hard to insert rows
and easier to add at the end. Is it possible to just place my 200
rows with the additional data on Sheet 3?

p.s.-- This way I don't have to figure out if I need to delete up to
row 155, 255 or 500 and keep the new data.

3. I don't really care for the color part of the code for this
spreadsheet. It can be deleted.

Thanks for your help...
 
J

Joel

the IDs are not eactly matching. This is usually caused by extra spaces in
the strings or some of the letters are in uppercase. Try these changes. I
added MatchCase = False and added TRIM in two locations. The code is looking
for an exact match in ID which means it is checking the entire cell to match.

Sub Duplicates()
'
' NOTE: The macro assumes there is a header in the both worksheets
' The macro starts at row 2 and sort data automatically
'
ScreenUpdating = False

'get first empty row of sheet1
With Sheets("Sheet1")
LastRow = .Range("A" & Rows.Count).End(xlUp).Row
NewRow = LastRow + 1
End With

'find matching rows in sheet 2
With Sheets("Sheet2")
RowCount = 2
Do While .Range("A" & RowCount) <> ""
ID = trim(.Range("A" & RowCount))
Employee = trim(.Range("B" & RowCount))
'compare - look for ID in Sheet 1
With Sheets("Sheet1")
Set c = .Columns("A").Find(what:=ID, _
LookIn:=xlValues, lookat:=xlWhole,MatchCase:=False)
If Not c Is Nothing Then
'add to end of sheet 1
.Range("A" & NewRow) = ID
.Range("B" & NewRow) = Employee
NewRow = NewRow + 1
End If
End With

RowCount = RowCount + 1
Loop
End With

'sort and highlight data
RowCount = 2
With Sheets("Sheet1")
LastRow = .Range("A" & Rows.Count).End(xlUp).Row
'sort data by column A
.Rows("1:" & LastRow).Sort _
header:=xlYes, _
Key1:=.Range("A1"), _
order1:=xlascendiing

Do While .Range("A" & RowCount) <> ""
Set FirstItem = .Range("A" & RowCount)
Set SecondItem = .Range("A" & (RowCount + 1))
If FirstItem.Value = SecondItem.Value Then
FirstItem.Interior.Color = RGB(255, 0, 0)
SecondItem.Interior.Color = RGB(255, 0, 0)
End If

RowCount = RowCount + 1
Loop
End With
ScreenUpdating = True

End Sub
 
T

Ty

the IDs are not eactly matching.  This is usually caused by extra spaces in
the strings or some of the letters are in uppercase.  Try these changes..  I
added MatchCase = False and added TRIM in two locations.  The code islooking
for an exact match in ID which means it is checking the entire cell to match.

Sub Duplicates()
   '
   ' NOTE: The macro assumes there is a header in the both worksheets
   '       The macro starts at row 2 and sort data automatically
   '
   ScreenUpdating = False

   'get first empty row of sheet1
   With Sheets("Sheet1")
      LastRow = .Range("A" & Rows.Count).End(xlUp).Row
      NewRow = LastRow + 1
   End With

   'find matching rows in sheet 2
   With Sheets("Sheet2")
      RowCount = 2
      Do While .Range("A" & RowCount) <> ""
         ID = trim(.Range("A" & RowCount))
         Employee = trim(.Range("B" & RowCount))
         'compare - look for ID in Sheet 1
         With Sheets("Sheet1")
            Set c = .Columns("A").Find(what:=ID, _
               LookIn:=xlValues, lookat:=xlWhole,MatchCase:=False)
            If Not c Is Nothing Then
               'add to end of sheet 1
               .Range("A" & NewRow) = ID
               .Range("B" & NewRow) = Employee
               NewRow = NewRow + 1
            End If
         End With

         RowCount = RowCount + 1
      Loop
   End With

   'sort and highlight data
   RowCount = 2
   With Sheets("Sheet1")
      LastRow = .Range("A" & Rows.Count).End(xlUp).Row
      'sort data by column A
      .Rows("1:" & LastRow).Sort _
         header:=xlYes, _
         Key1:=.Range("A1"), _
         order1:=xlascendiing

      Do While .Range("A" & RowCount) <> ""
         Set FirstItem = .Range("A" & RowCount)
         Set SecondItem = .Range("A" & (RowCount + 1))
         If FirstItem.Value = SecondItem.Value Then
            FirstItem.Interior.Color = RGB(255, 0, 0)
            SecondItem.Interior.Color = RGB(255, 0, 0)
         End If

         RowCount = RowCount + 1
      Loop
   End With
   ScreenUpdating = True

End Sub






- Show quoted text -
Click to expand...

It did the exact same thing as the other code. I made a mistake on
the first code.

What about my problem #2?

2. I don't mind adding at the end of the current data but I have
about 7-10 columns of ADDITIONAL DATA on Sheet1 to the right of each
id such as
NAME, Department, Dpt Number...etc. Since it is hard to insert rows
and easier to add at the end.

Is it possible to just place my 200 rows with the ADDITIONAL DATA on
Sheet 3?
 
J

Joel

I can easily do any or all of three things below:

1) Highlight the dups on Sheet 2
2) Put duplicates on sheet 3
3) If column B on sheet 1 has more data than A add the dups to sheet 1
starting after the LastRow in either A or b. Then sort on A. I can leave
the rows without column A data at the beginning or end of Sheet 1.
 
T

Ty

I can easily do any or all of three things below:

1) Highlight the dups on Sheet 2
2) Put duplicates on sheet 3
3) If column B on sheet 1 has more data than A add the dups to sheet 1
starting after the LastRow in either A or b.  Then sort on A.  I can leave
the rows without column A data at the beginning or end of Sheet 1.









- Show quoted text -
Click to expand...

Sheet 1 is the main source of information. I need the whole row of
data on Sheet 1 to the right Col C to Col H that matches the person.
So, When the comparison is done on Col A on Sheet 1 to Sheet2 ColA,
Sheet1, Col C to Col H data need to be placed in the Row.
 
J

Joel

there are a million different ways to do comparisons like this. Everybody
wants something a little dfifferent. Pardon me if I didn't interprete you
request properly. I think you want columns C - H on sheet 1 put on sheet 2
columns C - H. What is confusing me is your previous request ask for the
data to be placed either on sheet 1 or sheet 3. Now it is sheet 2. If it is
wrong in only takes me 2 minutes to make the changes. No big deal.

Sub Duplicates()
'
' NOTE: The macro assumes there is a header in the both worksheets
' The macro starts at row 2 and sort data automatically
'

'find matching rows in sheet 2
With Sheets("Sheet2")
RowCount = 2
Do While .Range("A" & RowCount) <> ""
ID = Trim(.Range("A" & RowCount))
'compare - look for ID in Sheet 1
With Sheets("Sheet1")
Set c = .Columns("A").Find(what:=ID, _
LookIn:=xlValues, lookat:=xlWhole, MatchCase:=False)

If Not c Is Nothing Then
'copy sheet 1 to sheet 2
Set CopyRange = _
.Range("C" & c.Row & ":H" & c.Row)
CopyRange.Copy Destination:=Sheets("Sheet2").Range("C" &
RowCount)
End If
End With
RowCount = RowCount + 1
Loop
End With

ScreenUpdating = True

End Sub
 
T

Ty

there are  a million different ways to do comparisons like this.  Everybody
wants something a little dfifferent.  Pardon me if I didn't interprete you
request properly.  I think you want columns C - H on sheet 1 put on sheet 2
columns C - H.  What is confusing me is your previous request ask for the
data to be placed either on sheet 1 or sheet 3.  Now it is sheet 2.  If it is
wrong in only takes me 2 minutes to make the changes.  No big deal.

Sub Duplicates()
   '
   ' NOTE: The macro assumes there is a header in the both worksheets
   '       The macro starts at row 2 and sort data automatically
   '

   'find matching rows in sheet 2
   With Sheets("Sheet2")
      RowCount = 2
      Do While .Range("A" & RowCount) <> ""
         ID = Trim(.Range("A" & RowCount))
         'compare - look for ID in Sheet 1
         With Sheets("Sheet1")
            Set c = .Columns("A").Find(what:=ID, _
               LookIn:=xlValues, lookat:=xlWhole, MatchCase:=False)

            If Not c Is Nothing Then
               'copy sheet 1 to sheet 2
               Set CopyRange = _
                  .Range("C" & c.Row & ":H" & c.Row)
               CopyRange.Copy Destination:=Sheets("Sheet2").Range("C" &
RowCount)
            End If
         End With
         RowCount = RowCount + 1
      Loop
   End With

   ScreenUpdating = True

End Sub





...

read more »- Hide quoted text -

- Show quoted text -
Click to expand...

Thanks for your help. I was tired last night. I'm sorry for the
confusion. Sheet 2 is ONLY used for the comparison. ONLY need 1
column of data from Sheet 2. Sheet 1 is the main source of
information. What you had was working ok except for the data to the
right Col C through Col H starting from Row 2 on Sheet 1.

Management is only interested in all of Sheet 1 data plus the 1 column
of data from Sheet 2.

I really appreciate your patience and assistance.
 
J

Joel

Can you be a little more specifc. I'm not sure which code you need modified.
Repost what you want modified with the description of the change in To/From
format.
 
T

Ty

Can you be a little more specifc.  I'm not sure which code you need modified.
 Repost what you want modified with the description of the change in To/From
format.





...

read more »- Hide quoted text -

- Show quoted text -
Click to expand...

Hope you are in the best of moods today. I really apologize for all
of the confusion. I don't want to repeat what I said before because I
have a better understanding of what needs to be done. Manually, I was
doing inserts/copy/paste. It is difficult to do inserts. Not
impossible but difficult. I'm gonna follow your logic for the code.
What you did on the first code was ok except for the following:

On sheet 1 when your first code was done. The changes on Sheet 1 were
one line below the original data on Sheet 1. Example:

One way of visually showing the SS:
ColA ColB ColC
ID1 blank cell [all of the data from Col C to Col H(actually P)]
ID1 data(from Sheet2) [no data]
ID1 data(from Sheet2) [no data]

Another way of visually representing the SS:
Row 2: ColA=ID1 ColB=blank cell ColC:ColH=[all of the data from Col
C to Col H(actually P)]
Row 3: ColA=ID1 ColB=data(from Sheet2) ColC:ColH=[no data]
Row 4: ColA=ID1 ColB=data(from Sheet2) ColC:ColH=[no data]

After the sort is done, there is essentially 1 extra ID/Employee being
displayed because you have the original id on Sheet 1 and the end
result of the code. Manually, I was just inputting the Data from
Sheet 2 in the blank cell. Then ONLY if there was extra Data I was
inserting a row and inputting the other 2 or 3 cells/rows.

So, basically Row 3-4 is the end results of the code. Which is
GREAT! I just wanted those results to include [all of the data from
Col C to Col P]. If possible, ONLY place the end results on Sheet 3
with the Sheet1: ColC:ColP data matching each id.

If that is not clear, I will try to answer your questions first before
you display code.

Again, Thank you.
 
J

Joel

The best way of doing this is to copy sheet1 to sheet 3. Then add sheet 2 to
end of sheet 3. Next sort sheet 3 by column A.

Now we must get rid of non duplicates. So I check if the column A data
match the next and previous rows and place an X in Column IV. So column IV
now contains an X on the rows to delete. I use autofilter to get only the
X's and delete these rows. See code below.


Sub Duplicates()
'
' NOTE: The macro assumes there is a header in the both worksheets
' The macro starts at row 2 and sort data automatically
'
ScreenUpdating = False

'copy sheet 1 to sheet 3
With Sheets("Sheet3")
Sheets("Sheet1").Cells.Copy _
Destination:=.Cells

'find last row
LastRowA = .Range("A" & Rows.Count).End(xlUp).Row
LastRowB = .Range("B" & Rows.Count).End(xlUp).Row

If LastRowA > LastRowB Then
LastRow = LastRowA
Else
LastRow = LastRowB
End If

NewRow = LastRow + 1

With Sheets("Sheet2")
'find last row
LastRow = .Range("A" & Rows.Count).End(xlUp).Row
End With

'copy sheet 2 to end of sheet 3
Sheets("Sheet2").Rows("1:" & LastRow).Copy _
Destination:=.Rows(NewRow)

'Sort Data
.Rows("1:" & LastRow).Sort _
header:=xlYes, _
Key1:=.Range("A1"), _
order1:=xlAscending


'Mark row which aren't duplicates so they can be removed

RowCount = 3
Do While .Range("A" & RowCount) <> ""
'check if ID matches either previous or next row
If .Range("A" & RowCount) <> .Range("A" & (RowCount - 1)) And _
.Range("A" & RowCount) <> .Range("A" & (RowCount + 1)) Then

.Range("IV" & RowCount) = "X"

End If
RowCount = RowCount + 1
Loop

'put anything in cell IV1 so filter works properly
.Range("IV1") = "Anything"
'filter on x's
.Columns("IV:IV").AutoFilter
.Columns("IV:IV").AutoFilter Field:=1, Criteria1:="X"

Set VisibleRows = .Rows("2:" & LastRow) _
.SpecialCells(xlCellTypeVisible)
'delete rows with X's
VisibleRows.Delete
'turn off autfilter
.Columns("IV:IV").AutoFilter
'clear IV1
.Range("IV1").Clear
End With

ScreenUpdating = True

End Sub


Ty said:
Can you be a little more specifc. I'm not sure which code you need modified.
Repost what you want modified with the description of the change in To/From
format.






































...

read more »- Hide quoted text -

- Show quoted text -
Click to expand...

Hope you are in the best of moods today. I really apologize for all
of the confusion. I don't want to repeat what I said before because I
have a better understanding of what needs to be done. Manually, I was
doing inserts/copy/paste. It is difficult to do inserts. Not
impossible but difficult. I'm gonna follow your logic for the code.
What you did on the first code was ok except for the following:

On sheet 1 when your first code was done. The changes on Sheet 1 were
one line below the original data on Sheet 1. Example:

One way of visually showing the SS:
ColA ColB ColC
ID1 blank cell [all of the data from Col C to Col H(actually P)]
ID1 data(from Sheet2) [no data]
ID1 data(from Sheet2) [no data]

Another way of visually representing the SS:
Row 2: ColA=ID1 ColB=blank cell ColC:ColH=[all of the data from Col
C to Col H(actually P)]
Row 3: ColA=ID1 ColB=data(from Sheet2) ColC:ColH=[no data]
Row 4: ColA=ID1 ColB=data(from Sheet2) ColC:ColH=[no data]

After the sort is done, there is essentially 1 extra ID/Employee being
displayed because you have the original id on Sheet 1 and the end
result of the code. Manually, I was just inputting the Data from
Sheet 2 in the blank cell. Then ONLY if there was extra Data I was
inserting a row and inputting the other 2 or 3 cells/rows.

So, basically Row 3-4 is the end results of the code. Which is
GREAT! I just wanted those results to include [all of the data from
Col C to Col P]. If possible, ONLY place the end results on Sheet 3
with the Sheet1: ColC:ColP data matching each id.

If that is not clear, I will try to answer your questions first before
you display code.

Again, Thank you.
Click to expand...
 
T

Ty

Can you be a little more specifc.  I'm not sure which code you need modified.
 Repost what you want modified with the description of the change in To/From
format.
Click to expand...
read more »- Hide quoted text -
Click to expand...
- Show quoted text -
Click to expand...

Hope you are in the best of moods today.  I really apologize for all
of the confusion.  I don't want to repeat what I said before because I
have a better understanding of what needs to be done.  Manually, I was
doing inserts/copy/paste.  It is difficult to do inserts.  Not
impossible but difficult.  I'm gonna follow your logic for the code.
What you did on the first code was ok except for the following:

On sheet 1 when your first code was done.  The changes on Sheet 1 were
one line below the original data on Sheet 1.  Example:

One way of visually showing the SS:
ColA ColB ColC
ID1 blank cell [all of the data from Col C to Col H(actually P)]
ID1 data(from Sheet2) [no data]
ID1 data(from Sheet2) [no data]

Another way of visually representing the SS:
Row 2: ColA=ID1  ColB=blank cell  ColC:ColH=[all of the data from Col
C to Col H(actually P)]
Row 3: ColA=ID1  ColB=data(from Sheet2)  ColC:ColH=[no data]
Row 4: ColA=ID1  ColB=data(from Sheet2)  ColC:ColH=[no data]

After the sort is done, there is essentially 1 extra ID/Employee being
displayed because you have the original id on Sheet 1 and the end
result of the code.  Manually, I was just inputting the Data from
Sheet 2 in the blank cell.  Then ONLY if there was extra Data I was
inserting a row and inputting the other 2 or 3 cells/rows.

So, basically Row 3-4 is the end results of the code.  Which is
GREAT!  I just wanted those results to include [all of the data from
Col C to Col P].  If possible, ONLY place the end results on Sheet 3
with the Sheet1: ColC:ColP data matching each id.

If that is not clear, I will try to answer your questions first before
you display code.

Again, Thank you.
Click to expand...

I like the earlier code. I didn't step through this new code but the
end result looks like a copy of Sheet 1 and Sheet 2 on Sheet 3. On
Sheet 3, Sheet 1 data is up top and Sheet 2 data is at the bottom.

The earlier code was OK. The end results before the sort &
highlighting was exactly what was needed. It just did not have the
data from Sheet1 Col C:Col P.
 
J

Joel

I left out one line. the code didn't sort enough rows so it kept the data
seperated.

From
'Sort Data
.Rows("1:" & LastRow).Sort _
header:=xlYes, _
Key1:=.Range("A1"), _
order1:=xlAscending


To
'Sort Data
LastRow = .Range("A" & Rows.Count).End(xlUp).Row
.Rows("1:" & LastRow).Sort _
header:=xlYes, _
Key1:=.Range("A1"), _
order1:=xlAscending

Ty said:
Can you be a little more specifc. I'm not sure which code you need modified.
Repost what you want modified with the description of the change in To/From
format.
Click to expand...
:
there are a million different ways to do comparisons like this. Everybody
wants something a little dfifferent. Pardon me if I didn't interprete you
request properly. I think you want columns C - H on sheet 1 put on sheet 2
columns C - H. What is confusing me is your previous request ask for the
data to be placed either on sheet 1 or sheet 3. Now it is sheet 2. If it is
wrong in only takes me 2 minutes to make the changes. No big deal.
Click to expand...
Sub Duplicates()
'
' NOTE: The macro assumes there is a header in the both worksheets
' The macro starts at row 2 and sort data automatically
'
Click to expand...
'find matching rows in sheet 2
With Sheets("Sheet2")
RowCount = 2
Do While .Range("A" & RowCount) <> ""
ID = Trim(.Range("A" & RowCount))
'compare - look for ID in Sheet 1
With Sheets("Sheet1")
Set c = .Columns("A").Find(what:=ID, _
LookIn:=xlValues, lookat:=xlWhole, MatchCase:=False)
Click to expand...
If Not c Is Nothing Then
'copy sheet 1 to sheet 2
Set CopyRange = _
.Range("C" & c.Row & ":H" & c.Row)
CopyRange.Copy Destination:=Sheets("Sheet2").Range("C" &
RowCount)
End If
End With
RowCount = RowCount + 1
Loop
End With
Click to expand...
ScreenUpdating = True
Click to expand...
End Sub
Click to expand...
:
I can easily do any or all of three things below:
Click to expand...
1) Highlight the dups on Sheet 2
2) Put duplicates on sheet 3
3) If column B on sheet 1 has more data than A add the dups to sheet 1
starting after the LastRow in either A or b. Then sort on A. I can leave
the rows without column A data at the beginning or end of Sheet 1.
Click to expand...
:
the IDs are not eactly matching. This is usually caused by extra spaces in
the strings or some of the letters are in uppercase. Try these changes.. I
added MatchCase = False and added TRIM in two locations. The code is looking
for an exact match in ID which means it is checking the entire cell to match.
Click to expand...
Sub Duplicates()
'
' NOTE: The macro assumes there is a header in the both worksheets
' The macro starts at row 2 and sort data automatically
'
ScreenUpdating = False
Click to expand...
'get first empty row of sheet1
With Sheets("Sheet1")
LastRow = .Range("A" & Rows.Count).End(xlUp).Row
NewRow = LastRow + 1
End With
Click to expand...
'find matching rows in sheet 2
With Sheets("Sheet2")
RowCount = 2
Do While .Range("A" & RowCount) <> ""
ID = trim(.Range("A" & RowCount))
Employee = trim(.Range("B" & RowCount))
'compare - look for ID in Sheet 1
With Sheets("Sheet1")
Set c = .Columns("A").Find(what:=ID, _
LookIn:=xlValues, lookat:=xlWhole,MatchCase:=False)
If Not c Is Nothing Then
'add to end of sheet 1
.Range("A" & NewRow) = ID
.Range("B" & NewRow) = Employee
NewRow = NewRow + 1
End If
End With
Click to expand...
RowCount = RowCount + 1
Loop
End With
Click to expand...
'sort and highlight data
RowCount = 2
With Sheets("Sheet1")
LastRow = .Range("A" & Rows.Count).End(xlUp).Row
'sort data by column A
.Rows("1:" & LastRow).Sort _
header:=xlYes, _
Key1:=.Range("A1"), _
order1:=xlascendiing
Click to expand...
Do While .Range("A" & RowCount) <> ""
Set FirstItem = .Range("A" & RowCount)
Set SecondItem = .Range("A" & (RowCount + 1))
If FirstItem.Value = SecondItem.Value Then
FirstItem.Interior.Color = RGB(255, 0, 0)
SecondItem.Interior.Color = RGB(255, 0, 0)
End If
Click to expand...
RowCount = RowCount + 1
Loop
End With
ScreenUpdating = True
Click to expand...
End Sub
Click to expand...
:
The code below assumes theree is a header row. It is much quicker to add
data to empty rows at the end of the worksheet then to insert rows in the
middle of a worksheet. The code adds duplicate items from sheet 2 to sheet 1
at the end of sheet 1. Then sorts sheets 1 by column A. finally the code
highlights the duplicate rows in sheet 1.
Click to expand...
Sub Duplicates()
'
' NOTE: The macro assumes there is a header in the both worksheets
' The macro starts at row 2 and sort data automatically
'
ScreenUpdating = False
Click to expand...
'get first empty row of sheet1
With Sheets("Sheet1")
LastRow = .Range("A" & Rows.Count).End(xlUp).Row
NewRow = LastRow + 1
End With
Click to expand...
'find matching rows in sheet 2
With Sheets("Sheet2")
RowCount = 2
Do While .Range("A" & RowCount) <> ""
ID = .Range("A" & RowCount)
Employee = .Range("B" & RowCount)
'compare - look for ID in Sheet 1
With Sheets("Sheet1")
Set c = .Columns("A").Find(what:=ID, _
LookIn:=xlValues, lookat:=xlWhole)
If Not c Is Nothing Then
'add to end of sheet 1
.Range("A" & NewRow) = ID
.Range("B" & NewRow) = Employee
NewRow = NewRow + 1
End If
End With
Click to expand...
RowCount = RowCount + 1
Loop
End With
Click to expand...
'sort and highlight data
RowCount = 2
With Sheets("Sheet1")
LastRow = .Range("A" & Rows.Count).End(xlUp).Row
'sort data by column A
.Rows("1:" & LastRow).Sort _
header:=xlYes, _
Key1:=.Range("A1"), _
order1:=xlascendiing
Click to expand...
Do While .Range("A" & RowCount) <> ""
Set FirstItem = .Range("A" & RowCount)
Set SecondItem = .Range("A" & (RowCount + 1))
If FirstItem.Value = SecondItem.Value Then
FirstItem.Interior.Color = RGB(255, 0, 0)
SecondItem.Interior.Color = RGB(255, 0, 0)
End If
Click to expand...
RowCount = RowCount + 1
Loop
End With
ScreenUpdating = True
Click to expand...
End Sub
Click to expand...
:
I have several postings. All of the answers solved my problem. Here
is another problem that I can't resolve with just VLOOKUP. VLOOKUP
only grabs the first line of data from the other sheet.
Click to expand...
I'm trying to use the same VB script from my first post(down below)..
I need to look at Sheet #2 in comparison to Sheet #1. Whenever col
1:sheet2 has matching data, then sheet #1 need to INSERT ROW and copy
sheet2:column 2:cell data to sheet1:column2 plus sheet2:column1:cell
data to sheet1:column1. All changes will be made on Sheet #1 after
viewing Sheet #2.
Click to expand...
More detail:
col 1 in both sheets will have the same type of data. Example: last 4
SSN.
Click to expand...
sheet1
col A
2255
3322
1134
8844
Click to expand...
col B
blank
Click to expand...
Sheet2
col A
2255
2255
2255
Click to expand...
col B
Ty
Lincoln
Tony
Click to expand...
Sub Duplicates()
'
' NOTE: You must select the first cell in the column and
' make sure that the column is sorted before running this macro
'
ScreenUpdating = False
FirstItem = ActiveCell.Value
SecondItem = ActiveCell.Offset(1, 0).Value
Offsetcount = 1
Do While ActiveCell <> ""
If FirstItem =

read more »- Hide quoted text -
Click to expand...
- Show quoted text -
Click to expand...

Hope you are in the best of moods today. I really apologize for all
of the confusion. I don't want to repeat what I said before because I
have a better understanding of what needs to be done. Manually, I was
doing inserts/copy/paste. It is difficult to do inserts. Not
impossible but difficult. I'm gonna follow your logic for the code.
What you did on the first code was ok except for the following:

On sheet 1 when your first code was done. The changes on Sheet 1 were
one line below the original data on Sheet 1. Example:

One way of visually showing the SS:
ColA ColB ColC
ID1 blank cell [all of the data from Col C to Col H(actually P)]
ID1 data(from Sheet2) [no data]
ID1 data(from Sheet2) [no data]

Another way of visually representing the SS:
Row 2: ColA=ID1 ColB=blank cell ColC:ColH=[all of the data from Col
C to Col H(actually P)]
Row 3: ColA=ID1 ColB=data(from Sheet2) ColC:ColH=[no data]
Row 4: ColA=ID1 ColB=data(from Sheet2) ColC:ColH=[no data]

After the sort is done, there is essentially 1 extra ID/Employee being
displayed because you have the original id on Sheet 1 and the end
result of the code. Manually, I was just inputting the Data from
Sheet 2 in the blank cell. Then ONLY if there was extra Data I was
inserting a row and inputting the other 2 or 3 cells/rows.

So, basically Row 3-4 is the end results of the code. Which is
GREAT! I just wanted those results to include [all of the data from
Col C to Col P]. If possible, ONLY place the end results on Sheet 3
with the Sheet1: ColC:ColP data matching each id.

If that is not clear, I will try to answer your questions first before
you display code.

Again, Thank you.
Click to expand...

I like the earlier code. I didn't step through this new code but the
end result looks like a copy of Sheet 1 and Sheet 2 on Sheet 3. On
Sheet 3, Sheet 1 data is up top and Sheet 2 data is at the bottom.

The earlier code was OK. The end results before the sort &
highlighting was exactly what was needed. It just did not have the
data from Sheet1 Col C:Col P.
Click to expand...
 
T

Ty

I left out one line.  the code didn't sort enough rows so it kept the data
seperated.

From
      'Sort Data
      .Rows("1:" & LastRow).Sort _
         header:=xlYes, _
         Key1:=.Range("A1"), _
         order1:=xlAscending

To
      'Sort Data
      LastRow = .Range("A" & Rows.Count).End(xlUp).Row
      .Rows("1:" & LastRow).Sort _
         header:=xlYes, _
         Key1:=.Range("A1"), _
         order1:=xlAscending





...

read more »- Hide quoted text -

- Show quoted text -
Click to expand...

I'm sorry..I'm being difficult and breaking a record for the most
post.

I made the change. The code merges the 2 sheets together on Sheet 3
but the first line of each match looks like the old code. I contine
to like the old code but it need to insert the whole line of data from
sheet 1 on the first match. The other matches can stay blank.

Regards,
Ty
 
J

Joel

Are all the columns displayed on Sheet3? The code is copying the entire row
from sheet 1. You said in one of your previous postings that column A on
sheet1 wasn't filled in. Is this the cause of the problem. I bet you some
of the rows on sheet 3 have data in columns C - H and some don't. Make sure
no rows and columns are hidden in sheet 3.
 
J

Joel

One other possiblility. The only rows in Sheet 1 that have data in columns c
- h don't have duplicates so they are being deleted. It is possible is the
data in column A in sheets 1 & 2 don't match EXACTLY then the rows from sheet
1 will be deleted. Make sure there ae no spaces in the column A data on both
sheets A & B. Also make sure leading zeroes ae the same. And make sure
there aen't single quotes in front of the numbers on either sheet 1 or sheet
2.

A quick check is to see if any of the data in column A (both sheets 1 & 2)
arre formated as either General or Number (not text).
 
J

Joel

One last idea. Are there any formulas in Column A (sheet 1 or 2). I may
need to copy and paste special values to eliminate the problem.
 
T

Ty

One last idea.  Are there any formulas in Column A (sheet 1 or 2).  Imay
need to copy and paste special values to eliminate the problem.





...

read more »- Hide quoted text -

- Show quoted text -
Click to expand...

There are no formula's. It's just that the new code is doing almost
exactly the same as the original code except adding Sheet 2 additional
information. Here I my steps when I do it manually:

1. I inserted Col B next to the ID.
2. Then I copied the Data from sheet 2 in the cell next to the id on
Sheet 1.
3. If there was more data matching the id on Sheet 1, I will insert
another row and add that data.

Note: I really would like to see the cell next to the id filled in
with the data from Sheet 2. I wish I can email you a screen shot.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

I need help with macro 3
Insert xlBottom border to group data 1
Macro & VB101 and Excel 3
Changing Cells depending on data entered in other cells 1
Copying data from Sheet1 which is on multiple rows for each customerto Sheet2 on single row. 2
Named Range maker code 9
Excel How to capture data to a specified excel sheet(first workbook) of two different workbooks from the 0
looping programme require 16

Top