my subscriptions

dougiek commented on a post in r/excel
-excel-lent 1 point

Hard to visualize what you're doing, but you only need to have unique values on one side of the relationship, not both sides. And that only applies to the column you're joining on. The one side of the relationship is usually a lookup/dimension table, while the many side is a data/fact table.

In a database account numbers and payment terms would be in dimension tables, while transactions would be fact tables.

What were you trying to do?

dougiek 1 point

That was just an example - I forget what specifically I was trying to do but the example is something I've been working with recently.

So the payment term lookup value would be unique but the table I'd need to join it to would have repeating values. It would basically be a left join on the two tables. Pull in the value if it exists and blank/null if not.

-excel-lent 2 points

Yes, except that you're not actually joining them. You'd have to make a calculated column using the DAX measure RELATED for that.

The beauty is you don't have to. If you're working in Pivot land you'd just throw the payment term from the lookup table into the pivot, and the filters would flow down to any measures relating to transactions. Filters flow from the one side to the many side.

You might be wondering why this is useful when you could just join the column you need in PQ and throw that in the pivot instead, which will do the same thing. This article puts it more eloquently than I could (scroll down to the Data Modeling heading), but basically performance, multiple fact tables, and a cleaner, easier to maintain, scaleable data model.

dougiek 1 point

True. Thanks for the detailed replies. I sent my work email that article. I’ll have to read it tomorrow.

Definitely on my priority list to experience not with PP in my spare time.

I wish they allowed DAX in PQ. M is a pain to work with especially with no intellisense and case-sensitive.

Load more comments
dougiek commented on a post in r/excel
mscexceluser 1 point

Thank you for the tip; that's helpful. However, the macro includes date/time/ initials for recording purposes.

dougiek 1 point

Here is a much more cleaned up version of your macro. Once you add it to a module go back to the sheet and bring up the macros selection box ALT+F8, select the Update_Date macro, click options on the right and then change the shortcut to SHIFT+U (hold shift and hit "u"). It should override any native shortcut - just tested it.

Option Explicit
Sub Update_Date()

Dim DateTime As String
Dim Initials As String

DateTime = Now()
Initials = "SPT"

With ActiveCell
    .Value2 = DateTime & " " & Initials
    With .Interior
        .ThemeColor = xlThemeColorAccent5
        .TintAndShade = 0.7
    End With
    With .Font
        .Name = "Arial"
        .Size = 8
        .Color = 2
    End With
End With

End Sub
mscexceluser 1 point

I appreciate the cleaned up VBA. I've tried to update the shortcut, and I'm still having the same issues. The worksheet zooms 5%... Like I said, before I tried this macro on this computer (brand-new Thinkpad, running Windows 10 Pro 64x) I've used it on many others with no issue. Here's a screen cap of me setting the shortcut just to prove that I'm not a total idiot.

dougiek 1 point

Hmm....not sure then. My CTRL+SHIFT+U shortcut wasn't zoom. It was expand/collapse formula bar. When I assigned the macro that same shortcut, it overrode the formula bar action and would run the macro.

Not sure where yours is getting a zoom action from though...

Load more comments
dougiek commented on a post in r/excel
rnelsonee 15 points

Been using since version 3 or 4 (early 1990's). And no, just a hobby. I mean, I use Excel for basic work stuff, but I'm an engineer and sometimes project manager, so it's stupid things like storing tables of data or doing cost estimates. All the real data stuff gets done elsewhere.

I just like r/excel because it's like mini-problems to solve whenever you want. And ostensibly helps people too.

dougiek 8 points

I just like r/excel because it's like mini-problems to solve whenever you want.

This. When I have free time at work I spend most of it here because you get a chance to solve problems that you might not come across in your own work. Which leads to learning new methods/features.

atcoyou 4 points

^ This. I highly recommend people who are new and want to learn more... just hang out and try your hand at some problems. You will learn over time, and even if you don't get the answer, someone here will, so it is like there is an answer in the back of the book. Also don't be shy about trying to help when you are new once you gain expertise in a certain area. If it works, it is worthy of sharing imho.

dougiek 2 points

It’s also really cool to see how many different ways the same problem can be solved. Some are truly ingenious and not the normal thought process.

Load more comments
dougiek commented on a post in r/excel
useless_wizard 5 points

My two cents.
I like the competition idea but a UDF will mostly engage the community that knows vba.
How about adding competitions about basic excel, some index "matches" (pun intended), lookups, text parsing etc. and dividing that by levels. Something for the people below 50 clippys, something for the ones between 50-100 and so on.
As for the UDF, maybe add in a bonus for anyone who can do it without the UDF , using just excel (it will depend on the problem, of course).
And did I hear something about an award of 10 clippy points per solution and 5 for an interesting attempt :)

