Fantasy Football - Footballguys Forums
Ned

***Official*** Excel Help Corner

Recommended Posts

Posted (edited)
4 minutes ago, SaintsInDome2006 said:

Greetings helpful XL geniuses. Query:

- Is there a way to extract all names in a filtered column?

So if you have 1500 rows, and col. C has say 233 names that are showing x times each, how can I get a single list of just the 233 names? Is that possible, without manually hammering out the list?

Thanks!

 

Click on Data, then click on the remove duplicates with that column highlighted. It might be better to copy/paste them to a different tab first. I put a screenshot in the below link.

 

https://twitter.com/FishSkiand/status/1255218033162956804

Edited by MTskibum
  • Love 1

Share this post


Link to post
Share on other sites
17 minutes ago, MTskibum said:

 

Click on Data, then click on the remove duplicates with that column highlighted. It might be better to copy/paste them to a different tab first. I put a screenshot in the below link.

 

https://twitter.com/FishSkiand/status/1255218033162956804

MT, this is really awesome, thanks a lot. - SID :banned:

Share this post


Link to post
Share on other sites

Is there an easy way to transpose a bunch of data that’s in a random grouping

for example I have 2 columns with data, then a bunch of additional data underneath that I need in column C D E, etc

but once group might have 3 rows, the next 20

ive just been doing brute force copy paste transpose and then delete what i transposed  over 

Example

https://docs.google.com/spreadsheets/d/13QEwEf-GnYWs8dfs-y1wFFENDX5HD1_NJbbYPZxjPqc/edit

Share this post


Link to post
Share on other sites

Dan, google "excel stack multiple columns".  This will put the data into 1 column for you, then you can paste/transpose.  Or you can alter the code to what you need.

 

There are a few options for this, none are great IMO

Share this post


Link to post
Share on other sites

appreciate the help here....

wife does real estate work and she is working on a pivot table that has multiple years.  she has multiple properties in each year and each property has a dollar value associated with it.  when she selects a year and views the properties, she’d like it to sort the properties by high dollar value to low dollar value.....for each year.  any ideas of a formula?

Share this post


Link to post
Share on other sites

This wouldn't be a formula, but if it's in a pivottable, you would just need to set the sort to be by property value - kind of depends on how she is setting up the pivot table and what other things she is looking to be able to do (which also determines if a pivot table is the best feature to use for what she is looking to do)

  • Thanks 1

Share this post


Link to post
Share on other sites
On 4/28/2020 at 2:28 PM, SaintsInDome2006 said:

Greetings helpful XL geniuses. Query:

- Is there a way to extract all names in a filtered column?

So if you have 1500 rows, and col. C has say 233 names that are showing x times each, how can I get a single list of just the 233 names? Is that possible, without manually hammering out the list?

Thanks!

use a pivot table

Share this post


Link to post
Share on other sites

Existing Formula:

The below logic outputting the month value (example: June-20) if it hits any of the conditions below, else N/A
Currently, for the red logic only, if there is a date in the current month and column DC2 = 'C', it also is outputting that date (June-20). However, it is also doing so for June dates that are prior to today.

How would I update these to only evaluate those dates that are today AND within the current month?

=IF(BO2="A",TEXT(Z2,"mmmm-yy"),
IF(AND(BO2="B",MONTH(O2)=MONTH(TODAY())),TEXT(O2,"mmmm-yy"),
IF(AND(D2="C",MONTH(AT2)=MONTH(TODAY())),TEXT(AT2,"mmmm-yy"),
IF(AND(BO2="D",MONTH(DA2)=MONTH(TODAY())),TEXT(DA2,"mmmm-yy"),
IF(AND(OR(BO2="E",BO2="Fs"),MONTH(AT2)<>MONTH(TODAY())),"N/A",
IF(AND(OR(BO2="E",BO2="F"),OR(MONTH(AT2)=MONTH(TODAY()),MONTH(O2)=MONTH(TODAY()))),TEXT(TODAY(),"mmmm-yy"),"N/A"))))))

Share this post


Link to post
Share on other sites

could use a favor here....

column A has numbers.  wife would like a formula in column b to say;

if A is  <7,000 B says Low, between 7,001-15,000 medium and over 15,000 high.

we can’t crack it.  thx

Share this post


Link to post
Share on other sites
11 minutes ago, Chemical X said:

could use a favor here....

column A has numbers.  wife would like a formula in column b to say;

if A is  <7,000 B says Low, between 7,001-15,000 medium and over 15,000 high.

we can’t crack it.  thx

=IF(A1<7000,"Low",(IF(AND(A1>7001,A1<15000),"Medium","High"))) 

