Solved

Excel extract

Posted on 2013-10-24
3
183 Views
Last Modified: 2013-10-25
How do I pull one record out of a column that has the same record multiple time? See example below. Say in column A, I have two of 98177 and I want one record out of that column and list it on a different column and so forth. Thank you in advance.
98177
98177
98023
98106
98106
98058
98058
98058
98166
98115
98115
98115
98178
98178
98106
98106
98106
98106
98030
98030
98125
98125
98106
0
Comment
Question by:chekn3
3 Comments
 
LVL 81

Expert Comment

by:byundt
ID: 39599250
You can identify the first item where duplicates occur later with:
=IF(AND(COUNTIF(A1,A:A)>1,MATCH(A1,A:A,0)=ROW(A1)),"First","")
The above formula returns "First" on the first occurrence of a value from column A that has duplicates.

If you want a value from column H on that same row, you could use:
=IF(AND(COUNTIF(A1,A:A)>1,MATCH(A1,A:A,0)=ROW(A1)),H1,"")
0
 
LVL 4

Accepted Solution

by:
Satish Auti earned 500 total points
ID: 39599623
Susan Harkins discusses how to copy unique records (only the first time each number appears in column A) to a different worksheet using Advanced Filter. She also presents a formula to determine if a cell in column A is duplicated so you can use it for Conditional Formatting.
refer : http://www.techrepublic.com/blog/windows-and-office/how-to-find-duplicates-in-excel-245163/



<<The discussion in italics was added to comply with a new policy by Experts Exchange to eliminate blind links. A blind link is one in which there is no discussion other than "refer". The reason for the new policy is to improve the quality of discussion and Google page rank.

Blind links are subject to automatic deletion anywhere in Experts Exchange, but in the Excel TA, we are trying to show by example how it ought to be done.

byundt--Microsoft Excel Topic Advisor>>
0
 

Author Closing Comment

by:chekn3
ID: 39600848
The advance filter worked exactly what I was looking for. Thank you.
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Removing Office 2010 after installing Office 2016 7 53
How to code SharePoint 2013 online 4 55
Excel Formatting test 26 61
Formatting Problem within Excel 3 51
Microsoft Office Picture Manager was included in Office 2003, 2007, and 2010, but not in Office 2013. Users had hopes that it would be in Office 2016/Office 365, but it is not. Fortunately, the same zero-cost technique that works to install it with …
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
This video shows the viewer how to set up and create Footnotes in their document. Click on the References tab: Select "Insert Footnote": Type in desired text:
The viewer will learn how to make their project stand out over others by learning how to change colors and shapes, add spaces, change directions, and add bullets to their charts.

919 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now