Excel Guru's? Need help!

NoahFence · 8th May 2012, 08:52 AM

I have a spreadsheet I'm using to create dummy data, and have come across an issue. I can solve it the long way, but thought I'd ask for a bit of help.

I have about 200 records, and under the column "RecipientCompany" there are a handful of companies repeated throughout.

I would like to populate the columns "SenderFirstName" and "SenderLastName" based on the company, as if one person is sending out a brochure based on the recpient's company.

We'll say that the RecipientCompany is in column E - The sender first name is in column K and the last name in Column L.

I can't make the sender's full name one column due to the fact that his or her 1st name will be used in the body text of the letter.

Naturally, since I'm just making this stuff up I could just do it the long way, but I actually had this issue come up for real not too long ago and it seems to be helpful information.

(My customer populated a field with all the same info, when it really needed to be two different ones)

madurobob · 8th May 2012, 09:01 AM

Not sure I understand. You have two sets of data, right? One is a matched set of RecipientCompany, SenderFirstName and SenderLastName and the other is a larger set of records with many duplicates of a few RecipientCompany names but no SenderFirstName/SenderLastName?

Wouldn't a simple vlookup find both the first and last names from the matched set of records?

Floyt · 8th May 2012, 09:22 AM

That's how I would interpret it as well...?

NoahFence · 8th May 2012, 09:35 AM

Hmm... not quite.

I have one column populated with the company name.

I would like to populate the other columns with an appropriate sender first name, and sender last name.

Say Joe Schmo works for ACME Skeptics. I want to find all references to ACME Skeptics, and populate column K with "JOE" and column L with "Schmo"

I just don't want to do it manually. Should I just sort the company name column and make my life easier that way?

I don't actually have the Sender Name - I'm inventing it as this is just a pet project to familiarize myself with this variable data software "FusionPro"

Floyt · 8th May 2012, 09:57 AM

Still sounds like a vlookup job to me. You would fill column K with a formula saying "take <company name> from the current row, look up <company name> in the reference list [where each company is paired with two names] , return associated <first name>". Same for last name.

I might not be getting the layout of your data though

GreyArea · 8th May 2012, 10:54 AM

Are you familiar with the "Concatenate" command? You could make a new column that combines the text in the first and last name columns. Note the space in between.

=CONCATENATE(K1," ",L1)

And if you want to produce that concatenation only when some other key word is in the same row, you can do something like this:

=IF(E1="Acme",CONCATENATE(K1," ",L1),"")

This is just a typical If-Then. The last part (the two sets of quotation marks with nothing between them) is the output when "Acme" is not on that line.

You should be able to copy that from the first cell and down the entire column, with Excel adjusting for the Row number along the way.

However, I am not sure how other programs will see the results. They might see it as text, but they might also see the formula itself, and put that in the "Dear Hmm-Hmm-Hmm" field in your letter. I've never had to do that part.

I hope this helps.

Wudang · 8th May 2012, 12:11 PM

I use VBA more than formulas so sorry if this is a dumb question: but what does CONCATENATE give that you wouldn't get by joining with "&"s?

Most programs work on the cell.value (i.e. the resultant text) rather than cell.formula so should be okay

GreyArea · 8th May 2012, 12:32 PM

Originally Posted by Wudang

I use VBA more than formulas so sorry if this is a dumb question: but what does CONCATENATE give that you wouldn't get by joining with "&"s?

I don't know. But thanks for the ampersand tip. I'll have to try that out some more.

NoahFence · 8th May 2012, 12:53 PM

Originally Posted by GreyArea

Are you familiar with the "Concatenate" command? You could make a new column that combines the text in the first and last name columns. Note the space in between.

=CONCATENATE(K1," ",L1)

And if you want to produce that concatenation only when some other key word is in the same row, you can do something like this:

=IF(E1="Acme",CONCATENATE(K1," ",L1),"")

This is just a typical If-Then. The last part (the two sets of quotation marks with nothing between them) is the output when "Acme" is not on that line.

You should be able to copy that from the first cell and down the entire column, with Excel adjusting for the Row number along the way.

However, I am not sure how other programs will see the results. They might see it as text, but they might also see the formula itself, and put that in the "Dear Hmm-Hmm-Hmm" field in your letter. I've never had to do that part.

I hope this helps.

Thanks! That was fun in a spreadsheet sort of way.

