Jump to content
Fantasy Football - Footballguys Forums

***Official*** Excel Help Corner


Ned

Recommended Posts

19 minutes ago, HaFo SaFo said:

Strange problem, my zeros aren't zero sometimes... Ever seen this and anything I can do about it? Or has my spreadsheet stumbled into a floating point error and this will never be fixed and I should just hard-code the answer?

(And, yes, that's happened to me before, I have a formula that divides a whole number by 5, so my count goes up incrementally by 0.2, but every so often 6/5 is 1.2 and then 7/5 is also 1.2 because of floating point math and then 8/5 is 1.6 and I never get a 1.4 in the sequence... "0.2, 0.4, 0.6, 0.8, 1.0, 1.2, 1.2 ,1.6" and when we went to Microsoft support they said "Excel uses floating point math and it will never be correct, and we can't fix it without re-inventing both the computer, and mathematics as a whole, so you're out of luck. Stop using decimals, use whole numbers only, and your problems will go away.")

 

Anyway, I have a spreadsheet that tracks repayments. I have how much I lent out, and then every time a partial payment comes in, it shows me how much I have left. So, for example, if I lend out $100 and I get paid back $20, I put $100 in column A, $20 in column B, then Column C is A-B and shows I have $80 owed to me still.

Fine.

Now, sometimes, I get paid back a little extra. Like, I'll get a tip. So if I'm paid back $105 it'll say I'm owed $-5. For neatness, I'd rather just show $0 owed to me. So I put in a formula: MAX(A-B,0). So if I'm paid back $105 it'll instead show the MAX of the values of -5 or 0, so... 0. Right?

But in one row in particular, row 13 of 50, for just this one row, the math doesn't add up. I have been paid back all $100 so I expect the answer to be zero. But, instead, when it evaluates A-B it gets 0. OK. But when I did the MAX(A-B, 0), the answer it shows is: "1.42E-14".

 

1.42E-14... that's a floating point error and I'm SOL, right? No other way to get my negative numbers to evaluate to zero without using MAX or an IF formula, so I think I'm dead and I'm just going to have to hard-key in a 0. 

 

Stupid math.

Not sure what downstream implications there might exist. If the c column is just for the visuals, how about =MAX(A-B-0.0001,0) then display to the whole dollar or whole cent? Would that take care of the problem?

Link to comment
Share on other sites

3 hours ago, HaFo SaFo said:

Strange problem, my zeros aren't zero sometimes... Ever seen this and anything I can do about it? Or has my spreadsheet stumbled into a floating point error and this will never be fixed and I should just hard-code the answer?

(And, yes, that's happened to me before, I have a formula that divides a whole number by 5, so my count goes up incrementally by 0.2, but every so often 6/5 is 1.2 and then 7/5 is also 1.2 because of floating point math and then 8/5 is 1.6 and I never get a 1.4 in the sequence... "0.2, 0.4, 0.6, 0.8, 1.0, 1.2, 1.2 ,1.6" and when we went to Microsoft support they said "Excel uses floating point math and it will never be correct, and we can't fix it without re-inventing both the computer, and mathematics as a whole, so you're out of luck. Stop using decimals, use whole numbers only, and your problems will go away.")

 

Anyway, I have a spreadsheet that tracks repayments. I have how much I lent out, and then every time a partial payment comes in, it shows me how much I have left. So, for example, if I lend out $100 and I get paid back $20, I put $100 in column A, $20 in column B, then Column C is A-B and shows I have $80 owed to me still.

Fine.

Now, sometimes, I get paid back a little extra. Like, I'll get a tip. So if I'm paid back $105 it'll say I'm owed $-5. For neatness, I'd rather just show $0 owed to me. So I put in a formula: MAX(A-B,0). So if I'm paid back $105 it'll instead show the MAX of the values of -5 or 0, so... 0. Right?

But in one row in particular, row 13 of 50, for just this one row, the math doesn't add up. I have been paid back all $100 so I expect the answer to be zero. But, instead, when it evaluates A-B it gets 0. OK. But when I did the MAX(A-B, 0), the answer it shows is: "1.42E-14".

 

1.42E-14... that's a floating point error and I'm SOL, right? No other way to get my negative numbers to evaluate to zero without using MAX or an IF formula, so I think I'm dead and I'm just going to have to hard-key in a 0. 

 

Stupid math.

Have you tried clearing the contents of the cell (not just deleting, but selecting clear contents), and reentering the formula?

Link to comment
Share on other sites

Existing Formula:

The logic checks the Column K (rows through 2-14) for "Rej"

=SUM(IF(FREQUENCY(IF($A$2:$A$14=A2,IF($D$2:$D$14="Final",IF($I$2:$I$14="C D w/ I",IF(ISERROR(SEARCH("Rej",$K$2:$K$14)),$E$2:$E$14)))),--$E$2:$E$14),$E$2:$E$14))

How would I remove the logic to check for "Rej" without impacting the rest of this formula?

Link to comment
Share on other sites

  • 3 weeks later...

Hi all much respect and appreciation for your past help. I have a new question:

Is there a way to filter for all rows associated with a given entry?

For instance if A is invoice no, B is customer and C is product (and then B & C may vary as the invoice no. In A stays the same for multiple rows)...

.... is there a way that I can pull in all entries for that given invoice no. in A?

Link to comment
Share on other sites

hey all, wife is back with a pair of vlookup type questions.  These are way over my head, but hopefully i can explain;

she has a source sheet and a sheet that she set up to pull info from the source.  in column A of the source, which is her driver here, there are properties with numeric 5 digit values.  i.e. - 12345.  (columns b thru whatever have info too - but column A of the source is pulling the info).  there are also properties with this type of value; 12345-01 or 12345-02.  the sheet she has set up only has 5 digit numbers.

1) on her sheet, only the properties that have dashes, i.e. 12345-01 are moving info to her sheet.  properties without a dash are not moving info........what would be the cure for this?

2) on her created sheet, lets say she has 12345.  on the source sheet, there is 12345-01, 12345-02, 12345-03, etc.  she'd like the info from all the dash whatevers to populate into her sheet.  i.e. - 12345-01, 12345-02, 12345-03 each have a square footage associated with them.  she'd like these values to populate into her 12345 overall square footage as a combined total.  

