LinkBack URL
About LinkBacks
Hello,
I have a list of numbers and I'd like to count the duplicates but the only way to explain what I'd like to do is to show you in a workbook.
Thank you for your time in advance
Sans
Hello,
I have a list of numbers and I'd like to count the duplicates but the only way to explain what I'd like to do is to show you in a workbook.
Thank you for your time in advance
Sans
Last edited by sans; 11-03-2011 at 09:52 AM.
..........
Last edited by Jack in the UK; 11-03-2011 at 07:20 AM.
...................................
Last edited by Jack in the UK; 11-03-2011 at 07:20 AM. Reason: Going Mad !
....................
Try the UDF:
Please Login or Register to view this content.
Please Login or Register to view this content.
Thank you very much for your replies.
How do I get the result for each row compared to its previous rows? For instance, B30:H30 compared to B29:H29, B28:H28 etc, then B31:H31 compared to B30:H30,B29:H29 etc. So basically get the all the counts for each row compared to the previous 11 rows.
I hope I am making sense,
Thank you
Sans
I think so - see attachment.Originally Posted by sans
But the formula with OFFSET - not a good idea. Maybe fill the table with macro rather than UDF?
This is exactly what I was looking for! Thank you so much for your help!!! It works great.
@Nilem
Wouldn't this sufffice ?
Please Login or Register to view this content.
@Snb
It's a very good idea and a very nice code.
Maybe I'm wrong, but I do not like to use Application (WorksheetFunction) in the code.
Is it possible for the code to include a range such as A1:G1 for the numbers to be checked and results which I can easily alter?
Thank you
Can you show in the book (file) what solution do you expect?
The solution is great, it is exactly what I am looking for. However, when I copy the sheet to another workbook, I have to change the ranges of the numbers. And I don't quite understand how to do it in the vba code as I can't see any range such as C1:I1 that can be changed.
Sans, do not look the VBA.
UDF is a custom function that works like a normal function of the sheet.
In the formula:
$B9:$H9 is the first rangePlease Login or Register to view this content.
OFFSET($B9:$H9,-K$7,0) - this is the second range, which is offset from the first range on the K$7 rows up.
Just type the address ranges in the formula as needed.
To make it easier to understand. For example, you want to count the duplicates in ranges B12:H12 and A2:F2
If you work in a different workbook, copy the Module1 with code function to your book.Please Login or Register to view this content.
Last edited by nilem; 11-03-2011 at 02:44 PM.
Thank you for the explanation. I did what you suggested but I just managed to get it working as I didn't realize than the numbers in the offset by rows heading were part of the formula, so I wasn't using them and was getting wrong results. Everything works great now.
Thank you again for your help!
Sans
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks