Easy way to merge 5-6 columns

[Prof Wonmug]

I have 5-6 lists of vocabulary words. I would like to merge them into
a single list without duplicates.

Is there an easy way to do this? I did a quick Internet search and
found a number of add-ins. This is a one-time task, so I'd rather not
install anything.

I could just paste each list onto the end of the previous one, then
sort, and manually delete duplicates. That would be slightly tedious.
The lists range from 500 to 5,000 words. I could write a macro to
delete the duplicatea, but that would take me most of a day with my
skills.

I was hoping there miught be some built-in Excel function that would
do most of the work.

I am using Excel 2007.



Second problem. After creating the merged list, I would then like to
create a column for each of the original tables with a check mark or
an "x" or even the actual word in each cell if that word was in that
list. Like this:


A B C D E F
1 Words 1 2 3 4 5
2 abbreviate X X
3 abberation X X X
4 abeyance X X X

 

[rumkus]

Below sub will find unique values from the lists that are resided in
columns A,B,C and dump found values in column D.
Please check original post (By RB Smissaert) at:

http://groups.google.com/group/micr...t&q=unique+values+collection#12b4b92724f904f3

Rgds


Sub GetUniqueItems()

Dim i As Long
Dim LR As Long
Dim arr
Dim arrUnique
Dim coll As Collection

Set coll = New Collection

'Column1

With Sheets(1)
LR = .Cells(.Rows.Count, 1).End(xlUp).Row
arr = .Range(.Cells(1), .Cells(LR, 1))
On Error Resume Next
For i = 1 To UBound(arr)
coll.Add arr(i, 1), CStr(arr(i, 1))
Next i
On Error GoTo 0
End With


'Column2

With Sheets(1)
LR = .Cells(.Rows.Count, 2).End(xlUp).Row
arr = .Range(.Cells(2), .Cells(LR, 2))
On Error Resume Next
For i = 1 To UBound(arr)
coll.Add arr(i, 1), CStr(arr(i, 1))
Next i
On Error GoTo 0
End With

'Column3

With Sheets(1)
LR = .Cells(.Rows.Count, 3).End(xlUp).Row
arr = .Range(.Cells(3), .Cells(LR, 3))
On Error Resume Next
For i = 1 To UBound(arr)
coll.Add arr(i, 1), CStr(arr(i, 1))
Next i
On Error GoTo 0
End With


'transfer the collection to an array
ReDim arrUnique(1 To coll.Count, 1 To 1)


For i = 1 To coll.Count
arrUnique(i, 1) = coll.Item(i)
Next i


'dump the array with unique numbers in Column4
With Sheets(1)
.Range(.Cells(4), .Cells(UBound(arrUnique), 4)) = arrUnique
End With

End Sub

 

[Pete_UK]

Copy/paste all the lists under one another as you describe. Make sure
you have a header in the first cell (eg "WORDS"), then highlight all
the data and the header. Click on Data | Advanced Filter and in the
pop-up click on Copy to another location, put C1 in the Copy to box,
and check Unique values only. Click OK and your reduced list will
appear in column C - you can delete columns A and B if you don't need
them.

I'm not sure what you want to do in the second part of your post.

Hope this helps.

Pete

 

[Prof Wonmug]

Below sub will find unique values from the lists that are resided in
columns A,B,C and dump found values in column D.
Please check original post (By RB Smissaert) at:

http://groups.google.com/group/micr...t&q=unique+values+collection#12b4b92724f904f3

Rgds


Sub GetUniqueItems()

Dim i As Long
Dim LR As Long
Dim arr
Dim arrUnique
Dim coll As Collection

Set coll = New Collection

'Column1

With Sheets(1)
LR = .Cells(.Rows.Count, 1).End(xlUp).Row
arr = .Range(.Cells(1), .Cells(LR, 1))
On Error Resume Next
For i = 1 To UBound(arr)
coll.Add arr(i, 1), CStr(arr(i, 1))
Next i
On Error GoTo 0
End With


