Excel Experts - GoFuckYourself.com

WiredGuy · 11-15-2007, 02:57 AM

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 · 11-15-2007, 03:08 AM

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

V_RocKs · 11-15-2007, 03:09 AM

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

BlueDesignStudios · 11-15-2007, 03:09 AM

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 · 11-15-2007, 03:11 AM

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 · 11-15-2007, 03:24 AM

.
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 · 11-15-2007, 03:25 AM

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 · 11-15-2007, 03:29 AM

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 · 11-15-2007, 03:31 AM

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 · 11-15-2007, 03:42 AM

Quote:

Originally Posted by 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

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

SexSearchSuzanne · 11-15-2007, 10:42 AM

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 · 11-15-2007, 10:48 AM

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

I put Suzanne on this case!

WiredGuy · 11-15-2007, 01:57 PM

Quote:

Originally Posted by 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?

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 · 11-15-2007, 02:05 PM

Quote:

Originally Posted by O MARINA

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

I put Suzanne on this case!

Suzanne and shit

O MARINA · 11-15-2007, 02:14 PM

Quote:

Originally Posted by datatank

Suzanne and shit

I LOVE YOU n shit

RuthB · 11-15-2007, 02:50 PM

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 · 11-15-2007, 03:54 PM

Are the duplicates in the same position?

If yes remove dupliacte the use concatenate

GrouchyAdmin · 11-15-2007, 03:58 PM

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 · 11-15-2007, 04:01 PM

Quote:

Originally Posted by 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.

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 · 11-15-2007, 05:57 PM

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 · 11-15-2007, 07:52 PM

Quote:

Originally Posted by 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.

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

Dragan777 · 11-15-2007, 08:33 PM

Quote:

Originally Posted by 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
.

yes make it like that easy

11-15-2007, 02:57 AM	#1
WiredGuy Pounding Googlebot Industry Role: Join Date: Aug 2002 Location: Canada Posts: 34,482	Excel Experts 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 __________________ I play with Google.

11-15-2007, 03:09 AM	#4
BlueDesignStudios Confirmed User Industry Role: Join Date: Feb 2003 Location: Australia Posts: 9,492	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 __________________ Blue Design Studios - Adult Design Specialists! Email me for a free quote: [email protected]

11-15-2007, 03:11 AM	#5
WiredGuy Pounding Googlebot Industry Role: Join Date: Aug 2002 Location: Canada Posts: 34,482	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 __________________ I play with Google.

11-15-2007, 03:24 AM	#6
reyz007 Confirmed User Join Date: Feb 2003 Posts: 1,495	. 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 . __________________ --------------------------------------------- + + + NEED TRAFFIC??? CLICK HERE!! + + + --------------------------------------------- Last edited by reyz007; 11-15-2007 at 03:25 AM..

11-15-2007, 03:29 AM	#8
WiredGuy Pounding Googlebot Industry Role: Join Date: Aug 2002 Location: Canada Posts: 34,482	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 __________________ I play with Google.

11-15-2007, 03:08 AM	#2
V_RocKs Damn Right I Kiss Ass! Industry Role: Join Date: Dec 2003 Location: Cowtown, USA Posts: 32,409	Well... I only know how to do it with 1 column... so combining the two might be needed...

11-15-2007, 03:25 AM	#7
Eman - PG PG Co-Boss Industry Role: Join Date: Nov 2003 Location: GFY Posts: 524	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.

11-15-2007, 03:31 AM	#9
Violetta Affiliate Join Date: Jul 2004 Posts: 28,735	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 __________________ M&A Queen

11-15-2007, 10:42 AM	#11
SexSearchSuzanne Confirmed User Industry Role: Join Date: Mar 2006 Location: Canada Posts: 299	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? __________________ Suzanne SexSearch.com XXXMatch.com Xpress.com PassionSearch.com ManPlay.com

11-15-2007, 10:48 AM	#12
O MARINA I'm clockin' ya, Versace shade watchin' ya Industry Role: Join Date: Mar 2003 Location: internet Posts: 13,795	Weird guy don't ever say I don't do anything for you I put Suzanne on this case!

11-15-2007, 02:50 PM	#16
RuthB Let's Get Paxumized! Industry Role: Join Date: May 2005 Location: Vancouver, Canada Posts: 7,247	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. __________________ Send & Receive Mass Global Payments - Mass P2P/Wire/EFT/SEPA - Adult Industry Friendly - Award Winning Payment Service - Fast, Reliable & Secure! Paxum ...... Paxum Bank Email: [email protected] ~ Telegram: PaxumRuth

11-15-2007, 03:54 PM	#17
RayBonga too cool for highschool Join Date: Nov 2005 Location: East side, West side, Worldwide! Posts: 12,164	Are the duplicates in the same position? If yes remove dupliacte the use concatenate

11-15-2007, 03:58 PM	#18
GrouchyAdmin Now choke yourself! Industry Role: Join Date: Apr 2006 Posts: 12,085	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. __________________ AIM: GrouchyGfy

11-15-2007, 05:57 PM	#20
captnjack Registered User Join Date: Sep 2007 Posts: 36	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.