0

enter image description here

Ok, I don't think I explained properly before I need the table on the left to be consolidated into the table on the right.

Thanks

  • Are you looking for a formula or a macro? – Chris Strickland Jan 12 '19 at 23:35
  • A formula @ChrisStrickland –  Jan 13 '19 at 01:58
  • I updated my answer to use A1 reference style and relative references so you can copy it, now that I can see the row and column numbers. I am pretty sure that the other answer will also work, and it's worth looking at both to see how they work, because index+match is a really handy tool to have in your toolbelt. – Chris Strickland Jan 13 '19 at 02:04

2 Answers2

0

If your values are always numeric, and if your ids are unique, and if a column only has one entry for each id, you can do it with a formula. Take your id and date column and copy them, then remove duplicates. Then use sumifs to calculate the values:

=sumifs(C:C, $A:$A, J2)

Copy this starting in L2 and copy over and down).

Let me know if that helps.

Chris Strickland
  • 3,388
  • 1
  • 16
  • 18
  • I updated my question to make more sense I included row and column headers –  Jan 13 '19 at 02:00
  • I edited the formula, now that I can see your rows and columns. Let me know if this works. It's a little simpler than the other answer, but I'm pretty sure that answer will work. It's worth learning that index/match combination. I don't think it's necessary here, but it's very useful. – Chris Strickland Jan 13 '19 at 02:07
  • Thanks, for your help. I used your formula in L2 and got the value 318.9. I need for example the first three rows to be consolidated into one for id 1256163 (date 2002-01-24) and then the next two rows for id 1256163 (date 2002-02-04) to be placed into one also. Hope this makes sense –  Jan 13 '19 at 02:16
  • 1
    Fixed it =SUMIFS(C:C, $A:$A, J2,$B:$B,K2) –  Jan 13 '19 at 02:34
  • @JBrowne13: sorry I missed your comment. So that means that your ids weren't unique, but the id + date combo was. Yes, that makes sense, and it looks like you figured out the right fix. So this is rectified for you now? – Chris Strickland Jan 13 '19 at 04:51
0

I have no idea why you thought that omitting the row and column headers that would have identified your cell ranges would be beneficial to receiving an answer but I will assume that id is in A1.

In L2 where you have 52.8, put this formula then drag right and down.

=sumifs(index($c:$f, 0, match(l$1, c$1:f$1, 0)), $a:$a, $j2, $b:$b, $k2)
  • I updated my question to make more sense I included row and column headers @user10896914 –  Jan 13 '19 at 01:59