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?
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.
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.
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.
Thank you for the tip; that's helpful. However, the macro includes date/time/ initials for recording purposes.
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
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.
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...https://support.office.com/en-us/article/keyboard-shortcuts-in-excel-for-windows-1798d9d5-842a-42b8-9c99-9b7213f0040f#ID0EABFAAA=2007
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.
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.
^ 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.
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.
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 :)
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.
In another column you could do
and then sum that column
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
You can edit to your specific range for better calculation speed.
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. http://access-excel.tips/excel-vba-usedrange-property/
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.
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
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.
Yeah I'm not sure. I thought it was odd. Especially when on the same computer/sheet etc. Updated with a picture.
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.
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.
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".
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.
What ETL tool? Or are you referring to PQ?
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.
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?
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.
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
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.
You can check out using an nth match formula or you could use a pivot table. It would display all of the lines that are equal to your selected order number.
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.
I would think tracking the information in a format similar to this where each line item is a book/movie you rated.
Then you can pivot it like this with the grand total being the average.
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.