Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Matching data from 2 columns
I have 2 columns of data. Both should ultimately have identical data,
but both have extranious data I need to separate. These lists will be 300+ long, so by hand is taking forever. Example: Column A Column B 12345 12344 12346 12345 12347 12346 12348 12347 12349 12349 I need a way to extract the 12344 from column B since it is not in A, and 12348 from A since it is not in B. They do not have the same number of entries. Any ideas? Thanks sooooo much in advance! |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Matching data from 2 columns
I don't know how to automatically do it, but you can use conditional
formatting to highlight the ones you need to delete. It might speed things up a bit until a smarter person tells you how to do it right. :) 1. Highlight Column A 2. Select Format-Conditional Formating 3. Select "Formula Is" 4. Insert =COUNTIF($B:$B,A1)=0 as your formula 5. Select an obvious formatting like a yellow background color 6. Click Okay 7. Click Okay All cells that don't have a matching value in column B should now be highlighted. Repeat the procedure on column B but use this formula instead =COUNTIF($A:$A,B1)=0 to highlight extraneous cells in column B. "doohen" wrote: I have 2 columns of data. Both should ultimately have identical data, but both have extranious data I need to separate. These lists will be 300+ long, so by hand is taking forever. Example: Column A Column B 12345 12344 12346 12345 12347 12346 12348 12347 12349 12349 I need a way to extract the 12344 from column B since it is not in A, and 12348 from A since it is not in B. They do not have the same number of entries. Any ideas? Thanks sooooo much in advance! |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Matching data from 2 columns
How about this
Take all the rows from Column B and add them to Column A Sort on A Insert a column before column A assuming that your data starts in b6 in the new column =--(b6<b5) - in row 5 Copy this equation all the way down Copy - paste special (values) new column Sort on new colum and get rid of the zeros "doohen" wrote: I have 2 columns of data. Both should ultimately have identical data, but both have extranious data I need to separate. These lists will be 300+ long, so by hand is taking forever. Example: Column A Column B 12345 12344 12346 12345 12347 12346 12348 12347 12349 12349 I need a way to extract the 12344 from column B since it is not in A, and 12348 from A since it is not in B. They do not have the same number of entries. Any ideas? Thanks sooooo much in advance! |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Matching data from 2 columns
Similar to Sloth's, but using Advanced Filter.
Let's assume your data looks like this: Database Database List1 List2 45 45 45 46 46 50 48 50 50 50 51 52 54 53 54 53 55 53 54 55 Criteria Criteria Test Test FALSE FALSE List1 List2 45 45 45 46 46 50 50 50 54 50 54 54 55 55 1. Select the header Database, List1 and its data entries and Insert Name Create Top Row 2. Select the header List1 and its data entries and Insert Name Create Top Row 3. Select the header Criteria and the two cells below it and Insert Name Create Top Row 4. In the cell in the first column that says FALSE, enter =COUNTIF(List2,List1)0 5. In the adjacent FALSE cell in the second column, enter =COUNTIF(List1,List2)0 6. Data Filter Advanced Filter Copy to another location 7. Type into List Range: Database 8. Type into Criteria: Criteria 9. Select a cell of your choice to Copy To: A seven entry list with the header List1 should appear as shown above. Repeat steps 1, 2, 3, 6, 7, 8 and 9 for List2 in the second column. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Matching data from 2 columns
I tried your highlighting method and it ended up just highlighting the
entire column after applying. I will play around with that idea and see if I can figure something out. Thanks so much for your input, I appreciate the help! |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Matching data from 2 columns
The only I can think that would cause that is if you inserted this as your
formula =COUNTIF($B:$B,$A$1)=0 if you did, remove the $'s from the A1 reference. Other than that I don't know what you did wrong because I used your example and got the desired results. "doohen" wrote: I tried your highlighting method and it ended up just highlighting the entire column after applying. I will play around with that idea and see if I can figure something out. Thanks so much for your input, I appreciate the help! |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Matching data from 2 columns
Once you get Sloth's highlighting method down,
you can automatically delete those cells by copying the array to Word and bringing it back. The conditional format will have changed to regular format. Now you can Find that format, Replace it with a blank, Goto Special Blanks and Delete Shift cells up |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Matching data from 2 columns
Sorry I forgot to mention some of these numbers have letters as well.
Thanks! |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Matching data from 2 columns
I am extremely green using Excel. I am unsure what the "database"
header refers to. Is that assuming the data is coming from different workbooks, and those are the titles? (I'm too much of a knucklehead to get the friendly Sloth highlighting method to work...) Thank you for your input. |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Matching data from 2 columns
The method I gave you would work for alpha as well as numeric
"doohen" wrote: Sorry I forgot to mention some of these numbers have letters as well. Thanks! |
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Matching data from 2 columns
Type in the words Database and Criteria just the way they are spelled.
These are reserved words used only in Advanced Filter and when used as shown will automatically fill in the blanks in the Filter window. The word Test is an arbitrary filler word. Advanced Filter is much harder to use than Sloth's method, but once you learn it, it will come in handy in many situations. |
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Matching data from 2 columns
Just to make things a little harder, here is a way to do it
with letters in the data and array formulas. This is even harder than Advanced Filter, but it is in real time. Assuming your data is arranged like this: List3 List4 45C 45E 45A 46C 46C 50B 48F 50F 50F 50F 51A 52C 54F 53C 54F 53F 55B 53C 54F 55B Name List3, List4, including the blank row. Name the following: Set1 Refers To: =ROW(INDEX(A:A,1):INDEX(A:A,COUNTA(List3)+1)) Set2 Refers To: =ROW(INDEX(A:A,1):INDEX(A:A,COUNTA(List4)+1)) The Result1 and Result2 array formulas (CSE) are respectively: =INDEX(List3,LARGE(IF((COUNTIF(List4,List3)0)*Set 1=0,1,Set1),Set1)) =INDEX(List4,LARGE(IF((COUNTIF(List3,List4)0)*Set 2=0,1,Set2),Set2)) Result1 Result2 55B 55B 54F 54F 54F 50F 50F 50F 46C 46C |
#13
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Matching data from 2 columns
I finally figured out what the issue was. Somehow I had a space in
front of every value in my second column of data. NOW, everything is working fabulously! Thanks again to all who gave help. It is much appreciated!!! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Matching and aranging data form two sources | Excel Worksheet Functions | |||
Data in narrow columns truncated when saving as DBF | Excel Discussion (Misc queries) | |||
Help PLEASE! Not sure what answer is: Match? Index? Other? | Excel Worksheet Functions | |||
How to take matching data from 2 columns and put in the same row? | New Users to Excel | |||
Pulling data from 1 sheet to another | Excel Worksheet Functions |