Working with Text in Columns in Excel -- please help
January 19, 2006 8:31 AM Subscribe
Excel Question: I have 2 lists of text data separated into 2 columns -- column A and column B. Column A is a significantly longer list (close to 10k cells) than Column B (5-6k cells), but many of the cells in Column A are identical to cells in Column B. What I would like to do is generate a Column C that only contains cells from Column A that are NOT also in Column B.
Excel Question: I have 2 lists of text data separated into 2 columns -- column A and column B. Column A is a significantly longer list (close to 10k cells) than Column B (5-6k cells), but many of the cells in Column A are identical to cells in Column B. What I would like to do is generate a Column C that only contains cells from Column A that are NOT also in Column B.
It doesn't matter what order the text in Column C ends up or if Column C contains empty cells in between utilized cells. I can also swap the order of Columns A and B if necessary (don't know if that matters or not!) so that the shorter column is to the right of the longer column.
What is the easiest/fastest/least work-intensive way to do this?
(btw this is my first question on AskMefi so please be kind if I have done this wrong -- I did search through old questions and didn't see anything similar).
Excel Question: I have 2 lists of text data separated into 2 columns -- column A and column B. Column A is a significantly longer list (close to 10k cells) than Column B (5-6k cells), but many of the cells in Column A are identical to cells in Column B. What I would like to do is generate a Column C that only contains cells from Column A that are NOT also in Column B.
It doesn't matter what order the text in Column C ends up or if Column C contains empty cells in between utilized cells. I can also swap the order of Columns A and B if necessary (don't know if that matters or not!) so that the shorter column is to the right of the longer column.
What is the easiest/fastest/least work-intensive way to do this?
(btw this is my first question on AskMefi so please be kind if I have done this wrong -- I did search through old questions and didn't see anything similar).
I hope you get a good answer, because this is something I need to do all the time.
posted by Four Flavors at 9:02 AM on January 19, 2006
posted by Four Flavors at 9:02 AM on January 19, 2006
Is it a requirement that this be done in Excel? I'm sure it has the capability, if properly scripted, but this sounds like exactly the kind of operation that the UNIX columnar text processing toolchain is designed for (the cut, sort, and uniq commands in particular). String them together on a pipe and you're done.
These tools are available for Windows. Most folks will point you at cygwin, but that's overkill.
posted by majick at 9:33 AM on January 19, 2006
These tools are available for Windows. Most folks will point you at cygwin, but that's overkill.
posted by majick at 9:33 AM on January 19, 2006
Put the data into a database.
Run a not exits query.
posted by orthogonality at 9:41 AM on January 19, 2006
Run a not exits query.
posted by orthogonality at 9:41 AM on January 19, 2006
er, not exists
posted by orthogonality at 9:42 AM on January 19, 2006
posted by orthogonality at 9:42 AM on January 19, 2006
It seems like the very last example given on fidelity's first link would work for what you are talking about, except you would have to switch the columns. I tested it and it works.
posted by contessa at 10:15 AM on January 19, 2006
posted by contessa at 10:15 AM on January 19, 2006
Response by poster: Fidelity -- thanks! I am trying both and having some success but will have to continue to work on it on home computer as my work computer just isn't up to the task of processing such large spreadsheets and keeps crashing. It is doing what I want tho.
Thanks everyone else for their responses
posted by Soulbee at 10:23 AM on January 19, 2006
Thanks everyone else for their responses
posted by Soulbee at 10:23 AM on January 19, 2006
This thread is closed to new comments.
Or like this? (Step 2)
posted by fidelity at 8:47 AM on January 19, 2006