Share via

Microsoft Excel: Extracting certain data from table

Anonymous
2021-09-12T04:59:28+00:00

Good Evening,

I wanted to know if you can help me. I am using a PC and the latest version of Excel. I have a table that has Names in cell A1 with the names listed in A2-A13 and in B1, I have ID #, with ID numbers listed in B2-B13. From C1-L1, I have (Lesson 1, Lesson 2, Lesson 3 etc). Within C2-L13, I have random Letters such as C and MS. I want to either filter or extract only the MS data. Basically I want to find a filter or formula that looks in the table for "MS" and let me know the ID and "Lesson #" for each instance that it occurs. So if a row has more than 1 MS in it, it will give me the ID# and all lessons within that row in which MS occurs.

I tried Hlookup, Vlookup, countif, index and match, find and filter as well as auto filter with a criteria which either doesn't give me the end result I am looking for or it's giving me an error. Can you please point me in the right direction? I can draft a table with sample data if it will help. Thanks.

Microsoft 365 and Office | Excel | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments
Answer accepted by question author
  1. Anonymous
    2021-09-18T13:10:59+00:00

    Hi. Your 365 version can do this, (ie 1 line per lesson missed).

    However, if you are willing to do 1 line per student, then the following is pretty simple:

    B9 is just simply: =FILTER(Lessons,B2:K2="MS")

    and copy down.

    A single cell formula to return it all on multiple lines requires a little fancy worksheet function.

    Image

    1 person found this answer helpful.
    0 comments

14 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2021-09-12T12:32:35+00:00

    Hi Jen,

    Thank you so much for assisting me with this. The screenshot is below of some random made-up data.

    I would like to create a formula that will search for "MS" in the table and when it finds it, it will give me all instances of the corresponding ID# and Missed Session on page 2. Also, if more than one missed session is found within the table, I want it to be able to provide all instances of the missed session. The various instances to appear in different cells.

    So sheet 2 should have

    ID # Missed Session

    12345 Lesson 4

    23456 Lesson 9

    34567 Lesson 3

    45678 Lesson 1

    45678 Lesson 7

    26485 Lesson 4

    97853 Lesson 9

    0 comments
  2. Deleted

    This answer has been deleted due to a violation of our Code of Conduct. The answer was manually reported or identified through automated detection before action was taken. Please refer to our Code of Conduct for more information.

    Comments have been turned off. Learn more

  3. Deleted

    This answer has been deleted due to a violation of our Code of Conduct. The answer was manually reported or identified through automated detection before action was taken. Please refer to our Code of Conduct for more information.

    Comments have been turned off. Learn more

  4. Anonymous
    2021-09-12T06:00:43+00:00

    Hi Yve_R!

    I'm Jen, and I'd be happy to help you out with this issue.

    May you please provide a screenshot of your data (with sensitive information removed) and give us more details of what your expected result is or perhaps provide an example? Thanks in advance.

    Kind Regards,

    Jen :)

    0 comments