How JustAnswer Works:

  • Ask an Expert
    Experts are full of valuable knowledge and are ready to help with any question. Credentials confirmed by a Fortune 500 verification firm.
  • Get a Professional Answer
    Via email, text message, or notification as you wait on our site.
    Ask follow up questions if you need to.
  • 100% Satisfaction Guarantee
    Rate the answer you receive.

Ask Jess M. Your Own Question

Jess M.
Jess M., Computer Support Specialist
Category: Microsoft Office
Satisfied Customers: 6322
Experience:  Computer Software Specialist for more than 10 years
49766785
Type Your Microsoft Office Question Here...
Jess M. is online now
A new question is answered every 9 seconds

I need some help with advances formulas in excel

Customer Question

I need some help with advances formulas in excel for mac
Submitted: 1 year ago.
Category: Microsoft Office
Customer: replied 1 year ago.
I have a list of 11,371 unique user names and a list of 30,282 user names with names and need a formula to match the names in the unique user list to that of the one with the emails
Expert:  Jess M. replied 1 year ago.

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.

Best regards,
Jess

Customer: replied 1 year ago.
here is the sample list
Customer: replied 1 year ago.
there is a second part to my question as well. Once the unique user list matches the user list I have a third list of numbers that I will need to correspond on a separate sheet within the same document. Is this also something you can help with?
Expert:  Jess M. replied 1 year ago.

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?

Expert:  Jess M. replied 1 year ago.

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.

Customer: replied 1 year ago.
I would like the names in the first column to match the name and email in the second column and remove the names and emails that do not match
Customer: replied 1 year ago.
What files would you like me to send? I apologize for the confusion
Expert:  Jess M. replied 1 year ago.

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?

Customer: replied 1 year ago.
that is my desired outcome with the corresponding unique user names
Expert:  Jess M. replied 1 year ago.

Please download the sample file and check the third column I created. Is that the format of your desired output?

http://filesxpress.com/d-85cfae3f

Customer: replied 1 year ago.
No I need the user name and email in column "C" to match the unique user name in column "A"
Expert:  Jess M. replied 1 year ago.

What about this sample output:

http://filesxpress.com/d-b54431d2

Customer: replied 1 year ago.
That will work for the purposes of what im doing.Now for my second question I have a master document that I need to add numbers into that correspond with the outcome to the first question. What information should I send for this?
Expert:  Jess M. replied 1 year ago.

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.

Customer: replied 1 year ago.
here is the master document as you will see in the last column on the "result" tab I need the numbers for the unique users to go there. Which is the 11,371
Expert:  Jess M. replied 1 year ago.

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.

Customer: replied 1 year ago.
Once the list of emails are extracted from the first question, the numbers I am referring to are in column "C" in the second tab under "user" that as of right now corresponds with the original list of unique user names (the 11,371). I need the numbers to go into the master document using the emails that you extracted. does that help?
Expert:  Jess M. replied 1 year ago.

You mean, you need the numbers to go into the users tab? Or you want to extract 2 columns: username and the number?

Customer: replied 1 year ago.
The number are currently under the users tab I need them to go into the result tab in the last column in correspondence with the unique user emails to extracted
Expert:  Jess M. replied 1 year ago.

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.

Customer: replied 1 year ago.
Is there any other information you need from me for now?
Expert:  Jess M. replied 1 year ago.

None so far, you have already provided all the needed information to accomplish the tasks.

Expert:  Jess M. replied 1 year ago.

Hi,

Thank you for your patience. I have completed the file and you can download it from this link:

http://filesxpress.com/d-7ed945d1

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!

Best regards,
Jess

Customer: replied 1 year ago.
None of the data you sent me is correct. You only spilt the names and emails but did not match them to the unique users list. And the uses number is ***** assigned to the first 11,371 users on the list
I have attached another document hopefully to explain more of what I need.
Under the "users" tab There is currently 4 columns
Column "A" is unique users
Column "B" is number of uses
Column "C" is where the extracted emails from Column "D" matching the unique users names in column "A" need to appear NAMED Unique user emails
Column D is the entire user listAfter the emails from Column "D" are matched to the unique user name from Column "A" in Column "C" in the users tab,in the results tab I need the Unique user emails from Column "C" from users tab to be sorted within the results tab aka: the master document.Let me know if this helps and if there is any confusion please
Expert:  Jess M. replied 1 year ago.

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.

Jess

Customer: replied 1 year ago.
that is fine, please move these at your leisure to help with the process
Expert:  Jess M. replied 1 year ago.

Thank you. I am completing the files now. Will be sending to you the final files in a while.

Customer: replied 1 year ago.
What kind of timeframe are we looking at so I can plan accordingly
Expert:  Jess M. replied 1 year ago.

30 minutes

Customer: replied 1 year ago.
Thank you!
Expert:  Jess M. replied 1 year ago.

I have completed the first file I made for you, here is the link:

http://filesxpress.com/d-653a76b0

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.

Best regards,

Jess

Customer: replied 1 year ago.
No this is fine. So the emails are now correct however the numbers still are not showing up correctly in the results tab. They are still only assigned to first 11,371 people and not throughout the entire document
Expert:  Jess M. replied 1 year ago.

Hi,

Thank you for writing back. All done, here is the final file:

http://filesxpress.com/d-0ac2d442

The formula for the USES numbers is now adjusted to cover the 11371 people only.

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!

Best regards,
Jess

Expert:  Jess M. replied 12 months ago.

Hi,

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.

Thank you.

Best regards,

Jess