SOLVED

Subset of data

New Contributor

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

 

3 Replies
best response confirmed by Ric_S_CLT (New Contributor)
Solution

@Ric_S_CLT 

That could be

=LET( f, FILTER(Table1, Table1[Issue Source] = $A$9),
  INDEX( f, SEQUENCE( ROWS(f) ), XMATCH( A10:D10, Table1[#Headers] ) ) )

for

image.png

Nice, thank you! That worked. Had to play with it for a bit - didn't seem to want to work when I started the new table in a different tab, but did work when I started the new table in the same tab and then cut-and-pasted into a new tab. Thanks again!

@Ric_S_CLT , you are welcome. It shall work in different sheet, just play with references.