VLOOKUP Insert & Copy

[Ty]

...

read more »- Hide quoted text -

- Show quoted text -

"You said in one of your previous postings that column A on
sheet1 wasn't filled in." Correction I said cells in Column B were
not filled in. Every cell in Column B should be filled in.

All of the columns C through P are displayed. Which is OK. It is
just that again(repeate), the cell on Col B next to the data on the
ROW is blank. Again, when I did it manually, I would just copy and
past the data into B2.

 

[Joel]

Lets try again.

1) Do you like the two rows size row approach. One long row(from sheet A)
and one short (data from sheet 1) ?
2) Was the problem just that column B was not filled in with the data from
sheet 2 for rows that came from sheet 1?

I made some small changes to the code below to add sheet 2 column B into
sheet 3 column B. I used VLOOKUP formula in column B (sheet 3) to get the
data from sheet 2). Then replace the formula using PasteSpecial.

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
LastRow2 = .Range("A" & Rows.Count).End(xlUp).Row
End With

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

'Sort Data
LastRow = .Range("A" & Rows.Count).End(xlUp).Row
.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


'add formual in column B to get data from sheet 2
.Range("B2").Formula = _
"=VLOOKUP(A2,Sheet2!A$1:B$" & LastRow2 & ",2)"

LastRow = .Range("A" & Rows.Count).End(xlUp).Row
'copy formula down column B
.Range("B2").Copy _
Destination:=.Range("B2:B" & LastRow)

'replace formula with data
.Columns("B").Copy
.Columns("B").PasteSpecial _
Paste:=xlPasteValues

End With

ScreenUpdating = True

End Sub

 

[Ty]

Lets try again.  

1) Do you like the two rows size row approach.  One long row(from sheetA)
and one short (data from sheet 1) ?  
2) Was the problem just that column B was not filled in with the data from
sheet 2 for rows that came from sheet 1?

I made some small changes to the code below to add sheet 2 column B into
sheet 3 column B.  I used VLOOKUP formula in column B (sheet 3) to get the
data from sheet 2).  Then replace the formula using  PasteSpecial.

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
         LastRow2 = .Range("A" & Rows.Count).End(xlUp).Row
      End With

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

      'Sort Data
      LastRow = .Range("A" & Rows.Count).End(xlUp).Row
      .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

      'add formual in column B to get data from sheet 2
      .Range("B2").Formula = _
         "=VLOOKUP(A2,Sheet2!A$1:B$" & LastRow2 & ",2)"

      LastRow = .Range("A" & Rows.Count).End(xlUp).Row
      'copy formula down column B
      .Range("B2").Copy _
         Destination:=.Range("B2:B" & LastRow)

      'replace formula with data
      .Columns("B").Copy
      .Columns("B").PasteSpecial _
         Paste:=xlPasteValues

   End With

   ScreenUpdating = True

End Sub





...

read more »- Hide quoted text -

- Show quoted text -

I'm excited!! Very close. The only problem now is that it placed
some more columns from Sheet2 on the other rows/cells that are suppose
to be empty. In others words it did not just ONLY use Column B. If
that can't be solved, I can solve that by deleting those col/rows from
sheet2. I don't need them anyways.

 

[Ty]

...

read more »- Hide quoted text -

- Show quoted text -

On 2nd review this morning. It's not working.

In response to one of the questions above. Yes, the problem was that
Col B was not being filled in. The problem today: The 2nd id is
being placed as all of the id's for that Employee. The 3rd problem is
the number of id's is a little off for some of them towards the
bottom. For example, 1 employee should have 1 id but they have 2.
I'm gonna step through the code.

 

[Ty]

...

read more »- Hide quoted text -

- Show quoted text -

I'm slowly stepping through the lines of code. I can see what you are
doing. The following
sections make this sheet look like the end results of the first code.
Column B cell next to
the Col A:cell is blank. Then the End results of the code has been
copied under the Col A
cell with the match in Col B.

'check if ID matches either previous or next row.
'clear IV1

PROBLEM: It's the VLOOKUP section that fills in the blank cell in
ColB:sheet3.---the ID is
the same for all ColB:cells matching the Employee.