Copying data in excel

Microsoft Microsoft office excel 2007 ac...
April 22, 2010 at 06:41:02
Specs: Windows XP
Hi there, I've just been given this list of people and numbers on one sheet and the same list of people without the numbers on another sheet and need to copy over the numbers. It would be easy if it was in the same order but they are scattered about the list in different groups and listed more than once.

I am begging for any assistance I can get as it looks to be over 1,000 names I'd have to match up.

This is 1 list of names

Lewis, Sean
Bryant, Victoria G.
Woodruffe, Valinda R.
Reese, Whitney

The second list has numbers attached to each name

Barfield, Ruby 568,141
Bryant, Victoria 45,649
Woodruffe, Valinda 45,878
Reese, Whitney 5,979
Reese, Whitney 45,879

Thanks,
Sharon


See More: Copying data in excel

Report •

#1
April 22, 2010 at 06:45:34
Based on you example, I have a question:

Reese, Whitney shows up once in the list without numbers, but twice in the other list.

If that is the case, how do you plan to handle names that have more than one number associated with them but only appear once in the non-numbered list?


Report •

#2
April 22, 2010 at 06:51:19
Sorry that was my fault, the larger number was actually a different name.

Report •

#3
April 22, 2010 at 07:02:28
Hi,

Three other questions:
1. Are the names in a single cell. For example is 'Lewis, Sean' in one cell or are the last and first names in two different cells.
2. In the list with numbers, is the number in a separate cell or is it in the same cell just separated from the name by a space.
3. Are names identical. In your example you have 'Woodruffe, Valinda R.' and 'Woodruffe, Valinda'

It would be helpful to know which columns the data is in.

Regards


Report •

Related Solutions

#4
April 22, 2010 at 07:03:33
But you said:

It would be easy if it was in the same order but they are scattered about the list in different groups and listed more than once.

What is listed more than once?

How do the different groups enter into this?

I think we need a little more detail regarding your lists.


Report •

#5
April 22, 2010 at 07:13:03
Sorry if I'm not being too clear.

The names ARE in a single cell on both sheet, on the second sheet the numbers are on a differnet cell right next to the names.

First sheet, has the people listed 4 or 5 times in different lines through out, e.g. Woodruffe, Valinda is listed 4 times in sheet 1. I need to get her numbers: 45,878, in a cell next to her name on all 4 lines.

hope this helps, and thanks for your patience.


Report •

#6
April 22, 2010 at 07:37:00
You didn't address 2 of Humar's questions:

3. Are names identical. In your example you have 'Woodruffe, Valinda R.' and 'Woodruffe, Valinda'

It would be helpful to know which columns the data is in.

We don't want to offer solutions that have to be re-worked due to lack of details.


Report •

#7
April 22, 2010 at 07:43:17
Names are in column B and they are identical.
Numbers are on a differnet sheet, same doc in column C.

Sorry


Report •

#8
April 22, 2010 at 07:43:49
Assuming the names in both lists match exactly, VLOOKUP should work:

=VLOOKUP(Sheet2!B1,Sheet1!$B$1:$C$6,2,0)

This will lookup the name in Sheet2!B1 in the list of names on
Sheet1!B1:B6 and return the corresponding value from Column C.

Drag this down along side the list of names in Sheet2.

Obviously, you'll need adjust the ranges to fit your situation.


Report •

#9
April 22, 2010 at 07:53:48
Hi,

If a name on Sheet1 is not found (or is not an exact match), you will get a #NA in the cell.
As this is a one-off exercise, you can just correct these by hand. If there are lots of #NA's and the names do appear on both sheets, please post back.

Note that when adjusting the ranges in the formula, the range of cells with both names and numbers uses $ signs in the address but the address for the cell containing the name to be found does not have $ signs.

Once you have the numbers in column C, copy the numbers in column C, then with the same selection do a PasteSpecial... and select 'Values and number formats' and click OK.
This removes the VLOOKUP() formula and replaces it with the values.
(I am assuming that as the names were in column B, you put DerbyDad03's formula in column C)

If this works, I would still do some spot checks to ensure that nothing slipped out of position - particularly check the first and last name in each group, to see that the correct number has been copied.

Regards


Report •

#10
April 22, 2010 at 07:57:44
A posting tip:

Keep in mind that when posting in a forum such as this, no one but you can see your workbook. The more detail you provide, the easier it is for us to help.

We also don't know your level of expertise, so we don't know if you can modify a generic solution (such as mine) to fit your exact needs.

When you say things like "Numbers are on a differnet sheet" this best we can do is offer a generic formula referenceing sheets which may not match your workbook.

Even if you don't want to give us exact sheet names, etc. humor us and make up names (or use Sheet1 and Sheet2) so that we have something to work with.

Thanks for listening and come on back whenever you need more help.

DerbyDad03
Office Forum Moderator


Report •

#11
April 22, 2010 at 08:05:42
I keep getting Value.

Report •

#12
April 22, 2010 at 08:09:21
Please, Please, Please provide some details!

Read Response # 10

Sheet names, cell ranges, the exact formula you tried, etc.

We're working blind and you are not providing the detail we need to help further.


Report •

#13
April 22, 2010 at 10:13:18
I GOT IT!!!!!!!!!!

Thank you so much, I don't know what I was doing wrong the first time, but I tried it again and it worked.

You're an excel genius.
To figure this out with someone like me who can not explain herself correctly, GENIUS!!!


Report •

Ask Question