Need help with formula to list unique values from a list based on a condition

You need to put quotes ( " ) around Canada in your formula, as it is a text value.

Also, the formula you are trying to use is an array formula, so you need to commit it using the key combination of Ctrl-Shift-Enter (CSE), rather than the usual < Enter >.

I'm not sure what you are trying to do though, so I don't know if that gives you what you want when you copy it down.

Hope this helps.

Pete

Thanks Pete, that made sense!

Tony, thanks for your input as well. The formula works, but it will not repeat same Origins.
Replace "German" with "French", and my desired outcome would be 2 lines with French - 25'000 and French - 20'000.

To further complicate matters, I also need it to be within a date range (for example within April 2017).

The solution in the attached file does not make use of array formulae, so hopefully you'll be able to understand it more easily.

I've used G1 for the date that you are interested in (enter it as the 1st of the month, though it will only display the month and year), and then put this formula in cell E3:

=IF(AND(D3="Canada",A3>=$G$1,A3<=EOMONTH($G$1,0)),MAX(E$2:E2)+1,"-")

This can be copied down as far as you like (as shown by the hyphens) and it sets up a criteria of being within the month specified in G1 AND having a destination of "Canada" - if this is met then it just sets up a unique sequential numbering for those records. Then in G4 you can have this formula:

=IFERROR(INDEX(B:B,MATCH(ROWS($1:1),$E:$E,0)),"")

which will bring the origin country for the record marked with a 1 in column E, and you can copy the formula into H4 to get the appropriate MT value. When you copy the formulae down, the ROWS($1:1) part becomes ROWS($1:2), and so will bring the second record which matches the criteria, and so on for subsequent rows. Copy the two formulae down as far as you need them (until you get blanks), and then you can change the date in G1 to see the extract change - I've added some records for May 2017.

Hope this helps.

Pete

Peter_UK, Thanks a lot! I see where your "helper column" is useful.
However, I have to show a full quarter (April, May, June), so 3 helper columns would be required, which I am trying to avoid.
Is there a way to combine the formula from the helper column into the MATCH/INDEX function?

I was looking at Tony Valko's approach again as well. But I failed at even getting it to even reference the date range, let alone match the MT with the Origin.

May I ask you to help once again?

Thank you!

You can change the formula in E20 to this:

=IF(AND(D20="Canada",A20>=$G$18,A20<=EOMONTH($G$18,2)),MAX(E$19:E19)+1,"-")

(note the change in red) in order to include 2 months in addition to the starting month - just copy this down and the secondary table will give the same results as yours.

Hope this helps.

Pete

@Tony Valko: I didnt say my formulas worked!

@ Pete_UK: I need each month in a separate table. It's the requested format I have to deliver.

Originally Posted by mischge

... I need each month in a separate table...

Your sample file in Post #7 doesn't show that. Perhaps you could attach a more realistic file showing more examples of what you have and what you want to achieve from it.

Pete

@Peter_UK: see attached.

@ Tony Valko: Amazing! Thank you so much! Works like a charm