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

Honestly, I didn't understan anything!
OK. You have some user John Smith, and his username is 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?

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

sample.xlsx (8.9 KB, 9 views)

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

VLookupExample.xlsx (11.3 KB, 9 views)