so, her sheet has no dashes, but from the source, she'd like anything separated by a dash, 12345-01, 12345-02, to consolidate into her main 12345 row.

no idea how these things work, but any theories might push her in the right direction and would be greatly appreciated. 

Link to comment
Share on other sites

On 3/13/2021 at 10:58 AM, SaintsInDome2006 said:

Hi all much respect and appreciation for your past help. I have a new question:

Is there a way to filter for all rows associated with a given entry?

For instance if A is invoice no, B is customer and C is product (and then B & C may vary as the invoice no. In A stays the same for multiple rows)...

.... is there a way that I can pull in all entries for that given invoice no. in A?

Unless I'm misunderstanding the question, you should be able to simply turn on filtering and then could easily filter row A for a particular invoice number, and it will return all rows with that invoice number.

Just select any cell in the top row (presumably your headers), then on the "Data" tab, there is a section called "Sort & Filter". Click on Filter and you will now see the down arrows in each cell of the top row. If you click on those, you will get all sorts of filtering options.

  • Like 1
Link to comment
Share on other sites

On 3/13/2021 at 3:15 PM, Chemical X said:

hey all, wife is back with a pair of vlookup type questions.  These are way over my head, but hopefully i can explain;

she has a source sheet and a sheet that she set up to pull info from the source.  in column A of the source, which is her driver here, there are properties with numeric 5 digit values.  i.e. - 12345.  (columns b thru whatever have info too - but column A of the source is pulling the info).  there are also properties with this type of value; 12345-01 or 12345-02.  the sheet she has set up only has 5 digit numbers.

1) on her sheet, only the properties that have dashes, i.e. 12345-01 are moving info to her sheet.  properties without a dash are not moving info........what would be the cure for this?

2) on her created sheet, lets say she has 12345.  on the source sheet, there is 12345-01, 12345-02, 12345-03, etc.  she'd like the info from all the dash whatevers to populate into her sheet.  i.e. - 12345-01, 12345-02, 12345-03 each have a square footage associated with them.  she'd like these values to populate into her 12345 overall square footage as a combined total.  

so, her sheet has no dashes, but from the source, she'd like anything separated by a dash, 12345-01, 12345-02, to consolidate into her main 12345 row.

no idea how these things work, but any theories might push her in the right direction and would be greatly appreciated. 

I'm not following all of the sheets, etc., but can she add a column to the source sheet that has the dashes on it.

Ideally, she'd want to be able to add a column that essentially has the master 5 digit value only in it (ie, if column A is 12345-01, the added column would be 12345. Same for if column A is 12345-02, etc.) That can be accomplished using a LEFT() formula to bring in those first 5 digits. Then, on her sheet where she wants to sum everything with the same leading 5 characters, she could do a SUMIF formula to add all the square footages for everything that matches here reference in that new column (so everything with 12345 in the new column would be summed up, etc.)

