Good Morning all,

I need some help on coding the following
I have File1 and File2. I need File1 to look and Column A on File2 and if
there is Values there that is not in File1 Column A add it to the bottom of
File1 ColumnA
Example:

File1
Item Values
1234
2345
23456

File2
Item Values
123
2345
23456
231
234

It would add 231 and 234 to File1..

Thanks,
Aaron

Aaron:

Would code below work as an alternative. Combine both sets of data and
delete the duplicates. This code only handles the delete process.

Alan

Sub Delete_Dupes() 'Sub deletes duplicates in a single column
'This macro will delete duplicate rows in a range.
'To use, select a single-column range of cells,
'comprising the range of rows from which duplicates are to be deleted,
'e.g., C2:C99. To determine whether a row has duplicates, the values
in the
'selected column are compared. Entire rows are not compared against one
another.
'Only the selected column is used for comparison.
'When duplicate values are found in the active column,
'the first row remains, and all subsequent rows are deleted.


Dim Col As Integer
Dim r As Long
Dim C As Range
Dim N As Long
Dim V
Dim Rng As Range

'On Error Resume Next 'GoTo EndMacro
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
ActiveSheet.UsedRange
'Cells(1, 1).Select
Col = ActiveCell.Column
If Selection.Rows.Count > 1 Then
Set Rng = Selection
Else
Set Rng = ActiveSheet.UsedRange.Rows
End If
N = 0
For r = Rng.Rows.Count To 1 Step -1
V = Rng.Cells(r, Col).Value
If Application.WorksheetFunction.CountIf(Rng.Columns(Col), V) > 1
Then
Rng.Rows(r).Delete
N = N + 1
End If
Next r
EndMacro:
End Sub
Aaron wrote:
> Good Morning all,
>
> I need some help on coding the following
> I have File1 and File2. I need File1 to look and Column A on File2 and if
> there is Values there that is not in File1 Column A add it to the bottom of
> File1 ColumnA
> Example:
>
> File1
> Item Values
> 1234
> 2345
> 23456
>
> File2
> Item Values
> 123
> 2345
> 23456
> 231
> 234
>
> It would add 231 and 234 to File1..
>
> Thanks,
> Aaron

Sub ABC()
Dim sh1 as Worksheet, sh2 as Worksheet
Dim lastrow as Long, i as Long
Dim rng as Range, cell as Range
set sh1 = Workbooks("File1.xls").Worksheets(1)
set sh2 = Workbooks("File2.xls").Worksheets(1)
lastrow = sh.Cells(rows.count,1).End(xlup).row
i = 1
set rng = sh2.Range(sh2.cells(1,1),sh2.cells(1,1).End(xldown))
for each cell in rng
if application.countif(sh1.Columns(1),cell) = 0 then
sh1.cells(lastrow + i,1) = cell
i = i + 1
end if
Next
End sub

--
Regards,
Tom Ogilvy

"Aaron" wrote:

> Good Morning all,
>
> I need some help on coding the following
> I have File1 and File2. I need File1 to look and Column A on File2 and if
> there is Values there that is not in File1 Column A add it to the bottom of
> File1 ColumnA
> Example:
>
> File1
> Item Values
> 1234
> 2345
> 23456
>
> File2
> Item Values
> 123
> 2345
> 23456
> 231
> 234
>
> It would add 231 and 234 to File1..
>
> Thanks,
> Aaron

Thanks Tom. This worked great!

"Tom Ogilvy" wrote:

> Sub ABC()
> Dim sh1 as Worksheet, sh2 as Worksheet
> Dim lastrow as Long, i as Long
> Dim rng as Range, cell as Range
> set sh1 = Workbooks("File1.xls").Worksheets(1)
> set sh2 = Workbooks("File2.xls").Worksheets(1)
> lastrow = sh.Cells(rows.count,1).End(xlup).row
> i = 1
> set rng = sh2.Range(sh2.cells(1,1),sh2.cells(1,1).End(xldown))
> for each cell in rng
> if application.countif(sh1.Columns(1),cell) = 0 then
> sh1.cells(lastrow + i,1) = cell
> i = i + 1
> end if
> Next
> End sub
>
> --
> Regards,
> Tom Ogilvy
>
> "Aaron" wrote:
>
> > Good Morning all,
> >
> > I need some help on coding the following
> > I have File1 and File2. I need File1 to look and Column A on File2 and if
> > there is Values there that is not in File1 Column A add it to the bottom of
> > File1 ColumnA
> > Example:
> >
> > File1
> > Item Values
> > 1234
> > 2345
> > 23456
> >
> > File2
> > Item Values
> > 123
> > 2345
> > 23456
> > 231
> > 234
> >
> > It would add 231 and 234 to File1..
> >
> > Thanks,
> > Aaron

Alan,

No, sorry, I don't need to delete the data just make the two files match
with the same data.

Thanks!
Aaron

"(E-Mail Removed)" wrote:

> Aaron:
>
> Would code below work as an alternative. Combine both sets of data and
> delete the duplicates. This code only handles the delete process.
>
> Alan
>
> Sub Delete_Dupes() 'Sub deletes duplicates in a single column
> 'This macro will delete duplicate rows in a range.
> 'To use, select a single-column range of cells,
> 'comprising the range of rows from which duplicates are to be deleted,
> 'e.g., C2:C99. To determine whether a row has duplicates, the values
> in the
> 'selected column are compared. Entire rows are not compared against one
> another.
> 'Only the selected column is used for comparison.
> 'When duplicate values are found in the active column,
> 'the first row remains, and all subsequent rows are deleted.
>
>
> Dim Col As Integer
> Dim r As Long
> Dim C As Range
> Dim N As Long
> Dim V
> Dim Rng As Range
>
> 'On Error Resume Next 'GoTo EndMacro
> Application.ScreenUpdating = False
> Application.Calculation = xlCalculationManual
> ActiveSheet.UsedRange
> 'Cells(1, 1).Select
> Col = ActiveCell.Column
> If Selection.Rows.Count > 1 Then
> Set Rng = Selection
> Else
> Set Rng = ActiveSheet.UsedRange.Rows
> End If
> N = 0
> For r = Rng.Rows.Count To 1 Step -1
> V = Rng.Cells(r, Col).Value
> If Application.WorksheetFunction.CountIf(Rng.Columns(Col), V) > 1
> Then
> Rng.Rows(r).Delete
> N = N + 1
> End If
> Next r
> EndMacro:
> End Sub
> Aaron wrote:
> > Good Morning all,
> >
> > I need some help on coding the following
> > I have File1 and File2. I need File1 to look and Column A on File2 and if
> > there is Values there that is not in File1 Column A add it to the bottom of
> > File1 ColumnA
> > Example:
> >
> > File1
> > Item Values
> > 1234
> > 2345
> > 23456
> >
> > File2
> > Item Values
> > 123
> > 2345
> > 23456
> > 231
> > 234
> >
> > It would add 231 and 234 to File1..
> >
> > Thanks,
> > Aaron
>
>

OK, I think I jumped the gun a little here. I looked at the code and thought
it would work without trying it. I put a 1 after the sh in the lastrow
statement. But is is not
doing anything.

Sorry and Thanks
Aaron

"Tom Ogilvy" wrote:

> Sub ABC()
> Dim sh1 as Worksheet, sh2 as Worksheet
> Dim lastrow as Long, i as Long
> Dim rng as Range, cell as Range
> set sh1 = Workbooks("File1.xls").Worksheets(1)
> set sh2 = Workbooks("File2.xls").Worksheets(1)
> lastrow = sh.Cells(rows.count,1).End(xlup).row
> i = 1
> set rng = sh2.Range(sh2.cells(1,1),sh2.cells(1,1).End(xldown))
> for each cell in rng
> if application.countif(sh1.Columns(1),cell) = 0 then
> sh1.cells(lastrow + i,1) = cell
> i = i + 1
> end if
> Next
> End sub
>
> --
> Regards,
> Tom Ogilvy
>
> "Aaron" wrote:
>
> > Good Morning all,
> >
> > I need some help on coding the following
> > I have File1 and File2. I need File1 to look and Column A on File2 and if
> > there is Values there that is not in File1 Column A add it to the bottom of
> > File1 ColumnA
> > Example:
> >
> > File1
> > Item Values
> > 1234
> > 2345
> > 23456
> >
> > File2
> > Item Values
> > 123
> > 2345
> > 23456
> > 231
> > 234
> >
> > It would add 231 and 234 to File1..
> >
> > Thanks,
> > Aaron

Sub ABC()
Dim sh1 As Worksheet, sh2 As Worksheet
Dim lastrow As Long, i As Long
Dim rng As Range, cell As Range
Set sh1 = Workbooks("File1.xls").Worksheets(1)
Set sh2 = Workbooks("File2.xls").Worksheets(1)
lastrow = sh1.Cells(Rows.Count, 1).End(xlUp).Row
i = 1
Set rng = sh2.Range(sh2.Cells(1, 1), sh2.Cells(1, 1).End(xlDown))
For Each cell In rng
If Application.CountIf(sh1.Columns(1), cell) = 0 Then
sh1.Cells(lastrow + i, 1) = cell
i = i + 1
End If
Next
End Sub

Worked perfectly for me. There was a typo in the original although (sh
should have been Sh1 in one location), but I am sure you would have gotten an
error and picked that up. The code refers to the first worksheet in the tab
order in each workbook, so if that is not corrrect, you will need to adjust
it.

It looks in column A starting in Cell A1 with no empty cells in the data.

--
Regards,
Tom Ogilvy


"Aaron" wrote:

