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

Closed
DittDott Posts 2 Registration date Monday July 8, 2019 Status Member Last seen July 10, 2019 - Jul 8, 2019 at 10:51 PM
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 - Jul 11, 2019 at 11:42 AM
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

1 response

TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 552
Jul 9, 2019 at 11:45 AM
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
0
DittDott Posts 2 Registration date Monday July 8, 2019 Status Member Last seen July 10, 2019
Jul 10, 2019 at 06:13 PM
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 :)
0
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 552
Jul 11, 2019 at 11:42 AM
Glad I could help!
0