Return Header Data for first consecutive cell

  • Hello,


    I am working on a project and I am looking to identify consecutive months of sales. I have been able to pull in the data need by using the following formula for months of consecutive sales: {=MAX(FREQUENCY(IF(U126:AF126>0,COLUMN(U126:AF126)),IF(U126:AF126=0,COLUMN(U126:AF126))))}


    What I would like to do next is in the cell to the right of this formula I would like to pull in the header information from the first consecutive cell. I have an example below from my data set. The far right column I have manually populated the data but would like to run a formula to pull the data in. My data set is fairly large. - Thank you very much.


    [TABLE="border: 0, cellpadding: 0, cellspacing: 0"]

    [tr]


    [TD="width: 117"] [/TD]
    [TD="width: 64"]2017[/TD]
    [TD="width: 64"]2017[/TD]
    [TD="width: 64"]2017[/TD]
    [TD="width: 64"]2017[/TD]
    [TD="width: 64"]2017[/TD]
    [TD="width: 64"]2018[/TD]
    [TD="width: 64"]2018[/TD]
    [TD="width: 64"]2018[/TD]
    [TD="width: 64"]2018[/TD]
    [TD="width: 64"]2018[/TD]
    [TD="width: 64"]2018[/TD]
    [TD="width: 64"]2018[/TD]
    [TD="width: 127"] [/TD]
    [TD="width: 64"] [/TD]

    [/tr]


    [tr]


    [td][/td]


    [td]

    August

    [/td]


    [td]

    September

    [/td]


    [td]

    October

    [/td]


    [td]

    November

    [/td]


    [td]

    December

    [/td]


    [td]

    January

    [/td]


    [td]

    February

    [/td]


    [td]

    March

    [/td]


    [td]

    April

    [/td]


    [td]

    May

    [/td]


    [td]

    June

    [/td]


    [td]

    July

    [/td]


    [td][/td]


    [td][/td]


    [/tr]


    [tr]


    [td]

    Material

    [/td]


    [td]

    20

    [/td]


    [td]

    21

    [/td]


    [td]

    22

    [/td]


    [td]

    23

    [/td]


    [td]

    24

    [/td]


    [td]

    25

    [/td]


    [td]

    26

    [/td]


    [td]

    27

    [/td]


    [td]

    28

    [/td]


    [td]

    29

    [/td]


    [td]

    30

    [/td]


    [td]

    31

    [/td]


    [TD="width: 127"]Frequency of consecutive Sales[/TD]

    [td]

    Need to Pop this Cell

    [/td]


    [/tr]


    [tr]


    [td]

    97077.01.050.6

    [/td]


    [td][/td]


    [TD="align: right"]10824[/TD]

    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [TD="align: right"]1320[/TD]
    [TD="align: right"]792[/TD]
    [TD="align: right"]2376[/TD]
    [TD="align: right"]1857[/TD]
    [TD="align: right"]528[/TD]
    [TD="align: right"]5[/TD]

    [td]

    March

    [/td]


    [/tr]


    [tr]


    [td]

    97602.08.108.0

    [/td]


    [TD="align: right"]108[/TD]
    [TD="align: right"]162[/TD]
    [TD="align: right"]216[/TD]
    [TD="align: right"]108[/TD]

    [td][/td]


    [TD="align: right"]162[/TD]

    [td][/td]


    [TD="align: right"]594[/TD]
    [TD="align: right"]108[/TD]
    [TD="align: right"]1080[/TD]
    [TD="align: right"]324[/TD]
    [TD="align: right"]378[/TD]
    [TD="align: right"]5[/TD]

    [td]

    March

    [/td]


    [/tr]


    [tr]


    [td]

    97120.08.192.0

    [/td]


    [td][/td]


    [TD="align: right"]78[/TD]

    [td][/td]


    [TD="align: right"]156[/TD]

    [td][/td]


    [td][/td]


    [td][/td]


    [TD="align: right"]78[/TD]
    [TD="align: right"]234[/TD]
    [TD="align: right"]156[/TD]
    [TD="align: right"]78[/TD]
    [TD="align: right"]156[/TD]
    [TD="align: right"]5[/TD]

    [td]

    March

    [/td]


    [/tr]


    [tr]


    [td]

    97202.01.102.0

    [/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [TD="align: right"]270[/TD]
    [TD="align: right"]108[/TD]
    [TD="align: right"]216[/TD]
    [TD="align: right"]216[/TD]
    [TD="align: right"]108[/TD]
    [TD="align: right"]5[/TD]

    [td]

    March

    [/td]


    [/tr]


    [tr]


    [td]

    97202.01.078.0

    [/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [TD="align: right"]216[/TD]
    [TD="align: right"]162[/TD]
    [TD="align: right"]162[/TD]
    [TD="align: right"]486[/TD]
    [TD="align: right"]108[/TD]
    [TD="align: right"]5[/TD]

    [td]

    March

    [/td]


    [/tr]


    [tr]


    [td]

    97123.01.071.5

    [/td]


    [TD="align: right"]696[/TD]

    [td][/td]


    [TD="align: right"]1044[/TD]
    [TD="align: right"]348[/TD]
    [TD="align: right"]1044[/TD]
    [TD="align: right"]1218[/TD]
    [TD="align: right"]3480[/TD]

    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [TD="align: right"]1914[/TD]
    [TD="align: right"]5[/TD]

    [td]

    October

    [/td]


    [/tr]


    [/TABLE]

  • Hello,


    My recommendation would be to attach your workbook along with your next message ...:wink:


    For any potential contributor willing to give you a hand ... this would simplify his work ...:smile:

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!