dougiek 2 points

community that knows vba

And even then, only the ones that know it well enough to write it all from scratch if it's to be live streamed lol. I would imagine that's a very small population of people here.

Something for the people below 50 clippys, something for the ones between 50-100 and so on.

I'd say this works but the clippy points aren't necessarily a good identifier of skill level since they're more based on who has more time to answer questions here. I see some answers from people with less than 10 who's answer gives the impression they are a much more advanced user than me (at 112). I'd probably just offer 2 or 3 different versions that anyone can enter without a requirement.

dougiek commented on a post in r/excel
dougiek 1 point

In another column you could do =N(SUM(D1,F1)>0)

and then sum that column

IAmTheJudasTree 1 point

Yeah, adding a column and even using an IF OR formula, and then adding up the total of that column would work, but I was trying to figure out if there's a way to do this all in a single formula without creating another column just for dummy variables

dougiek 1 point


You can edit to your specific range for better calculation speed.

dougiek commented on a post in r/excel
dougiek 1 point

Do you have a lot of blank cells in a "continuous" range?

Or are you just referring to all the blank cells in the rest of the sheet?

Edit: In lieu of waiting for a response, the UsedRange property might be appropriate here.

ashleymarieeee 1 point

Thank you!! The data gets pasted into the first 13 columns or so, and the next columns are all formulas that have been pulled down to row 5,000. I'd love to not calculate these formulas if there's no adjacent data.

Used Range is really good to know about, but I think it'd still calculate the formulas based on blank data, which is what I'm trying to avoid.

dougiek 3 points

Ohhhh I see. Personally, I would either use a table that will auto-expand (you'd need VBA to delete if it's smaller though) or use the dynamic range to apply the formulas. That way you aren't sacrificing file size and calculation time for thousands of potentially superfluous formulas to account for expansion of data.

I changed that methodology in a report that had sometimes 100-200k extra rows of formulas depending on the week. That significantly slows it down. So rather than accommodate the calculation problem, I would address the root-cause being only putting formulas in the rows that have data.

Edit: Something like this is what I generally use for a dynamic range based on the last cell (the column you base this off would of course have to actually have a value in the true last row)

Dim LastRow As Integer

LastRow = Cells(Rows.Count, 1).End(xlUp).Row

Range("F2:F" & LastRow).Select
dougiek commented on a post in r/excel
isaythingslike 3 points

I would caution against the search for an asterisk * as it can lead to a wildcard search.

dougiek 1 point

Unless you want the wildcard search which is useful depending on the use case.

man-teiv 4 points

I wish there was a guide to figuring out when to add those pesky +1 or -1 in the =MID(FIND()) formulas. I always get those wrong. I think the rules are always changing just to spite me.

dougiek 2 points

Haha trial and error is my friend with those.

dougiek commented on a post in r/excel
dougiek 2 points

I wonder if it has something to do with those colors being theme colors. That's weird that it doesn't give a unique color index number though. But...I don't know enough about the codes.

ajskelt 1 point

Yeah I'm not sure. I thought it was odd. Especially when on the same computer/sheet etc. Updated with a picture.

dougiek 3 points

Try using .Color instead.

I don't really understand the ColorIndex property lol


I wonder if it's just giving the colorindex for the closest color match? It looks like ColorIndex is 56 fixed colors.

dougiek commented on a post in r/excel
messyjesse_ 4 points

Probably the single most important concept to translate Excel to SQL, in my opinion, is INDEX-MATCH. On the surface it behaves almost exactly like a SQL JOIN would in its output. You'd be teaching yourself the basics of normalization--arguably the single most important concept in relational database design--without even realizing it.

And great story OP.

dougiek 1 point

I'd say VLOOKUP translates to the same sort of concept - since VLOOKUP requires you to have the lookup value on the "Left" it kind of helps with the concept of LEFT JOIN.

I've only recently gotten hands on with SQL and I can certainly say having a great Excel-based foundation of data manipulation/transformation is very helpful because it's the same things essentially. Just in a different "syntax".

itsnotaboutthecell 15 points

Most people I know that write SQL who are now using Power Query (Get & Transform) have even reduced writing SQL with the ETL tool now available. I would recommend looking into Power BI as well if you get into using a lot of data to tell a story.

Also congrats - I love hearing how Excel has offered advancements in people's lives.

dougiek 2 points

What ETL tool? Or are you referring to PQ?

Load more comments
dougiek commented on a post in r/excel
dougiek 2 points

I haven't actually had a chance to look at the actual documents (blocked at work) but I've had this tab open for months (procrastination lol).

Again, not sure what the documents actually look like but it's best to try and incorporate relevant data or functions that are done by the team. It will help them to better understand when/where they can apply some of the features they don't know about.

For example, I just sent out a short Excel email on joining text with some various methods (&, TEXTJOIN, etc) but made sure that the examples were things that people constantly ask me about like joining first and last names.

dougiek commented on a post in r/excel
ADingo8MyMemes 1 point

I have heard of VBA and Macros before, but I have basically no knowledge of how to set one up or how to use one. As for the PT, would you be willing to type out a basic Step By Step setup of how i would go about formatting it the way i need it to be?

dougiek 1 point

The basic Step by Step would probably be best Googled if you mean how to use a Pivot Table in general.

But essentially you would add all of the columns to the "Rows" field of the pivot. Then you can set the filter (drop-down like normal filters) on the name to the specific employee for that tab. You'll probably want to make the "Report Layout" tabular so it shows like a normal table would.

ADingo8MyMemes 1 point

Each sheet needs to have the information in Rows and Fit into the Overhead columns. I'm not entire sure as to why, but I know that this is how my CFO wants things to look when we are done.

I'm not entirely sure that a pivot table would help me, because In the end, we end up printing each sheet so it matches the same formatting. Along with we send each sheet to the respective managers of each Employee

dougiek 1 point

I mean, you could make the Pivot Tables match the formatting you want. The benefit to the PT is just that it's essentially just a link to the data. So you don't have to copy/paste anything. You just refresh and it's updated with any changes in the data.

So each sheet would be filtered to the employee and upon refresh the PT's would update with the current Amex charges.

Ideally, you'd probably want VBA for this to loop through the unique employee names, filter the Amex data, and copy to new tabs while retaining formatting (or reapplying it). You could probably use PowerQuery as well.

Load more comments
dougiek commented on a post in r/frugalmalefashion
Rhett_Rick 13 points

It’s a sale people don’t understand because they’re impatient. Smart shoppers will buy now and use credit card price protection if SSENSE won’t adjust (which they often will) to get lower prices and make sure you get the items you want. Doing that requires patience, paperwork, and access to credit.

dougiek -3 points

Who is eyeing $300 t-shirts without access to credit though lol

CrazeRage 6 points

People using their parent's money.

dougiek 2 points

haha fair enough

dougiek commented on a post in r/excel
dougiek 1 point

I would think tracking the information in a format similar to this where each line item is a book/movie you rated.

Name Date Who Rating
Book1 5/1/2018 Jeff 8
Book1 5/1/2018 Josh 9

Then you can pivot it like this with the grand total being the average.

Name Doug Josh Grand Total
Book1 8 9 8.5
dougiek commented on a post in r/excel
dougiek 3 points

Does your formula for the dates have blanks? For whatever reason, the pivot recognizes a true blank cell differently than a formula that returns "". True blanks don't affect grouping but the formula ones will not allow you to group.

You could try using PowerQuery to do the data cleaning and then your output will have true blanks and thus be able to be manipulated & grouped correctly through a pivot.

view more:
next ›
6,927 Karma
6,000 Post Karma
927 Comment Karma

Following this user will show all the posts they make to their profile on your front page.

About dougiek

  • Reddit Birthday

    November 26, 2016

Other Interesting Profiles

    Want to make posts on your
    own profile?

    Sign up to test the Reddit post to profile beta.

    Sign up