unique record list with formula condition to month

F

faizee

Board Regular
Joined
Jan 28, 2009
Messages
214
Office Version
  1. 2016
Platform
  1. Windows
I NEED THE UNIQUE RECORD LIST WITH FORMULA, OF EACH SELECTED MONTH

[TABLE="width: 517"]
<TBODY>[TR]
[TD]NAMES</SPAN>[/TD]
[TD]DATE</SPAN>[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]JOHN</SPAN>[/TD]
[TD="align: right"]5-Jan-12</SPAN>[/TD]
[TD][/TD]
[TD]UNIQUE RECORD OF MONTH</SPAN>[/TD]
[TD="align: right"]Feb-12</SPAN>[/TD]
[/TR]
[TR]
[TD]FAIZAN</SPAN>[/TD]
[TD="align: right"]6-Jan-12</SPAN>[/TD]
[TD][/TD]
[TD] [/TD]
[TD][/TD]
[/TR]
[TR]
[TD]ZEESHAN</SPAN>[/TD]
[TD="align: right"]5-Jan-12</SPAN>[/TD]
[TD][/TD]
[TD] [/TD]
[TD][/TD]
[/TR]
[TR]
[TD]FAIZAN</SPAN>[/TD]
[TD="align: right"]10-Jan-12</SPAN>[/TD]
[TD][/TD]
[TD] [/TD]
[TD][/TD]
[/TR]
[TR]
[TD]ZEESHAN</SPAN>[/TD]
[TD="align: right"]15-Jan-12</SPAN>[/TD]
[TD][/TD]
[TD] [/TD]
[TD][/TD]
[/TR]
[TR]
[TD]FAIZAN</SPAN>[/TD]
[TD="align: right"]25-Jan-12</SPAN>[/TD]
[TD][/TD]
[TD] [/TD]
[TD][/TD]
[/TR]
[TR]
[TD]JOHN</SPAN>[/TD]
[TD="align: right"]5-Feb-12</SPAN>[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]SALMAN</SPAN>[/TD]
[TD="align: right"]15-Feb-12</SPAN>[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]FAIZAN</SPAN>[/TD]
[TD="align: right"]20-Feb-12</SPAN>[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]FAIZAN</SPAN>[/TD]
[TD="align: right"]21-Feb-12</SPAN>[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]FAIZAN</SPAN>[/TD]
[TD="align: right"]25-Feb-12</SPAN>[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]ZEESHAN</SPAN>[/TD]
[TD="align: right"]28-Feb-12</SPAN>[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]ZEESHAN</SPAN>[/TD]
[TD="align: right"]15-Feb-12</SPAN>[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]SADFER</SPAN>[/TD]
[TD="align: right"]5-Mar-12</SPAN>[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 3"]THE RESULT OF FEB-12, SHOULD BE</SPAN>[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]UNIQUE LIST</SPAN>[/TD]
[TD="align: right"]12-Feb</SPAN>[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]JOHN</SPAN>[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]SALMAN</SPAN>[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]FAIZAN</SPAN>[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]ZEESHAN</SPAN>[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</TBODY><COLGROUP><COL><COL><COL><COL><COL></COLGROUP>[/TABLE]
 
Sort by date Sort by votes
Dear aladin
one more simple thing

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

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.
faizee said:
yes,
u r right
Click to expand...

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.

[TABLE="width: 121"]
<COLGROUP><COL style="WIDTH: 48pt" width=64><COL style="WIDTH: 73pt; mso-width-source: userset; mso-width-alt: 3470" width=98><TBODY>[TR]
[TD="class: xl65, width: 64, bgcolor: transparent"]KAD[/TD]
[TD="class: xl65, width: 98, bgcolor: transparent, align: right"]4[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]KAD[/TD]
[TD="class: xl66, bgcolor: transparent"]Unique List[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]LAD[/TD]
[TD="class: xl65, bgcolor: transparent"]KAD[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]NAD[/TD]
[TD="class: xl65, bgcolor: transparent"]LAD[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]KAD[/TD]
[TD="class: xl65, bgcolor: transparent"]NAD[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]LAD[/TD]
[TD="class: xl65, bgcolor: transparent"]VAD[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]VAD[/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]KAD[/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]VAD[/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[/TR]
</TBODY>[/TABLE]

B1, control+shift+enter, not just enter:
Rich (BB code): 
=SUM(IF(FREQUENCY(IF(A1:A10<>"",MATCH(A1:A10,A1:A10,0)),
  ROW(A1:A10)-ROW(A1)+1),1))
Rich (BB code):

B3, control+shift+enter, not just enter, and copy down:
Rich (BB code): 
=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...

[TABLE="width: 96"]
<COLGROUP><COL style="WIDTH: 48pt" span=2 width=64><TBODY>[TR]
[TD="class: xl65, width: 64, bgcolor: transparent"]KAD[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent"]KAD[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]KAD[/TD]
[TD="class: xl65, bgcolor: transparent"]LAD[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]LAD[/TD]
[TD="class: xl65, bgcolor: transparent"]NAD[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]NAD[/TD]
[TD="class: xl65, bgcolor: transparent"]VAD[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]KAD[/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]LAD[/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]VAD[/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]KAD[/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]VAD[/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[/TR]
</TBODY>[/TABLE]

B1, control+shift+enter, not just enter, and copy down:
Rich (BB code): 
=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))),"")
 
Upvote 0
there is another condition,,
[TABLE="width: 344"]
<TBODY>[TR]
[TD]CARD NO RAW</SPAN>[/TD]
[TD]DATE</SPAN>[/TD]
[TD]TIME IN </SPAN>[/TD]
[TD]TIME OUT</SPAN>[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10000</SPAN>[/TD]
[TD]1-Jan-12</SPAN>[/TD]
[TD]Absent</SPAN>[/TD]
[TD]sick</SPAN>[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10000</SPAN>[/TD]
[TD]2-Jan-12</SPAN>[/TD]
[TD]Absent</SPAN>[/TD]
[TD]sick</SPAN>[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]17</SPAN>[/TD]
[TD]1-Sep-12</SPAN>[/TD]
[TD]8:01</SPAN>[/TD]
[TD]19:28</SPAN>[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]17</SPAN>[/TD]
[TD]3-Sep-12</SPAN>[/TD]
[TD]Absent</SPAN>[/TD]
[TD]late</SPAN>[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]17</SPAN>[/TD]
[TD]4-Sep-12</SPAN>[/TD]
[TD]9:01</SPAN>[/TD]
[TD]20:33</SPAN>[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]17</SPAN>[/TD]
[TD]5-Sep-12</SPAN>[/TD]
[TD]9:01</SPAN>[/TD]
[TD]18:00</SPAN>[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]17</SPAN>[/TD]
[TD]6-Sep-12</SPAN>[/TD]
[TD]9:01</SPAN>[/TD]
[TD]13:00</SPAN>[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]18</SPAN>[/TD]
[TD]7-Sep-12</SPAN>[/TD]
[TD]Absent</SPAN>[/TD]
[TD]late</SPAN>[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]17</SPAN>[/TD]
[TD]8-Sep-12</SPAN>[/TD]
[TD]9:01</SPAN>[/TD]
[TD]20:31</SPAN>[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]21</SPAN>[/TD]
[TD]10-Sep-12</SPAN>[/TD]
[TD]Absent</SPAN>[/TD]
[TD]sick</SPAN>[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]17</SPAN>[/TD]
[TD]11-Sep-12</SPAN>[/TD]
[TD]9:01</SPAN>[/TD]
[TD]20:31</SPAN>[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]17</SPAN>[/TD]
[TD]12-Sep-12</SPAN>[/TD]
[TD]9:01</SPAN>[/TD]
[TD]20:26</SPAN>[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]17</SPAN>[/TD]
[TD]13-Sep-12</SPAN>[/TD]
[TD]Absent</SPAN>[/TD]
[TD]work</SPAN>[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]17</SPAN>[/TD]
[TD]17-Dec-01</SPAN>[/TD]
[TD]Absent</SPAN>[/TD]
[TD]work</SPAN>[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]17</SPAN>[/TD]
[TD]15-Sep-12</SPAN>[/TD]
[TD]9:01</SPAN>[/TD]
[TD]20:39</SPAN>[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]17</SPAN>[/TD]
[TD]19-Aug-12</SPAN>[/TD]
[TD]Absent</SPAN>[/TD]
[TD]late</SPAN>[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 5"]I want the list of only record related to specific card no, and month, where "time in" is "Absent" then in result</SPAN>[/TD]
[/TR]
[TR]
[TD="colspan: 3"]in give only date and time out,,</SPAN>[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 2"]for example</SPAN>[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Card No</SPAN>[/TD]
[TD="align: right"]17</SPAN>[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Month</SPAN>[/TD]
[TD="align: right"]Sep-12</SPAN>[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Date</SPAN>[/TD]
[TD]Time out</SPAN>[/TD]
[TD][/TD]
[TD]Date</SPAN>[/TD]
[TD]Time out</SPAN>[/TD]
[/TR]
[TR]
[TD]3-Sep-12</SPAN>[/TD]
[TD]late</SPAN>[/TD]
[TD][/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]13-Sep-12</SPAN>[/TD]
[TD]work</SPAN>[/TD]
[TD][/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 5"]after filling all row of column 1&2 if the data is still left then it should continues in 3&4 columns,,</SPAN>[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Thank you</SPAN>[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</TBODY><COLGROUP><COL><COL><COL><COL><COL></COLGROUP>[/TABLE]
 
Upvote 0
You must log in or register to reply here.

Similar threads

Vintage79
  • Solved
Count consecutive days and gaps between days
Replies
2
Views
205
Vintage79
Vintage79
W
  • Solved
Usign the =sort function in a Dynamic Array to sort by Month
Replies
3
Views
529
Fluff
Fluff
E
  • Question Question
VBA code clean up
Replies
9
Views
482
mumps
mumps
W
  • Solved
Validate month based on contract dates & Calculate number of weeks in that month to calculate hours booked per month
2
Replies
16
Views
2K
phiero21
P
F
  • Solved
Formula - Sumif(s)? Sumproduct? etc.
2
Replies
16
Views
247
Fluff
Fluff

Forum statistics

Threads
1,223,264
Messages
6,171,081
Members
452,377
Latest member
bradfordsam

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top