Forum Discussion
How do I populate a worksheet/book from a master list that has thousands of duplicate values?
I need to create a parent/master sheet that populates other sheets, 90 to be exact, based on their 23-24 location. One sheet/book per distinct location. I'd prefer to populate separate workbooks, but can settle for separate sheets.
The original file has almost 70k rows. 23-24 (A) and 24-25 (C) contain 90 distinct locations. 23-24 Status (B) indicates whether the 23-24 location is their Primary or Secondary. ID# (D) will contain some duplicates because the people listed can belong to more than one location. Names (E/F) and 23-24 Room # (G) have numerous duplicate values.
In the example below, I want 'Master' to auto-populate '23-24 Location 1' using criteria from column A. If 23-24 (A) says 'Location 1' then I want that entire row to populate in the '23-24 Location 1' sheet.
XLOOKUPs don't like the duplicate values and will only populate the first row. I tried using INDEX(....(MATCH(....)) but ended up with the same result.
How do I get around the duplicate value component?
MASTER
| 23-24 | 23-24 Status | 24-25 | ID # | Last Name | First Name | 23-24 Room # |
| Location 1 | P | Location 2 | 11111111 | Burt | Burt | 4 |
| Location 2 | P | Location 1 | 11111112 | Bob | Bob | 3 |
| Location 1 | S | Location 2 | 11111113 | Bob | Burt | 5 |
| Location 2 | P | Location 1 | 11111114 | Burt | Bob | 1 |
| Location 1 | P | Location 2 | 11111115 | Ally | Ally | 6 |
| Location 2 | P | Location 1 | 11111116 | Curly | Ally | 4 |
| Location 1 | S | Location 2 | 11111117 | Bob | Curly | 3 |
| Location 2 | S | Location 1 | 11111111 | Burt | Burt | 4 |
23-24 Location 1
| 23-24 | 23-24 Status | 24-25 | ID # | Last Name | First Name | 23-24 Room # |
| Location 1 | ||||||
2 Replies
=FILTER(A4:G11,A4:A11="Location 1")
FILTER should work if i correctly understand what you want to do. FILTER works between sheets as well but for visualisation i've made an example within one worksheet.
- You are my hero, thank you!!
