-1

I have a requirement where i need to enter the value of a SubTotal in each row for the corresponding group. Below is a snapshot of the subtotals in in Column Q and this value manualy entered by me in Column M. I'm happy to create a Macro to do this or a Formula, but i can not seem to think of the logic on how to achieve this. Can anyone advise please.

Many thanks

enter image description here

Community
  • 1
  • 1
Prospidnick
  • 133
  • 1
  • 10
  • 1
    Did either the answers work for you? If so please mark the one with which you went as the correct answer. you do this by clicking on the green check mark by the correct answer. It will close the question as answered. It is something only you can do. – Scott Craner Feb 16 '16 at 20:37

2 Answers2

2

Put the following Array Formula in M5:

=IF(A5<>"",INDEX($Q5:$Q$1000,MATCH(0,IF($A5:$A$1000="",0,1),0)),"")

Press Ctrl-Shift-Enter instead of Enter or Tab to exit edit mode. Then copy down.

enter image description here

Scott Craner
  • 148,073
  • 10
  • 49
  • 81
  • Many thanks Scott, works like a dream. I have used Index Match before, but not quite like this and i have to say that i'm still unsure as to the logic, but i will endeavour to work it out. – Prospidnick Feb 17 '16 at 14:01
2

This formula worked for me:

=IF(ISBLANK(A5),"",IF(OR(ISBLANK(A4),A4="Ship-to Pt"),INDEX(Q:Q,MATCH(A5,A:A,0)+COUNTIF(A:A,A5)),M4))

Drag down the dataset.

You can set specific row limits if desired.

Scott Holtzman
  • 27,099
  • 5
  • 37
  • 72
  • Thanks for the idea, this did not work for me, only because i did not explain that column A is not unique and therefore it returns the first value that matches. – Prospidnick Feb 17 '16 at 14:04