Regarding your question #1, there are a ton of possibilities - what is she using to pull in the info? If it's a VLookup or other similar lookup formula, then the lookups need to be an exact match (yes, there are option to do a closest to match, but in this case an exact match seems needed), so usually the first thing I'd look for is any differences in the lookup columns - trailing or leading spaces, something like that which would make the values not a match

  • Thanks 1
Link to comment
Share on other sites

5 hours ago, acarey50 said:

Unless I'm misunderstanding the question, you should be able to simply turn on filtering and then could easily filter row A for a particular invoice number, and it will return all rows with that invoice number.

Just select any cell in the top row (presumably your headers), then on the "Data" tab, there is a section called "Sort & Filter". Click on Filter and you will now see the down arrows in each cell of the top row. If you click on those, you will get all sorts of filtering options.

Thanks. Well the problem is say imagine 250,000 rows of invoices.

So say a company sells multiple product lines - widget1, widget2, widget3, widget4, widget5, etc.

So a given invoice may be:

Invoice No.     Product    Date

5900623        Widget3     5/9/2008

5900623        Widget2     5/9/2008

5900623        Widget8     5/9/2008

(etc.)

And then imagine thousands upon thousands of invoices, so it's basically impossible to just "know" the invoices you want. You just want to pull all products involved with any given invoice that has at least one sale of Widget3.

Follow?

TIA, many times over for any who have any idea how to do this.

Link to comment
Share on other sites

19 minutes ago, SaintsInDome2006 said:

Thanks. Well the problem is say imagine 250,000 rows of invoices.

So say a company sells multiple product lines - widget1, widget2, widget3, widget4, widget5, etc.

So a given invoice may be:

Invoice No.     Product    Date

5900623        Widget3     5/9/2008

5900623        Widget2     5/9/2008

5900623        Widget8     5/9/2008

(etc.)

And then imagine thousands upon thousands of invoices, so it's basically impossible to just "know" the invoices you want. You just want to pull all products involved with any given invoice that has at least one sale of Widget3.

Follow?

TIA, many times over for any who have any idea how to do this.

Your original question was about "a given entry", implying you would know a specific invoice #.  I'm not sure how you could "filter for all rows associated with a given entry" without knowing a data value associated with that entry.

  • Thanks 1
Link to comment
Share on other sites

14 minutes ago, SaintsInDome2006 said:

Thanks. Well the problem is say imagine 250,000 rows of invoices.

So say a company sells multiple product lines - widget1, widget2, widget3, widget4, widget5, etc.

So a given invoice may be:

Invoice No.     Product    Date

5900623        Widget3     5/9/2008

5900623        Widget2     5/9/2008

5900623        Widget8     5/9/2008

(etc.)

And then imagine thousands upon thousands of invoices, so it's basically impossible to just "know" the invoices you want. You just want to pull all products involved with any given invoice that has at least one sale of Widget3.

Follow?

TIA, many times over for any who have any idea how to do this.

That's a bit more complicated that how I interpreted your initial questions of "is there a way that I can pull in all entries for that given invoice no. in A?" which I interpreted as filtering for every entry with that invoice number.

I'm not sure I completely follow though what it is you are trying to be able to do.

  • Are you saying you want to be able to find every other product ever sold on any invoice that includes widget 3?
  • Are you just looking for every invoice where widget 3 was sold? Easy enough, just filter on widget, though I am guessing this is not what you are asking.
  • Something else (I swear I am not being intentionally obtuse, I think I am just truly not understanding what it is you are trying to accomplish)
Link to comment
Share on other sites

1 hour ago, heckmanm said:

Your original question was about "a given entry", implying you would know a specific invoice #.  I'm not sure how you could "filter for all rows associated with a given entry" without knowing a data value associated with that entry.

Thanks, yes I agree - sorry - I originally fired this off in the hopes of pushing forward. Sorry for the confusion or inconvenience.

Link to comment
Share on other sites

1 hour ago, acarey50 said:

That's a bit more complicated that how I interpreted your initial questions of "is there a way that I can pull in all entries for that given invoice no. in A?" which I interpreted as filtering for every entry with that invoice number.

I'm not sure I completely follow though what it is you are trying to be able to do.

  • Are you saying you want to be able to find every other product ever sold on any invoice that includes widget 3?
  • Are you just looking for every invoice where widget 3 was sold? Easy enough, just filter on widget, though I am guessing this is not what you are asking.
  • Something else (I swear I am not being intentionally obtuse, I think I am just truly not understanding what it is you are trying to accomplish)

