Friday, September 23, 2016

Troubleshooter of Error: count of unique text-value cells in one column that also have specific text values in two other columns

count of unique text-value cells in one column that also have specific text values in two other columns

I have a worksheet that has 28 columns, with data in almost 11,000 rows.

Column D is "name" which is a text string
Column X has text values of either Yes or No
Column AA has a 4 character text string (e.g.
ABCD - only 6 possible values in this column, representing business units)

I need to count the number of unique values in D for each unique value in AA, e.g.
ABCD  has 5 unique names, EFGH has 30 unique names, etc.
This is UniqueNamesPerBusinessUnit
Next, I need to count the number of unique values in D that also have the value Yes in X, for each value in AA.
This is UniquePositiveNamesPerBusinessUnit
This will allow me to then get a "score" percentage of UniquePositiveNamesPerBusinessUnit/UniqueNamesPerBusinessUnit
I'm more than willing to make intermediate calculations in separate cells - one for each business unit for UniqueNames and then UniquePositiveNames?

I've tried samples that use matches within frequency, but only ever get #N/A values when copying the formula from the office site examples, even just to get a unique list of names, but I need to also consider the extra parameters of business unit, and then
the Yes/No in column X, and at that point my eyes glaze over.

Ideas?

Keys to the Problem count of unique text-value cells in one column that also have specific text values in two other columns

Download Error Fixer for Free Now

Let's say the data are on a sheet named Data, from row 2 to row 11,000.
On another sheet, enter the text Business Unit in A1,
UniqueNames in B1, UniquePositiveNames in C1, and
Score in D1.
Enter the unique business units (ABCD etc.) in A2:A7.
In B2, enter the following array formula, confirmed with
Ctrl+Shift+Enter:
=SUM(IF(FREQUENCY(IF(Data!$AA$2:$AA$11000=$A2,MATCH(Data!$D$2:$D$11000,Data!$D$2:$D$11000,0)),IF(Data!$AA$2:$AA$11000=$A2,MATCH(Data!$D$2:$D$11000,Data!$D$2:$D$11000,0)))>0,1))
In C2, enter the following array formula, confirmed with
Ctrl+Shift+Enter:
=SUM(IF(FREQUENCY(IF((Data!$AA$2:$AA$11000=$A2)*(Data!$X$2:$X$11000="Yes"),MATCH(Data!$D$2:$D$11000,Data!$D$2:$D$11000,0)),IF((Data!$AA$2:$AA$11000=$A2)*(Data!$X$2:$X$11000="Yes"),MATCH(Data!$D$2:$D$11000,Data!$D$2:$D$11000,0)))>0,1))
In D2, enter the following 'ordinary' formula
=C2/B2
and format D2 as a percentage.
Select B2:D2 and fill down to B7:D7.

Machine Throttling
  • Click the Start button.
  • In the Search box, type "regedit" (without quotes) and press Enter.
  • Look for the following registry key: "HKEY_LOCAL_MACHINE\SOFTWARE\ Microsoft\ SQMClient\Windows\DisabledSessions".
  • On the right window pane, delete the subkey: "Machine Throttling".
  • Restart your computer.

Recommended Method to Repair the Problem: count of unique text-value cells in one column that also have specific text values in two other columns:

How to Fix count of unique text-value cells in one column that also have specific text values in two other columns with SmartPCFixer?

1. You can Download Error Fixer here. Install it on your system. When you open SmartPCFixer, it will perform a scan.

2. After the scan is finished, you can see the errors and problems need to be repaired. Click Fix All.

3. The Repair part is finished, the speed of your computer will be much higher than before and the errors have been fixed. You can also use other functions in this software. Like dll downloading, windows updating and print spooler error repair.


Related: How Can You Update & Download NVidia 6100, 6800 GS/XT Display Driver v.260.89 WHQL,Where to Download NVidia GeForce 8800 GT WHQL-certified driver v.196.21,How to Update & Download NVidia GeForce Go 7400 Display Driver v.260.19.12,[Solved] Download NVidia GeForce GTX 460M Driver v.331.82,Download NVidia Quadro Plex Model IV VGA Driver v.304.43 Certified,Way to Download RealTek RTL8100C(L) Driver v.5.01,Way to Download RealTek RTL8100E Drivers v.694,Way to Update & Download RealTek RTL8101L Auto Installation Program v.6.110 driver,Method to Update & Download RealTek RTL8111G PXE and RPL ROM code v.2.58 driver,How to Update & Download RealTek RTL8411B(N) Driver v.10.003,Best Way to Update & Download ASUS A53SV nVidia Graphics Driver v.8.17.12.6686,Method to Herunterladen ASUS K75VJ Intel Rapid Storage Technology Treiber v.11.6.0.1030,How Can You Update & Download ASUS CG8580 Intel Chipset Driver v.9.3.0.1019,Method to Update & Download ASUS K41VD Intel INF Update Driver v.9.1.1.1015,Way to Update & Download ASUS Pro70T NB Probe v.3.0.0026 driver
Read More: Creating a chart [Solved],Tech Support: Corrupt Registry, missing system file, can't locate file - happened after windows update & automatic reboot,Troubleshooting:count the number of cells \"without a range\",How to Fix Error - counting values that only appears once?,Solution to Error: Copy and paste from 1 worksheet to another (problems).,Closing Internet Explorer causes error,Compare 2 Tables and Calculate the PROFIT Faster,Computer goes to Windows Boot Manager after Start,Connect to one of my network computer remotely when don't allow exception box Marked,computer locking up randomly

No comments:

Post a Comment

Note: Only a member of this blog may post a comment.