VLOOKUP Insert & Copy

  • Thread starter Thread starter Ty
  • Start date Start date
...

read more »- Hide quoted text -

- Show quoted text -
Click to expand...

"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.
 
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
 
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 -
Click to expand...

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.
 
...

read more »- Hide quoted text -

- Show quoted text -
Click to expand...

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.
 
...

read more »- Hide quoted text -

- Show quoted text -
Click to expand...

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.
 
Back
Top