Avoid repeated names

JungJung

Board Regular
Joined
Jul 13, 2006
Messages
75
Hello all,

I'm in the process of developing a spreadsheet that will calculate the number of days between the date a pensioner death was opened and the current date. A major problem I've run into is that the .txt file is generated automatically each day, so, for example, if there is was a pensioner death opened on Monday, Tuesday's .txt file would show it as one day old, but if it isn't closed, Wednesday's .txt file will show it as two days old, leading to multiple entries of the same person in the spreadsheet when I copy and paste the lists.

Is there a function/macro that can search a column for an SSN and remove any previous occurences if found (it would have to remove the entire row, not just the SSN)? Does anyone have any other suggestions? Any input is appreciated. Thanks!
 

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
Say your SSN's are in column A. In row 1 of a spare column enter:

=COUNTIF(A$1:A1,A1)>1

and copy down as far as necessary. AutoFilter that column for TRUE, select the visible rows and delete them. Then turn off AutoFilter and delete the unneeded new column.
 

Forum statistics

Threads
1,136,262
Messages
5,674,709
Members
419,521
Latest member
Jasonnie

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
Top