Eliminating duplicates in a an excel table?

Question

I have an excel spreadsheet with column heading a,b,c,d etc. There is duplicate info in some of the columns but not all. Ex: Column a will have two or three of the same value with different info in the remaining columns. I want to use a function (vlookup?) to find the same value and keep the row with the info i want....clear as mud I know but any help would help me out greatly!

Answer 1

Yes, it's about as clear as mud.

It sounds like you want to manually review the rows with duplicate info. in a given col. and then decide to delete one or more of the duplicate rows. Is that correct, or can you specify a rule or rules ahead of time that could be used in a formula?

Either way, VLOOKUP doesn't seem to be the way to approach it.

If you want to manually review the duplicate rows, I suggest sorting the data by the row in question. You do this by selecting all the rows and columns containing your data, then selecting Sort... under the Data menu.

If your database is so huge that it would be very time-consuming to scan the sorted data for duplicates, here's a way to simplify finding them:

Say the data is sorted on col. A and you are looking for duplicates in A. Insert a new column B (select current col. B and Insert - Columns). Now in cell B2 enter the formula:

=IF(A2=A1,"x","")

that will put an "x" in B2 if A2 is a duplicate of A1, otherwise the contents of B2 will be set to blank.

Copy and paste from B2 down through the last row of data. Now you can easily scan down the col. looking for x's.

If this doesn't solve your problem, I think you'll need to explain the situation in more detail.

Answer 2

Try advanced filter and filter out the unique rows. The rest are just duplicates.

Answer 3

so some distance as i understand, you are able to in trouble-free terms attempt this in a unmarried sheet at a time in Excel, so which think ofyou've got to integrate documents from all sheets into one sheet. Use Microsoft Excel help and variety in the word "filter out for unique archives" and it is going to help you attempt this in a unmarried sheet. in case you understand the thanks to apply Microsoft get admission to, you are able to import your documents right into a database. once you create a table in get admission to, each and each and every record has to receive a unique identifier. case in point, in a database of workers, the unique identifier would properly be the SSN (considering each and every worker would have an SSN unique to them). as quickly because it is determined up, get admission to gained't help you enter yet another record with an similar unique identifier (for this reason, SSN), for this reason scuffling with reproduction archives. in case you don't understand get admission to and also you need to be updating worker documents regularly, I really propose getting to attraction to close get admission to or getting someone to set it up for you. it is really elementary to set it up, besides the undeniable fact that that is even a lot less complicated to maintain. it is going to similar you time in the destiny. (Edited to characteristic: easily, CMC1217's theory about Pivot Tables ought to paintings for numerous sheets in case you understand the thanks to apply it. i'm no longer particular if the subtotal function will paintings throughout the time of numerous sheets. I nonetheless imagine it is going to likely be more beneficial useful in the destiny to rework to get admission to.)