> OK, I think I jumped the gun a little here. I looked at the code and thought
> it would work without trying it. I put a 1 after the sh in the lastrow
> statement. But is is not
> doing anything.
>
> Sorry and Thanks
> Aaron
>
> "Tom Ogilvy" wrote:
>
> > Sub ABC()
> > Dim sh1 as Worksheet, sh2 as Worksheet
> > Dim lastrow as Long, i as Long
> > Dim rng as Range, cell as Range
> > set sh1 = Workbooks("File1.xls").Worksheets(1)
> > set sh2 = Workbooks("File2.xls").Worksheets(1)
> > lastrow = sh.Cells(rows.count,1).End(xlup).row
> > i = 1
> > set rng = sh2.Range(sh2.cells(1,1),sh2.cells(1,1).End(xldown))
> > for each cell in rng
> > if application.countif(sh1.Columns(1),cell) = 0 then
> > sh1.cells(lastrow + i,1) = cell
> > i = i + 1
> > end if
> > Next
> > End sub
> >
> > --
> > Regards,
> > Tom Ogilvy
> >
> > "Aaron" wrote:
> >
> > > Good Morning all,
> > >
> > > I need some help on coding the following
> > > I have File1 and File2. I need File1 to look and Column A on File2 and if
> > > there is Values there that is not in File1 Column A add it to the bottom of
> > > File1 ColumnA
> > > Example:
> > >
> > > File1
> > > Item Values
> > > 1234
> > > 2345
> > > 23456
> > >
> > > File2
> > > Item Values
> > > 123
> > > 2345
> > > 23456
> > > 231
> > > 234
> > >
> > > It would add 231 and 234 to File1..
> > >
> > > Thanks,
> > > Aaron

Stange, I had caught the sh1. I deleted the data and just put numbers in and
it worked fine, not sure if there was a issue with the format of the first
set of data I was using. Thank you again Tom.

"Tom Ogilvy" wrote:

> Sub ABC()
> Dim sh1 As Worksheet, sh2 As Worksheet
> Dim lastrow As Long, i As Long
> Dim rng As Range, cell As Range
> Set sh1 = Workbooks("File1.xls").Worksheets(1)
> Set sh2 = Workbooks("File2.xls").Worksheets(1)
> lastrow = sh1.Cells(Rows.Count, 1).End(xlUp).Row
> i = 1
> Set rng = sh2.Range(sh2.Cells(1, 1), sh2.Cells(1, 1).End(xlDown))
> For Each cell In rng
> If Application.CountIf(sh1.Columns(1), cell) = 0 Then
> sh1.Cells(lastrow + i, 1) = cell
> i = i + 1
> End If
> Next
> End Sub
>
> Worked perfectly for me. There was a typo in the original although (sh
> should have been Sh1 in one location), but I am sure you would have gotten an
> error and picked that up. The code refers to the first worksheet in the tab
> order in each workbook, so if that is not corrrect, you will need to adjust
> it.
>
> It looks in column A starting in Cell A1 with no empty cells in the data.
>
> --
> Regards,
> Tom Ogilvy
>
>
> "Aaron" wrote:
>
> > OK, I think I jumped the gun a little here. I looked at the code and thought
> > it would work without trying it. I put a 1 after the sh in the lastrow
> > statement. But is is not
> > doing anything.
> >
> > Sorry and Thanks
> > Aaron
> >
> > "Tom Ogilvy" wrote:
> >
> > > Sub ABC()
> > > Dim sh1 as Worksheet, sh2 as Worksheet
> > > Dim lastrow as Long, i as Long
> > > Dim rng as Range, cell as Range
> > > set sh1 = Workbooks("File1.xls").Worksheets(1)
> > > set sh2 = Workbooks("File2.xls").Worksheets(1)
> > > lastrow = sh.Cells(rows.count,1).End(xlup).row
> > > i = 1
> > > set rng = sh2.Range(sh2.cells(1,1),sh2.cells(1,1).End(xldown))
> > > for each cell in rng
> > > if application.countif(sh1.Columns(1),cell) = 0 then
> > > sh1.cells(lastrow + i,1) = cell
> > > i = i + 1
> > > end if
> > > Next
> > > End sub
> > >
> > > --
> > > Regards,
> > > Tom Ogilvy
> > >
> > > "Aaron" wrote:
> > >
> > > > Good Morning all,
> > > >
> > > > I need some help on coding the following
> > > > I have File1 and File2. I need File1 to look and Column A on File2 and if
> > > > there is Values there that is not in File1 Column A add it to the bottom of
> > > > File1 ColumnA
> > > > Example:
> > > >
> > > > File1
> > > > Item Values
> > > > 1234
> > > > 2345
> > > > 23456
> > > >
> > > > File2
> > > > Item Values
> > > > 123
> > > > 2345
> > > > 23456
> > > > 231
> > > > 234
> > > >
> > > > It would add 231 and 234 to File1..
> > > >
> > > > Thanks,
> > > > Aaron