Hi, welcome and thank you for your question. My name is ***** ***** I am glad to assist you today.
Can you please send me a copy or dummy of your 2 lists? Just do not send the entire lists, just a sample data showing the structure and few data. For instance, you have 11,371 unique data, and 30,282 non-unique data. SAVE a copy of these lists, and in those copies, leave like 20 or 50 data in the unique list, and 50 or 100 data in the non-unique list. That way, I can build a formula to do your requirements that will work on the original bigger lists.
You can attach the files here using the paper clip icon, or upload them in http://filesXpress.com and then give me the short download links.Please let me know by replying to me here so that I can help you further.
Thank you. Where is the non-unique list? Or do you want to just extract the emails in column B where names and emails are mixed?
Please provide the necessary files so that I can understand your requirements better. If you can provide a SAMPLE OUTPUT in those files, the better.
In a formula, you cannot remove the contents of the column. I suggest then that we leave those data in the 2 columns and just save your desired output in a separate sheet or column.
So your desired output is a single column with mixed name and email?
Please download the sample file and check the third column I created. Is that the format of your desired output?
What about this sample output:
If you cannot send the master document, please capture a screen shot and attach it here and show me what do you want to accomplish.
What numbers are you referring to? The last row is NOT 11,371 but 29,505. Please tell me the cell where you want the number to be stored.
You mean, you need the numbers to go into the users tab? Or you want to extract 2 columns: username and the number?
Thank you for that clarification. This is a complex scenario and I will try my best to come up with a solution using a formula. Otherwise, a macro will be needed and I will let you know.
None so far, you have already provided all the needed information to accomplish the tasks.
Thank you for your patience. I have completed the file and you can download it from this link:
I directly used the USERS tab, but I created a copy of the tab and named it USERS_MODIFIED. There, I added the necessary "helper columns". These columns are needed to hold data that Excel needs in order to accomplish your desired results.
The first helper column is "Extracted Names". This stores the NAMES in the column containing the name+email mixture. This will be used in the needed lookup or comparison.
The second helper column is "Extracted Emails".
I already added the numbers in the USES column in the RESULTS tab.
I hope that helped.Please remember to rate my service positively (3-5 stars/faces) if this helped. Tips are always highly appreciated!
If you need further assistance, please do not rate me negatively with 1 or 2 faces. Instead, please reply to me so that I can help you further.
Thank you for writing back and I am sorry to miss that part. I can complete the file I gave you if you want since it is only 1 step to go. If you want me to use the latest file you sent me, I still need to create and use "helper columns" and even REARRANGE them for lookup purposes. I will try to do both files for you.
Please give me a moment.
Thank you. I am completing the files now. Will be sending to you the final files in a while.
I have completed the first file I made for you, here is the link:
I added the UNIQUE emails column as this is the missing data, and updated the USES column in results.
The helper columns I created are needed to accomplish your requirements. If they are unnecessary or messy to look, you can "hide" those helper columns but their "order" is needed to be retained for the lookup functions to work.
Do you still want me to complete the last file you sent me?
I hope that helped.
Thank you for writing back. All done, here is the final file:
The formula for the USES numbers is now adjusted to cover the 11371 people only.
How is it going? Were you able to use the formula I provided in the revised master document file that I sent you? I am just following up with you so I may know if you still need help with the problem.