Excel Experts

[WiredGuy]

This seems like such a simple operation but it has me stumped, even after googling it. Anyone know how to accomplish this in Excel?

Column A: List of keywords
Column B: Another list of keywords

I want Column C to contain a merged list of the keywords in A and B which would keep only the unique keywords (remove duplicates).

Example:
ColumnA: a, b, c
ColumnB: c,d,e
ColumC: a, b, c, d, e

It seems simple enough, but I'm having a hard time finding out how to do it.

Thanks
WG

[V_RocKs]

Well... I only know how to do it with 1 column... so combining the two might be needed...

[V_RocKs]

As in... combine both columns top to bottom... then run the unique filter

[BlueDesignStudios]

that's not easy to code up in just one column C without use of a macro

I suggest you split a,b,c,c,d,e into separate columns, then use the COUNT() and FIND() functions to produce the output you need

[WiredGuy]

I'm not even sure how to combine columns A and B together (even with duplicates). It seems like such a simple operation but I've been stumped on it.
WG

[reyz007]

.
hhh, very simple

just copy column B into column A and run data>filter>advanced filter

condition e. g. <>""

and check unique records only

106522520, i can do it for you
.

[Eman - PG]

Try copying the whole column and then pasting it into the other. Use the sort function to sort it by letter. There should be a free duplicate add-in for Excel on Google that will remove duplicates or spit out a unique list.

[WiredGuy]

Hmmm, I should add that this should be done by formula rather than copy/paste and using filters. This is a task that would need to be repeated on a daily basis so I'd rather just pull the data from CSV and try to automate this as much as possible. I'm hoping to not have to copy/paste columns and do manual filters. I was more or less hoping I could just drop a formula or macro to do the job.
WG

[Violetta]

I think this is the first time I have seen you ask a "computer" question here WG...

Though I understand you, excel can be some hard shit

[reyz007]

Hmmm, I should add that this should be done by formula rather than copy/paste and using filters. This is a task that would need to be repeated on a daily basis so I'd rather just pull the data from CSV and try to automate this as much as possible. I'm hoping to not have to copy/paste columns and do manual filters. I was more or less hoping I could just drop a formula or macro to do the job.
WG

so, do it via pivot table report and you just need refresh on a daily base

[SexSearchSuzanne]

The problem with Excel is that half the time I come up with brilliant time-saving, easy solutions, and half the time I am baffled. Your question has me baffled. Besides manually copying and pasting and then do a filter, I'm not sure what the solution is.

But since we are on the topic of how to do things in Excel ... one problem I have never figured out is how to combine information for two sets of keywords ... that is, one set of data has keywords and data A, B, and C and the other set of data has keywords and data D. I want to get a report that matches up the keywords with A, B, C, and D. I actually just found a tool online for $50 that seems to solve this problem, but it's not available for a Mac. Anyone have any thoughts on this one?

[O MARINA]

Weird guy
don't ever say I don't do anything for you

I put Suzanne on this case!

[WiredGuy]

The problem with Excel is that half the time I come up with brilliant time-saving, easy solutions, and half the time I am baffled. Your question has me baffled. Besides manually copying and pasting and then do a filter, I'm not sure what the solution is.

But since we are on the topic of how to do things in Excel ... one problem I have never figured out is how to combine information for two sets of keywords ... that is, one set of data has keywords and data A, B, and C and the other set of data has keywords and data D. I want to get a report that matches up the keywords with A, B, C, and D. I actually just found a tool online for $50 that seems to solve this problem, but it's not available for a Mac. Anyone have any thoughts on this one?

Its odd huh, the simplest things are very baffling but some of the most complex database operations are a sinch. I can do this lookup in about 5 lines of code in perl, but I really don't want to export and re-import the data to do this. I think using macros may be possible too but I'd rather keep this as simple as possible without copying/pasting keywords all the time.
WG

[datatank]

Weird guy
don't ever say I don't do anything for you

I put Suzanne on this case!

Suzanne and shit

[O MARINA]

Suzanne and shit

I LOVE YOU n shit

[RuthB]

Hey WG,

I can't find a way to auto copy the contents of two columns and put them into one column.

But if you're using the latest version of Excel there is a way to remove duplicate entries.

Highlight the column you want to have checked
Click on Data tab (main menu tabs along top)
Under 'Data Tools' you'll see 'Remove duplicates'
Click that and make sure it shows the column you have selected as 'checked'.
Then hit OK and it will go through that column and remove any duplicates it finds (deletes them permanently)

That's using Excel with Vista O/S

You may just have to get someone to copy/paste those two columns together for you each day. I can't find a formula or macro for that at all.

[RayBonga]

Are the duplicates in the same position?

If yes remove dupliacte the use concatenate

[GrouchyAdmin]

There's several ways to do this outside of Excel; UNIX has 'uniq', but doesn't do unique by words, it does it by lines.

I've written a utility to take two sets of lists, and only remove those that contain the first set from the second set.

If you want it to only contain the similar lists between the two, I could easily modify it to do that, and it'll run on Windows with support tools.

Feel free to hit me up on ICQ.

[WiredGuy]

There's several ways to do this outside of Excel; UNIX has 'uniq', but doesn't do unique by words, it does it by lines.

I've written a utility to take two sets of lists, and only remove those that contain the first set from the second set.

If you want it to only contain the similar lists between the two, I could easily modify it to do that, and it'll run on Windows with support tools.

Feel free to hit me up on ICQ.

I got this done in perl already in 5 lines, but it really needs to be done in Excel since I don't want to import, export and then re-import the data. Especially for a daily routine...
WG

[captnjack]

concatenta formula is =A1&", "&B1 which puts a comma space between data in A1 and B1 so if a1 is doe and b1 is john you now have doe, john in whatever cell you entered the formula presumably c1. If you do not want to insert a comma space leave out the &", " after the A1. If you want to combine 3 columns its =A1&", "&B1&", "&C1 However this is a forumla and as such each row will be unquie until you convert it to text. The only way I know how to convert to text is copy, paste special, value. So you are still stuck with the copy command.

[WiredGuy]

concatenta formula is =A1&", "&B1 which puts a comma space between data in A1 and B1 so if a1 is doe and b1 is john you now have doe, john in whatever cell you entered the formula presumably c1. If you do not want to insert a comma space leave out the &", " after the A1. If you want to combine 3 columns its =A1&", "&B1&", "&C1 However this is a forumla and as such each row will be unquie until you convert it to text. The only way I know how to convert to text is copy, paste special, value. So you are still stuck with the copy command.

That's what I ended up doing, copy/paste values.
WG

[Dragan777]

.
hhh, very simple

just copy column B into column A and run data>filter>advanced filter

condition e. g. <>""

and check unique records only

106522520, i can do it for you
.

yes make it like that easy