Show only the first occurance in spreadsheet

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a very large s/s that has a lot of recurring data in it. It is broken
into sections (not w/s) by date. I need to see only the first occurance of
each name in each section. For example, here is a sample:

1/1/2005
Time Reader Person
5:53:40 AM 0 Doe, John
5:56:35 AM 3 Smith, Mary
6:10:31 AM 0 Doe, John
6:14:19 AM 13 Doe, John
6:34:57 AM 13 Brown, Harry
6:37:31 AM 11 Smith, Mary

1/1/2005
5:49:48 AM 0 Smith, Mary
5:53:24 AM 3 Smith, Mary
5:56:44 AM 0 Brown, Harry
6:10:34 AM 13 Doe, John
6:11:01 AM 13 Brown, Harry
6:18:57 AM 11 Smith, Mary

What I want to see is this:

1/1/2005
Time Reader Person
5:53:40 AM 0 Doe, John
5:56:35 AM 3 Smith, Mary
6:34:57 AM 13 Brown, Harry

1/1/2005
5:49:48 AM 0 Smith, Mary
5:56:44 AM 0 Brown, Harry
6:10:34 AM 13 Doe, John

TIA
 
Try the following...

1) Select Column B

2) Edit > Go to > Special > Blanks > OK

3) With blank cells highlighted, enter the following:

=""

4) Confirm with CONTROL+ENTER, not just ENTER

5) Enter a 0 (zero) in cell D2

6) Enter the following formula in D3 and copy down:

=IF(ISNA(MATCH(C3,INDEX($C$1:C2,MATCH(REPT("z",255),$B$1:B2)):C2,0)),LOOK
UP(9.99999999999999E+307,$D$1:D2)+1,"")

7) Select your entire table > Data > Filter > AutoFilter > and filter
Column D for 'NonBlanks'

8) Copy and paste your filtered table to another location

Hope this helps!
 
Back
Top