MS Excel / VBA help thread

Do you see a break down by iOS?
So iOS 14 X number

And then an unknown value or something?

I don't work in marketing analytics and doubt I'm helping but I am interested in the answer when you get one.

Yes I can see a breakdown by iOS in the data, but the number of iOS15 is very low, while the number of iOS14 has gone down too, this suggests to me that people are upgrading to iOS15 and then dropping off. I think what's going to be my angle in presenting this is:

Here's your ratio of iOS(all versions), look at it drop off a cliff since iOS15 came in, so the iOS15 users are coming through as not iOS at all. This can be correlated by the fact iOS14 has decreased, but based on industry averages, and your own for the last 12 months, this can't be caused by a huge swathe of your users switching from Apple to a non-Apple or even non-Android device. Further evidence in what I'm seeing is that the number of users opening not in the desktop/smartphone/tablet segment has gone up massively.

I aggregated the data because it was in a terrible format and I'm reluctant to go back and dig back into it because it's time consuming and I don't have time, but I think I'll have to, to prove that the increase in opens is coming from users with no device info.

OK I cracked it without having to dig, the ratios of clicks isn't changing, only opens.

The ratio of iOS users opening has dropped massively, but the iOS clicks have stayed the same.

I can run a SQL query to show a discrepancy between users who click using iOS15 and what their device and OS info says on their open.

I think I am getting old, I should have solved this ages ago, can't believe it's taken me this long.

I miss working in a big team where you can bounce these ideas off other people, I appreciate everyone's help. Your suggestions helped my process.

If anyone is interested here are some charts which support my theory. Don't watch my visuals, this is a rush job.

4 Attachments

That's good. And ties in with the thing about "clicks being the measure now for iOS 15"

Using that to talk through with the customer to say "look, for iOS we now need to use this. Its a known problem beep beep boop"

With a table like this:

What's an easy way to find all of the unique entries in the table? I don't care what year/territory they are in, I just want an overall unique list.

Feels like it should be an easy formula but I can't think of how.

I dunno if you can do it in excel, without going into power query, (easily anyway), but you want to turn it into tidy data right and then count when concat(territory+year) = 1?

It lends itself better to a pivot on the years, group by, strategy?

Excel conditional formatting has a "highlight unique values" option built in, but I guess you wanted something to spit out the unique values as data?

I find it's hard to generate a list of values in Excel, formulas always seem to result in a single value.
If you know the range of possible values (looks like A to Z here) then you can create a column of possible values and countif each one in turn.

Looking at answers online it sounds like you'd need an array formula to generate your list of unique entries, and I never got the hang of array formulas.

"you can't count colour"

Ha, indeed! But you can find it easily. Depends what the result needs to be used for.

I like using those words when and where I can....

Colour is helpful though and you can count it with Vba

I feel like you could make a really complicated scenario where you use a combination of countif and search but macro/VBA using remove duplicates or using a pivot would make the most sense.

I think Office 365 has a "UNIQUE" function but I've never used it.

Cheers. It's easy in Powerquery (unpivot columns and then remove duplicates) but normally when I want to do this it's a quick and dirty one where I just want a list to email and then discard.

I was hoping some of the new spill formulas, UNIQUE being the obvious one, would give something useful but can't see anything.

"you can't count colour" - I have a function for counting and summing colour, just for those people who insist on colouring things in.

Those spill functions are fun until they're not.

Doesn't get it into a nice list straight away, but you can use COUNTIF
E.g.
if the data is called Table1 then
=IF(COUNTIF(Table1[[2017]:[2021]],Table1[@2017])=1,Table1[@2017],"")

1 Attachment

You could use “create multiple consolidation range” to convert it in a flat structure then it is a simple countifs

Can I check what we're trying to get here?
Is it unique counts of a letter in a year (column) by a country (row) or ever (frame)?

Uh this just triggered a memory of the worst analyst that I used to manage.

They just didn't grasp that random colouring in of spreadsheets a) wasn't useful, and b) wasn't real work.

^ You could use array functions for the above unique value bit.

Array functions are even more fragile than normal excel functions though, so be warned.

I'm just looking to get a list of the items with duplicates removed. e.g.

Value
A
B
C
D
E
F
G
H
I
K
M
N
O
P
Q
R
S
T
U
V
W
X
Y
Z

The "create multiple consolidation range" looks like it should do what I want but it doesn't (or at least I can't get it to).

Which bit is it failing on? it should get you to the flat file list, and from that you could use a countif to filter?

Then I think the better way to do this is by powerquery? You need to turn this wide thing into a narrow thing.
That tends to lend itself to physically doing it yourself in pivots, power query, or using the functions in R.

OR
https://trumpexcel.com/source-data-for-pivot-table/
INDEX($B$2:$E$5,MATCH(G2,$A$2:$A$5,0),MATCH(H2,$B$1:$E$1,0))

Well this is what Multiple Consolidation Range does.... depivots in effect. its a useful thing