unique record list with formula condition to month

[faizee]

I NEED THE UNIQUE RECORD LIST WITH FORMULA, OF EACH SELECTED MONTH

NAMES</SPAN>	DATE</SPAN>
JOHN</SPAN>	5-Jan-12</SPAN>		UNIQUE RECORD OF MONTH</SPAN>	Feb-12</SPAN>
FAIZAN</SPAN>	6-Jan-12</SPAN>
ZEESHAN</SPAN>	5-Jan-12</SPAN>
FAIZAN</SPAN>	10-Jan-12</SPAN>
ZEESHAN</SPAN>	15-Jan-12</SPAN>
FAIZAN</SPAN>	25-Jan-12</SPAN>
JOHN</SPAN>	5-Feb-12</SPAN>
SALMAN</SPAN>	15-Feb-12</SPAN>
FAIZAN</SPAN>	20-Feb-12</SPAN>
FAIZAN</SPAN>	21-Feb-12</SPAN>
FAIZAN</SPAN>	25-Feb-12</SPAN>
ZEESHAN</SPAN>	28-Feb-12</SPAN>
ZEESHAN</SPAN>	15-Feb-12</SPAN>
SADFER</SPAN>	5-Mar-12</SPAN>
THE RESULT OF FEB-12, SHOULD BE</SPAN>
UNIQUE LIST</SPAN>	12-Feb</SPAN>
JOHN</SPAN>
SALMAN</SPAN>
FAIZAN</SPAN>
ZEESHAN</SPAN>

<TBODY>
</TBODY><COLGROUP><COL><COL><COL><COL><COL></COLGROUP>

 

[Robert Mika]

This is two step process:
If your data are in range A1:B15
In D1: 1/2/2012
In E1:
=SUMPRODUCT(--(MONTH($B$2:$B$15)=MONTH(D1)))
In E2:
=IF(ROWS($C$2:$C2)<=$E$1,INDEX($A$2:$A$200,SMALL(IF(MONTH($B$2:$B$200)=MONTH($D$1),ROW($C$2:$C$200)-ROW($C$2)+1),ROWS($C$2:$C2))),"")
confirm Control+Shift+Enter

In G1="unique"
In G2:
=IFERROR(INDEX($E$2:$E$15,MATCH(0,COUNTIFS($G$1:G1,$E$2:$E$15),0)),"")
confirm Control+Shift+Enter

This will work in Excel 07/10.
For Excel 03:
=IF(ISNA(MATCH(0,COUNTIFS($G$1:G1,$E$2:$E$15),0)),"",INDEX($E$2:$E$15,MATCH(0,COUNTIFS($G$1:G1,$E$2:$E$15),0)))

 

[Aladin Akyurek]

I NEED THE UNIQUE RECORD LIST WITH FORMULA, OF EACH SELECTED MONTH
...

This does also carry out the year test... It's pretty fast and extendible.

NAMES	DATE		4
JOHN	5-Jan-12		UNIQUE RECORD OF MONTH	1-Feb-12
FAIZAN	6-Jan-12		JOHN
ZEESHAN	5-Jan-12		FAIZAN
FAIZAN	10-Jan-12		ZEESHAN
ZEESHAN	15-Jan-12		SALMAN
FAIZAN	25-Jan-12
JOHN	5-Feb-12
SALMAN	15-Feb-12
FAIZAN	20-Feb-12
FAIZAN	21-Feb-12
FAIZAN	25-Feb-12
ZEESHAN	28-Feb-12
ZEESHAN	15-Feb-12
SADFER	5-Mar-12

<colgroup><col style="width: 71pt; mso-width-source: userset; mso-width-alt: 3384;" width="95"> <col style="width: 90pt; mso-width-source: userset; mso-width-alt: 4266;" width="120"> <col style="width: 48pt;" width="64"> <col style="width: 227pt; mso-width-source: userset; mso-width-alt: 10752;" width="302"> <col style="width: 128pt; mso-width-source: userset; mso-width-alt: 6058;" width="170"> <tbody>
</tbody>

Sheet1, A:B, houses the sample you provided. Adjust to suit.

E2: 1-Feb-12 (a month/year of interest, given as the first day date)

Define Ivec using Insert | Name | Define or Formulas | Name Manager as referring to:

=ROW(Sheet1!$A$2:$A$15)-ROW(Sheet1!$A$2)+1

D1, control+shift+enter, not just enter:

