3

I have columns "Manifestos A" and "Manifestos B" (with its correspondent "volumes"). "A" has more than 1000 lines and "B" around 800, so its impossible doing by hand.

Column "Manifestos C" must have the results of "A" > "B" matching and pairing, and add blank cells where there is no match .

This was done before here and was all OK until I found out that some cells might have the same values in columns "A" and "B". I did not foresaw this.

I've made an example to show what happened when I applied the answer I got to my other question:

worksheet showing 3 tables with 3 column each (User, Manifesto and Volumes), with duplicate Manifesto values highlighted in all tables and duplicate volumes for same manifestos in column "Volumes C", showing the error

Note that the pairing is perfectly fine, but the "Volumes C" cells are being repeated from the volumes coupled up with the first "17616" manifesto cell on column "Manifestos B", and this cannot happen.

The formula for cell K3 and bellow:

=IF(NOT(ISERROR(MATCH(C3,G$3:G$15,0))),C3,"")

And for cell L3 and bellow

=IFERROR(VLOOKUP(K3,G$3:H$15,2,0),"")

Now I have to add the "User" filter to the equation so I can get the expected results below:

worksheet showing 3 tables with 3 column each (User, Manifesto and Volumes), with duplicate Manifesto values highlighted in all tables and distinct volumes for same manifestos in column "Volumes C", showing the expected result

Community
  • 1
  • 1
Human_AfterAll
  • 231
  • 3
  • 12

1 Answers1

4

you could try this formula in "L3" and copy it down:

=SUMIFS($H$3:$H$15;$F$3:$F$15;J3;$G$3:$G$15;K3)
Mathieu Guindon
  • 69,817
  • 8
  • 107
  • 235
jemmy
  • 60
  • 1
  • 6
  • Excel has returned that I've "entered an excessive number of arguments for this function".tks – Human_AfterAll Nov 14 '14 at 04:29
  • @Human_AfterAll this should work. Replace `;` with `,` though. – Mathieu Guindon Nov 14 '14 at 04:30
  • The `SUMIFS` should be the *value_if_false* in an `IF` function to make an empty value when `$K3` is an empty string: `=IF($K3="","",SUMIFS(...))` – Mathieu Guindon Nov 14 '14 at 04:35
  • @retailcoder my bad, was translating it as `SUMIF`. had to increase all by 1, but it worked like a charm!!! the final formula on "L3" is `=SUMIFS($H$4:$H$16;$F$4:$F$16;J4;$G$4:$G$16;K4)`. thank you very much! will give it some further testing tomorrow and will get back if any errors! – Human_AfterAll Nov 14 '14 at 04:42
  • thanks @retailcoder! Continental European OS, forgot to change the syntax....and was a bit lazy with the blank values... – jemmy Nov 14 '14 at 08:51