pushbutton and countif/deletes

[Loopey83]

Hello

I may be asking for a bit much. I've been working with some man made macros and was wondering if there's something i can do in VBA that can make life a little easier.

Attached is my spreadsheet 28487

Tab 1 has the info we are provided by another team - currently we paste it in, strip the formatting, split the outlets in row b into singular rows so it's easier to count.
Tab 2 has a breakdown of all the media outlets that we get queries from - i have a basic countif formula counting each one separately. I did try to nest it, but ran into issues when it was counting the various ways some words are used to mean the same thing ie the word "Clyde" as it would be written multiple ways. The wildcard search caused issues elsewhere, as there are outlets like evening express, and daily express, and in the nest it would count both of those as both so it would mess with the count.
Tab 3 is the tally of all the media

I crudely recorded a couple of Macros. One to strip formatting, one to split column B using text to column thing and I've been looking at a delete macro for each media outlet.

I want to create a button that will run multiple macros one after the other, maybe with a short break. So run clear formatting, pause for a second, run split data, pause for a second. I've only managed to get a button to run one macro.
I was also wondering if i could use code that replicates the count done on tab 2 = so that it just appears in the table on the Tally based on the way the outlets are set out on Breakdown.
Finally i was wondering if there was code finds all the words that sit on the breakdown tab and removes it from row B - F which would be ran separately at end - this will allow us to identify and add new outlets as we go until we get to the stage where everything is captured within the keywords on the Breakdown tab.

and a bonus - i was wondering if there was a way to count how many rows after each heading in CAPS - the numbers of rows vary between each subject so a simple count doesn't seem to work.
I don't know enough to write a formula that finds the word COMMUNITIES, counts how many rows have text and stops counting when it reaches EXTERNAL AFFAIRS the repeat the same for each topic. The number of media logged against each query will change on a daily basis so my regular count's don't work and have been doing it manually.

Thank you so much for your time

[SamT]

I want to create a button that will run multiple macros one after the other,


Option Explicit 'At top of code page. Requires all Variable to be Explicitly Declared, finds typos.

Sub Button_Click()
Dim Calc As Variant

With Application 'Speed up Code
.ScreenUpdating = False
Calc = .Calculation 'Save Default setting
.Calculation = xlCalculationManual 'Stop auto calculation
End with

Sheets("Paste Data").Activate 'Needed because you don't specify a sheet in the Macros
Strip_formatting 'Run one macro
DoEvents 'Wait a bit
Splits_Media 'Run the next Macro
DoEvents

Sheets("Tally").Activate

With Application
.ScreenUpdating = True
.Calculate 'Solve all formulas
.Calculation = Calc 'reset to default
End with
End sub


use code that replicates the count done on tab 2 Yes, but it would take a lot of work and would be harder to maintain and modify than what you are already using. I think your current system is pretty good. I only see a couple of "Personal Preference" edits I would make. "The only good system is a system you understand."

To include sheet references in Macros, start recording the Macro on a sheet that you are not working on, then immediately select the proper sheet. Practice your actions (on a copied sheet) before recording a Macro so you don't include unnecessary actions in the Macro.

[Paul_Hossler]

What SamT said


Below is just sample / suggestions / guess since your sample WB looks like it's already had the macros run on it. Also it wasn't clear which WS was being processed

Also, I didn't understand the 'bonus question'


Plus

1. Don't use 'Selection' in a macro - use the specific object(s)

2. Always specify the Worksheet - never assume that the one you think is active will still be active after the users get involved

3. Main() chains the macros together




Option Explicit


Sub Main()
Strip_formatting
Splits_Media
Paste_as_value
Format_all
Delete_Some
End Sub




Sub Strip_formatting()
'
' Strip_formatting Macro
' strips all formatting
'
' Keyboard Shortcut: Ctrl+Shift+A
'
With Worksheets("Paste Data").Cells.Interior
.Pattern = xlNone
.TintAndShade = 0
.PatternTintAndShade = 0
End With
End Sub


