Solved

Excel extract

Posted on 2013-10-24
3
191 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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 9

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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

As with any other System Center product, the installation for the Authoring Tool can be quite a pain sometimes. This article serves to help you avoid making these mistakes and hopefully save you a ton of time on troubleshooting :)  Step 1: Make sur…
Having trouble getting your hands on Dynamics 365 Field Service or Project Service trial? Worry No More!!!
This video walks the viewer through the process of creating Hyperlinks for the web and other documents. Select the "Insert" tab: Click "Hyperlink":  Type "http://" followed by a web address to reference a website or navigate to a document to ref…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

728 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