Eh, alright, I do have 250,000 rows of data. basically I am trying to avoid doing what I need to do linearly or manually. That is I do not want to squint at each row of data one by one to carefully examine it, I'd like to eliminates the invoices (sales) which I do not need to see, which would save me a lot of trouble, mistakes and eye strain.

To answer your question - of the three:

  • Yes.
  • No.
  • Ha, thanks, man, it's me, I'm the difficult one, I've failed at explaining (and I really really appreciate you help). - Ok I will try to summary this better - without revealing what I do, which is complicated - say it's permissible to sell Widget3 with Widget1 but not Widget2. - I'm trying to go through all the invoices where Widget3 has been sold, so I can look at all the products sold with Widget3, so I can see which sales (ie invoices) were appropriate/permissible, and which were not.
Edited by SaintsInDome2006
Link to comment
Share on other sites

11 minutes ago, SaintsInDome2006 said:

Eh, alright, I do have 250,000 rows of data. basically I am trying to avoid doing what I need to do lienarly or manually.

To answer your question - of the three:

  • Yes.
  • No.
  • Ha, thanks, man, it's me, I'm the difficult one, I've failed at explaining (and I really really appreciate you help). - Ok I will try to summary this better - without revealing what I do, which is complicated - say it's permissible to sell Widget3 with Widget1 but not Widget2. - I'm trying to go through all the invoices where Widget3 has been sold, so I can look at all the products sold with Widget3, so I can see which sales (ie invoices) were appropriate/permissible, and which were not.

Ok - that is quite a bit more complicated.

Best I can do is provide this link that is for result from a request about using the same field as both row and column in a Pivot Table (you can't from what I can find) to count the distinct combinations, essentially what you are trying to do with the product combinations within an invoice.

I think the PowerPivot solution has promise to at least help you identify how often any impermissible combinations were sold together, but it may not get you to the level of being able to identify the exact invoices/customers.

 

One other option, and this really depends on how many impermissible combinations there are, would be to put the data into a pivottable, Invoice number as the row (could also add customer name or any other identifier you need, just set it as a tabular layout with repeating item labels), product line as the column, and, if you have a quantity field, use that for the value, otherwise any field that isn't blank as the data as you at least want a 1 to show up for when there is a product sold on a particular invoice.

Then, for example, you could filter all invoices with say Widget 1, and if you need to see if Widget 4 was ever sold with it, you then filter for widget 4, and you now have your list of every invoice where that product combination was sold.

If there is a large number of impermissible combinations, then perhaps we could take the pivottable results and build another check to run through the various combinations to flag any invoice with an impermissible combination.

  • Thanks 1
Link to comment
Share on other sites

On 3/15/2021 at 11:24 AM, acarey50 said:

I'm not following all of the sheets, etc., but can she add a column to the source sheet that has the dashes on it.

Ideally, she'd want to be able to add a column that essentially has the master 5 digit value only in it (ie, if column A is 12345-01, the added column would be 12345. Same for if column A is 12345-02, etc.) That can be accomplished using a LEFT() formula to bring in those first 5 digits. Then, on her sheet where she wants to sum everything with the same leading 5 characters, she could do a SUMIF formula to add all the square footages for everything that matches here reference in that new column (so everything with 12345 in the new column would be summed up, etc.)

Regarding your question #1, there are a ton of possibilities - what is she using to pull in the info? If it's a VLookup or other similar lookup formula, then the lookups need to be an exact match (yes, there are option to do a closest to match, but in this case an exact match seems needed), so usually the first thing I'd look for is any differences in the lookup columns - trailing or leading spaces, something like that which would make the values not a match

This was helpful and pushed her in the right direction, but now she has the following issue, which is somewhat related to question 2;

so, on her source sheet, let's say she has a row of values she wants to add (i.e. row 2, columns a, b, c, d).  she wants to add these 4 figures and have them move to her created sheet into 1 space (i.e. a1).  any ideas on how to accomplish this?

for reference, the source sheet has square footage totals, let's say a2, b2, c2, d2 each being 25,000.  on her created sheet for the matching property,  she has 1 space to total square footage.  so, she'd like that space to be 100,000.

Thx

 

 

Link to comment
Share on other sites

58 minutes ago, Chemical X said:

This was helpful and pushed her in the right direction, but now she has the following issue, which is somewhat related to question 2;

so, on her source sheet, let's say she has a row of values she wants to add (i.e. row 2, columns a, b, c, d).  she wants to add these 4 figures and have them move to her created sheet into 1 space (i.e. a1).  any ideas on how to accomplish this?

for reference, the source sheet has square footage totals, let's say a2, b2, c2, d2 each being 25,000.  on her created sheet for the matching property,  she has 1 space to total square footage.  so, she'd like that space to be 100,000.

Thx

 

 

Will the second sheet have the property number as a unique identifier? And are the number of columns to be summed the same for each property, or at the very least is there a max number of columns that may need to be summed up?

If there is a unique identifier, and a cap on the number of columns with square footages listed (though technically you could work around this), then I believe your two best options would be to either use a SUMIF formula on the created sheet, or, if you can add a column with total square footage to the source, you could do a lookup on the created sheet to pull over the total square footage.

This may be a simplistic version of what she actually needs to do, but let's say on your source sheet the columns are:

  • Column A - Property (unique identifier)
  • Column B - SQ Footage 1
  • Column C - SQ Footage 2
  • Column D - SQ Footage 3
  • Column E - SQ Footage 4

Then, on her created sheet, you would have:

  • Column A - Property (the unique identifier)
  • Column B - Total Square Footage (SUMIF formula that will look for the matching Property number and return the sum of columns A thru D)

The nice thing about using this is if your source sheet has multiple line items for each property (not knowing the actual source data, not sure if this is possible), then it will sum up the values from all the columns for all of those rows that match the Property value.

I think this is likely the best option from what I understand of the project, but if it won't work for some reason we can look at the option of adding a total square footage column to the source and pulling from there. It is essentially the same process, especially if it is possible for a property to have multiple rows on the source.

 

Link to comment
Share on other sites

2 minutes ago, acarey50 said:

Will the second sheet have the property number as a unique identifier? And are the number of columns to be summed the same for each property, or at the very least is there a max number of columns that may need to be summed up?

If there is a unique identifier, and a cap on the number of columns with square footages listed (though technically you could work around this), then I believe your two best options would be to either use a SUMIF formula on the created sheet, or, if you can add a column with total square footage to the source, you could do a lookup on the created sheet to pull over the total square footage.

This may be a simplistic version of what she actually needs to do, but let's say on your source sheet the columns are:

  • Column A - Property (unique identifier)
  • Column B - SQ Footage 1
  • Column C - SQ Footage 2
  • Column D - SQ Footage 3
  • Column E - SQ Footage 4

Then, on her created sheet, you would have:

  • Column A - Property (the unique identifier)
  • Column B - Total Square Footage (SUMIF formula that will look for the matching Property number and return the sum of columns A thru D)

The nice thing about using this is if your source sheet has multiple line items for each property (not knowing the actual source data, not sure if this is possible), then it will sum up the values from all the columns for all of those rows that match the Property value.

I think this is likely the best option from what I understand of the project, but if it won't work for some reason we can look at the option of adding a total square footage column to the source and pulling from there. It is essentially the same process, especially if it is possible for a property to have multiple rows on the source.

 

i think you re accurate....gonna slide this by the boss and see what happens.  many thx for all the help.

Link to comment
Share on other sites

4 minutes ago, acarey50 said:

Will the second sheet have the property number as a unique identifier? And are the number of columns to be summed the same for each property, or at the very least is there a max number of columns that may need to be summed up?

If there is a unique identifier, and a cap on the number of columns with square footages listed (though technically you could work around this), then I believe your two best options would be to either use a SUMIF formula on the created sheet, or, if you can add a column with total square footage to the source, you could do a lookup on the created sheet to pull over the total square footage.

This may be a simplistic version of what she actually needs to do, but let's say on your source sheet the columns are:

  • Column A - Property (unique identifier)
  • Column B - SQ Footage 1
  • Column C - SQ Footage 2
  • Column D - SQ Footage 3
  • Column E - SQ Footage 4

Then, on her created sheet, you would have:

  • Column A - Property (the unique identifier)
  • Column B - Total Square Footage (SUMIF formula that will look for the matching Property number and return the sum of columns A thru D)

The nice thing about using this is if your source sheet has multiple line items for each property (not knowing the actual source data, not sure if this is possible), then it will sum up the values from all the columns for all of those rows that match the Property value.

I think this is likely the best option from what I understand of the project, but if it won't work for some reason we can look at the option of adding a total square footage column to the source and pulling from there. It is essentially the same process, especially if it is possible for a property to have multiple rows on the source.

 

do you know a formula for this....?

Link to comment
Share on other sites

50 minutes ago, Chemical X said:

do you know a formula for this....?

Assuming you mean the SUMIF formula, here is the microsoft page on the formula

For your case, in the cell where you want to have the total square footage you essentially would put:

=SUMIFS(SourceSheet!$B:$E,SourceSheet!$A:$A,A2)

The actual reference to SourceSheet will be based on the name of the source file (if the source sheet and her created sheet are in separate files) and the actual sheet name in the file (assuming not the default Sheet1)

 

I used SUMIFS (it is essentially the same as SUMIF) as it allows the use of multiple criteria range should that be a necessity, either in the future or just in case the real sheet/needs is a bit more complex than the sample case.

 

Link to comment
Share on other sites

17 hours ago, acarey50 said:

Assuming you mean the SUMIF formula, here is the microsoft page on the formula

For your case, in the cell where you want to have the total square footage you essentially would put:

=SUMIFS(SourceSheet!$B:$E,SourceSheet!$A:$A,A2)

The actual reference to SourceSheet will be based on the name of the source file (if the source sheet and her created sheet are in separate files) and the actual sheet name in the file (assuming not the default Sheet1)

 

I used SUMIFS (it is essentially the same as SUMIF) as it allows the use of multiple criteria range should that be a necessity, either in the future or just in case the real sheet/needs is a bit more complex than the sample case.

 

Very helpful says the boss....thx!

Link to comment
Share on other sites

  • 2 weeks later...
On 3/17/2021 at 10:36 AM, acarey50 said:

No problem - glad I was able to help

new problem for you or the group;

wife has an excel sheet.  let’s say column a are city names and column b is square footage of space.  if she types the acronym bau in column c she’d like column d to have the value of column a copied over....this would be for the whole chart.  

so, if any row in column c gets bau, that row’s column b value will repeat in column d.

 

thx

 

 

Link to comment
Share on other sites

Seems like a pretty simple IF statement if understanding this correctly.

IN column D, let's assume starting in row 2

=IF(C2="BAU",B2,"")

Copy that down column D

What that formula is doing is looking at column C, and if it has BAU in it, it will show the value from column B, if it doesn't, it will show as blank.

Link to comment
Share on other sites

  • 2 months later...

Here is a stupid problem. One of my employees has done something in her program that causes her view to shrink when she pushes ctrl. So she can’t use shortcuts without the spreadsheet shrinking. I was thinking maybe sticky keys but all other programs are fine. 
 

Any ideas?  I’m about to uninstall and reinstall. 

Link to comment
Share on other sites

14 hours ago, Nick Vermeil said:

Here is a stupid problem. One of my employees has done something in her program that causes her view to shrink when she pushes ctrl. So she can’t use shortcuts without the spreadsheet shrinking. I was thinking maybe sticky keys but all other programs are fine. 
 

Any ideas?  I’m about to uninstall and reinstall. 

Just by pressing the CTRL key alone, it zooms out?   Because CTRL plus scrolling the mouse wheel will zoom in/out in most programs. 

Link to comment
Share on other sites

10 minutes ago, Nathan R. Jessep said:

Just by pressing the CTRL key alone, it zooms out?   Because CTRL plus scrolling the mouse wheel will zoom in/out in most programs. 

Just pressing the CTRL key.  It's the weirdest thing.  

Link to comment
Share on other sites

4 minutes ago, Nick Vermeil said:

Just pressing the CTRL key.  It's the weirdest thing.  

Hmmm, have you checked the shortcuts list to see if she somehow added a custom one with the CTRL key?

Only other thing I can think of is if she somehow created a macro that invokes the Zoom function when the CTRL key is pressed. 

 

  • Thanks 1
Link to comment
Share on other sites

14 minutes ago, Nathan R. Jessep said:

Hmmm, have you checked the shortcuts list to see if she somehow added a custom one with the CTRL key?

Only other thing I can think of is if she somehow created a macro that invokes the Zoom function when the CTRL key is pressed. 

 

Doesn't appear to be the case but thanks for the advice.  Looks like a reinstall

Link to comment
Share on other sites

15 hours ago, Nick Vermeil said:

Here is a stupid problem. One of my employees has done something in her program that causes her view to shrink when she pushes ctrl. So she can’t use shortcuts without the spreadsheet shrinking. I was thinking maybe sticky keys but all other programs are fine. 
 

Any ideas?  I’m about to uninstall and reinstall. 

 

Try a different mouse before you reinstall. That fixed 2 other users with this same error.

 

https://answers.microsoft.com/en-us/msoffice/forum/msoffice_excel-mso_win10-mso_o365b/pressing-ctrl-key-is-zooming-out-in-excel/3b575a47-6876-4710-ac98-9a79a89b11a5

  • Thanks 1
Link to comment
Share on other sites

  • 1 month later...
12 hours ago, Chemical X said:

here is a strange question;

in excel, my cell contents don’t appear in the formula bar…..almost like they are invisible.

thoughts?

Did you try expanding the formula bar height to see if there's extra carriage returns in the cells themselves?

Link to comment
Share on other sites

37 minutes ago, Ned said:

Did you try expanding the formula bar height to see if there's extra carriage returns in the cells themselves?

looks like a formatting issue, solved and thx!

  • Like 1
Link to comment
Share on other sites

  • 1 month later...

Hi ExcelGuys - quick question for some work I have to do tonight…

Is there an easy way to run a tool or a formula to see differences in two lists? So say Project 1 was done 4 weeks ago and Project 3 was run two days ago and they use the same fields in columns A-D. Is there a way I can tell the new entries just by running a certain formula, tool, button or option? 

I’ve started this manually (I’ll grind through it) but there must be an easier way. THANKS as always.

Link to comment
Share on other sites

37 minutes ago, SaintsInDome2006 said:

Hi ExcelGuys - quick question for some work I have to do tonight…

Is there an easy way to run a tool or a formula to see differences in two lists? So say Project 1 was done 4 weeks ago and Project 3 was run two days ago and they use the same fields in columns A-D. Is there a way I can tell the new entries just by running a certain formula, tool, button or option? 

I’ve started this manually (I’ll grind through it) but there must be an easier way. THANKS as always.

i have nothing to add, but i use mrexcel.com sometimes and those guys are money…..

  • Thanks 1
Link to comment
Share on other sites

48 minutes ago, SaintsInDome2006 said:

Hi ExcelGuys - quick question for some work I have to do tonight…

Is there an easy way to run a tool or a formula to see differences in two lists? So say Project 1 was done 4 weeks ago and Project 3 was run two days ago and they use the same fields in columns A-D. Is there a way I can tell the new entries just by running a certain formula, tool, button or option? 

I’ve started this manually (I’ll grind through it) but there must be an easier way. THANKS as always.

 

Hey SID!  Long time.

Can you unpack this a little bit?  Not sure I'm following you.  What are you doing manually?

Link to comment
Share on other sites

2 hours ago, Dinsy Ejotuz said:

 

Hey SID!  Long time.

Can you unpack this a little bit?  Not sure I'm following you.  What are you doing manually?

Hola! Yes indeed, hope you & yours are well, I'm good. I wrote this while at the Saints game hoping there might be some advice when I landed back at my desk.

Ok: 4 columns of info, A-D. Pretty simple.

I've received two worksheets. One version of this worksheet started 3 weeks ago and it's cumulative. So say it analyzed numbered items out of a group of 1000. Say that first sheet analyzed 250 rows of data 3 weeks ago.

- But now, 3 weeks later, I've received an update, and it has say 480 total pieces reviewed. Some of the original 250 is in that 480, but it's not indicated which are new, and I'm wondering if XL can simply identify which ones (230) are new.

I can do this manually by (eta: sorting by col. A) highlighting the 480 from the 2nd set, drop in the rows from the 1st sheet, and any time there's a new entry it won't have a matching non-highlighted row. So I am grinding through this data using this manual self-coded system, deleting the matches and highlighting in a different color the new ones.

So, basically, is there a tool or button I can employ to do this automatically without doing this drill?

Sorry, don't mean to cause any homework for anyone. Winston played great tonight and feeling fine in the A/C. - Any advice is appreciated. - Thanks.

Edited by SaintsInDome2006
Link to comment
Share on other sites

1 hour ago, SaintsInDome2006 said:

Hola! Yes indeed, hope you & yours are well, I'm good. I wrote this while at the Saints game hoping there might be some advice when I landed back at my desk.

Ok: 4 columns of info, A-D. Pretty simple.

I've received two worksheets. One version of this worksheet started 3 weeks ago and it's cumulative. So say it analyzed numbered items out of a group of 1000. Say that first sheet analyzed 250 rows of data 3 weeks ago.

- But now, 3 weeks later, I've received an update, and it has say 480 total pieces reviewed. Some of the original 250 is in that 480, but it's not indicated which are new, and I'm wondering if XL can simply identify which ones (230) are new.

I can do this manually by (eta: sorting by col. A) highlighting the 480 from the 2nd set, drop in the rows from the 1st sheet, and any time there's a new entry it won't have a matching non-highlighted row. So I am grinding through this data using this manual self-coded system, deleting the matches and highlighting in a different color the new ones.

So, basically, is there a tool or button I can employ to do this automatically without doing this drill?

Sorry, don't mean to cause any homework for anyone. Winston played great tonight and feeling fine in the A/C. - Any advice is appreciated. - Thanks.

 

Copy/paste the new data directly below the old data and then remove duplicates.

You can highlight the new data first if you want to keep track of what is new.

 

You could also concatenate the columns into column e in both data sets and then do a vlookup on 1 column e into the other column e.

 

Probably a few more ways to do it as well.

  • Thanks 1
Link to comment
Share on other sites

7 hours ago, MTskibum said:

 

Copy/paste the new data directly below the old data and then remove duplicates.

You can highlight the new data first if you want to keep track of what is new.

 

You could also concatenate the columns into column e in both data sets and then do a vlookup on 1 column e into the other column e.

 

Probably a few more ways to do it as well.

:goodposting: That's the most direct way to do it.

Info on removing duplicates

  • Thanks 1
Link to comment
Share on other sites

7 hours ago, MTskibum said:

 

Copy/paste the new data directly below the old data and then remove duplicates.

You can highlight the new data first if you want to keep track of what is new.

 

You could also concatenate the columns into column e in both data sets and then do a vlookup on 1 column e into the other column e.

 

Probably a few more ways to do it as well.

I did the first thing, I think the concatenation is something I need to revisit. I've done it before but it's been a while. I appreciate the reply. -SID

Link to comment
Share on other sites

1 hour ago, SaintsInDome2006 said:

I did the first thing, I think the concatenation is something I need to revisit. I've done it before but it's been a while. I appreciate the reply. -SID

concatenate is very easy.  some use the formula, but an easier way is to simply use the & sign.  Like this:

=A1&B1

Put that in C1 and you get the contents of A1 and B1 merged together.

  • Like 1
Link to comment
Share on other sites

21 hours ago, bryhamm said:

concatenate is very easy.  some use the formula, but an easier way is to simply use the & sign.  Like this:

=A1&B1

Put that in C1 and you get the contents of A1 and B1 merged together.

I'm lazy and always go this way.  I see almost no value to the formula.

  • Like 1
Link to comment
Share on other sites

  • 4 weeks later...

I've got a problem that I've tried a few solutions for and haven't locked it down.

I have a string of numbers and letters.  I need the 8 characters immediately before a specific string of 3 letters.  This can come anywhere in a string that is about 100 characters long.

 

So like

 

1223898AAXETAAST2310341ABC132305uAASERWA1@#%^AAAt235ZOMG

I need the 8 characters immediately before "ABC" where the next string may not have the ABC in this exact spot.

Link to comment
Share on other sites

13 minutes ago, culdeus said:

I've got a problem that I've tried a few solutions for and haven't locked it down.

I have a string of numbers and letters.  I need the 8 characters immediately before a specific string of 3 letters.  This can come anywhere in a string that is about 100 characters long.

 

So like

 

1223898AAXETAAST2310341ABC132305uAASERWA1@#%^AAAt235ZOMG

I need the 8 characters immediately before "ABC" where the next string may not have the ABC in this exact spot.

 

=RIGHT(LEFT(A1,FIND("ABC",A1)-1),8)

Where A1 is your string, and "ABC" is your target

Edited by Fat Nick
  • Like 3
  • Love 1
Link to comment
Share on other sites

  • 2 weeks later...

Ok, have an issue that I'm not sure how to solve.  I've got a table of cells with text.  In each cell an account is separated from the description by a dash.  I need to parse the account into one cell, and then parse the account to another.  Basically taking everything from the right of the dash into on cell and everything to the right into another.  And all the accounts / descriptions are of different length. 

Link to comment
Share on other sites

3 minutes ago, coopersdad said:

Ok, have an issue that I'm not sure how to solve.  I've got a table of cells with text.  In each cell an account is separated from the description by a dash.  I need to parse the account into one cell, and then parse the account to another.  Basically taking everything from the right of the dash into on cell and everything to the right into another.  And all the accounts / descriptions are of different length. 

https://support.microsoft.com/en-us/office/split-text-into-different-columns-with-the-convert-text-to-columns-wizard-30b14928-5550-41f5-97ca-7a3e9c363ed7

Your delimiter would be the dash.

  • Love 1
Link to comment
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.

×
  • Create New...