Menu

Copying Excel data from one sheet to another based upon contents of a cell

Report
Posts
2
Registration date
Monday July 8, 2019
Status
Member
Last seen
July 10, 2019
 -
TrowaD
Posts
2587
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
January 23, 2020
 -
Hi, I have a spreadsheet containing two sheets.

Sheet 1 contains the following:

Col A - username
Col B - Full Name
Col C - Email address
Col D - Phone number

Sheet 2 contains the following:

Col A - Active Directory group name
Col B - user name

I require a macro/formula to look at the username in Sheet 2, col B, find it in Sheet 1 Col A, and then copy the contents of Columns B, C and D back to Sheet 2, Columns C, D and E

The end result is that Sheet 2 will be populated with the AD Group Name, Username, Full Name, email addy and phone number.

Hope you can help.

Regards
Dittdott
See more

1 reply

Answer 1 / 1
Posts
2587
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
January 23, 2020
 390
Hi DittDott,

Excel has the VLOOKUP function for that.

Formula for Sheet2 C2:
=VLOOKUP(B2,Sheet1!$A$2:$D$6,2,0)

Formula for Sheet2 D2:
=VLOOKUP(B2,Sheet1!$A$2:$D$6,3,0)

Formula for Sheet2 E2:
=VLOOKUP(B2,Sheet1!$A$2:$D$6,4,0)

Adjust the range $A$2:$D$6 to match yours, check the results, then select C2, D2 and E2 and drag the formulas down as far as needed.

Best regards,
Trowa
Report
DittDott
Posts
2
Registration date
Monday July 8, 2019
Status
Member
Last seen
July 10, 2019
Awesome! thank you so much, bit of a numpty when it comes to Excel, I never use. Really appreciate the feedback. Have an awesome day Trowa :)
Report
TrowaD
Posts
2587
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
January 23, 2020
 390
Glad I could help!
Reply Ask a question