The program that I'm using is actually using a comma-delimited .CSV file that I save the spreadsheet as. It removes formatting and silly stuff like that. Except for spaces. I sure do enjoy the part when I look at the final product and the address field looks like this

Bob (insert enormous space here) Smith
2332 Anywhere Road
South . Hadley
MA

(no worries - I already have a trim function to get rid of the empty space)

RenaissanceBiker · 8th May 2012, 01:04 PM

Pivot table?

Diazo · 9th May 2012, 03:32 PM

Originally Posted by Wudang

I use VBA more than formulas so sorry if this is a dumb question: but what does CONCATENATE give that you wouldn't get by joining with "&"s?

As far as I am aware, CONCATENATE and & are the same command, they just have slightly different syntax.

=CONCATENATE("A","B")

does the same as

= "A" & "B"

for any microsoft office product in both for both formula entry in access/excel and in VBA.

D.

Wudang · 10th May 2012, 02:23 AM

Thanks. I was assuming orthogonality in Ms products, how embarassing.

8th May 2012, 08:52 AM	#1
NoahFence Psycho Kitty Join Date: Mar 2011 Location: Patriot Nation Posts: 9,322	Excel Guru's? Need help! I have a spreadsheet I'm using to create dummy data, and have come across an issue. I can solve it the long way, but thought I'd ask for a bit of help. I have about 200 records, and under the column "RecipientCompany" there are a handful of companies repeated throughout. I would like to populate the columns "SenderFirstName" and "SenderLastName" based on the company, as if one person is sending out a brochure based on the recpient's company. We'll say that the RecipientCompany is in column E - The sender first name is in column K and the last name in Column L. I can't make the sender's full name one column due to the fact that his or her 1st name will be used in the body text of the letter. Naturally, since I'm just making this stuff up I could just do it the long way, but I actually had this issue come up for real not too long ago and it seems to be helpful information. (My customer populated a field with all the same info, when it really needed to be two different ones)
	__________________ Our truest life is when we are in our dreams awake. -Henry David Thoreau

8th May 2012, 09:01 AM	#2
madurobob Philosopher Join Date: Jul 2007 Location: Blue Heaven, NC Posts: 5,531	Not sure I understand. You have two sets of data, right? One is a matched set of RecipientCompany, SenderFirstName and SenderLastName and the other is a larger set of records with many duplicates of a few RecipientCompany names but no SenderFirstName/SenderLastName? Wouldn't a simple vlookup find both the first and last names from the matched set of records?
	__________________ Insert witty phrase or out of context post by another member here.

8th May 2012, 09:22 AM	#3
Floyt Chordate Join Date: Apr 2003 Location: Cape Town! Not mugged yet. Looking for chameleons. Posts: 1,425	That's how I would interpret it as well...?
	__________________ They had no god; they had no gods; they had no faith. What they appear to have had is a working metaphor. - Ursula K. Le Guin, "Always Coming Home"

8th May 2012, 09:35 AM	#4
NoahFence Psycho Kitty Join Date: Mar 2011 Location: Patriot Nation Posts: 9,322	Hmm... not quite. I have one column populated with the company name. I would like to populate the other columns with an appropriate sender first name, and sender last name. Say Joe Schmo works for ACME Skeptics. I want to find all references to ACME Skeptics, and populate column K with "JOE" and column L with "Schmo" I just don't want to do it manually. Should I just sort the company name column and make my life easier that way? I don't actually have the Sender Name - I'm inventing it as this is just a pet project to familiarize myself with this variable data software "FusionPro"
	__________________ Our truest life is when we are in our dreams awake. -Henry David Thoreau

8th May 2012, 09:57 AM	#5
Floyt Chordate Join Date: Apr 2003 Location: Cape Town! Not mugged yet. Looking for chameleons. Posts: 1,425	Still sounds like a vlookup job to me. You would fill column K with a formula saying "take <company name> from the current row, look up <company name> in the reference list [where each company is paired with two names] , return associated <first name>". Same for last name. I might not be getting the layout of your data though
	__________________ They had no god; they had no gods; they had no faith. What they appear to have had is a working metaphor. - Ursula K. Le Guin, "Always Coming Home"

8th May 2012, 10:54 AM	#6
GreyArea Graduate Poster Join Date: Aug 2009 Posts: 1,000	Are you familiar with the "Concatenate" command? You could make a new column that combines the text in the first and last name columns. Note the space in between. =CONCATENATE(K1," ",L1) And if you want to produce that concatenation only when some other key word is in the same row, you can do something like this: =IF(E1="Acme",CONCATENATE(K1," ",L1),"") This is just a typical If-Then. The last part (the two sets of quotation marks with nothing between them) is the output when "Acme" is not on that line. You should be able to copy that from the first cell and down the entire column, with Excel adjusting for the Row number along the way. However, I am not sure how other programs will see the results. They might see it as text, but they might also see the formula itself, and put that in the "Dear Hmm-Hmm-Hmm" field in your letter. I've never had to do that part. I hope this helps.
	__________________ _{I am the 0.0000000142857142857143% Tradition is a murky and dangerous bog.}

8th May 2012, 12:11 PM	#7
Wudang BOFH Join Date: Jun 2003 Location: Sheffield Posts: 8,243	I use VBA more than formulas so sorry if this is a dumb question: but what does CONCATENATE give that you wouldn't get by joining with "&"s? Most programs work on the cell.value (i.e. the resultant text) rather than cell.formula so should be okay
	__________________ Aphorism: Subjects most likely to be declared inappropriate for humor are the ones most in need of it. -epepke

8th May 2012, 12:32 PM	#8
GreyArea Graduate Poster Join Date: Aug 2009 Posts: 1,000	Originally Posted by Wudang I use VBA more than formulas so sorry if this is a dumb question: but what does CONCATENATE give that you wouldn't get by joining with "&"s? I don't know. But thanks for the ampersand tip. I'll have to try that out some more.
	__________________ _{I am the 0.0000000142857142857143% Tradition is a murky and dangerous bog.}

8th May 2012, 12:53 PM	#9
NoahFence Psycho Kitty Join Date: Mar 2011 Location: Patriot Nation Posts: 9,322	Originally Posted by GreyArea Are you familiar with the "Concatenate" command? You could make a new column that combines the text in the first and last name columns. Note the space in between. =CONCATENATE(K1," ",L1) And if you want to produce that concatenation only when some other key word is in the same row, you can do something like this: =IF(E1="Acme",CONCATENATE(K1," ",L1),"") This is just a typical If-Then. The last part (the two sets of quotation marks with nothing between them) is the output when "Acme" is not on that line. You should be able to copy that from the first cell and down the entire column, with Excel adjusting for the Row number along the way. However, I am not sure how other programs will see the results. They might see it as text, but they might also see the formula itself, and put that in the "Dear Hmm-Hmm-Hmm" field in your letter. I've never had to do that part. I hope this helps. Thanks! That was fun in a spreadsheet sort of way. The program that I'm using is actually using a comma-delimited .CSV file that I save the spreadsheet as. It removes formatting and silly stuff like that. Except for spaces. I sure do enjoy the part when I look at the final product and the address field looks like this Bob (insert enormous space here) Smith 2332 Anywhere Road South . Hadley MA (no worries - I already have a trim function to get rid of the empty space)
	__________________ Our truest life is when we are in our dreams awake. -Henry David Thoreau Last edited by NoahFence; 8th May 2012 at 12:54 PM. Reason: too funny - even this board removes empty spaces but excel doesn't unless you tell it to

8th May 2012, 01:04 PM	#10
RenaissanceBiker Eats shoots and leaves. Join Date: Aug 2006 Location: South Carolina Posts: 6,803	Pivot table?
	__________________ "Truth does not contradict truth." - St. Augustine "Faith often contradicts faith. Therefore faith is not an indication of truth." - RenaissanceBiker

9th May 2012, 03:32 PM	#11
Diazo Scholar Join Date: Jun 2006 Posts: 100	Originally Posted by Wudang I use VBA more than formulas so sorry if this is a dumb question: but what does CONCATENATE give that you wouldn't get by joining with "&"s? As far as I am aware, CONCATENATE and & are the same command, they just have slightly different syntax. =CONCATENATE("A","B") does the same as = "A" & "B" for any microsoft office product in both for both formula entry in access/excel and in VBA. D.
Diazo Scholar Join Date: Jun 2006 Posts: 100	__________________ Avatar Source Yes, Touhou's gotten to me also.

10th May 2012, 02:23 AM	#12
Wudang BOFH Join Date: Jun 2003 Location: Sheffield Posts: 8,243	Thanks. I was assuming orthogonality in Ms products, how embarassing.
	__________________ Aphorism: Subjects most likely to be declared inappropriate for humor are the ones most in need of it. -epepke