Excel formula to diplay last billing month

[Aleksandar89]

Hello everyone! I have a little problem. I need a formula to show the last month for specific company.
So for example for company xyz what is the last month that items were sold and then on for company abc.
Between columns Month and Firm is also amount sold in original dataset. Does anyone have solution for this problem?

[TABLE="width: 252"]
<colgroup><col><col><col></colgroup><tbody>[TR]
[TD] Month[/TD]
[TD]Firm[/TD]
[TD]Last month[/TD]
[/TR]
[TR]
[TD]Jan[/TD]
[TD]xyz[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Jan[/TD]
[TD]xyz[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Feb[/TD]
[TD]xyz[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Feb[/TD]
[TD]xyz[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Feb[/TD]
[TD]xyz[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Mar[/TD]
[TD]xyz[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Mar[/TD]
[TD]xyz[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Jan[/TD]
[TD]abc[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Feb[/TD]
[TD]abc[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[lrobbo314]

I have month in column A and Firm in column B. Then in Column G I have made a unique list of the companies, i.e. xyz, and abc. In column G, I put in the following formula,

=INDEX($A$2:$A$10,LARGE(IF($B$2:$B$10=G2,ROW($A$2:$A$10)-ROW($A$2)+1),1))

You will need to adjust the cell references, but this should do what you want. Also, this is an array formula so after typing it in, hit ctrl+shift+enter to enter the formula.

 

[Aleksandar89]

Thank you very mutch. Is maybe there another way to write formula in cell c2 in column last month and then paste down? I would like to have all that data in one table.

 

[lrobbo314]

Yeah, you can put the formula in C2 and it would work the same way. But there is going to be a lot of duplicate information.Not a big deal if it doesn't bother you though.

 

[lrobbo314]

So that formula would look like,

=INDEX($A$2:$A$10,LARGE(IF($B$2:$B$10=B2,ROW($A$2:$A$10)-ROW($A$2)+1),1))

 

[Macropod]

Cross-posted at: https://chandoo.org/forum/threads/count-unique-number-of-months-based-on-criteria.36722/
Please read Mr Excel's policy on Cross-Posting in rule 13: http://www.mrexcel.com/forum/board-announcements/99490-forum-rules.html

 

[Aladin Akyurek]

In C2 enter and copy down:

=LOOKUP(9.99999999999999E+307,1/($B$2:$B$10=$B2),$A$2:$A$10)

 

[Aleksandar89]

Thanks all. It really helped me. Is maybe there also similar formula to get first billing month?

 

[Aladin Akyurek]

Thanks all. It really helped me. Is maybe there also similar formula to get first billing month?

In D2 just enter and copy down:

=INDEX($A$2:$A$10,MATCH(B2,$B$2:$B$10,0))