'Column2

With Sheets(1)
LR = .Cells(.Rows.Count, 2).End(xlUp).Row
arr = .Range(.Cells(2), .Cells(LR, 2))
On Error Resume Next
For i = 1 To UBound(arr)
coll.Add arr(i, 1), CStr(arr(i, 1))
Next i
On Error GoTo 0
End With

'Column3

With Sheets(1)
LR = .Cells(.Rows.Count, 3).End(xlUp).Row
arr = .Range(.Cells(3), .Cells(LR, 3))
On Error Resume Next
For i = 1 To UBound(arr)
coll.Add arr(i, 1), CStr(arr(i, 1))
Next i
On Error GoTo 0
End With


'transfer the collection to an array
ReDim arrUnique(1 To coll.Count, 1 To 1)


For i = 1 To coll.Count
arrUnique(i, 1) = coll.Item(i)
Next i


'dump the array with unique numbers in Column4
With Sheets(1)
.Range(.Cells(4), .Cells(UBound(arrUnique), 4)) = arrUnique
End With

End Sub

Thanks for that. I have it copied into one of my add-in modules, but
can't figure out how to run it. Up to now, I've only used functions
(UDFs) that I call from inside a cell expression (=myudf(A1)).

I tried that (=GetUniqueItems()) and got the "#NAME?" error.

I am able to run UDFs in this manner from that same add-in module.

How to I get this SUB to execute?

I tried Alt-F8, but no macros were visible.

 

[Prof Wonmug]

Copy/paste all the lists under one another as you describe. Make sure
you have a header in the first cell (eg "WORDS"), then highlight all
the data and the header. Click on Data | Advanced Filter and in the
pop-up click on Copy to another location, put C1 in the Copy to box,
and check Unique values only. Click OK and your reduced list will
appear in column C - you can delete columns A and B if you don't need
them.

That worked perfectly. Thanks. Now I have my merged list with no
duplicates.

I'm not sure what you want to do in the second part of your post.

I want to create a table with N rows and M+1 columns. Column A will
have the master list from the step above.

Column B will have a "1" in each cell next to a word in Column A that
is also in the first list and a "0" everywhere else. Column C will be
the same for the second list and so on.

If my original lists were:

1 2 1 5
3 4 2 6
5 6 3 7
7 8 4 8

The new table would look like this:

1 1 0 1 0
2 0 1 1 0
3 1 0 1 0
4 0 1 1 0
5 1 0 0 1
6 0 1 0 1
7 1 0 0 1
8 0 1 0 1

Do you have any magic for that?

Running Excel 2007.

 

[Pete_UK]

I don't know if your original lists are in separate sheets (or even
workbooks), or if they are next to each other on one sheet. Assume
that you have applied names to each list (eg list1, list2 etc). Then
you can do this in B2 (assuming a header in B1):

=ISNUMBER(MATCH($A2,list1,0))*1

and copy this down. The *1 will change the TRUE and FALSE to 1 and 0.
In C2 you would have a similar formula so you can just copy from B2 to
C2, but change list1 to list2. Do the same for the other lists.

Hope this helps.

Pete

 

[rumkus]

How to I get this SUB to execute?

Please check following address. Rgds

http://www.contextures.com/xlvba01.html

 

[drabbacs]

If I understand correctly, I believe you're looking for a true false
matrix.

Assuming column A to have the master list of words. Columns B-F will
use the following formula structure.

=if(isna(vlookup($A2,list1,1,false)),0,1)
=if(isna(vlookup($A2,list2,1,false)),0,1)
....
=if(isna(vlookup($A2,list5,1,false)),0,1)

replace list1 with the cell range for the first list of words, list2
for the second, etc. Make sure to set the reference as absolute before
copying down the page.

The isna() function traps the even of the word not appearing (which
would normally return N/A#).
The if basically say if you don't find it give me zero otherwise give
me 1

Hope this helps.

Drabbacs