LinkBack URL
About LinkBacks
I have a worksheet where I want to count all unique values. The data is spread across rows and columns. A lot of the solutions I'm reading are for a single column only and I can't figure it out.
Thanks!
I have a worksheet where I want to count all unique values. The data is spread across rows and columns. A lot of the solutions I'm reading are for a single column only and I can't figure it out.
Thanks!
Attach a sample of your workbook, showing enough to demonstrate your full dataset.
Go Advanced -> Manage Attachments -> Upload
Thank you. Just uploaded the sheet I'm working with. I just want to figure out how many unique "play names" are on the entire sheet.
Try in "B42"Formula:
Please Login or Register to view this content.
atul
If my answer (or that of other members) has helped you, please say "Thanks" by clicking the Add Reputation button at the foot of one of their posts.
Also: if your problem is solved, please take the time to mark your thread as SOLVED by going to the top of your first post, selecting "Thread Tools" and then "Mark thread as solved".
I think your formula only looks at the B column and that worked. It counted all unique values from B2:B40. What I need is unique values from B2:T40. What would even be better is if I could choose the specific columns. So the better range would be columns C, F, J, M, Q, and T. Still rows 2:40.
What you are asking for, AFAIK, is VERY difficult to achieve. I know of only one formula-based approach. However, you do have some problems.
Problem 1. Your data are NOT contiguous.
Problem 2. You have merged cells all over the place. A total nightmare.
Problem 3. There are many blank cells in your range.
Problem 4. You have 6 lists.
I have attached a sample sheet to illustrate the issue. I have used 4 lists. There are NO blank cells.
the formula is horrendous:
Formula:Please Login or Register to view this content.
It could be extended to 6 lists, but time and coffee would be needed. There's no guarantee that your merged cells wouldn't screw it all up. However, your raw data would HAVE to change. Delete the value in J3 (JJ). It's one of the unique values. The formula falls over. Now add a space to the cell. The formula works again, but would have to have 1 subtracted from the result to give a correct count (as it counts the space as a unique value)
So... can YOU modify the INPUT so that a space is returned (or some other symbol) in EVERY cell that does not otherwise have a value.
You probably can modify the formula to allow for blanks, but that adds a level of complexity that is way beyond my pay grade... not that I get paid for this stuff!!
Glenn
None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.
Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh
OK. Ignore my last post. I have found another way!! It is PERFECTLY OK with blank cells and is MUCH easier to adapt to 6 lists.
I won't be doing any more now as it's beer o'clock here in Ireland. So... I'll be back in the UK morning.
Oh man, leave me hanging! Thanks for your help.
Oh man... deprive me of my beer.... no way!!
Dear Glenn Sir : You are correct.
Never! Thank you for your help thus far and looking forward to seeing your solution tomorrow.Originally Posted by Glenn Kennedy
OK. One step forward and one step backwards!!
I did waste a bit of time, because I misread your post and was counting UNIQUE values (totally stupid of me, to be honest) rather than DISTINCT values. However, I now have it working on MY test sheet. I have attached it here. You can test it. Add a few distinct names. Add a few duplicates. Make a few blank cells. It's fine.
It is, however, an array formula.
Formula:Please Login or Register to view this content.
Array Formulae are a little different from ordinary formulae in that they MUST be confirmed in the FIRST CELL ONLY by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. After that, the array can be dragged down as normal, to cover the desired range.
You will know the array is active when you see curly brackets { } - or "curly braces" for those of you in the USA, or "flower brackets" for those of you in India - appear around the outside of your formula. If you do not use CTRL+SHIFT+ENTER you will (almost always) get an error message or an incorrect answer. Press F2 on that cell and try again.
Don't type the curly brackets yourself - it won't work...
If you see any issues with my sheet, let me know. NOW open your sheet. Same ranges, same formula, silly answer. There is "something" in your blank cells that is throwing the calculation out. On my sheet, if you include a space in a cell, it WILL count as a distinct value, but not as a fraction of one!!
It's not possible to track down any further as you are working with linked data, to which I have no access.
Does anyone else know what might be wrong by looking at the link??
The blanks are being overcounted, the logic is frying my brain (and the aesthetics are frying my eyes), but I think that this works.
=SUM(IFERROR(1/(COUNTIFS(list1,list1,list1,">A")+COUNTIFS(list2,list1,list2,">A")+COUNTIFS(list3,list1,list3,">A")+COUNTIFS(list4,list1,list4,">A")+COUNTIFS(list5,list1,list5,">A")+COUNTIFS(list6,list1,list6,">A")),0))
+SUM(IFERROR(1/(COUNTIFS(list2,list2,list2,">A")+COUNTIFS(list1,list2,list1,">A")+COUNTIFS(list3,list2,list3,">A")+COUNTIFS(list4,list2,list4,">A")+COUNTIFS(list5,list2,list5,">A")+COUNTIFS(list6,list2,list6,">A")),0))
+SUM(IFERROR(1/(COUNTIFS(list3,list3,list3,">A")+COUNTIFS(list1,list3,list1,">A")+COUNTIFS(list2,list3,list2,">A")+COUNTIFS(list4,list3,list4,">A")+COUNTIFS(list5,list3,list5,">A")+COUNTIFS(list6,list3,list6,">A")),0))
+SUM(IFERROR(1/(COUNTIFS(list4,list4,list4,">A")+COUNTIFS(list1,list4,list1,">A")+COUNTIFS(list2,list4,list2,">A")+COUNTIFS(list3,list4,list3,">A")+COUNTIFS(list5,list4,list5,">A")+COUNTIFS(list6,list4,list6,">A")),0))
+SUM(IFERROR(1/(COUNTIFS(list5,list5,list5,">A")+COUNTIFS(list1,list5,list1,">A")+COUNTIFS(list2,list5,list2,">A")+COUNTIFS(list3,list5,list3,">A")+COUNTIFS(list4,list5,list4,">A")+COUNTIFS(list6,list5,list6,">A")),0))
+SUM(IFERROR(1/(COUNTIFS(list6,list6,list6,">A")+COUNTIFS(list1,list6,list1,">A")+COUNTIFS(list2,list6,list2,">A")+COUNTIFS(list3,list6,list3,">A")+COUNTIFS(list4,list6,list4,">A")+COUNTIFS(list5,list6,list5,">A")),0))
My only thought is that if the blanks were being overcounted, then it is possible that genuine records could also be overcounted.
The workbook has recalculated to #value errors so I need to reload it to check properly. must remember to paste values before editing this time.
It's only the blank cells that result from the link that seem to be problematic... In List 1 there are 5 such cases (rows10,13,21,27 &30), but they are evaluating as 23!!!
I think my formula is OK... it's someting to do with the linked cell. But I don't know what... However, yours seems to overcome the linked cell issue...
We'll see what the OP has to say....
Last edited by Glenn Kennedy; 08-15-2018 at 12:18 PM.
I haven't looked at Glenn's or Jason's yet. I've been doing battle with this since the day it was posted. Isolating the section headers that I took not to be team names drove me bats.
I needed to consolidate the team names in one table.
I did it as a single formula. It should have been done in three ... but why do it the easy way? Besides I'm tired now.LOL
1st step isolate the relevant columns A, H and O with CHOOSE(COLUMNS($A$1:A$1),$A$2:$A$40,$H$2:$H$40,$O2:$O40)
Nest that in this formula ... A45:C56.
Formula:Please Login or Register to view this content.
Then this returns 17 for the number of unique teams.Formula:Please Login or Register to view this content.
Last edited by FlameRetired; 08-15-2018 at 07:31 PM.
Dave
The only way I have been able to account for the links missing or present is to look at the adjacent columns B, I and P. I also made 3 shorter formulas.
In A45:A56Formula:Please Login or Register to view this content.
B45:B56 and C45:C56 are the same though addressing different columns.Formula:Please Login or Register to view this content.Formula:Please Login or Register to view this content.
I'm still trying to make a shorter set of formulas.
These are shorter.Formula:Please Login or Register to view this content.Formula:Please Login or Register to view this content.Formula:Please Login or Register to view this content.
The SUMPRODUCT/COUNTIF that counts the unique names is the same.
Last edited by FlameRetired; 08-15-2018 at 11:00 PM.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks