Here is the formula for those interested: =IFERROR(IF(AND(MATCH($A3,$A:$A,0),MATCH($A3,Sheet1!$A:$A,0)),"x",),"No match") Get your first row up and running and make sure the A3 (in my example, A10 in the video) matches the placement of the value you want to find across all the sheets. And that Sheet1 (S1 in his example) match the sheet you are trying to find the match within. Then you can change the 'x' and 'No match;' to whatever you want
I've stacked the data, in Column A, added the sheet name in column B, and then built a pivot table on top of it. I put account number down the side and sheet name across the top and use count of account number as my value. I then sort on "account number" descending, based on "count of account number". I've found its quicker than building out a formula for multiple sheets and saves the step of removing duplicates.
The space where the $A10 is in the formula is supposed to actually be where your first value starts. If the first number is in cell A2, then it would be $A2. If it's in A5, then it would be $A5. The formula has to be changed and manipulated according to where your data is on your unique excel sheet.
Now can you create a list of the numbers that are used but are not on your unique list tab to search from? For instance, can you create another column for numbers that are used but not searched? Almost like an excess column. This will keep the amount of numbers used on both tabs correct.
I have the same problem but I want all sheet name in column B if the value found on other sheets. for ex: A2 value found in S1, S2 and S4, so in column B1 should have value field with S1,S2,S4. is it possible either with formula or vba
Im' having the same problem as Michelle , I've even created a workbook with the values above and am getting an error "The formula you typed contains an error." Is the formula above correct, I'm having problems with the A10 value or $A10 as its not recognizes. Appreciate any help.
You don’t explain how you selected the ranges. I understand you’re looking for A10 but what does the range in S1 represent? Data from S1 or the original sheet....
rather than following the formula used for the Times Duplicated column if you just go into the formulas in excel, choose COUNTIF, then choose the range you want to check that have the "x". So in his example video if you just select the F10 field to enter the formula you can set your range as B10:E10 and then put the x into the criteria field. That's it and much easier.
Hi, Thank you for sharing. I am interested to see the formula =IFERROR(IF(AND(MATCH($A10,$A:$A,0),MATCH($A10,'S1'!$A:$A,0)),"x",),"") in Cell B11, B12, B13, and so on.... When you put a constant $ sign, how can this change dynamically for the value in Column B11, B12, B13 and so on? I am confuse. I tried on my own but it is not giving back any results. Please advise. thank you.
Hello, I had tried the fist formula and I received a message of missing a parameter. This is the adapted formula to my situation =iferror(IF(AND(MATCH($A2,$A:$A,), MATCH($A2,Sheet2!$D:$D,0),"x"),"") Can you help me with it?
Im'm having the same problem , I've even created a workbook with thevalues above and am getting an error "The formula you typed contains an error." Is the formula above correct, I'm having problems with the A10 value or $A10 as its not recognized. Appreciate any help.
Would be great if you could post the formulas you use in the description so we could easily copy paste?
Here is the formula for those interested:
=IFERROR(IF(AND(MATCH($A3,$A:$A,0),MATCH($A3,Sheet1!$A:$A,0)),"x",),"No match")
Get your first row up and running and make sure the A3 (in my example, A10 in the video) matches the placement of the value you want to find across all the sheets. And that Sheet1 (S1 in his example) match the sheet you are trying to find the match within. Then you can change the 'x' and 'No match;' to whatever you want
This just helped us with a urgent work matter! I appreciate your posting this video!
I've stacked the data, in Column A, added the sheet name in column B, and then built a pivot table on top of it. I put account number down the side and sheet name across the top and use count of account number as my value. I then sort on "account number" descending, based on "count of account number". I've found its quicker than building out a formula for multiple sheets and saves the step of removing duplicates.
Great video. Perfect. Exactly what I needed. The formula worked perfectly. Thanks for posting!
How do you import S1 into the formula?
The space where the $A10 is in the formula is supposed to actually be where your first value starts. If the first number is in cell A2, then it would be $A2. If it's in A5, then it would be $A5. The formula has to be changed and manipulated according to where your data is on your unique excel sheet.
Hello,Is there any way to add to the formula to include if not a match place something in field for example "no match"?
How can find out duplicate data in both different excel & multipal sheets. I need solutions. Please help
Thank you for this super helpful tutorial - it saved my day!
Now can you create a list of the numbers that are used but are not on your unique list tab to search from? For instance, can you create another column for numbers that are used but not searched? Almost like an excess column. This will keep the amount of numbers used on both tabs correct.
I have the same problem but I want all sheet name in column B if the value found on other sheets.
for ex: A2 value found in S1, S2 and S4, so in column B1 should have value field with S1,S2,S4. is it possible either with formula or vba
Im' having the same problem as Michelle , I've even created a workbook with the values above and am getting an error "The formula you typed contains an error."
Is the formula above correct, I'm having problems with the A10 value or $A10 as its not recognizes. Appreciate any help.
You don’t explain how you selected the ranges. I understand you’re looking for A10 but what does the range in S1 represent? Data from S1 or the original sheet....
Need to explain the formula syntax
I am very grateful to you for uploading this video.. :)
rather than following the formula used for the Times Duplicated column if you just go into the formulas in excel, choose COUNTIF, then choose the range you want to check that have the "x". So in his example video if you just select the F10 field to enter the formula you can set your range as B10:E10 and then put the x into the criteria field. That's it and much easier.
Hi, Thank you for sharing. I am interested to see the formula =IFERROR(IF(AND(MATCH($A10,$A:$A,0),MATCH($A10,'S1'!$A:$A,0)),"x",),"") in Cell B11, B12, B13, and so on.... When you put a constant $ sign, how can this change dynamically for the value in Column B11, B12, B13 and so on? I am confuse. I tried on my own but it is not giving back any results. Please advise. thank you.
by put the range you need in this ($A10,$A:$B,0) so you will open the range
Thank you mate, this is great solution for my exact need :))) Thumbs up!
This formula is showing error after match formula I did Match($A5,$A,$A,0 after 0 its showing error plz help
Hello, I had tried the fist formula and I received a message of missing a parameter. This is the adapted formula to my situation =iferror(IF(AND(MATCH($A2,$A:$A,), MATCH($A2,Sheet2!$D:$D,0),"x"),"")
Can you help me with it?
you didn't put your data range. you left that part blank. there should be numbers beside the $A___:$A___. same thing beside the D value.
I've tried both above and below and it's not working.
Im'm having the same problem , I've even created a workbook with thevalues above and am getting an error "The formula you typed contains an error."
Is the formula above correct, I'm having problems with the A10 value or $A10 as its not recognized. Appreciate any help.
please, u may need to slow down and ensure ur listeners are following u. i was just lost from the moment u introduced the topic
Just hit pause.
THANK YOU!
EXCELLENT
Could not follow this at all.. Not a some exanation of the formula
thank you
👍🏽