combining ranges on several worksheets without duplicates?

Barq

New Member
Joined
Nov 16, 2005
Messages
18
Hi,

I am trying to combine the first 2 columns of several ranges on different worksheets but without any duplicates (stacking them, i.e. the resulting range has 2 columns too) and then naming the range.

I am having a lot of trouble with this! I tried several methods, one of which has 2 For loops which deletes a row when a duplicate is found, but I keep getting error messages (something about arrays...)! Please help! I was also putting the ranges together in a primitive way by re-importing the first 2 columns from the original data text files... a more sophisticated way would be much appreciated :LOL:

Thanks!
 

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December

Nimrod

MrExcel MVP
Joined
Apr 29, 2002
Messages
6,259
Hello Barq:
Duplicates from the list can be removed by using the "copy unique" option of the "Advanced Filter " ... would that help ?
 

Barq

New Member
Joined
Nov 16, 2005
Messages
18
Hi,

not really, since I would like to automate this. Basically, this combined ranged will be used to populated a combo box. Right now, the data is imported automatically everyday, so I need the combo box to be updated too.

Thanks.
 

Nimrod

MrExcel MVP
Joined
Apr 29, 2002
Messages
6,259
Hello Barq
If you could provide more detail I may be able to help you with some code.

What is the name of the target Combo box ?
Is the Combo Box in a userform or on a sheet ?
What is the source of the data ?
Is the source data in a particular column of a particular sheet ? If so what sheet and what column ?
 

Barq

New Member
Joined
Nov 16, 2005
Messages
18
Hi Nimrod,

Right now, I have 9 worksheets that imports data from 9 different .csv files. The first columns of each worksheet are IDs and the second, names. What I want to do is to combined the first 2 columns from each of the 9 sheets onto a new worksheet without duplicates every time any of imported data on the 9 worksheet are refreshed.

I already wrote some code for a combo box that will be filled by a named range from a worksheet.

Thanks for your help!
 

Nimrod

MrExcel MVP
Joined
Apr 29, 2002
Messages
6,259
The following code creates a target sheet then it goes to ALL the sheets in the workbook to copy
uniques entries from columns A:B. The copy process is achieved thru Advanced Filter.

Code:
Public Sub ExcludeDupesFromList()
Dim TargSh As Worksheet
Dim SrcSh As Worksheet
Dim TargRng As Range

' MAKE TARGET SHEET
Sheets.Add
Set TargSh = ActiveSheet

' COPY ONLY UNIQUE FROM EACH SOURCE SHEET
For Each SrcSh In Worksheets
    If Not SrcSh.Name = TargSh.Name Then
    Set TargRng = TargSh.Range("A" & TargSh.Cells(65536, 1).End(xlUp).Row + 1)
    Call CopyUnique(SrcSh.Name, "A:B", TargSh.Name, TargRng.Address)
    End If
Next SrcSh

' REMOVE DUPES FROM TARG SHEET
Call CopyUnique(TargSh.Name, "A:B", TargSh.Name, "C1")
TargSh.Columns("A:B").Delete

End Sub

Private Sub CopyUnique(SrcShName, SrcCols, TargSh, TargRng)
On Error GoTo TheEnd

'ADVANCED FILTER UNIQUE COPY
    Sheets(SrcShName).Columns(SrcCols).AdvancedFilter _
    Action:=xlFilterCopy, _
    CopyToRange:=Sheets(TargSh).Range(TargRng), _
    Unique:=True
    
TheEnd:
End Sub
 

Barq

New Member
Joined
Nov 16, 2005
Messages
18
Thanks! I'll give it a try today.
 

Barq

New Member
Joined
Nov 16, 2005
Messages
18
Hi Nimrod,
I tried the code, it worked great up till the part where it tried to filter and copy Columns A and B to Column C. I tried to step through the code, what I found was that nothing was being copied to column C at all....

Yikes..
 

Barq

New Member
Joined
Nov 16, 2005
Messages
18
Hi Again Nimrod,

I got the code to work by adding a line to delete the two blank cells in the first line. Don't really know why it would not work with the blank cells, but now it works! Thanks a bunch.
 

Forum statistics

Threads
1,171,066
Messages
5,873,608
Members
432,986
Latest member
SteveGos

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top