Dear excelers,
I'm looking for a formula that could check if in the same cell across sheets 1-1000 occured any duplicates. I've never looked up so many pages in google, but i just cant come up with a solution. The formula can return logical value or the total numer of duplicates. I would be happy with either one.
Thank you for your help.
Hi roax and welcome to the forum,
You are looking for a "3D" formula in Excel, similar to CountIf(). On this site it claims there is no 3D function of CountIf().
https://support.office.com/en-us/art...2-787d0bc888b6
I don't see any 3D functions that might give you an answer. If you could supply a sample workbook with about 4 sheets showing where and how you want your answer might help us solve this question.
One test is worth a thousand opinions.
Click the * Add Reputation below to say thanks.
Try this:
- Put the names of all relevant sheets into an appropriate range, say X1:1000 (there should be no empty cells);
- Use the following formula to get the total number of duplicates in cells A1:
=SUMPRODUCT(COUNTIF(INDIRECT("'"&X1:X1000&"'!A1"),A1))-1
Hey, thanks for you quick answers.Originally Posted by Root_
I've made sample workbook and tried to use the formula that you gave me. The numbers on the other sheets are {4,2,4,2}, yet the formula returns value -1 not 2. It seems it is looking for the duplicates of the A1 value (in this case 0) from the current sheet.
I'm attaching the the workbook as it was proposed
test1.xlsx
Hi roax,
I couldn't get Root's formula to work either.
@roax, @MarvinP:
My bad, I've obviously misread the requirements. Somehow, I thought about checking for duplicates for a cell on the current sheet, not about any duplicates. I will try to suggest something different.
OK, try this for the total number of numerical duplicates in cells A1 of your test file:
=COUNT(Sheet1:Sheet4!A1)-SUM(--(FREQUENCY(Sheet1:Sheet4!A1,Sheet1:Sheet4!A1)>0))
Limitation: this formula works for numerical values only; it ignores text values and blank cells.
In the attached this works for numbersand this for textFormula:
Please Login or Register to view this content.All array entered.Formula:Please Login or Register to view this content.
Haven't found a way to do it for mixed data, and haven't managed to adapt Root_'s formula to text.
Dave
Hey, thank you for you answer.In the attached this works for numbersand this for textFormula:Please Login or Register to view this content.All array entered.Formula:Please Login or Register to view this content.
Haven't found a way to do it for mixed data, and haven't managed to adapt Root_'s formula to text.
It was just what I was looking for.
I'm just wondering if it is possible for the formula to exclude the blank cells or some given value. It would be great if you could help me with that
@Dave,
I just can't make your formula work for my example of this problem. What am I doing wrong? Teach me please.
3D Max Frequency.xlsx
Cannot get Dave's formula to work either. For example, when A1's have the same numerical value on all four sheets, i returns 1. My formula from post #7 returns 3.
My formula returns 3 in my upload as well. I'm working on a construction that I am also going to try to adapt to your formula. Yours is much shorter and simpler than mine.
@ MarvinP
Looking at your upload I think I may have interpreted incorrectly. My formula focuses on duplicates in single cells A1/B1 only across 7 sheets.
Your upload gives me pause for thought. Due to the nature of INDIRECT the cell reference to A1 remains absolute. I've never worked with relative references across sheets with INDIRECT before as your upload suggests. I am going to get right on it.
@roax
I am working on the blank cells part. The only method that occurs to me will change all numbers to text "numbers". It doesn't seem that will cause problems for the duplicate counts, however if the construction is used for ... summing/averaging or such it would need some work.
Last edited by FlameRetired; 09-25-2017 at 11:22 AM.
Edit I spoke too soon. This formula counts duplicate blanks. The one post #16 does not. My apologies.
I uploaded MarvinP's workbook with this formula array entered in 'Dup Answer' A1 filled down and across.
@ Marvin Though your workbook has only numbers this formula works with numbers/text/mixed data/ignores blanks.
=SUM(IF(FREQUENCY(MATCH(CELL("contents",IF(1,+INDIRECT("'"&$G$1:$G$4&"'!"&CHAR(COLUMNS($A$1:A$1)+64)&ROWS(A$1:A1))))&"",CELL("contents",IF(1,+INDIRECT("'"&$G$1:$G$4&"'!"&CHAR(COLUMNS($A$1:A$1)+64)&ROWS(A$1:A1))))&"",0),ROW(INDIRECT("1:"&COUNTA($G$1:$G$4))))>1,1))
Formula:Please Login or Register to view this content.
Last edited by FlameRetired; 09-25-2017 at 10:14 PM.
@ Root_
The only way I could get your formula to work on text as well as numbers was to eliminate COUNT. In the end there is little difference from the one posted immediately above.
=SUM(--(FREQUENCY(MATCH(CELL("contents",IF(1,+INDIRECT("'"&$F$1:$L$1&"'!"&CHAR(COLUMNS($A$1:A$1)+64)&ROWS(A$1:A1))))&"",CELL("contents",IF(1,+INDIRECT("'"&$F$1:$L$1&"'!"&CHAR(COLUMNS($A$1:A$1)+64)&ROWS(A$1:A1))))&"",0),ROW(INDIRECT("1:"&COUNTA($F$1:$L$1))))>1))
I failed to mention that I didn't come up with that "CELL("contents",IF(1,+INDIRECT" construction on my own.
I borrowed shamelessly from Lori in XOR LX's blog.
That formula post #13 needs a tweak.It wasn't ignoring blanks. Now it does.Formula:Please Login or Register to view this content.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks