Excel Help

Posted By Message

DRMom
Two in Blue

Member since 5/05

20223 total posts

Name:
Melissa

Excel Help

I only know the very basics. I have two spreadsheets. One with addresses and a list of about 325 clients. The other with 120 or so targets from this list, but no addresses. I need to transfer the addresses from the big spreadsheet to the appropriate row in the smaller one. Is there a way to do this?

Posted 6/11/13 1:34 PM
 
Long Island Weddings
Long Island's Largest Bridal Resource

Salason
♥

Member since 6/05

9878 total posts

Name:

Re: Excel Help

you need to do a Vlookup... if you type in vlookup it should take you to the function box that explains how to create the formula.

Posted 6/11/13 1:37 PM
 

kedra5
Think Less, Live More

Member since 7/11

3544 total posts

Name:
Kedra

Re: Excel Help

Posted by Salason

you need to do a Vlookup... if you type in vlookup it should take you to the function box that explains how to create the formula.



Yes this...it carries over information from one spreadsheet to another.

Posted 6/11/13 2:13 PM
 

BellyBlessings
LIF Adolescent

Member since 8/08

574 total posts

Name:

Re: Excel Help

vlookup is the absolute best way to do this...

If you aren't comfortable with functions, you can also combine both lists to one spreadsheet, make sure the same info is in the same columns (all last names in the same column, all first names in the same column...) highlight the last name column and select conditional formatting in the home tab, highlight cell rules, duplicate values, then click OK. This highlights all last names with that are duplicated. You can then filter out the highlighted names and have what you are looking for. Then, with some copying and pasting, have the results you need!

I can also do this for you if you like.

Posted 6/11/13 2:32 PM
 

racheK
Hudson's Momma

Member since 10/10

2853 total posts

Name:
Rachel

Re: Excel Help

I love vlookups! There are a few small things that can mess you up even if you think you are doing it properly...I can help too!

Posted 6/11/13 3:01 PM
 

DRMom
Two in Blue

Member since 5/05

20223 total posts

Name:
Melissa

Re: Excel Help

Wher am I typing in vlookup

Posted 6/11/13 3:06 PM
 

Salason
♥

Member since 6/05

9878 total posts

Name:

Re: Excel Help

Posted by DRMom

Wher am I typing in vlookup



click that little fx button next to the formula bar... type in vlookup and it will walk you through it

Posted 6/11/13 3:12 PM
 

LIMomma
LIF Adolescent

Member since 6/12

523 total posts

Name:
Momma

Re: Excel Help

Vlookups work best with matching exact information (so for example if there are two Smiths this might not be your best option unless you concatenate the first and last name). If not first thing is to make sure the information is sorted alphabetically, and the same type of format (general, text)

Once you do those two steps in the column you want to match type

1. =VLOOKUP(
2. Click on the cell of the data in the spreadsheet you are in for what you want to match to the other spreadsheet...in this case last name
3. Type a comma
4. Click and highlight your data in the other sheet. This should be ALL the data that you want to bring over to the other spreadsheet. click from the first last name across to the last field that you want to bring over and all the way down to the last line.
5. Press F4 until you see dollar signs in front of your selection for both the column and the row. This keeps the selection from moving when you use it for the other columns you want to bring over
6. type a comma
7. Type the NUMBER of columns over from where you started for the information you want to bring back. For example on your selection if A is last name and C is street address type 3 (Excel can not calculate Letters - you have to change it to a number)
8. Type a comma
9. I recommend that you type false so that you only get exact matches (you can leave this out if you want to find the closest one)
10. Type a close parenthesis

you can copy and past this formula by highlighting the formula and copy and pasting it in all the other cells, then simply change the 3 after the comma to 4 or 5 or 6 depending on if you have a city column, state column, zip column.

You should also make sure there is no weird spacing either after the text or with certain names that would stop the data from coming over.

Hope this helps

PS -- The other way to do this (I do this when I have small amounts of data)

Highlight the data with the addresses in a color, copy and paste it into the other sheet. Sort in ascending order by last name -- you will see the ones that have addresses and you can delete the other rows.....

FM me with any questions if you want.

Message edited 2013-06-11 18:05:46.753000.

Posted 6/11/13 6:03 PM
 

DRMom
Two in Blue

Member since 5/05

20223 total posts

Name:
Melissa

Re: Excel Help

Posted by LIMomma

Vlookups work best with matching exact information (so for example if there are two Smiths this might not be your best option unless you concatenate the first and last name). If not first thing is to make sure the information is sorted alphabetically, and the same type of format (general, text)

Once you do those two steps in the column you want to match type

1. =VLOOKUP(
2. Click on the cell of the data in the spreadsheet you are in for what you want to match to the other spreadsheet...in this case last name
3. Type a comma
4. Click and highlight your data in the other sheet. This should be ALL the data that you want to bring over to the other spreadsheet. click from the first last name across to the last field that you want to bring over and all the way down to the last line.
5. Press F4 until you see dollar signs in front of your selection for both the column and the row. This keeps the selection from moving when you use it for the other columns you want to bring over
6. type a comma
7. Type the NUMBER of columns over from where you started for the information you want to bring back. For example on your selection if A is last name and C is street address type 3 (Excel can not calculate Letters - you have to change it to a number)
8. Type a comma
9. I recommend that you type false so that you only get exact matches (you can leave this out if you want to find the closest one)
10. Type a close parenthesis

you can copy and past this formula by highlighting the formula and copy and pasting it in all the other cells, then simply change the 3 after the comma to 4 or 5 or 6 depending on if you have a city column, state column, zip column.

You should also make sure there is no weird spacing either after the text or with certain names that would stop the data from coming over.

Hope this helps

PS -- The other way to do this (I do this when I have small amounts of data)

Highlight the data with the addresses in a color, copy and paste it into the other sheet. Sort in ascending order by last name -- you will see the ones that have addresses and you can delete the other rows.....

FM me with any questions if you want.



Thank you SO much. Thanks to everyone else that offered to help as well. I also got an FM from a lovely lady who said I could send to her. You are the best!

Posted 6/12/13 10:44 AM
 
 

Potentially Related Topics:

Topic Posted By Started Replies Forum
Anyone know how to transfer addresses from Microsoft excel.... jlk51496 1/29/12 2 Families Helping Families ™
Can a Word doc be merged into an Excel doc? NFT Smileyd17 7/9/09 1 Families Helping Families ™
Excel Wizards... nevermind! Lucky09 5/10/08 0 Families Helping Families ™
can you format a table in Word, the same way you can in Excel? randella 6/13/07 3 Families Helping Families ™
my excel freaking crashed- I lost 2 hours worth of work! Beth 1/24/07 8 Families Helping Families ™
where can i download microsoft excel for free?? w/no viruses? raken40 10/24/05 3 Families Helping Families ™