Sub Splits_Media()
'
' Splits_Media Macro
' Splits Media into other cells for ease of counting
'
' Keyboard Shortcut: Ctrl+Shift+B
'
Worksheets("Breakdown").Columns("B:B").TextToColumns _
Destination:=Range("B1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
Semicolon:=True, Comma:=True, Space:=False, Other:=False, FieldInfo:= _
Array(1, 1), TrailingMinusNumbers:=True
End Sub


Sub Paste_as_value()
'
' Paste_as_value Macro
'
' Keyboard Shortcut: Ctrl+Shift+C
'
With Worksheets("Paste Data")
.Columns("B:B").Copy
.Range("D1").PasteSpecial Paste:=xlPasteAllUsingSourceTheme, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
.Range("D1").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
End With
End Sub


Sub Format_all()
'
' Format_all Macro
' formats to readable
'
' Keyboard Shortcut: Ctrl+Shift+E
'
With Worksheets("Paste Data")
With .Cells
.HorizontalAlignment = xlGeneral
.VerticalAlignment = xlCenter
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With

.Columns("A:A").EntireColumn.AutoFit
.Columns("B:B").EntireColumn.AutoFit
End With
End Sub


Sub Delete_Some()
'
' Delete_BBC Macro
' delete bbc
'
' Keyboard Shortcut: Ctrl+Shift+G
'
'don't replace with spaces
With Worksheets("Breakdown")
.Columns("B:B").Replace What:="BBC", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False


.Cells.Replace What:="ITV", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
.Cells.Replace What:="STV", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
.Cells.Replace What:="GMB", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
End With
End Sub




For a better answer here, attach a small WB with the raw (unprocessed) data, and an example of where you think you want to go.

[Loopey83]

Thank you both for your help! this is very useful.

The paste data worksheet is the one where we put the data we will be working with. I'm not sure if that's what you meant by it was unclear which ws was being processed?
The last tab is where i want the data to end up into that nice little table.

For the bonus question. If you look at Paste Data tab 28488 (hopefully that's macro free - not enough coffee's yet)

The data is sent to us in the following format:



COMMUNITIES



Query 1
BBC, ITV, STV


Query 2
Daily Mail, Sunday Post


Query 3
Local Mag, other site


CONSTITUTION



Query 1
BBC, ITV; STV



Using the workbook i am able to find out how many media queries there are overall, but i also want to know how many queries there are per topic. So essentially i'd want my spreadsheet to count that there are 3 queries under communities, then multiply that by the number of media outlets so 3x7.

I'd want the count to start counting at 1 row after COMMUNITIES (Query 1) and stop counting when it hits CONSTITUTION - then start counting how many are under the next subject.

The number of rows beneath each subject changes on a daily basis, so any counts i do need to be adjusted, i tried setting a certain number of rows per subject but someday even the number of queries outnumber the rows i set. It's been hurting my head so I've just manually counted those.

[SamT]

I am assuming that there are a fixed number of Topics/Subjects that you track, and, that the number of Topics/Subjects you receive reports on changes each day.

What Paul and I are interested in seeing is the Raw Data that you receive before you format/adjust it to appear as it does on your attachment in sheet Paste Data. We are trying to understand your "Domain."

Of the Domain Knowledge we don't know, but might be helpful, is a complete list of Topics/Subjects and a cross reference listing of Full and Short names of the Media Outlets you watch. These two lists can be on a single Worksheet.

Example:


Topics/Subjects


Media Short Name

Media Full Name



COMMUNITIES AND LOCAL GOVERNMENT

?
?


EXTERNAL AFFAIRS

?
?


DEPUTY FIRST MINISTER AND EDUCATION

?
?


ECONOMY, FAIR WORK & CULTURE

Falklands Radio
Falklands Radio ?


ENVIRONMENT, CLIMATE CHANGE & ENERGY

106
Original 106 ?


FINANCE

Times Radio
Times Radio ?


Etc

Clyde
Radio Clyde ?


Etc

Clyde
Clyde 1 ?



I also strongly suggest that you change the Names "Clyde" on your Report sheet so they are different somehow. Using identical Names for different things can really screw stuff up.


how many queries there are per topic That's actually a nontrivial Procedure with the current data. One reason to have more Domain Knowledge.

[Loopey83]

Ah i completely misunderstood. So we get the data in as a table in a email at the end of the day. I've attached a word doc showing how we receive the data. We get it in this exact format only in an email. I've not attached the email due to the other information contained in it.

Theres's conditional formatting that's pulled from their google sheet, that's why there's rows of grey, purple red, but they have no relevance to what i'm doing. that's why i clear the formatting to start.

28491


There are 14 topics. so they are all the ones in bold caps - COMMUNITIES and LOCAL GOVERMENT, BREXIT, CONSTITUTION, HEALTH, LEVELS etc, these headers never change. but the number of queries relating to that header will change, and sometimes more than one outlet ask the same question about the same topic that someone else has.

The current full list of media/outlets is the work done on the second tab. As part of the working out how to count we have simply been adding to the list as we discover new outlets. I'm assuming (maybe blindly) that if a code is set up to look for 50 words, its easy enough to add a new word to that list. Similar to how i just copy and pasted the formula and changed the word.

There are multiple instances for Clyde because of how folk enter the data - i don't control that - yet. I'm trying get them to use a uniform approach but the sheet is populated by a team of 20 so it's difficult to get that message across. . So "Clyde" "* Clyde*" and "Clyde 1" are all the same thing, but when i used the countif, the wildcard wouldn't pick up the Clyde One entry and picked up different outlets. As another example - the Sun is similar to Clyde. If i had wildcard on the count if for Sun - the formula picked up the Sunday post, mail on sunday etc. So i was setting a new formula for every outlet as it appeared in the raw data we get, but it would also ignore Sun if it had a space in front of the word before the cell. It got annoying. So i just used a new search for each thing to make sure it picked up exactly what i wanted - then just added that to the sum that populated the end data.





I am assuming that there are a fixed number of Topics/Subjects that you track, and, that the number of Topics/Subjects you receive reports on changes each day.

What Paul and I are interested in seeing is the Raw Data that you receive before you format/adjust it to appear as it does on your attachment in sheet Paste Data. We are trying to understand your "Domain."

Of the Domain Knowledge we don't know, but might be helpful, is a complete list of Topics/Subjects and a cross reference listing of Full and Short names of the Media Outlets you watch. These two lists can be on a single Worksheet.

Example:


Topics/Subjects

Media Short Name
Media Full Name


COMMUNITIES AND LOCAL GOVERNMENT

?
?


EXTERNAL AFFAIRS

?
?


DEPUTY FIRST MINISTER AND EDUCATION

?
?


ECONOMY, FAIR WORK & CULTURE

Falklands Radio
Falklands Radio ?


ENVIRONMENT, CLIMATE CHANGE & ENERGY

106
Original 106 ?


FINANCE

Times Radio
Times Radio ?


Etc

Clyde
Radio Clyde ?


Etc

Clyde
Clyde 1 ?



I also strongly suggest that you change the Names "Clyde" on your Report sheet so they are different somehow. Using identical Names for different things can really screw stuff up.

That's actually a nontrivial Procedure with the current data. One reason to have more Domain Knowledge.

[Paul_Hossler]

1. Do you care about the 14 BOLD CAPS gray categories in Col A? You don't appear to use them

2. Do you care about the topics below the 14 BOLD CAPS gray categories in Col A? You don't appear to use them either

3. In your other sheets there's trailing spaces that will mess up matching



I think it's always a good idea to separate the data layer from the computation layer from the presentation layer

4. This is the Word data, and I split the multiple entries; I used different columns and the data must be cleaned to be consistent (same spelling, no extra spaces, etc.)

Another approach would be to add the category and media after splitting at the bottom

I'd envision a constantly updating macro as unhandled media is encountered and to do the formatting and standardization


28492


5. Simple CountIf() using the media name (again, must be consistent) and manually maintained: new Category and Media entered, old ones deleted

28493

Glasglow Times in 16 and 17 are not consistent since there's some trailing spaces




6. Simple pivot table to present the data ( you can add all the embellishments your want)
28494

[SamT]

I see... You have designed the workbook from a reader's POV. I am trying to redesign it from a Programmers POV.

The difference is that for a reader, words have meanings and carry weight. For a Programmer Words are just Strings and have zero meaning in and of themselves. We Treat "BBC," "New York Times," and "pub001" the same... Except difficulties.

I'll get back to you.

[Loopey83]

Paul that looks much cleaner than what i have atm! I can't see the pivot table attachment. It just said the link is invalid.

as for your first question that is in relation to the bonus question.

I do want to count how many queries are asked. per subject and multiply that by the number of media queries. But as i couldn't figure out a basic formula to start counting between the subject headers as the number of rows between them varied.

I had fiddled about with a find COMMUNITIES, count from the row below that, and stop counting once you hit BREXIT. Then start the count one row after BREXIT and count until you hit the next topic that was in caps, but i failed miserably. so i've left that off my sheet.

You and Sam are amazing for your help!



1. Do you care about the 14 BOLD CAPS gray categories in Col A? You don't appear to use them

2. Do you care about the topics below the 14 BOLD CAPS gray categories in Col A? You don't appear to use them either

3. In your other sheets there's trailing spaces that will mess up matching



I think it's always a good idea to separate the data layer from the computation layer from the presentation layer

4. This is the Word data, and I split the multiple entries; I used different columns and the data must be cleaned to be consistent (same spelling, no extra spaces, etc.)

Another approach would be to add the category and media after splitting at the bottom

I'd envision a constantly updating macro as unhandled media is encountered and to do the formatting and standardization


28492


5. Simple CountIf() using the media name (again, must be consistent) and manually maintained: new Category and Media entered, old ones deleted

28493

Glasglow Times in 16 and 17 are not consistent since there's some trailing spaces




6. Simple pivot table to present the data ( you can add all the embellishments your want)
28494

[Paul_Hossler]

Wonder why the attachment got lost

Here it is

If it were me, I'd

1. Copy/Paste the email data into a WS
2. Run a macro to handle the media (? terminology correct??) entries in Col B
Clean data (trim, UC, standardize where possible, etc.)
Split into each entry where applicable ("STV and That's TV" --> "STV" and "That's TV")
Either stack the entries in Col B or add to Col C, D, E, ...
Have a final 'format' pass to handle special cases

NB - If the Media in Col B of second screen shot was correct, that'd make it easier.
Anything NOT handled could be marked to manual tweaking and possible incorporation into the macro

3. Pivot tables are very handy

[Loopey83]

That actually looks amazing. It does everything I'm looking for. Also seems to be much easier than faffing about with my million count if's and it didn't even dawn on me to standardise everything instead of adding finds to take into account the inconsistences.

[Paul_Hossler]

standardise everything

That'll be the challenging part usually

If you provide an (extensive) sample of the types of raw data you get, I'll try to do something to get you started at least

1. Examples of the raw data, creative spellings, multiple entries, weird punctuation (I saw a semicolon), etc.

2. A 'database' with the Category and the Media to group the counts

[Loopey83]

I've uploaded both - no idea what happened with the formatting, it seems some of the data we get is set on A3 spreadsheets, or landscape but it gives you an idea of what we are playing with.

The database is pretty much as up to date as it was on the original spreadsheet. But i've attached it with the categories I'd like to be counted. There will be loads more to add, I'm going back through about 6 months of data then everything going forward, so it will be a never ending list i'd imagine.

I added an AKA column onto the media spreadsheet with the weird stuff, the most common is the leading space before a word.

2850028501


That'll be the challenging part usually

If you provide an (extensive) sample of the types of raw data you get, I'll try to do something to get you started at least

1. Examples of the raw data, creative spellings, multiple entries, weird punctuation (I saw a semicolon), etc.

2. A 'database' with the Category and the Media to group the counts

[Paul_Hossler]

Look at this interium version and see

1. WS 'WordDocument' is a paste of the input data and then some cleanup format

2. WS 'Translation' is sort of a From-To database

3.WS 'Categories' is a database that maps Media Outlet to reporting category

4. WS 'MediaFromWordDocument" is the core WS built from the Word data

5. WS 'Tally' has the counts for the categories, AND 23 that could not be categorized (no translation #2, no assigned Category #3, something else


Step 1 - Copy Word Doc and format it

Step 2 - Clean up the data, trim, remove double spaces, replace some with ~ to split

Step 3 - Split Step 2, if multiple Media Outlets in one cell, add 2nd, etc at bottom of list

Step 4 - Check each cell from Step 3 and use 'Translation' WS to change if necessary

Step 5 - Use Media Outlet from Step 4 and lookup Category from WS 'Categories'


Blank Category means translation failed, input wrong, no category, etc.

I expect there'll be some more special rules. I dropped leading 'The ' for consistency

------------------------------------------------------------------------------------------------------------

Edit:

I decided to use some worksheet formulas to reflect data cleanup immediately

I guessed (badly) as some parameters for the Media that weren't in the mapping. So look at the orange cells in Translation and Categories to correct

Finally, more rules can be added, e.g. "Ignore 'on Sunday' or 'Online'" and just use the base part

[Loopey83]

That actually looks like what I'm trying to achieve.

You were close with the outlets and spot on with the translations. i think unless you deal with the outlets on a regular basis it's definitely not easy to recognise what's a local and not.

28504

Leading The and Sunday and Online to be ignored is fine. i those words and the random , ; and space are the only things that should appear in front of the media outlets.

I assume, as i discover more outlets - it will be easy enough to add in more?

[Paul_Hossler]

Should be easy to add to the 'database'

Example

I added 2 outlets to the Word Import (red), one multiple with a translation established, and one without

I added a Translation for the multiple, all 3 to one category

I added the category above to Categories

Look at the red cells and you'll see

[Loopey83]

That's great actually.

I threw a an example in and i encountered an error when i hit the button - it says variable not defined and highlights the first =x1ReplaceFormula2 on the replace some punctuation with tilda section.

did i do something daft?

[Paul_Hossler]

I think it was a Excel version difference where I used a parameter that was only recently added

There's a couple of such that I am aware of, but I think I just learned a new one :yes

Try ver 4a and see

[Loopey83]

same again. Same place.

We are using different versions of excel could that be a reason? I'm forced to use 2016 at work

[Paul_Hossler]

Different versions I think


Try ver 4b

[Loopey83]

Made it past there, stuck at the next xlReplaceFormula2:

'replace double spaces with one space rTranslate.Replace What:=" ", Replacement:=" ", LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False, FormulaVersion:=xlReplaceFormula2

[Paul_Hossler]

Probably because I got careless and missed two more instances :(


Try ver 4c (fingers crossed)

You can also just delete the last part of the lines



, FormulaVersion:=xlReplaceFormula2

[Loopey83]

That's it. It works perfect.

I put in some new translations and media and ran it a few times and it's doing exactly what i need it to.

Thank you so much for your time with this.