Jun 10 2022 06:58 AM - edited Jun 10 2022 07:15 AM
I run a weekly report, using a filtered dump of data from a company database. The database is maintained by a utility department with a large user forum; in response to user forum requests, data fields are added frequently to the database – several times monthly, and can be inserted anywhere in the dataset. My report displays a static set of fields; the data in the fields changes often. I also need to be able to split the dataset up into several groups of data. The number of rows can vary week over week, as an item drops off – sometimes before its due date, so the due date is not a field that can be used to filter out. I’m looking for a formula(s) that would read the headers, extract only the fields that I need, and also extract the fields I need into several tabs based on one of the values. In the example below, the first table is a representation of the tab with this week’s full dataset; the second and third tables are the results I’m seeking to have extracted. Have a formula(s) that would accomplish this? Thank you. I had tried Filter, but Filter will return all the fields in the original dataset. How can I return only the fields I need (in the example, columns A, B, C and E, but exclude D? And keeping in mind that next week's data may have a new field inserted before Due Date, and I won't know that field's label or content until it appears).
| A | B | C | D | E |
1 | Issue ID | Issue Title | Issue Source | Issue Owner | Due Date |
2 | 123 | YadaYada | Self-Identified | Bob | 5/31/2022 |
3 | 456 | BadaBing | Audit | Karen | 12/31/2023 |
4 | 789 | Yips | Self-Identified | Sam | 8/31/2022 |
5 | 1010 | ZipZilch | Audit | Fred | 7/15/2023 |
6 | 1212 | XRayGoggles | Risk Review | Jeff | 9/15/2025 |
| A | B | C | D |
1 | Issue ID | Issue Title | Issue Source | Due Date |
2 | 123 | YadaYada | Self-Identified | 5/31/2022 |
4 | 789 | Yips | Self-Identified | 8/31/2022 |
| A | B | C | D |
1 | Issue ID | Issue Title | Issue Source | Due Date |
3 | 456 | BadaBing | Audit | 12/31/2023 |
5 | 1010 | ZipZilch | Audit | 7/15/2023 |
Jun 10 2022 10:18 AM
SolutionThat could be
=LET( f, FILTER(Table1, Table1[Issue Source] = $A$9),
INDEX( f, SEQUENCE( ROWS(f) ), XMATCH( A10:D10, Table1[#Headers] ) ) )
for
Jun 10 2022 02:21 PM
Jun 11 2022 02:00 AM
@Ric_S_CLT , you are welcome. It shall work in different sheet, just play with references.
Jun 10 2022 10:18 AM
SolutionThat could be
=LET( f, FILTER(Table1, Table1[Issue Source] = $A$9),
INDEX( f, SEQUENCE( ROWS(f) ), XMATCH( A10:D10, Table1[#Headers] ) ) )
for