Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

linking data

0

Hello .

i am wondering .
is it possible to link certain data to each other ?
for example lets take this
we have personnumbers   and the names (each person has his own number
Personnumber         Name
123                        Els
248                        Jake
347                        Sven
457                        Lucy
499                        Chyntia
512                        Laura
534                        Kate
579                        Jack

try to imagine this is a list of 1000+ people (since thats what its in real)
is it possible that when i for example need again the info of Laura and i type that in the collum Name that he automaticly give the personnumber 512 in the collum of personnumber ? if so how is this possible or could this be done  .
thanx a lot

excel
posted by: Sylvia (rep: 2) on Mon Feb 4, 2019 at 1:50 pm
Answer
Discuss

Answers

0
Selected Answer

Hi Sylvia, it's possible to do that the other way around. You can have it so that the name will appear when you type in the correct person-number. 

The reason it won't work correctly to return the person-number when you type in the name is because names are not at all unique. So, when you type in Sylvia and there are 8 Sylvias, which one should it refer to in order to return the correct person-number?

If you want to input the name based on the person-number, you can use any lookup function such as the Vlookup function or Index/Match functions to do that.

posted by: don (rep: 1989) on Mon Feb 4, 2019 at 1:59 pm
Discuss

Discussion

I took this more as an example but indeed there are several same person but in the file in included the last name. my main problem is i have a deadline this week and i got tons of papers from 2 years. in the papers there is the data of their name. but i should also insert their number each time (the number i have to look up from another exel file) . so i kinda am wondering if there would be a faster way to work then simply type all names then look up all their numbers. I think VLOOKUP is the best way, so gonna try it out tommorow at my work . thanks for helping .
Sylvia (rep: 2) Feb 4, '19 at 2:04 pm
In that case, you can check to see if there are any names that are duplicates and if there aren't too many, then just use a regular Vlookup for everything and go back to check the few names that are duplicates and fix those by hand. One way to check duplicates is to select the column of names, go go the Home tab > Conditional Formatting > Highlight Cells Rules > Duplicate Values and choose the color for the highlight and hit OK. Then just sort that column by name and or color and you can view all of the duplicate names next to each other and quickly go through them.
don (rep: 1989) Feb 4, '19 at 6:26 pm
Add to Discussion
0

Assuming you have first name and last name in different columns the formula below would find the ID number where both criteria are mached. Note that it is an array formula which must be confirmed with SHIFT+CTRL+ENTER in place of the usual Enter only. Type the formula in the Formula Bar (above the worksheet grid), then hold down SHIFT+CTL and hit ENTER before releasing the two keys. The formula will be displayed surrounded by curly braces in the formula bar. If not entered correctly it will show #N/A as result.

=INDEX('Source Sheet'!$G:$G,MATCH(1,('Source Sheet'!$C:$C=$A2)*('Source Sheet'!$B:$B=$B2),0))

For the purpose of this formula I created a "Target" sheet with the first name in column A and the last name in column B. The formula is designed to go into C2 of that same sheet and can be copied down. Its references to A2 and B2 are bolded above.

The "Source Sheet" is the sheet where the ID number exists. On that sheet I put the first name in column C, the last name in column B and the ID number in column G.

The formula ill extract the value from 'Source Sheet'!$G:$G where 'Source Sheet'!$C:$C=$A2 and 'Source Sheet'!$B:$B=$B2. Adjust the references as required.

posted by: Variatus (rep: 4889) on Tue Feb 5, 2019 at 12:12 am
Discuss


Answer the Question

You must create an account to use the forum. Create an Account or Login