An automated way of comparing an old spreadsheet with an updatedversion

  • Thread starter Thread starter alexlavington
  • Start date Start date
A

alexlavington

Hi,

I recieve an updated spreadsheet each month showing the newer entries
at the bottom of the sheet. What i need to do is find a way that this
newer data can be compared to previous entries to see if it has been
entered before (i.e Mr Smith was entered in Jan and again in May)
Currently I am having to manually search through the spreadsheet
(about 400 cells) and I figure there must be an easier way!

Thanks in advance

Alex
 
Hi Alex

Assuming that you have headers in row 1 and your data you are wanting to
search is in column A, enter in a blank column
=COUNTIF(A:A,A2)
and copy down as far as required using the fill handle.

Highlight your header row>Data>Filter>>Autofilter>from the dropdown on your
new column choose Customise>Greater Than>1
 
Assuming your data in Column A, enter the following formula in Column B
starting with Row 2:-

=IF(A:A<>"",IF(ISERROR(MATCH(A2,$A$1:A1,0)),"Unique","Repeat"),"")
Now, you have the repeated enteries shown as Repeat.

Pls revert back if this helps. Thanks.

--
Dilip Kumar Pandey
MBA, BCA, B.Com(Hons.)
(e-mail address removed)
(e-mail address removed)
New Delhi, India
 
Hi Alex

Assuming that you have headers in row 1 and your data you are wanting to
search is in column A, enter in a blank column
=COUNTIF(A:A,A2)
and copy down as far as required using the fill handle.

Highlight your header row>Data>Filter>>Autofilter>from the dropdown on your
new column choose Customise>Greater Than>1

--
Regards
Roger Govier









- Show quoted text -
Click to expand...


Thanks for the respones. An added problem I neglected to mention is
that some of the data has entry discrepancies - for example some
entries are listed Smith, J and some are listed as Smith, John. is
there anyway just to search the first part of the cell?

Thanks
Alex
 
Hi Alex

Try the following formula instead, then use the filter as described
previously
=SUMPRODUCT(--(LEFT($A$2:$A$1000,6)=LEFT(A2,6))*($A$2:$A$1000<>""))

This is testing for the first 6 characters (which would deal with Smith) and
maybe sufficient for your needs.

If there is always a comma after the name, you could use
=SUMPRODUCT(--(LEFT($A$2:$A$1000,FIND(",",A2)-1)=LEFT(A2,FIND(",",A2)-1))*($A$2:$A$1000<>""))which would deal with the whole of the name up to the comma.--RegardsRoger Govier<[email protected]> wrote in messageOn 30 Jun, 11:22, "Roger Govier" <roger@technology4unospamdotcodotuk>> wrote:>> Hi Alex>>>> Assuming that you have headers in row 1 and your data you are wanting to>> search is in column A, enter in a blank column>> =COUNTIF(A:A,A2)>> and copy down as far as required using the fill handle.>>>> Highlight your header row>Data>Filter>>Autofilter>from the dropdown onyour>> new column choose Customise>Greater Than>1>>>> -->> Regards>> Roger Govier>>>> <[email protected]> wrote in message>>>> > Hi,>>>> > I recieve an updated spreadsheet each month showing the newer entries>> > at the bottom of the sheet. What i need to do is find a way that this>> > newer data can be compared to previous entries to see if it has been>> > entered before (i.e Mr Smith was entered in Jan and again in May)>> > Currently I am having to manually search through the spreadsheet>> > (about 400 cells) and I figure there must be an easier way!>>>> > Thanks in advance>>>> > Alex- Hide quoted text ->>>> - Show quoted text ->>> Thanks for the respones. An added problem I neglected to mention is> that some of the data has entry discrepancies - for example some> entries are listed Smith, J and some are listed as Smith, John. is> there anyway just to search the first part of the cell?>> Thanks> Alex>
 
Back
Top