Solved

Excel to return a value of one on the first instance of finding a duolicate field in a tabled column

Posted on 2013-01-16
7
287 Views
Last Modified: 2013-01-16
hard to phrase, but here goes;

a) have a column of data that occasionally has duplicate values.  Have an adjacent column with =IF(MAX(COUNTIF(F:F,F:F))>1,"Yes","No"
b) next colmun over returns a 0 if No and a 1 if Yes
c) issue is, i only want to the number of instances of teh duplicates, not the total number of duplicates (the total number of "Yes"

This is a bizarre report based on Access Tables with Pivot Report outputs to achieve a syummarized report.  there is a long winding road why this is; but it is and that means the tally of unique records can;t depend on sorting, conditional formatting or juts a tradition Excel type solution.  really should be an Access query, but another long story is "not possible"  must be a read only data connection from Access tables to Excel.

I can try anything; just has to return the duplicate value count only once in a numeric way that can be summed at the row level.  Again, hard as heck to even phrase teh question.  Any takers, please?
0
Comment
Question by:VirtualKansas
[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
  • 4
  • 2
7 Comments
 
LVL 23

Accepted Solution

by:
NBVC earned 500 total points
ID: 38784656
Can you use instead.

=IF(AND(COUNTIF(F:F,F1)>1,COUNTIF(F$1:F1,F1)=COUNTIF(F:F,F1)),"Yes","No")
0
 

Author Comment

by:VirtualKansas
ID: 38784696
Wow; I think that worked!  Lots of data to test, test, test; I'll let you know quick as i can and thank you for that quick reply!
0
 
LVL 50

Expert Comment

by:barry houdini
ID: 38784848
Assuming up to 1000 rows of data this version will give you 1/0 directly

=(COUNTIF(F$1:F$1000,F1)>1)*(COUNTIF(F1:F$1000,F1)=1)

regards, barry
0
Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

 

Author Comment

by:VirtualKansas
ID: 38784860
OK; I'm digging deep here, so thanks for patience.

The grosss number returned looks like its spot on, and that is huge; but there is a catch that's nagging me.  The instance of "Yes" responses seems to not line up where the duplicate occured.  Though the gross number is best I've gotten; there is just one detail in the summary report.  

There is a bucket list that the "Yes" responses should correspond to the row where the duplicate resides.  Looks as though its actually down one row.  This could skew the bucket count, though the gross numbers look right.

Hard to complain with such a fast and amazing answer.  Any further thoughts or should I open a new thread and explain the request better?  God know's you earned these points just by speed & accuracy...
0
 
LVL 50

Expert Comment

by:barry houdini
ID: 38784939
Where did you put the first formula? Both NB_VC's suggestion and mine are designed to start in row 1, if you start in row 2 you should change all the F1 references to F2, or F3 for row 3 etc.

regard, barry
0
 

Author Closing Comment

by:VirtualKansas
ID: 38784954
OK; "by: NB_VC" I'm stunned to know how =IF(AND(COUNTIF(F:F,F1)>1,COUNTIF(F$1:F1,F1)=COUNTIF(F:F,F1)),"Yes","No") did it and seemingly kept buckets in order.  The only two issues I found were actually typo's in the database.  I cannot thank you enough; I'll keep checking but I have to give you all the thanks I can muuster and close this one out.  TY
0
 

Author Comment

by:VirtualKansas
ID: 38785018
Crud; I have to be honest and say by: barryhoudini caught my mistake.  In my rush to test I didn't stop to look at cell references.  Apologies for hastey close out of points.  I'll be back I'm sure and thanks, both...
0

Featured Post

[Webinar] Code, Load, and Grow

Managing multiple websites, servers, applications, and security on a daily basis? Join us for a webinar on May 25th to learn how to simplify administration and management of virtual hosts for IT admins, create a secure environment, and deploy code more effectively and frequently.

Question has a verified solution.

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

This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

732 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