Microsoft Office Forums Match two columns with display name and output with user name

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 02-14-2019, 05:11 AM
Borut2019 Borut2019 is offline Match two columns with display name and output with user name Windows 10 Match two columns with display name and output with user name Office 2016
Novice
Match two columns with display name and output with user name
 
Join Date: Feb 2019
Posts: 5
Borut2019 is on a distinguished road
Default Match two columns with display name and output with user name

Hi ,
I have column A with thousand User display names. There a lot of duplicate user names. But I do not want to delete them..
I need to match column A with correct user login name.
I have added two columns, or I can use second sheet, where I have column B with user logon name and column C with display name.
I would like in column D add the values of column B. when Value in column C match with value of column A. So if there is a match between John Smith in column A with column C: John Smith , should in column D be value of his user name that match Jsmith12.
I have add a sample table.


column A column B column C column D
John Smith Jsmith12 John Smith Jsmith12
John Smith Jkrem John Krem Jsmith12
John Krem MSibher1 Mathiu Siber Jkrem
Mathiu Siber TMAris Thomas Maris MSibher1
Thomas Maris AZimer1 Anton Zimer TMAris
Thomas Maris LKurz Lilian Kurz TMAris
Thomas Maris TMAris
* * *
* * *
Anton Zimer AZimer1
Lilian Kurz LKurz
Lilian Kurz LKurz

Thank you.
Br,
Borut
Reply With Quote
  #2  
Old 02-14-2019, 09:46 AM
ArviLaanemets ArviLaanemets is offline Match two columns with display name and output with user name Windows 8 Match two columns with display name and output with user name Office 2016
Expert
 
Join Date: May 2017
Posts: 463
ArviLaanemets will become famous soon enoughArviLaanemets will become famous soon enough
Default

Honestly, I didn't understan anything!
Quote:
John Smith Jsmith12 John Smith Jsmith12
OK. You have some user John Smith, and his username is Jsmith12.
Quote:
John Smith Jkrem John Krem Jsmith12
Now it starts to be confusing! Does (or did sometime) same John Smith have username Jkrem? And another user with name John Krem does (did) also have username Jsmith12?

And what do you want to achieve at all?
Reply With Quote
  #3  
Old 02-15-2019, 01:07 AM
Borut2019 Borut2019 is offline Match two columns with display name and output with user name Windows 10 Match two columns with display name and output with user name Office 2016
Novice
Match two columns with display name and output with user name
 
Join Date: Feb 2019
Posts: 5
Borut2019 is on a distinguished road
Default

Hi,
I have two reports.
In one csv report I have column A with User Name : John Smith. In column B i have some random strings…
I have second report of all users. Column A : user logon name : Jsmith12 and in column B I have display name : John Smith.
I have no control over report structure….

Problem is, that I can do import of first report only with user name and not display name.
So what I basically want to achieve is, that I insert in column B in first report, User logon name from second report. I will compare both display name and if they match, I need to insert user name. I can copy column A and B from second report to first report and use simple lookup, but it does not work.
Br,
Borut
Attached Files
File Type: xlsx sample.xlsx (8.9 KB, 3 views)
Reply With Quote
  #4  
Old 02-15-2019, 04:05 AM
ArviLaanemets ArviLaanemets is offline Match two columns with display name and output with user name Windows 8 Match two columns with display name and output with user name Office 2016
Expert
 
Join Date: May 2017
Posts: 463
ArviLaanemets will become famous soon enoughArviLaanemets will become famous soon enough
Default

You can't put formulas into csv-file. It is a text file!

You need a separate Excel file, and you copy data from scv-file into Excel file, or for every export you convert (with Save As) the csv-file to excel file, and then add formulas into it.

In attached file is an example, how you can read info from another table (Users) based on some ID field (PersonŃame). You have to be aware, that;

The 1st occurrence of key field (PersonName) in lookup table (Users) is referred. When you have several John Smith's (When you are from English-speaking country, you have more than 10 users, and there really is user John Smith, then there is very high probability you have several different persons with name John Smith ), then you get for all of them same result (Username) returned;

The searched ID's must match absolutely! A single typing error, and no match is found;

In lookup range (users table), the leftmost column must be the ID you use to look for (PersonName). 3rd parameter of formula indicates the number of column with return value in lookup table (2nd column in users table is Username). 0 as 4th parameter means, that exact match is wanted. Other values for this parameter assume, that the lookup table is ordered, and that you know what you do.
Attached Files
File Type: xlsx VLookupExample.xlsx (11.3 KB, 3 views)
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Match two columns with display name and output with user name Need to add columns that match other column cell size mmurphy613 Word Tables 4 07-19-2017 04:07 PM
Match two columns with display name and output with user name Compare cells in 2 columns in separate WBs and return data in Col 38-40 if there is match krispykreme Excel Programming 1 09-09-2016 11:25 AM
Match two columns with display name and output with user name How to compare two columns and match data Neydo222 Excel 2 09-12-2015 02:49 AM
Match two columns with display name and output with user name match two columns of data gbaker Excel 1 08-03-2014 04:01 PM
Match two columns with display name and output with user name Match information from 2 different columns gbaker Excel 2 08-18-2012 06:07 PM


All times are GMT -7. The time now is 04:28 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2019, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2019 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft