troubleshooting Question

Extract certain items from one list to create another list in Excel

Avatar of WeThotUWasAToad
WeThotUWasAToad asked on
Microsoft OfficeMicrosoft Excel
6 Comments1 Solution300 ViewsLast Modified:
Hello,

Is there a way, in Excel, to extract certain items from one list and display them in order in a different list?

For example, suppose you have a spreadsheet in which column B contains a list of names and column C contains some designation for each name — in this case, a letter between A & E as shown here:

2017-10-26a_EE.png
Is there a way for Excel to automatically populate a list which includes each name from column B for which the corresponding column C letter matches a letter entered manually in the box at F2? In other words, suppose the letter "A" is manually entered in cell F2 as shown in the next screenshot. What formula will result in the list (column F) of the names from column B with a corresponding "A" in column C?

2017-10-26b_EE.png
And then in another example, if the manually-entered letter is "B", the resulting column F list should change as follows:

2017-10-26d_EE.png
So the goal is not only to display the names matching the entered criteria, but display them in a list populated from top to bottom (with no blank rows) and in the same order as those names appear in the original (column B) list. The latter is illustrated in the next screenshot where changing the letter for Grace (yellow cell C15) to "B" causes her name to be inserted in its proper position (between Ashley & Sarah) in the results list (column F).

2017-10-26g.png
The desired functionality is identical to applying a filter to column C for the manually-entered letter (next screenshot below) to obtain the list of matching names as the new list (final screenshot below).

2017-10-26h.png
2017-10-26i.png
Thanks

File attached
2017-10-26_EE.xlsx
ASKER CERTIFIED SOLUTION
Alan
Consultant

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Join our community to see this answer!
Unlock 1 Answer and 6 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 6 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros