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>

 

[faizee]

Dear aladin
one more simple thing

if I have data in a1:a10, and i just want its unique record list. then?

 

[Aladin Akyurek]

Dear aladin
one more simple thing

if I have data in a1:a10, and i just want its unique record list. then?

Do you mean that you have entries in A1:A10 and you just want to extract a list of unique entries, using formulas?

 

[faizee]

yes,
u r right

 

[Aladin Akyurek]

yes,
u r right

This is a less complex task than those with the earlier specs.

What follows does not use any definitions...

A1:A10 houses the items of interest.

KAD	4
KAD	Unique List
LAD	KAD
NAD	LAD
KAD	NAD
LAD	VAD
VAD
KAD
VAD

<COLGROUP><COL style="WIDTH: 48pt" width=64><COL style="WIDTH: 73pt; mso-width-source: userset; mso-width-alt: 3470" width=98><TBODY>
</TBODY>

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

=SUM(IF(FREQUENCY(IF(A1:A10<>"",MATCH(A1:A10,A1:A10,0)),
  ROW(A1:A10)-ROW(A1)+1),1))

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

=IF(ROWS($B$3:B3)<=$B$1,INDEX($A$2:$A$10,
  SMALL(IF(FREQUENCY(IF($A$2:$A$10<>"",MATCH($A$2:$A$10,$A$2:$A$10,0)),
   ROW(A1:A10)-ROW(A1)+1),ROW(A1:A10)-ROW(A1)+1),ROWS($B$3:B3))),"")

If you are on a 2007 system or later...

KAD	KAD
KAD	LAD
LAD	NAD
NAD	VAD
KAD
LAD
VAD
KAD
VAD

<COLGROUP><COL style="WIDTH: 48pt" span=2 width=64><TBODY>
</TBODY>

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

=IFERROR(INDEX($A$2:$A$10,SMALL(IF(FREQUENCY(IF($A$2:$A$10<>"",
  MATCH($A$2:$A$10,$A$2:$A$10,0)),ROW(A1:A10)-ROW(A1)+1),
   ROW(A1:A10)-ROW(A1)+1),ROWS($B$1:B1))),"")

 

[faizee]

there is another condition,,

CARD NO RAW</SPAN>	DATE</SPAN>	TIME IN </SPAN>	TIME OUT</SPAN>
10000</SPAN>	1-Jan-12</SPAN>	Absent</SPAN>	sick</SPAN>
10000</SPAN>	2-Jan-12</SPAN>	Absent</SPAN>	sick</SPAN>
17</SPAN>	1-Sep-12</SPAN>	8:01</SPAN>	19:28</SPAN>
17</SPAN>	3-Sep-12</SPAN>	Absent</SPAN>	late</SPAN>
17</SPAN>	4-Sep-12</SPAN>	9:01</SPAN>	20:33</SPAN>
17</SPAN>	5-Sep-12</SPAN>	9:01</SPAN>	18:00</SPAN>
17</SPAN>	6-Sep-12</SPAN>	9:01</SPAN>	13:00</SPAN>
18</SPAN>	7-Sep-12</SPAN>	Absent</SPAN>	late</SPAN>
17</SPAN>	8-Sep-12</SPAN>	9:01</SPAN>	20:31</SPAN>
21</SPAN>	10-Sep-12</SPAN>	Absent</SPAN>	sick</SPAN>
17</SPAN>	11-Sep-12</SPAN>	9:01</SPAN>	20:31</SPAN>
17</SPAN>	12-Sep-12</SPAN>	9:01</SPAN>	20:26</SPAN>
17</SPAN>	13-Sep-12</SPAN>	Absent</SPAN>	work</SPAN>
17</SPAN>	17-Dec-01</SPAN>	Absent</SPAN>	work</SPAN>
17</SPAN>	15-Sep-12</SPAN>	9:01</SPAN>	20:39</SPAN>
17</SPAN>	19-Aug-12</SPAN>	Absent</SPAN>	late</SPAN>
I want the list of only record related to specific card no, and month, where "time in" is "Absent" then in result</SPAN>
in give only date and time out,,</SPAN>
for example</SPAN>
Card No</SPAN>	17</SPAN>
Month</SPAN>	Sep-12</SPAN>
Date</SPAN>	Time out</SPAN>		Date</SPAN>	Time out</SPAN>
3-Sep-12</SPAN>	late</SPAN>
13-Sep-12</SPAN>	work</SPAN>
after filling all row of column 1&2 if the data is still left then it should continues in 3&4 columns,,</SPAN>
Thank you</SPAN>

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

 

[faizee]

Dear Aladin
Please gudie me in my following post
http://www.mrexcel.com/forum/excel-...-list-zero-balance-condition.html#post3831795