That's one way that most directly translates your statements. There are several other ways to do it.

 

Share this post


Link to post
Share on other sites
Posted (edited)

A little easier to read and follow:

=IFS(A1<7000, "Low", A1>15000, "High", TRUE, "Medium")

Literally: "If it's less than 7000 say Low, if it's more than 15000 say High, otherwise, say Medium"

Edited by Walking Boot
  • Like 1

Share this post


Link to post
Share on other sites
1 minute ago, Walking Boot said:

A little easier to read and follow:

=IFS(A1<7000, "Low", A1>15000, "High", TRUE, "Medium")

Literally: "If it's less than 7000 say Low, if it's more than 15000 say High, otherwise, say Medium"

can you add N/A if 0 to the above?  also, is it ifs or if?

Share this post


Link to post
Share on other sites
20 minutes ago, Chemical X said:

can you add N/A if 0 to the above?  also, is it ifs or if?

Should be able to do it as :  =IFS(A1=0, "N/A", A1<7000, "Low", A1>15000, "High", TRUE, "Medium")

For the above context, IFS is the proper formula. For the earlier nested formula @Walking Boot provided, IF was the correct version.

I believe the IFS statement is the easiest way for this.

 

There are also some other ways to do this with a min/max table. So for example, if you expect the ranges you consider to be low, medium, high to possible change, or you might be adding in additional range names, you could consider something like that as well.

Share this post


Link to post
Share on other sites
13 minutes ago, Chemical X said:

can you add N/A if 0 to the above?  also, is it ifs or if?

"IFS" is like a super-powered "IF", which is what makes the second example easier to read. 

The first one I gave is literally "If A1 is less than 7000, say Low, otherwise, if A1 is between 7001 and 15000, say High, otherwise, say Medium". This is called a nested IF, there's one IF inside of another IF. The more you want to add, the more confusing it gets.

For example, now that you want a 4th condition, the IF statement looks like this:

=IF(A1=0, "NA",IF(AND(A1>0, A1<7000),"Low",(IF(AND(A1>6999.9,A1<15000),"Medium","High"))))

(PS, my first example was incorrect for the edge cases 7000 and 7001. I should have used 6999.9 instead of 7000)

This reads as "If A1 is 0, say NA otherwise, if A1 is greater than 0 but less than 7000, say Low, otherwise, if A1 is more than 6999.9 and less than 15000, say Medium, otherwise, say High"

 

The second answer, using IFS, can now easily be modified like this:

=IFS(A1=0, "NA", A1<7000, "Low", A1>15000, "High", TRUE, "Medium")

I just added the first pair of instructions "If A1 is Zero, say NA" in the first evaluation position. Note that in this case, Excel helps us by stopping as soon as a condition is true: even though A1 is also less than 7000 when it is equal to zero, since it found the first case is true, it stops evaluation, returns the text 'NA', and exits.

 

  • Love 1

Share this post


Link to post
Share on other sites
6 minutes ago, acarey50 said:

Should be able to do it as :  =IFS(A1=0, "N/A", A1<7000, "Low", A1>15000, "High", TRUE, "Medium")

For the above context, IFS is the proper formula. For the earlier nested formula @Walking Boot provided, IF was the correct version.

I believe the IFS statement is the easiest way for this.

 

There are also some other ways to do this with a min/max table. So for example, if you expect the ranges you consider to be low, medium, high to possible change, or you might be adding in additional range names, you could consider something like that as well.

thx, will give it a try.

Share this post


Link to post
Share on other sites

Actually, I think instead of using 6999.9 you can use the >= operator in the formula. Not knowing the source data, this would account for any unlikely scenario where the value is between 6999.9 and 7000.0

That would make the IF formula:

=IF(A1=0, "NA",IF(AND(A1>0, A1<7000),"Low",(IF(AND(A1>=7000,A1<15000),"Medium","High"))))

 

  • Like 2
  • Love 1

Share this post


Link to post
Share on other sites
5 minutes ago, Walking Boot said:

"IFS" is like a super-powered "IF", which is what makes the second example easier to read. 

The first one I gave is literally "If A1 is less than 7000, say Low, otherwise, if A1 is between 7001 and 15000, say High, otherwise, say Medium". This is called a nested IF, there's one IF inside of another IF. The more you want to add, the more confusing it gets.

For example, now that you want a 4th condition, the IF statement looks like this:

=IF(A1=0, "NA",IF(AND(A1>0, A1<7000),"Low",(IF(AND(A1>6999.9,A1<15000),"Medium","High"))))

(PS, my first example was incorrect for the edge cases 7000 and 7001. I should have used 6999.9 instead of 7000)

This reads as "If A1 is 0, say NA otherwise, if A1 is greater than 0 but less than 7000, say Low, otherwise, if A1 is more than 6999.9 and less than 15000, say Medium, otherwise, say High"

 

The second answer, using IFS, can now easily be modified like this:

=IFS(A1=0, "NA", A1<7000, "Low", A1>15000, "High", TRUE, "Medium")

I just added the first pair of instructions "If A1 is Zero, say NA" in the first evaluation position. Note that in this case, Excel helps us by stopping as soon as a condition is true: even though A1 is also less than 7000 when it is equal to zero, since it found the first case is true, it stops evaluation, returns the text 'NA', and exits.

 

appreciate the replies.  first one didn’t work.  gonna try this after a quick meal.  many thx.  i will advice if it worked.

Share this post


Link to post
Share on other sites
4 minutes ago, acarey50 said:

Actually, I think instead of using 6999.9 you can use the >= operator in the formula. Not knowing the source data, this would account for any unlikely scenario where the value is between 6999.9 and 7000.0

That would make the IF formula:

=IF(A1=0, "NA",IF(AND(A1>0, A1<7000),"Low",(IF(AND(A1>=7000,A1<15000),"Medium","High"))))

 

 

I always forget how to format a proper less-than-or-equal-to in Excel. Unfortunately it doesn't just simply recognize the characters ≤ or ≥

Share this post


Link to post
Share on other sites
8 minutes ago, acarey50 said:

Actually, I think instead of using 6999.9 you can use the >= operator in the formula. Not knowing the source data, this would account for any unlikely scenario where the value is between 6999.9 and 7000.0

That would make the IF formula:

=IF(A1=0, "NA",IF(AND(A1>0, A1<7000),"Low",(IF(AND(A1>=7000,A1<15000),"Medium","High"))))

 

so, this is the winning formula!!  i owe you something from the iWorld!

Share this post


Link to post
Share on other sites
4 minutes ago, Walking Boot said:

 

I always forget how to format a proper less-than-or-equal-to in Excel. Unfortunately it doesn't just simply recognize the characters ≤ or ≥

acarey’s formula worked, i didn’t get to use yours, though i assume it would’ve gone thru too.  honestly, many thx for taking the time to think it thru.

Share this post


Link to post
Share on other sites
2 hours ago, Chemical X said:

so, this is the winning formula!!  i owe you something from the iWorld!

I just leveraged the work @Walking Boot did and made a minor modification, just like I do with google and most of my work related questions.

  • Like 2

Share this post


Link to post
Share on other sites
20 hours ago, Walking Boot said:

 

I always forget how to format a proper less-than-or-equal-to in Excel. Unfortunately it doesn't just simply recognize the characters ≤ or ≥

Is there a forum dedicated to just excel questions, like this one?

Share this post


Link to post
Share on other sites
11 minutes ago, Chemical X said:

Is there a forum dedicated to just excel questions, like this one?

Google is usually your best friend - there are lots of forums, but I have found google to be easier and quicker in most cases over a forum, unless it is to the point of needing som fine tuning.

Sites like chandoo.org and mrexcel.com off the top of my head are some of the first I look to in the google search results.

  • Like 1
  • Thanks 1

Share this post


Link to post
Share on other sites
1 hour ago, acarey50 said:

Google is usually your best friend - there are lots of forums, but I have found google to be easier and quicker in most cases over a forum, unless it is to the point of needing som fine tuning.

Sites like chandoo.org and mrexcel.com off the top of my head are some of the first I look to in the google search results.

I agree. The only thing to watch for with Google is often I'll get sucked into a site that's trying to sell me their Excel add-in instead of just giving formulas or VBA programming.

Not a big deal, but it slows me down when I'm trying to solve a specific problem, not looking to buy some software solution that I wouldn't even trust not to have a virus.

  • Thanks 1

Share this post


Link to post
Share on other sites
38 minutes ago, Psychopav said:

I agree. The only thing to watch for with Google is often I'll get sucked into a site that's trying to sell me their Excel add-in instead of just giving formulas or VBA programming.

Not a big deal, but it slows me down when I'm trying to solve a specific problem, not looking to buy some software solution that I wouldn't even trust not to have a virus.

Yeah, there are a few of those - usually they say - here's a formula way to do it, here's a VBA way to do it and hey, here's our add-on you can but that will do it with a few button clicks.

Share this post


Link to post
Share on other sites

Looks like Microsoft just updated Excel for Mac, and now I'm having this quirky issue.

The "recent fonts" drop-down list in the home ribbon used to show frequently used variants of several fonts. For example, I'd be able to drop-down and see both Arial Narrow and Arial Black, two fonts I use all the time in formatting my reports. And no others, since those two fonts make up about 95% of my spreadsheets.

However, now, it only lists the base font in the main drop down: Arial, plain. Then there's an arrow> over to all the Arial options: Narrow, Bold, Bold Italic, Black, Narrow Bold Italic, etc. So what was one click--drop-down, select Arial Black, is now click, arrow over, search among the list of not recently used fonts in the Arial family, find the one I do want, click.

Lame.

Anyone know how to, either: restore the previous functionality to Excel for Mac, or, have a way to create a shortcut, button, script, or macro to "toggle" between two fonts? That is, if there was a shortcut key like Command-Whatever, and I pressed it when the cell was Arial Narrow, it'd switch the cell to Arial Black, and vice versa.

It would have to be a shortcut, button, script, or macro that was local to my machine and not living in the spreadsheet, as my spreadsheets get passed around to various departments around the world and I don't want them to "break" if the other user doesn't have what I have. Also, I would rather add it once, to my machine, rather than have to program it into all 500+ spreadsheets I'm maintaining globally.

 

 

Long shot, I know, but, my normally adept Google-Fu has come up short on finding any solution.

Share this post


Link to post
Share on other sites
10 hours ago, Walking Boot said:

Looks like Microsoft just updated Excel for Mac, and now I'm having this quirky issue.

The "recent fonts" drop-down list in the home ribbon used to show frequently used variants of several fonts. For example, I'd be able to drop-down and see both Arial Narrow and Arial Black, two fonts I use all the time in formatting my reports. And no others, since those two fonts make up about 95% of my spreadsheets.

However, now, it only lists the base font in the main drop down: Arial, plain. Then there's an arrow> over to all the Arial options: Narrow, Bold, Bold Italic, Black, Narrow Bold Italic, etc. So what was one click--drop-down, select Arial Black, is now click, arrow over, search among the list of not recently used fonts in the Arial family, find the one I do want, click.

Anyone know how to, either: restore the previous functionality to Excel for Mac, or, have a way to create a shortcut, button, script, or macro to "toggle" between two fonts? That is, if there was a shortcut key like Command-Whatever, and I pressed it when the cell was Arial Narrow, it'd switch the cell to Arial Black, and vice versa.

It would have to be a shortcut, button, script, or macro that was local to my machine and not living in the spreadsheet, as my spreadsheets get passed around to various departments around the world and I don't want them to "break" if the other user doesn't have what I have. Also, I would rather add it once, to my machine, rather than have to program it into all 500+ spreadsheets I'm maintaining globally.

While the QAT (Quick Access Bar) doesn't already have specific fonts to choose, I would try recording a Macro and adding it to the QAT at the top of the Excel Frame
https://www.howtogeek.com/232620/how-to-add-a-macro-to-the-quick-access-toolbar-in-office/

Looks like your specific question was asked:   
https://answers.microsoft.com/en-us/msoffice/forum/all/how-add-specific-fonts-or-characters-to-quick/cc0e6649-3403-4d9e-a9e7-bd9febe1c620?auth=1

  • Thanks 1

Share this post


Link to post
Share on other sites
Posted (edited)
2 hours ago, Nemesis said:

While the QAT (Quick Access Bar) doesn't already have specific fonts to choose, I would try recording a Macro and adding it to the QAT at the top of the Excel Frame
https://www.howtogeek.com/232620/how-to-add-a-macro-to-the-quick-access-toolbar-in-office/

Looks like your specific question was asked:   
https://answers.microsoft.com/en-us/msoffice/forum/all/how-add-specific-fonts-or-characters-to-quick/cc0e6649-3403-4d9e-a9e7-bd9febe1c620?auth=1

 

Edit: Ahh, personal macro saves. OK, I'll give it a shot. Only downside is that it changes the size as well as the font, which might need some tweaking. I'll give it a shot.

 

Edit 2: OK, it looks like I can manually edit the macro after saving to remove the change to font size... I think this will work great! Thanks!!

Edited by Walking Boot
  • Like 1

Share this post


Link to post
Share on other sites

This is tangential to Excel and I'm looking for any solution that can help me with this: 

I have a list of 100+ cases/projects for work.  These are currently in Excel and new cases get added each day.  We have multiple people working on each case and I'd like some place to capture the notes on each case as people work on this, such as

Case 245:

Sept 16th 3PM  Jane - "I just started working on this."

Sept 16th 5:04PM  Bill - "Did you hear about the story on CNN that might affect case 245 and 257?" 

I want to be able to quickly view the notes for each case and maintain the history of all that has transpired. 

Excel doesn't seem to be the right tool for this and our existing CRM sucks and won't be able to do this.   We have Microsoft Teams which can accomplish group chatter but I can't figure out how to make it specific to 100+ individual projects.    

Does anyone know of a collaboration solution where we can keep secure notes on all of these cases within our organization?  Is Azure within MS Teams able to do this?  If I can push new case numbers from Excel into the solution would be the cherry on top. 

Share this post


Link to post
Share on other sites
3 minutes ago, Brony said:

This is tangential to Excel and I'm looking for any solution that can help me with this: 

I have a list of 100+ cases/projects for work.  These are currently in Excel and new cases get added each day.  We have multiple people working on each case and I'd like some place to capture the notes on each case as people work on this, such as

Case 245:

Sept 16th 3PM  Jane - "I just started working on this."

Sept 16th 5:04PM  Bill - "Did you hear about the story on CNN that might affect case 245 and 257?" 

I want to be able to quickly view the notes for each case and maintain the history of all that has transpired. 

Excel doesn't seem to be the right tool for this and our existing CRM sucks and won't be able to do this.   We have Microsoft Teams which can accomplish group chatter but I can't figure out how to make it specific to 100+ individual projects.    

Does anyone know of a collaboration solution where we can keep secure notes on all of these cases within our organization?  Is Azure within MS Teams able to do this?  If I can push new case numbers from Excel into the solution would be the cherry on top. 

Totally depends on what you're company is willing to spend on.  I'm a Jira fan, but Trello might do the trick for you too.

https://www.atlassian.com/software/jira

https://trello.com/home

  • Thanks 1

Share this post


Link to post
Share on other sites
13 minutes ago, Ned said:

Totally depends on what you're company is willing to spend on.  I'm a Jira fan, but Trello might do the trick for you too.

https://www.atlassian.com/software/jira

https://trello.com/home

Thanks.  Everytime I go to the screenshots, examples for these types of solutions, I never encounter anything that resembles what I'm looking for.  Is the learning curve high to create something like I want? 

Share this post


Link to post
Share on other sites
2 hours ago, Brony said:

Thanks.  Everytime I go to the screenshots, examples for these types of solutions, I never encounter anything that resembles what I'm looking for.  Is the learning curve high to create something like I want? 

Are you on Office365?

Share this post


Link to post
Share on other sites
13 hours ago, Brony said:

Thanks.  Everytime I go to the screenshots, examples for these types of solutions, I never encounter anything that resembles what I'm looking for.  Is the learning curve high to create something like I want? 

Jira is likely the right answer, but there is definitely a learning curve to it. I converted our team to it this year - huge leap forward in project tracking.  Feel free to PM if you want to dive into it.

  • Like 1

Share this post


Link to post
Share on other sites
14 hours ago, Brony said:

Thanks.  Everytime I go to the screenshots, examples for these types of solutions, I never encounter anything that resembles what I'm looking for.  Is the learning curve high to create something like I want? 

I've used the two he mentioned and also Asana, and in my (very limited) experience, while Jira is popular with the devs and IT folks, etc. the non-technical teams preferred Asana.  Practically no learning curve, easy to create a list of projects and anyone can add comments.  Can also create sub-tasks, assign projects to people, and lots of other useful functionality but if you literally just want a list of projects that people can add comments to, any of these solutions will work but Asana might be easiest to pick up.  Trello also quite simple IIRC but no one seemed to like that one as much. 

  • Like 1
  • Thanks 1

Share this post


Link to post
Share on other sites

thanks for everyone's feedback.  I'll look into these.  Good stuff! 

Share this post


Link to post
Share on other sites
1 hour ago, Brony said:

yes.

Someone already suggest Trello, which I loved when it first came out years ago, so if you have Office365 then you have access to an app called Planner. It's like Trello, but has integration to Outlook that makes it almost exactly what you described. PM if you need assistance, but I use it for small project tracking (use a real project tracker if it's bigger than a bread basket, which O365 also has), assigning tasks to my analysts, and some collaborative stuff.

  • Thanks 1

Share this post


Link to post
Share on other sites

Macros in excel.

We have macros that create a workbook with multiple sheets and grabs data from 4 different reports that are downloaded into a file.  There is a simple user form with combobox created where an analyst selects a system from a drop down list and then a family from another drop down list.  The macros grab the data from reports that are specific to the selected system and family (of tools for example).

Example system from list is "EPS" and a family may be "ABC".  So the macro would grab the data and populate an excel worksheet with columns/rows that are associated in the report filtered by the selected system and family.  So we'd see only the EPS system and family of tools represented by ABC.  Any other family of EPS is deleted and data is cleaned up.

Dim System as String

Dim Family as String

to initialize variables:

System = ""

Family = ""

This works fine.  Normally, we only need just the one family with the system.  We always will only need just one system (per sheet) but there are occasions where we need, say, 3 families of that system to populate the same sheet.  

A drop down list only lists the families and can be selected only one at a time in the combobox.  

What I'd like to do is remove the (Family = "") and have it where it doesn't matter how many families are in the reports, just have the macro populate all families in the appropriate column on the sheet.  

IOW, if there is a text value in the "Family" column cell of the report, I want it populated on the sheet.  Any value... AA, AB, AC BB, BC, CC... any text value.  The macro already works that if the target cell does not have the "Family" selected (in the drop down list), it deletes the row and shifts up, so the only rows showing after completion is the system and family that was selected by user.  

User selects the system from drop down list in combobox.  Then hits button and whatever family is listed on the various reports will be populated on the sheet.

OR, is it possible to have an entry on the family drop down list like "All" where "All" isn't just text but points to a table that contains multiple families?  And then every instance of "family" in the rest of the macros will mean all of the families in the table (but just those families in the table... or Array?).  Is that possible?

I hope this makes sense.  Wish I was better at VBA.  

Thanks in advance.  

 

Share this post


Link to post
Share on other sites
14 hours ago, Jayrok said:

Macros in excel.

We have macros that create a workbook with multiple sheets and grabs data from 4 different reports that are downloaded into a file.  There is a simple user form with combobox created where an analyst selects a system from a drop down list and then a family from another drop down list.  The macros grab the data from reports that are specific to the selected system and family (of tools for example).

Example system from list is "EPS" and a family may be "ABC".  So the macro would grab the data and populate an excel worksheet with columns/rows that are associated in the report filtered by the selected system and family.  So we'd see only the EPS system and family of tools represented by ABC.  Any other family of EPS is deleted and data is cleaned up.

Dim System as String

Dim Family as String

to initialize variables:

System = ""

Family = ""

This works fine.  Normally, we only need just the one family with the system.  We always will only need just one system (per sheet) but there are occasions where we need, say, 3 families of that system to populate the same sheet.  

A drop down list only lists the families and can be selected only one at a time in the combobox.  

What I'd like to do is remove the (Family = "") and have it where it doesn't matter how many families are in the reports, just have the macro populate all families in the appropriate column on the sheet.  

IOW, if there is a text value in the "Family" column cell of the report, I want it populated on the sheet.  Any value... AA, AB, AC BB, BC, CC... any text value.  The macro already works that if the target cell does not have the "Family" selected (in the drop down list), it deletes the row and shifts up, so the only rows showing after completion is the system and family that was selected by user.  

User selects the system from drop down list in combobox.  Then hits button and whatever family is listed on the various reports will be populated on the sheet.

OR, is it possible to have an entry on the family drop down list like "All" where "All" isn't just text but points to a table that contains multiple families?  And then every instance of "family" in the rest of the macros will mean all of the families in the table (but just those families in the table... or Array?).  Is that possible?

I hope this makes sense.  Wish I was better at VBA.  

Thanks in advance.  

 

A couple of thoughts, although its hard to say for certain without seeing the code:

1 - Is Family a required dropdown? If not, you can just let the users leave the dropdown blank for when they want all families returned.  To do that, you can wrap the filtering piece of your code with an IF statement.  That gives you the ability to still filter families if you need to.  Like the below.

If Not FamilyDropDown = vbnullstring Then
    <your existing family filtering code>
End If

2 - By dimming as a string, you don't need to initialize the variables to "" - that's already done when you dim it.

 

 

 

 

Share this post


Link to post
Share on other sites
Posted (edited)
2 hours ago, Ned said:

A couple of thoughts, although its hard to say for certain without seeing the code:

1 - Is Family a required dropdown? If not, you can just let the users leave the dropdown blank for when they want all families returned.  To do that, you can wrap the filtering piece of your code with an IF statement.  That gives you the ability to still filter families if you need to.  Like the below.


If Not FamilyDropDown = vbnullstring Then
    <your existing family filtering code>
End If

2 - By dimming as a string, you don't need to initialize the variables to "" - that's already done when you dim it.

 

 

 

 

Thanks, Ned.  For various reasons I can't paste the code, but it's long and in-depth.  The family symbol is important as many parts of the code rely on that variable in all of the reports.  All of the corresponding data are associated with the family and dozens of columns/rows are created in the sheet.

We typically just work one family per sheet, so Family is a required dropdown.  But it doesn't have to be in this case.  Right now, it's set that if Family dropdown is left blank, it exits the sub and stops there.  

I changed the UserForm to remove the Family list.  So they only pick the system.  This works and it grabs all families but lots of other data is missing so I need to dig deeper.

The variable "Family" is littered throughout the code and is needed to process info.  It's tailored for one family at a time.

Could I create a table containing the required families as one object, and then have the dropdown selection to point to that table (instead of just text in the list) so that every instance in the code of "Family" it will be associated with all entries of the table?  That would seem to work if all instances of "Family" meant families X, Y and Z... instead of just Y.  

Or instead of pointing to drop down list just have the UserForm entry already say "Red", for instance.  So when the userform pops up they select the system and "Red" is already in the other box... where "Red" is tied to the table of families I need.  And then replace "Family" with "Red" throughout the code.  Would something like that work?  

Edited by Jayrok

Share this post


Link to post
Share on other sites
48 minutes ago, Jayrok said:

Thanks, Ned.  For various reasons I can't paste the code, but it's long and in-depth.  The family symbol is important as many parts of the code rely on that variable in all of the reports.  All of the corresponding data are associated with the family and dozens of columns/rows are created in the sheet.

We typically just work one family per sheet, so Family is a required dropdown.  But it doesn't have to be in this case.  Right now, it's set that if Family dropdown is left blank, it exits the sub and stops there.  

I changed the UserForm to remove the Family list.  So they only pick the system.  This works and it grabs all families but lots of other data is missing so I need to dig deeper.

The variable "Family" is littered throughout the code and is needed to process info.  It's tailored for one family at a time.

Could I create a table containing the required families as one object, and then have the dropdown selection to point to that table (instead of just text in the list) so that every instance in the code of "Family" it will be associated with all entries of the table?  That would seem to work if all instances of "Family" meant families X, Y and Z... instead of just Y.  

Or instead of pointing to drop down list just have the UserForm entry already say "Red", for instance.  So when the userform pops up they select the system and "Red" is already in the other box... where "Red" is tied to the table of families I need.  And then replace "Family" with "Red" throughout the code.  Would something like that work?  

I'm surprised removing the Family list for the UF didn't bomb the whole thing since it sounds like that list is depended on in a lot of places.

It's really hard to answer without seeing what you're working on, but I get why you can't share.

Honestly, if it were me, I'd take a step back and decide how to make it flexible instead of trying to patch in various fixes.  That's how bloated software happens.  Easiest solution is to use the ListBox instead of Combo so users can multi-select.  When the UF initializes, you can build the lists dynamically by reading tables you have hidden in a separate tab (gets you away from having to write out the lists inside the listbox or combobox).  Leaving the selection blank means you want all items that match that list, or you can do the "All" option like you suggested before.

 

Share this post


Link to post
Share on other sites
19 minutes ago, Ned said:

I'm surprised removing the Family list for the UF didn't bomb the whole thing since it sounds like that list is depended on in a lot of places.

It's really hard to answer without seeing what you're working on, but I get why you can't share.

Honestly, if it were me, I'd take a step back and decide how to make it flexible instead of trying to patch in various fixes.  That's how bloated software happens.  Easiest solution is to use the ListBox instead of Combo so users can multi-select.  When the UF initializes, you can build the lists dynamically by reading tables you have hidden in a separate tab (gets you away from having to write out the lists inside the listbox or combobox).  Leaving the selection blank means you want all items that match that list, or you can do the "All" option like you suggested before.

 

Good advice, thanks.  I appreciate your time and help.

Share this post


Link to post
Share on other sites
Posted (edited)

Need a little help here constructing the right graph.  I've got a data set of all of my NFL bets and projections placed this year (currently about 330 lines and grows every week).  I'm trying to build a graph so I can see if there is a correlation b/w the difference in what I project vs. the line of the bet and the result of the bet (win or loss).  I'm stuck.....and would really appreciate any help!  here's a link to the set of the 3 key columns (Description Below).

Position - Position of player I'm betting on

Abs Value % of projection - Absolute Value of the projection vs. the bet.  (Ex. If the bet is Derrick Henry over 90 yards, and I have him projected at 99 yards, it will show 10%)

Win/Loss - Actual record of Win or Loss of the bet

https://docs.google.com/spreadsheets/d/1ehpMgoNmFFEyPRYzyonvtsgNNnaoGFHutAW9EyJq6tQ/edit?usp=sharing

 

thanks!

Edited by Tiger Fan

Share this post


