# Solved: Excel - Match and copy between two worksheets



## Tamizpa (Jun 5, 2007)

I need to move data from a second worksheet to the first. See attached example. The second worksheet will be changing weekly (the real data will be about 18000 records), so I'd like the formulas to be in place with minimum intervention. I need to pull the AgentSet off of the second worksheet (ServerDump) and put it in Customer Form (Type of Agent). I would like to say 'BaselineAgent' if it's true. I've tried different formulas and think I should be using vlookup but can't get it to work. Any suggestions or examples of coding I could use? Thanks!


----------



## slurpee55 (Oct 20, 2004)

=VLOOKUP(A5,'Server Dump'!A,4,0) seems to work - will you just have one instance of a machine name each time?


----------



## Tamizpa (Jun 5, 2007)

Thanks...but I couldnt' get it to work. Yes, there should only be on instance on each page of the machine name. I put =VLOOKUP(A5,'Server Dump'!A,4,0) in B5 on Customer Form and got the error #NAME?. Is that the correct place? Thanks.


----------



## slurpee55 (Oct 20, 2004)

that should read AcolonD,4,0 - the web site converts it to Asmiley (I'll try it in code after this)

```
=VLOOKUP(A5,'Server Dump'!A:D,4,0)
```


----------



## Tamizpa (Jun 5, 2007)

Thanks! It worked. I will now go and apply it to the big spreadsheet and see if it will work there. I guess I don't really understand what each coding means. Is there an easy way to explain it to me or documentation somehwere ... one of the guys here is going to bring me in his advanced excel manual. Thanks.


----------



## Tamizpa (Jun 5, 2007)

Thanks, it worked on the big spreadsheet. I was able to understand it when looking at the explaination online and change to meet what I needed on a bigger scale.


----------



## slurpee55 (Oct 20, 2004)

What version of Excel do you have? From 2003 on it explains, to some degree, what it expects as you enter formulas.
For this (fairly simple) formula, it breaks out like this:
=VLOOKUP (Vertical LOOKUP - as opposed to Horizontal LOOKUP [HLOOKUP] or plain LOOKUP)
(A5, - the target value to lookup in the data
'Server Dump'! - the page containing the data...if on the same page, this part is skipped
A : D, - the array of columns containing the value you are looking for and the value you want to return...in this case, Column A contains the value from A5 and Column D contains the value you want to return
4, - in the column array listed above, the column index number...in this case, since we want data from Column D - the 4th column in this array, you enter 4. If you had 3 instead of 4, it would return Licensed or Unlicensed
0) - this is an optional value. I almost always use it, however. It requires an exact match when you have 0 or FALSE here. For instance, if you had in A5 "TestMachine11" - which does not exist in the array you look in on the other page - it will return #N/A when you have the 0 to require an exact match. 
Without it, Excel looks for a "close" match and returns that value - in this case, it picks "TestMachine1" as being the closest and returns the value for that.
Oh, and I really have learned most of this just hanging around here....


----------



## Tamizpa (Jun 5, 2007)

Hi! Do you think you can help me again. On this same spreadsheet, I need a count of baselined desktops...my only problem is I need to figure out the formula to count the baseline desktops and subtract the agents that have been deleted. I can't just remove the deleted ones, cause I want my customer to know which agents have disappeared. Take a look at the formula in B2 and see if I'm on the right track. Thanks!


----------



## slurpee55 (Oct 20, 2004)

I am sure it is possible to do it in one formula, but I would just make a new column (I used G) in which I would combine desktops and baseline
=(E2&F2)
and then in Baseline Count I would enter
=COUNTIF(G2:G16,"BaselineAgent"&"Deleted Agent")
I'm sure there are fancier ways to do this, but, I just am a patch-work type...


----------



## Tamizpa (Jun 5, 2007)

That sort of what I ws toying around with when you replied...Thanks! In the Countif statement I only counted "BaselineAgent" cause that's the only ones I wanted to see and it came out correct! Thanks. This spreadsheet is getting ugly with all the tied together formulas!


----------



## slurpee55 (Oct 20, 2004)

Glad to help - one of the better Excel guys could probably have done it more neatly, but I muddle through....


----------

