Help with a Formula

[cgendron]

Hi, i need help with a formula. It might sounds confusing what i'm asking for so sorry if it doesn't make sense.

I need a formula that will basically copy data from one spreadsheet to another but only if it matches certain fields.

I need something that states If column c (in worksheet A) matches column c (in worksheet B) then copy the data from column D and E (from worksheet B) to column D and E (in worksheet A). I then need to email this spreadsheet but i don't want to send out Worksheet B. If this type of formula is possible, how do i get the data to stick when i email worksheet A without all the formula fields showing my favorite error #VALUE!

thanks

 

[Simon Lloyd]

You can make the values static by clicking Ctrl+A then right click copy, right click paste special>values, data can only be "Copied to" manually or by VBA using a formula does not copy it, it merely displays a result.

 

[cgendron]

Yes of course, copying and paste values... I do that fairly often but didn't think of it until you mentioned it.
Thanks!
Now to get my confusing formula figured out

 

[Simon Lloyd]

Put this in D1 on sheet1 =IF(ISERROR(VLOOKUP(C1,Sheet2!$C$1:$E$16,1,FALSE)),"",VLOOKUP(C1,Sheet2!$C$1:$E$16,2,FALSE))

And this in E1 sheet 1 =IF(ISERROR(VLOOKUP(C1,Sheet2!$C$1:$E$16,1,FALSE)),"",VLOOKUP(C1,Sheet2!$C$1:$E$16,3,FALSE))

Adjust the $E$16 in each to encompass your entire range in column E sheet 2 and then copy the formula down

 

[cgendron]

<TABLE style="WIDTH: 361pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=481><COLGROUP><COL style="WIDTH: 222pt; mso-width-source: userset; mso-width-alt: 10825" width=296><COL style="WIDTH: 65pt; mso-width-source: userset; mso-width-alt: 3181" width=87><COL style="WIDTH: 74pt; mso-width-source: userset; mso-width-alt: 3584" width=98><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 222pt; HEIGHT: 15pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" id=td_post_2682787 class=xl63 height=20 width=296>I think that might work...here is what i'm looking at to do. On sheet one I have people's names in column C...below is an example...made up name of course taking up 4 rows. They can encompass multiple rows, depending on how much data is under them. I then have column D and E currently empty but need to fill column D with their SS# and column E with a date.


Shmoe, Joe
</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 65pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl63 width=87></TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 74pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl63 width=98></TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl63 height=20></TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl63></TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl63></TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl63 height=20></TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl63></TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl63></TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl63 height=20>Totals for Shmoe, Joe (2)</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl63></TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl63></TD></TR></TBODY></TABLE>

On sheet 2 i have the SSN # in column D and date in column E.... like this
123-45-6789 3-4-11
there is only one row for each person
I don't want to manually copy and paste each row, one at a time, from sheet 2 to sheet 1. I have about 12,000 rows of this data...usually takes me two days.

The other problem is, some people on sheet 1 may not exist on sheet 2 so i need some formula to match the name in column c sheet 1 to name in column c sheet 2 and then import the SSN and date to the appropriate fields.
thanks for your time!

 

[Simon Lloyd]

The vlookup does what you ask, naturally it doesn't IMPORT or COPY anything but displays the results you seem to request, did you try them?, the formula takes in to account that some names may not exist and will show a blank if there is no match.

 

[cgendron]

ok thank you. I did try it and i just get blanks...i may have to adjust/play around with the data ranges.
thanks!

 

[Simon Lloyd]

If you got blanks thats because whatever is in say C1 of sheet 1 isn't in column C of sheet 2, check your spellings, and whether there are leading or trailing spaces .

To check for extra spaces (not remove them yet) put this in a blank column on sheet2 =LEN(C1) and copy down, then look at the number of characters it says, does it match with what you can physically see in the cell? i.e C1

Or use this: =Len(C1) & " - Sheet1 Characters " & Len(Sheet1!C1) to show if both sheets match in length

 

[cgendron]

Well, i got the formula straight for the dates to copy over to sheet 1 column E, but in the SSN field, column D, i just get random SSN numbers that don't match what's on sheet 2. hmm

 

[Simon Lloyd]

It has to be something up with your data, one thing to remember is that vlookup will only return the first match so if you have multiple of the same name you will only ever get the first!