Create Contiguous Header Ranges From 1 Values in Excel
Question details
Create start-to-end header ranges for each consecutive group of columns whose values in the second row equal 1.
- Product
- Microsoft Excel for Microsoft 365
- Device & OS
- not provided
- Scenario
- The first row contains column headers and the second row contains 1 values that identify selected columns. The user needs each uninterrupted sequence of selected columns returned as one range.
- Observed behavior
- The desired result is a spilled list of text ranges using the first and last header in every consecutive group of columns marked with 1.
Arrange the headers in the first row and the 1 or non-1 markers directly below them in the second row. Use Microsoft 365 because this method requires dynamic-array functions such as LET, FILTER, SCAN, HSTACK, and BYROW.
Use a Dynamic-Array Formula to Group Consecutive Columns Marked 1
Use this method when your headers and marker values are in two horizontal rows and you want Excel to return one start-to-end range for every uninterrupted sequence of 1 values.
The formula filters the column positions where the second row equals 1, assigns a group number whenever the next selected position is no longer adjacent, and joins the first and last headers in each group.
Place the headers in row 1 and the corresponding marker values in row 2. For example, use A1:J1 for headers and A2:J2 for values.
Select an empty cell outside the source range, such as L1, and enter: =LET(rng,A1:J2,filtered,FILTER(COLUMN(rng),INDEX(rng,2,)=1),numbering,HSTACK(0,DROP(SCAN(0,SEQUENCE(,COLUMNS(filtered)),LAMBDA(ini,arr,IF(INDEX(filtered,,arr+1)<>INDEX(filtered,,arr)+1,ini+1,ini))),,-1)),BYROW(UNIQUE(TOCOL(numbering)),LAMBDA(v,TEXTJOIN(" - ",,INDEX(INDEX(rng,1,),SMALL(IF(numbering=v,filtered),1)),INDEX(INDEX(rng,1,),LARGE(IF(numbering=v,filtered),1)))))))
Replace A1:J2 in the rng definition with the full two-row range that contains your own headers and marker values, then press Enter to let the results spill downward.
Create Consecutive Header Ranges in WPS Spreadsheets
WPS Spreadsheets provides a familiar spreadsheet workspace for organizing header-and-marker data and using modern formulas to identify consecutive selections. Open the workbook, enter the grouping formula in an empty output area, and let the results spill into a clear list of header ranges.
- 1. Open the workbook in WPS Spreadsheets: Launch WPS Office, choose Spreadsheets, and open the Excel workbook containing the two-row header and marker data.
- 2. Confirm the data layout: Verify that the header labels are in the first row of the selected range and the 1 markers are in the second row.
- 3. Select an output cell: Click an empty cell outside the source data, such as L1, so the returned range list has enough blank cells to spill.
- 4. Enter the grouping formula: Use the formula bar to enter the LET-based contiguous-range formula, updating the A1:J2 reference to match your data range, then press Enter.
Frequently Asked Questions
What does the formula return for a single column marked with 1?
A group containing only one selected column returns that header as both ends of the range, such as "March - March." You can further customize the formula if you prefer to show only "March."
Can I use a range wider than A1:J2?
Yes. Change A1:J2 in the LET formula to your complete two-row source range, such as B1:AZ2. The first row must contain headers and the second row must contain the marker values.
Why are two columns marked 1 shown as separate ranges?
They are separate when their worksheet columns are not adjacent. For example, a 1 in column B and a 1 in column D are separated by column C, so the formula creates two groups.
Does this formula work in older versions of Excel?
No. It relies on Microsoft 365 dynamic-array functions including LET, FILTER, HSTACK, DROP, SCAN, BYROW, and TOCOL. Use Microsoft 365 or another spreadsheet version that supports these functions.
