Search code examples
excelpull-requestsummarize

Get the name of column header and export to different excel worksheet

I have to work with different checklists in different Excel workbooks in order to organize the progress.

I made a summary where I set a counter in the number of items that I am missing for different workbooks using the formula shown here:

=SUM(LEN(XXX)-LEN(SUBSTITUTE(XXX,"pending","")))/LEN("pending")

However, in order to not have to consult to each of the workbooks to find out which items are pending to complete, I am requesting some guidance to understand if it's possible to say that:

If item in cell is pending, extract the name to a different cell in my summary.

Here's an example workbook:

workbook example

Summary example

summary example

Thank you

Solution

  • I have Comment and a partial solution. I am ignoring the the cross workbook issues for simplicity. Get the basics working and deal with that later.

    In the formula

    =SUM(LEN(XXX)-LEN(SUBSTITUTE(XXX,"pending","")))/LEN("pending")

    the sum function is not used correctly. Sum add up all the numbers in a list and/or range of cells. You have a expression. It will work but is not needed.

    =(LEN(XXX)-LEN(SUBSTITUTE(XXX,"pending","")))/LEN("pending") should work.

    As for the sum of the item tagged with "pending" that can be done with a couple of extra rows that would be hidden in your final sheet.

    sample

    In the sample sheet. Rows 4 and 5 are intermidate calculation rows that will be hidden for final display. Row 4 has the same relative formula : =if(B3="pending",B2,"") for all cells. It copies the data in row 2 only if row 3 has the word "pending".

    The objective is to concatenate all of the non blank details into one cell. I chose a solution that would with Excel 2003+. (2019 could use TextJoin() as an alternative solution)

    in row 5 Cell b5 has

    =if(len(B4)>0,B4,"")

    Starting with C5 the rest have the relative formula

    =if(len(B5)>0,if(len(C4)>0,B5&"+"&C4,B5),C4)

    This progressively concatenates the cells together ignoring blank cells. F5 has the answer needed

    C8 has the

    =COUNTif(B3:F3,"pending")

    To count the number missing

    and D8 just points as F5 to pick up the results.

    For presentation purposes hide rows 4 and 5.