=SUM(IF(FREQUENCY(IF(A2:A15<>"",IF(B2:B15-DAY(B2:B15)+1=$E2,
  MATCH(A2:A15,A2:A15,0))),Ivec),1))

D3, control+shift+enter and copy down:

=IF(ROWS($D$3:D3)<=$D$1,INDEX($A$2:$A$15,SMALL(IF(FREQUENCY(IF($A$2:$A$15<>"",
  IF($B$2:$B$15-DAY($B$2:$B$15)+1=$E$2,MATCH($A$2:$A$15,$A$2:$A$15,0))),
   Ivec),Ivec),ROWS($D$3:D3))),"")

 

[faizee]

Thanks alot both of your.. specially alladin,,
please tell me. can we fix date with max of date range ..
like .. insteed of putting date manually. i like excel to take the max date of a range ,.,=max(a1:a10).

i tried it, but it didnt work..

 

[Aladin Akyurek]

Thanks alot both of your.. specially alladin,,

You are welcome.

please tell me. can we fix date with max of date range ..
like .. insteed of putting date manually. i like excel to take the max date of a range ,.,=max(a1:a10).

i tried it, but it didnt work..

Do you mean to replace the manual date entry in E2. If so:

=MAX(B2:B15)-DAY(MAX(B2:B15))+1

 

[faizee]

one thing more..
i am trying for sort (A-Z), but i guess its not workind due to array,
i tried to do with it table and filter option...

is there any way to do it?

 

[faizee]

Thanks .... All is well now..
one thing more...
is there any possiblity to get the unique records within the selected date range...
like last 12 months?

please help

 

[Aladin Akyurek]

Thanks .... All is well now..
one thing more...
is there any possiblity to get the unique records within the selected date range...
like last 12 months?

please help

Consider...

			2
			1-Mar-12
			1-Jan-12
NAMES	DATE		5
JOHN	5-Jan-12		UNIQUE RECORDf OF LAST 2 MONTH(S)
FAIZAN	6-Jan-12		JOHN
ZEESHAN	5-Jan-12		FAIZAN
FAIZAN	10-Jan-12		ZEESHAN
ZEESHAN	15-Jan-12		SALMAN
FAIZAN	25-Jan-12		SADFER
JOHN	5-Feb-12
SALMAN	15-Feb-12
FAIZAN	20-Feb-12
FAIZAN	21-Feb-12
FAIZAN	25-Feb-12
ZEESHAN	28-Feb-12
ZEESHAN	15-Feb-12
SADFER	5-Mar-12

<colgroup><col style="width: 58pt; mso-width-source: userset; mso-width-alt: 2730;" width="77"> <col style="width: 62pt; mso-width-source: userset; mso-width-alt: 2929;" width="82"> <col style="width: 25pt; mso-width-source: userset; mso-width-alt: 1166;" width="33"> <col style="width: 256pt; mso-width-source: userset; mso-width-alt: 12117;" width="341"> <tbody>
</tbody>

A4:B18 houses the relevant data.

D1: 2 (The parameter value specifying "last N months"; Adjust to suit.)

D2, just enter:

=MAX(B5:B18)-DAY(MAX(B5:B18))+1

The foregoing etermines the last month of the year in the data in the form of a first day date.

D3, just enter:

=EDATE(D2,-D1)

This determines the start month/year of the last N months streak.

D4, control+shift+enter, not just enter:

=SUM(IF(FREQUENCY(IF(A5:A18<>"",
  IF(B5:B18-DAY(B5:B18)+1>=EDATE(D2,-D1),
   MATCH(A5:A18,A5:A18,0))),Ivec),1))

Thi calculates the number of unique records in the last N months.

D6, control+shift+enter, not just enter, and copy down:

=IF(ROWS($D$6:D6)<=$D$4,INDEX($A$5:$A$18,
  SMALL(IF(FREQUENCY(IF($A$5:$A$18<>"",
   IF($B$5:$B$18-DAY($B$5:$B$18)+1>=EDATE($D$2,-$D$1),
    MATCH($A$5:$A$18,$A$5:$A$18,0))),Ivec),Ivec),ROWS($D$6:D6))),"")

Finally, this lists the unique (distinct) records of the last N months.

 

[faizee]

Thank you very much Aladin

Could you please also tell me. if we need the unique list of only last 12 month data..then what would be the formula.

 

[Aladin Akyurek]

Thank you very much Aladin

You are welcome.

Could you please also tell me. if we need the unique list of only last 12 month data..then what would be the formula.

Set D1 to 12, as specified in post #8. Am I missing someting?