Help with Formula

[tbcomputerguy]

I am trying to get a formula to work. How do i reference a change in cells. I am copying the range on the left (the date field is static..i fill that). when there is a change in the headings (A1,A3, A5) etc i would like to to show in N2, N4, N6 ETC. The blank rows on the right are rows i have deleted. I hope i have explained this well. So for instance on cells N6-N9, should all calculate to what is in A5. Likewise N13-14 should result in what is on A12. Follow me? I need the category with the respective dances in that category. There are 1100 hundred to do...to much to do manually.

 

[jayalaxmi]

Hi,

Can you please share the excel file so that it is easy to work.

Also show the expected results in the particular field so that it is easy to help you.

Thanks
Jaya

 

[tbcomputerguy]

Here you go..hope that helps

 

[GraH - Guido]

These kinds of data transformations are rather easy with Power Query (Get&Transform -> all done here with some mouse actions, no coding).
Sample attached.

 

[bosco_yip]

Formula solution way



In N2, array (CSE) formula copied down :

=IF(L2="","",INDEX(A$1:INDEX(A$1:A$1000,MATCH(2,INDEX(1/(B$1:B$1000=L2),0))),MATCH(2,1/(ISERROR(0+A$1:INDEX(A$1:A$10000,MATCH(L2,B$1:B$1000,0)))=TRUE))))

Regards
Bosco

 

[Peter Bartholomew]

I am not sure what formula this equates to since it is all embedded within named formulae.

First calculate a sequence of numbers derived from the row number, 'k':
= ROW(Timings)
The sequence numbers corresponding to header rows, 'headerRows' is determined by:
= IF( ISTEXT(Timings), k )
Look up the current row number within 'headerRows' to determine the relevant heading number 'p'
= LOOKUP( k, headerRows )
Finally use the pointer 'p' to return the relevant header for each timed item
= IF( ISNUMBER(Timings), LOOKUP( p, k, Timings ), "" )

If one were using modern Dynamic Arrays the first two formula would reduce to
= SEQUENCE( ROWS(Timings) )
and
= FILTER( k, ISTEXT(Timings) )