Link to post
Share on other sites
3 hours ago, Tiger Fan said:

Need a little help here constructing the right graph.  I've got a data set of all of my NFL bets and projections placed this year (currently about 330 lines and grows every week).  I'm trying to build a graph so I can see if there is a correlation b/w the difference in what I project vs. the line of the bet and the result of the bet (win or loss).  I'm stuck.....and would really appreciate any help!  here's a link to the set of the 3 key columns (Description Below).

Position - Position of player I'm betting on

Abs Value % of projection - Absolute Value of the projection vs. the bet.  (Ex. If the bet is Derrick Henry over 90 yards, and I have him projected at 99 yards, it will show 10%)

Win/Loss - Actual record of Win or Loss of the bet

https://docs.google.com/spreadsheets/d/1ehpMgoNmFFEyPRYzyonvtsgNNnaoGFHutAW9EyJq6tQ/edit?usp=sharing

 

thanks!

It sounds like what you want is a logistic regression.  Maybe too in-depth to explain it here but you can google "logistic regression excel" or something and there are short tutorials (or probably add-ins that can do it).

As a simpler alternative, you might want to just group all your projections into some sensible buckets (e.g. 0-10%, 11-20%, 21-30%, ... , 91-100%) and then calculate the win rate for each of those buckets.  And then plot the buckets vs. the actual win rates as scatter plot to see the correlation. 

Share this post


Link to post
Share on other sites
5 hours ago, Ignoratio Elenchi said:

It sounds like what you want is a logistic regression.  Maybe too in-depth to explain it here but you can google "logistic regression excel" or something and there are short tutorials (or probably add-ins that can do it).

As a simpler alternative, you might want to just group all your projections into some sensible buckets (e.g. 0-10%, 11-20%, 21-30%, ... , 91-100%) and then calculate the win rate for each of those buckets.  And then plot the buckets vs. the actual win rates as scatter plot to see the correlation. 

Thanks...will do some more research on the logistic regression...otherwise, the bucket solution is a great alternative!

Share this post


Link to post
Share on other sites

Hoping this is an easy one for the experts

I have a string of numbers that look like this ["12345", "23456", "34567"]

I want to be able to extract them all out individually without the quotes so I can sort them vertically in a column

12345

23456

34567

If there is an easy way to explain this, please let me know.  Thanks!

Share this post


Link to post
Share on other sites
8 minutes ago, Dwayne Hoover said:

Hoping this is an easy one for the experts

I have a string of numbers that look like this ["12345", "23456", "34567"]

I want to be able to extract them all out individually without the quotes so I can sort them vertically in a column

12345

23456

34567

If there is an easy way to explain this, please let me know.  Thanks!

 

Select the column

Under "data" tab select "Text to columns"

Select comma deliminated

Then, do a find and replace and replace " with blank values

Then copy the values and "paste special" and do a transpose paste.

 

You probably then want to go back and do text to columns one more time and change it back to tab deliminated.

 

  • Thanks 1

Share this post


Link to post
Share on other sites
On 4/28/2020 at 1:31 PM, MTskibum said:

 

Click on Data, then click on the remove duplicates with that column highlighted. It might be better to copy/paste them to a different tab first. I put a screenshot in the below link.

 

https://twitter.com/FishSkiand/status/1255218033162956804

Newer versions of Excel have added a UNIQUE function that will pull these values. Using that with the SORT and FILTER functions can make parsing data easier/more dynamic. For me at least.

Share this post


Link to post
Share on other sites

Can this be reviewed? Looking for quick help on these in excel.

 

COL A:           COL B:         COL C:     EXPECTED CALCUATION 1:            EXPECTED CALCULATION 2:

100                      45            40                     60                                                 0.015              

-                           40             40                      0                                                 0

-                           40             38                      2                                                0.005

-                          100               -                      -                                                -

EXPECTED CALCULATION 1:

IF Column C = '-', the expected calculation 1 should be set to '-'.

If Column A and Column C have data,, subtract the difference (A-C) in Expected Calculation 1.

If Column A = '-' but Column B and Column C have data, subtract the difference (A-C) in Expected Calculation 1.EXEPCTED 

EXPECTED CALCULATION 2:

Multiply the value from 'Expected Calculation 1' by 0.00025

If the value of 'Expected Calculation 1' is '-', then set the value of 'expected calculation 2' to '-'.

Share this post


Link to post
Share on other sites

=IF(C1 = "-", "-", IF(A1 <> "-", A1-C1, B1-C1))

=IF(D1="-", "-", D1*0.00025)

Share this post


Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.


  • Recently Browsing   0 members

    No registered users viewing this page.