Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Easy way to merge 5-6 columns
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Easy way to merge 5-6 columns
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/micro...b4b92724f904f3 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Easy way to merge 5-6 columns
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 On Aug 24, 9:58*pm, Prof Wonmug wrote: 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Easy way to merge 5-6 columns
On Tue, 24 Aug 2010 15:56:07 -0700 (PDT), "
wrote: 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/micro...b4b92724f904f3 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. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Easy way to merge 5-6 columns
On Tue, 24 Aug 2010 17:35:30 -0700 (PDT), Pete_UK
wrote: 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 we 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. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Easy way to merge 5-6 columns
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 On Aug 25, 5:38*am, Prof Wonmug wrote: On Tue, 24 Aug 2010 17:35:30 -0700 (PDT), Pete_UK wrote: 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 we *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. |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Easy way to merge 5-6 columns
How to I get this SUB to execute?
Please check following address. Rgds http://www.contextures.com/xlvba01.html |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Easy way to merge 5-6 columns
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 On Aug 25, 5:45*am, Pete_UK wrote: 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 On Aug 25, 5:38*am, Prof Wonmug wrote: On Tue, 24 Aug 2010 17:35:30 -0700 (PDT), Pete_UK wrote: 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 we *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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Is there an easy way to delete empty columns? | Excel Worksheet Functions | |||
Easy way to convert multiple columns into one | Excel Discussion (Misc queries) | |||
merge text from 2 columns into 1 then delete the old 2 columns | Excel Worksheet Functions | |||
How to merge columns from excel and how to set customize each columns | Excel Programming | |||
is there an easy way to merge data/information on several sheets . | Excel Discussion (Misc queries) |