Dynamic Counts

ellison

Board Regular
Joined
Aug 1, 2012
Messages
161
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 DataNew DataStatusTotal # Possibilities# TBC# Yes#NoExplanation (only for illustration)
compositeplasticTBC1100there is only one possible relationship between the old and new data. And it is "TBC".
woodoakTBC2110There are 2 possiblities for "wood" = oak & rustic. And one of those is "TBC" and one is "yes"
woodrusticYes2110ditto above
414878plasticNo1001Sometimes 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 materialsfinished productNo2011For "raw materials", there are 2 possible "new data's". and one is "yes" and one is "no".
raw materialscheck quarantineYes2011ditto above
 

Some videos you may like

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.

pbornemeier

Well-known Member
Joined
May 24, 2005
Messages
3,795
Use the CountIf Function in any cell except those in the status column

In 3 different cells use each of the following:
Code:
=COUNTIF(C:C,"TBC")
=COUNTIF(C:C,"Yes")
=COUNTIF(C:C,"No")
 

pbornemeier

Well-known Member
Joined
May 24, 2005
Messages
3,795
What would limit formula types from working? It may cause the same problems with any VBA solution.
 

ellison

Board Regular
Joined
Aug 1, 2012
Messages
161
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

Board Regular
Joined
Aug 1, 2012
Messages
161
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

Board Regular
Joined
Aug 1, 2012
Messages
161
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

MrExcel MVP
Joined
Nov 12, 2010
Messages
12,371
Office Version
365, 2010
Platform
Windows, Mobile
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
ABCDEFG
1Old DataNew DataStatusTotal # Possibilities# TBC# Yes#No
2compositeplasticTBC1100
3woodoakTBC2110
4woodrusticYes2110
5414878plasticNo1001
6raw materialsfinished productNo2011
7raw materialscheck quarantineYes2011
Sheet5
Cell Formulas
RangeFormula
D2:D7D2=COUNTIF($A$2:$A$7, A2)
E2:E7E2=COUNTIFS($A$2:$A$7,A2,$C$2:$C$7,"TBC")
F2:F7F2=COUNTIFS($A$2:$A$7,$A2,$C$2:$C$7,"Yes")
G2:G7G2=COUNTIFS($A$2:$A$7,$A2,$C$2:$C$7,"No")
 

ellison

Board Regular
Joined
Aug 1, 2012
Messages
161
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

MrExcel MVP
Joined
Nov 12, 2010
Messages
12,371
Office Version
365, 2010
Platform
Windows, Mobile
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?

Code:
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
 

Watch MrExcel Video

Forum statistics

Threads
1,095,267
Messages
5,443,432
Members
405,235
Latest member
1Thess521

This Week's Hot Topics

Top