excel - How can I include column headers from another column into a Filtered list? - Stack Overflow

admin管理员组

文章数量:1391987

Overview:

I have an Excel spreadsheet that contains three primary columns that will be utilized to create a main list:

• column E - IN UNIT: Y/N
• column F - COMMON: Y/N
• column G - Product: List of product items

I'm attempting to group the list of items by where IN UNIT = Y, and also if COMMON = Y. I have drafted a formula that contains 2 Filters and stacks the result set vertically.

What I'm struggling with is how can I also include the column headers (IN UNIT and COMMON) to the filtered result set? An example of the desired result is listed below:

=VSTACK(FILTER(Reference!G2:G378, Reference!E2:E378 = "y", Reference!G2:G378),FILTER(Reference!G2:G378, Reference!F2:F378 = "y", Reference!G2:G378))

Further guidance is much appreciated. Thanks

Overview:

I have an Excel spreadsheet that contains three primary columns that will be utilized to create a main list:

• column E - IN UNIT: Y/N
• column F - COMMON: Y/N
• column G - Product: List of product items

I'm attempting to group the list of items by where IN UNIT = Y, and also if COMMON = Y. I have drafted a formula that contains 2 Filters and stacks the result set vertically.

What I'm struggling with is how can I also include the column headers (IN UNIT and COMMON) to the filtered result set? An example of the desired result is listed below:

=VSTACK(FILTER(Reference!G2:G378, Reference!E2:E378 = "y", Reference!G2:G378),FILTER(Reference!G2:G378, Reference!F2:F378 = "y", Reference!G2:G378))

Further guidance is much appreciated. Thanks

• excel
• excel-formula

Share Improve this question Follow asked Mar 16 at 20:32 Jarvis DavisJarvis Davis 14166 bronze badges 1

• 4 So you mean =VSTACK(E1,FILTER(Reference!G2:G378, Reference!E2:E378 = "y"),F1,FILTER(Reference!G2:G378, Reference!F2:F378 = "y")) ? – P.b Commented Mar 16 at 21:09

Add a comment  | 

1 Answer 1

Sorted by: Reset to default 1

I am not sure what exactly you need, maybe is something like this:

Assuming that your data is in table named Table1 with three columns:)

Formula under title Products where [IN UNIT] = "y" or [COMMON] = "y":
=FILTER(Table2[Product],(Table1[IN UNIT]="y")+(Table1[COMMON]="y"))

Formula under title Products where [IN UNIT] = "y" or [COMMON] = "y":
=FILTER(Table2[Product],(Table1[IN UNIT]="y")*(Table1[COMMON]="y"))

Description:

Formula Filter(SelectionRange, CriteriaRange) returns values from a list of values in SelectionRange where element with same index in CriteriaRange = 1 (criteria is matched.
By putting criteria in brackets, you can use multiple criteria and combine them with logical AND or logical OR operators, which are, in case of values 0 or 1, same as multiplication for AND , addition for OR.
Just be sure that each list has the same number of elements.

本文标签： excelHow can I include column headers from another column into a Filtered listStack Overflow