Dynamic Counts

[ellison]

Good afternoon, we are tidying up some data & the data seems to be growing quicker than we are tidying it up!

What we confirm (by reviewing it manually) is whether the relationship between our "old data" and our "new data" is TBC, Yes or No.

***What we'd like to put in are dynamic counts of i) the total number of possibilities ii) # TBC, iii) # Yes iv) # No***

By the way, we have looked at various ways of concatenating info, then doing pivot tables & counts... But we do this so often every day on all sorts of differently updated files that we are hoping there may be a better solution? Maybe involving VBA? The files are generally 20K-50K rows and formula type options don't seem to work I'm afraid.

Our Data is arranged in 3 columns:

NB, the explanation column is only to help illustrate what's here, we wouldn't actually need that!

Thanks for any help

Old Data	New Data	Status	Total # Possibilities	# TBC	# Yes	#No	Explanation (only for illustration)
composite	plastic	TBC	1	1	0	0	there is only one possible relationship between the old and new data. And it is "TBC".
wood	oak	TBC	2	1	1	0	There are 2 possiblities for "wood" = oak & rustic. And one of those is "TBC" and one is "yes"
wood	rustic	Yes	2	1	1	0	ditto above
414878	plastic	No	1	0	0	1	Sometimes our old data is not a text string, it's an order code which is a number. It has only one possiblity and that is a "no"
raw materials	finished product	No	2	0	1	1	For "raw materials", there are 2 possible "new data's". and one is "yes" and one is "no".
raw materials	check quarantine	Yes	2	0	1	1	ditto above

 

[pbornemeier]

Use the CountIf Function in any cell except those in the status column

In 3 different cells use each of the following:

=COUNTIF(C:C,"TBC")
=COUNTIF(C:C,"Yes")
=COUNTIF(C:C,"No")

 

[mikerickson]

It looks like =COUNTIF(A:A, A1) would give you the results you posted.

 

[pbornemeier]

What would limit formula types from working? It may cause the same problems with any VBA solution.

 

[ellison]

Thanks for coming back. Formulas tend to grind to a halt on the files that we are reviewing because of there size. (& the actual PC's we are using are pretty beefy!)

Also, if I use the formula =COUNTIF(C:C,"TBC"), I can get counts of the "total" number of TBC's, but that isn't what we are after...

We need the solution to check each how many TBC (or yes or No) there are for each specific relationship.

Thanks

 

[ellison]

And same on =COUNTIF(A:A, A1) I'm afraid...

Just to try & explain the relationship at bit better...

Old Data, New Data, Status:
composite, plastic, TBC there is only one possible relationship for any old data which = "composite" and the status of that is "TBC"

wood, oak, TBC
wood, rustic, YES there are 2 possibilities for old info = wood. One of those is "TBC" one is "yes".

414878, plastic, no there is only one possible relationship for any old data which = "414878" and the status of that is "No"

raw materials, finished product, No
raw materials, check quarantine, Yes there are 2 possibilities for old info = raw materials. One of those is "finished product" and that is "no". the other is "check quarantine" and that is "yes".

 

[ellison]

Hi, just wondering if somebody may be able to shed some light on this one?!

We are back to copying, pasting, pivot tabling, concatenating & vlookuping...

And I can't help but think that that sort of combination of workings PLUS fat fingers could lead to some errors.

Any & all help much appreciated!

Best

 

[MARK858]

Based on the data posted, the below gives the same result...
If this isn't what you want then please supply new data to demonstrate what is required.

Book1
	A	B	C	D	E	F	G
1	Old Data	New Data	Status	Total # Possibilities	# TBC	# Yes	#No
2	composite	plastic	TBC	1	1	0	0
3	wood	oak	TBC	2	1	1	0
4	wood	rustic	Yes	2	1	1	0
5	414878	plastic	No	1	0	0	1
6	raw materials	finished product	No	2	0	1	1
7	raw materials	check quarantine	Yes	2	0	1	1
Sheet5

Cell Formulas
Range	Formula
D2:D7	D2	=COUNTIF($A$2:$A$7, A2)
E2:E7	E2	=COUNTIFS($A$2:$A$7,A2,$C$2:$C$7,"TBC")
F2:F7	F2	=COUNTIFS($A$2:$A$7,$A2,$C$2:$C$7,"Yes")
G2:G7	G2	=COUNTIFS($A$2:$A$7,$A2,$C$2:$C$7,"No")

 

[ellison]

Huge thanks Mark, but whilst that works excellently on our smaller spreadsheets, it grinds to a half on the larger ones.

We are wondering if a VBA based solution may be the way forward?!

 

[MARK858]

Out of interest if you strip all other formulas out of the sheet (or on a copy of the sheet without any formulas) and run the code below what does the msgbox state? Is that time for running the macro an issue (the code changes the formulas to values so there aren't any formulas after the code has run)? and how many rows of data do you have?

Sub XTime()
    Dim lr As Long
    Dim t As Double
    t = Timer
   
    With Application
        .Calculation = xlCalculationManual
        .ScreenUpdating = False
    End With
   
    lr = Range("A" & Rows.Count).End(xlUp).Row
   
    Range("D2:D" & lr).Formula = "=COUNTIF(" & Range("A2:A" & lr).Address & ",$A2)"
    Range("E2:E" & lr).Formula = "=COUNTIFS(" & Range("A2:A" & lr).Address & ",$A2," & Range("C2:C" & lr).Address & ",""TBC"")"
    Range("F2:F" & lr).Formula = "=COUNTIFS(" & Range("A2:A" & lr).Address & ",$A2," & Range("C2:C" & lr).Address & ",""Yes"")"
    Range("G2:G" & lr).Formula = "=COUNTIFS(" & Range("A2:A" & lr).Address & ",$A2," & Range("C2:C" & lr).Address & ",""No"")"
    Range("D2:G" & lr).Value = Range("D2:G" & lr).Value
   
     With Application
        .Calculation = xlCalculationAutomatic
        .ScreenUpdating = True
    End With
   
    MsgBox "Code took " & Format(Timer - t, "0.00 secs")
   
End Sub