Extract Unique values from the list of Duplicates and copy into another column

**nadeemm** · 10-16-2018, 09:08 AM

Hi there,
I have a request where i want to take out the Unique values from the list of duplicates jobs# & copy the unique jobs# into another column, & then copy the respective status into another column, as shown in the attached Excel ,"Example" sheet

Note: Unique status will be updated only if all the duplicate jobs# has the respective status , else it will be blank

i would appreciate if you could provide me some help on this
pls. find the excel file in the attachment!

Thanking you in anticipation
Abrar

**JeteMc** · 10-19-2018, 04:44 PM

Hello nadeemm and Welcome to Excel Forum.
Are you open to a formula solution with helpers?
If not disregard, however if so the following seems to work for the Unique Job # column on the example sheet:
1) Populate column 7 using: =COUNTIF(R2C2:RC[-5],RC[-5])
2) Populate column 8 using: =SUMPRODUCT((R2C2:R15C2=RC[-6])*(R2C1:R15C1=""))
3) Populate the Unique Job # column using: =IF(AND(RC[3]=1,RC[4]=0),RC[-2],"")
Note that the contents of columns 7 and 8 could be moved and/or hidden for aesthetic purposes.
If this is something you might be able to use I'll attempt to find formulas to fill the Unique Integration and Status columns.
Question: Is the R1C1 reference mandatory for this spreadsheet?
Let us know if you have any questions.

**nadeemm** · 10-19-2018, 07:41 PM

Thank you, sir, for your kind support

Actually, I was looking for a VBA to set this right, if this can be achievable thru formula's then also no issues

**JeteMc** · 10-19-2018, 08:10 PM

It would be helpful to know if you need the formulas written using R1C1 references, or can they be written using A1 references.

**nadeemm** · 10-20-2018, 08:08 AM

Yes sir, A1 reference will work for me
I'm attaching the file with "Sample Data" in the 'sorted data' sheet in the attached excel
pls. have a review to better understand the request

for Example:
The Duplication JOBs are having each separate "Status" for each milestone (Like - Integration Status, On-Air Status, PAT status, Handover Status..)
what we need is to pull out the Unique JOBs in a Separate Column (existing in the "Sorted Data" Sheet )and provide its Status for these different milestones(also existing in the "Sorted Data" Sheet ),
like IF a single which has many Duplicates, all may or may not have its respective "statuses" for their respective milestones(Like - Integration Status, On-Air Status, PAT status, Handover Status..)
IF all of the duplicates of a single JOB has all the "Status" filled then the Unique JOb will show the same 'Status' else blank
(Please refer the attached "Sample Data" in the 'Sorted Data' sheet for your understanding)

**JeteMc** · 10-20-2018, 08:49 AM

As no file is attached to post #5, I am going to use the file attached to post #2.
Changing to A1 references, the following formulas are used:
1) Helper column G: =COUNTIF($B$2:B2,B2)
2) Helper column H: =SUMPRODUCT(($B$2:$B$15=B2)*($A$2:$A$15=""))
3) Helper column I: =SUMPRODUCT(($B$2:$B$15=B2)*($C$2:$C$15=""))
4) Unique Job # column: =IF(AND(G2=1,H2=0),B2,"")
5) "Unique JOB# Status (Integration)" column: =IF(AND(G2=1,H2=0),A2,"")
6) "Unique JOB# Status (On Air)" column: =IF(AND(G2=1,I2=0),C2,"")
Note that the formulas in the latter two columns produce some results that differ from what was previously listed, although in both cases the 'status' is completed for all duplicates listed. I have highlighted the differences in red.

**nadeemm** · 10-20-2018, 09:20 AM

Please use this link to download the file , as i'm unable to upload it here

**Glenn Kennedy** · 10-20-2018, 09:28 AM

The link didn't work... but you CAN upload files here. Sometimes you have to try more than once or twice, though!!

Unfortunately the attachment icon doesn't work at the moment. So, to attach an Excel file you have to do the following: Just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.

**nadeemm** · 10-20-2018, 09:31 AM

I'm unable to share any links here! how should i share the file then?

**nadeemm** · 10-20-2018, 09:32 AM

did that , but not working, when uploading it displays error msg as "fail to upload"

**Glenn Kennedy** · 10-20-2018, 09:32 AM

See post 8. You've laready uploaded a file once (at post 1) so doing so again shouldn't be too big a problem.

**Glenn Kennedy** · 10-20-2018, 09:34 AM

Originally Posted by nadeemm

did that , but not working, when uploading it displays error msg as "fail to upload"

Do it again. And again. Sometimes it doesn't work. What size is the file? 10 - 20 carefully chosen rows is enough. 20,000 rows is useless as manual checking is pretty much impossible.

**nadeemm** · 10-20-2018, 09:42 AM

