• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Finding multiple cells from a previous column

Steve Morris

New Member
The goal is to find all of the employees from the first column that are associated with a city once a city is entered in a cell below the original chart. This example manually shows all of the employees associated with Memphis from the first two columns. I want this to show them in descending order as the city is changed. The spreadsheet is enclosed along with the specific problem definition in the first tab.

5/6/20225/13/2022
Employee 1MemphisMemphis
Employee 2NashvilleNashville
Employee 3LocalKnoxville
Employee 4NashvilleNashville
Employee 5NashvilleLocal
Employee 6KnoxvilleAtlanta
Employee 7LocalLocal
Employee 8AtlantaKnoxville
Employee 9ChattanoogaChattanooga
Employee 10ChattanoogaLocal
Employee 11ChattanoogaChattanooga
Employee 12MemphisAtlanta
Employee 13MemphisChattanooga
Employee 14MemphisKnoxville
Employee 15AtlantaAtlanta
CityDateDate
Memphis5/20/20225/7/2022
EmployeeEmployee
Employee 1Employee 1
Employee 12
Employee 13
Employee 14
 

Attachments

  • Employee Allocation Help_05 2022.xlsx
    12.6 KB · Views: 5
using power query, I was able to achieve what you are looking for, but with a slightly different presentation. I created a parameter query. Change the value in the combo box at A19. Click on Data-->Refresh All and your result will update and present starting in B31. You can examine the Power query by clicking on Data-->Queries and Connections -->Edit in the attached.
 

Attachments

  • Employee Allocation Help_05 2022.xlsx
    22.8 KB · Views: 1
In cell B22:
Code:
=FILTER($A$2:$A$16,INDEX($B$2:$G$16,0,MATCH(B$19,$B$1:$G$1,0))=$A$19,"None")
copy across to E22. No copying down! Make sure there's plenty of space below the formula for the results to spill down to.
 

Attachments

  • Chandoo48066Employee Allocation Help_05 2022.xlsx
    13.4 KB · Views: 4
That's right, but there has to be a date in cell F19 which matches one of the dates in B1:G1.
 
Back
Top