VirtualKansas
asked on
Excel to return a value of one on the first instance of finding a duolicate field in a tabled column
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))>
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?
a) have a column of data that occasionally has duplicate values. Have an adjacent column with =IF(MAX(COUNTIF(F:F,F:F))>
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?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Assuming up to 1000 rows of data this version will give you 1/0 directly
=(COUNTIF(F$1:F$1000,F1)>1
regards, barry
=(COUNTIF(F$1:F$1000,F1)>1
regards, barry
ASKER
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...
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...
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
regard, barry
ASKER
OK; "by: NB_VC" I'm stunned to know how =IF(AND(COUNTIF(F:F,F1)>1,
ASKER
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...
ASKER