what i'm doing with this file here is just sorting out data from "Raw data" sheet into "Sorted data" sheet upon some conditions,
my only task is not getting achievable when i want to operate the Unique JOBs from the duplicates into a separate column and also
put its respective 'Statuses" in their respective columns, (as i explain earlier)

**nadeemm** · 10-20-2018, 09:43 AM

Thanks Glenn it worked

**Glenn Kennedy** · 10-20-2018, 10:43 AM

Fine. But with 9000 rows to look at (not the 10-20 I wanted) I have now NO IDEA what you want!!

So. Each row in raw data column A is unique. So where are the duplicates that you are talking about?

On what basis did you decide that rows 7, 8 & 9 had the same job number? Where did the number come from?

Which columns in which sheet should i be looking at!!!

**Glenn Kennedy** · 10-20-2018, 10:57 AM

On sorted data, these three Telecom IDs:
217670
223526
219231

have been grouped together in column I with the same job number. Why? There is nothing in common (as far as I can see) in the raw data rows for these 3. Where did the unique job no. come from.

Make a SENSIBLE sample file of 10-20 rows and clearly indicate what your expected results are.

**nadeemm** · 10-20-2018, 11:04 AM

Dear sir,
I'm sorry if i'm not explaining it clearly

I was explaining the above calculations on the "Telecom JOB numbers" (column I) which has duplicates

& in the "SOrted data" sheet i have just written it to explain what i want to achieve with sample data

Note: please use the "Sorted data" sheet to write your formula

for Example:
The Duplication JOBs (column I) are having each separate "Status" for each milestone (Like - Integration Status, On-Air Status, PAT status, Handover Status..)
what I need is to pull out the Unique JOBs in a Separate Column (Column J) (existing in the "Sorted Data" Sheet )and provide its 'Status' for these different milestones (Columns K,N,R,U)(also existing in the "Sorted Data" Sheet ),

like IF a single "Telecom JOB" which has many Duplicates, all may or may not have its respective "statuses" for their respective milestones(Like - Integration Status, On-Air Status, PAT status, Handover Status..)
IF all of the duplicates of a single JOB has all the "Status" filled then the Unique JOb will show the same 'Status' else it will show "In Progress"

**Glenn Kennedy** · 10-20-2018, 11:20 AM

I think I understand now...

Take a look at this.

**nadeemm** · 10-20-2018, 11:54 AM

wow... amazing it worked perfectly
you are a true genius, hats off to you sir!

Thanks a lot

just in case, If possible can we convert this formula to VBA code! (Not so important as the formula is working for me,
I'm asking just because whenever i filter something it takes time in caluations and works slow)

**Glenn Kennedy** · 10-20-2018, 11:58 AM

You're welcome.

If that takes care of your original question, please select "Thread Tools" from the menu link above and mark this thread as SOLVED.

It'd also be appreciated if you were to click the Add Reputation button at the foot of any of the posts of all members who helped you reach a solution.

Extract Unique values from the list of Duplicates and copy into another column

LinkBack

Thread Tools

Rate This Thread

Display

Extract Unique values from the list of Duplicates and copy into another column

Re: Extract Unique values from the list of Duplicates and copy into another column

Re: Extract Unique values from the list of Duplicates and copy into another column

Re: Extract Unique values from the list of Duplicates and copy into another column

Re: Extract Unique values from the list of Duplicates and copy into another column

Re: Extract Unique values from the list of Duplicates and copy into another column

Re: Extract Unique values from the list of Duplicates and copy into another column

Re: Extract Unique values from the list of Duplicates and copy into another column

Re: Extract Unique values from the list of Duplicates and copy into another column

Re: Extract Unique values from the list of Duplicates and copy into another column

Re: Extract Unique values from the list of Duplicates and copy into another column

Re: Extract Unique values from the list of Duplicates and copy into another column

Re: Extract Unique values from the list of Duplicates and copy into another column

Re: Extract Unique values from the list of Duplicates and copy into another column

Re: Extract Unique values from the list of Duplicates and copy into another column

Re: Extract Unique values from the list of Duplicates and copy into another column

Re: Extract Unique values from the list of Duplicates and copy into another column

Re: Extract Unique values from the list of Duplicates and copy into another column

Re: Extract Unique values from the list of Duplicates and copy into another column

Re: Extract Unique values from the list of Duplicates and copy into another column

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Extract Unique Values from Duplicates with Criteria

Creating a list of unique values out of duplicates

[SOLVED] Formula to Extract Unique Values/Remove Duplicates Across Multiple Columns of Data

[SOLVED] Array Formula to extract all values (both duplicates and unique values in a range in Excel

List of unique text values from a list with duplicates

[SOLVED] Extract a list that allows specific value to have duplicates but not any other values.

[SOLVED] Extract an unique list from 2 different lists without duplicates

Bookmarks

Bookmarks

Posting Permissions