Forum Discussion

BrianJm86's avatar
BrianJm86
Copper Contributor
Mar 14, 2024

Provide column header from selection

Hi,

I am looking of a way to convert this vacation selection template from "x" selections to column headers but shift columns if more than one selection is made.

Have:

IDStoreDeptNameDec 31 - Jan 06Jan 07 - Jan 13Jan 14 - Jan 20Jan 21 - Jan 27Jan 28 - Feb 03Feb 04 - Feb 10Feb 11 - Feb 17Feb 18 - Feb 24Feb 25 - Mar 02Mar 03 - Mar 09Mar 10 - Mar 16Mar 17 - Mar 23Mar 24 - Mar 30Mar 31 - Apr 06Apr 07 - Apr 13Apr 14 - Apr 20Apr 21 - Apr 27Apr 28 - May 04May 05 - May 11May 12 - May 18May 19 - May 25May 26 - Jun 01Jun 02 - Jun 08Jun 09 - Jun 15Jun 16 - Jun 22Jun 23 -Jun 29Jun 30 - Jul 06Jul 07 - Jul 13Jul 14 - Jul 20Jul 21 - Jul 27Jul 28 - Aug 03Aug 04 - Aug 10Aug 11 - Aug 17Aug 18 - Aug 24Aug 25 - Aug 31Sep 01 - Sep 07Sep 08 - Sep 14Sep 15 - Sep 21Sep 22 - Sep 28Sept 29 - Oct 05Oct 06 - Oct 12Oct 13 - Oct 19Oct 20 - Oct 26Oct 27 - Nov 02Nov 03 - Nov 09Nov 10 - Nov 16Nov 17 - Nov 23Nov 24 - Nov 30Dec 01 - Dec 07Dec 08 - Dec 14Dec 15 - Dec 21Dec 22-Dec 28Dec 29 - Jan 04
300930926318StoreCR              x                            x         
300978152318StoreKA                               x                     
300922244318StoreAL      x                    x                         
300942085318StoreSW                        x                         x  
300880350318StoreBJ                  x        x                         
301027720318StoreGC                                                     
300409627318StoreDG                                xx                   
300576717318StoreJO              x                x         x           
300993701318StoreFT                                                 x   

I am looking to have it turn out like the below. I have to do this to 1,000+ rows and I am trying to not do it all by hand.

Want:

IDStoreDeptNameWeek 1Week 2Week 3Week 4
300930926318StoreCRApr 07 - Apr 13Oct 27 - Nov 02  
300978152318StoreKAAug 04 - Aug 10   
300922244318StoreALFeb 11 - Feb 17Jul 07 - Jul 13  
300942085318StoreSWJun 16 - Jun 22Dec 15 - Dec 21  
300880350318StoreBJMay 05 - May 11Jul 07 - Jul 13  
301027720318StoreGC    
300409627318StoreDGAug 11 - Aug 17Aug 18 - Aug 24  
300576717318StoreJOApr 07 - Apr 13Aug 04 - Aug 10Oct 13 - Oct 19 
300993701318StoreFTDec 08 - Dec 14   

 

It can be in a separate workbook or different sheet tab.

 

excel
Formulas and Functions
  • djclements's avatar
    djclements
    Mar 15, 2024

    BrianJm86 One possible dynamic array formula for MS365 would be:

     

    =LET(
    table, Master!A1:BE10,
    data, DROP(table, 1, 4),
    rowId, SEQUENCE(ROWS(data)),
    recordCount, BYROW(data, LAMBDA(r, COUNTIF(r, "x"))),
    maxCount, MAX(recordCount),
    newRows, maxCount-recordCount,
    cols, SEQUENCE(, maxCount),
    unpivot, HSTACK(TOCOL(IFS(recordCount>=cols, rowId), 2), TOCOL(IFS(data="x", DROP(TAKE(table, 1),, 4)), 2)),
    results, WRAPROWS(DROP(SORT(VSTACK(unpivot, EXPAND(TOCOL(IFS(newRows>=cols, rowId), 2),, 2, ""))),, 1), maxCount),
    HSTACK(TAKE(table,, 4), VSTACK("Week "&cols, results))
)

     

    It may seem like a lot but is relatively efficient and should perform quite well with 1,000+ rows of data. Adjust the range reference accordingly to select your entire table range (including the header row).

     

    Results

     

    Please see the attached workbook...

  • djclements's avatar
    djclements
    Bronze Contributor
    Mar 15, 2024

    BrianJm86 One possible dynamic array formula for MS365 would be:

     

    =LET(
    table, Master!A1:BE10,
    data, DROP(table, 1, 4),
    rowId, SEQUENCE(ROWS(data)),
    recordCount, BYROW(data, LAMBDA(r, COUNTIF(r, "x"))),
    maxCount, MAX(recordCount),
    newRows, maxCount-recordCount,
    cols, SEQUENCE(, maxCount),
    unpivot, HSTACK(TOCOL(IFS(recordCount>=cols, rowId), 2), TOCOL(IFS(data="x", DROP(TAKE(table, 1),, 4)), 2)),
    results, WRAPROWS(DROP(SORT(VSTACK(unpivot, EXPAND(TOCOL(IFS(newRows>=cols, rowId), 2),, 2, ""))),, 1), maxCount),
    HSTACK(TAKE(table,, 4), VSTACK("Week "&cols, results))
)

     

    It may seem like a lot but is relatively efficient and should perform quite well with 1,000+ rows of data. Adjust the range reference accordingly to select your entire table range (including the header row).

     

    Results

     

    Please see the attached workbook...

  • peiyezhu's avatar
    peiyezhu
    Bronze Contributor
    Mar 15, 2024

    BrianJm86 

    sql:

    create temp table aa as
    select rowid old_rowid, * from basic_to_one_dim;
    cli_one_dim~temp.aa~5;
    select ID,Store,Dept,Name,group_concat(属性,'</td><td>') `week1</td><td>week2</td><td>week3` from aaunion group by ID,Store,Dept,Name order by old_rowid;

     

     

Resources