Jump to content
Fantasy Football - Footballguys Forums

***Official*** Excel Help Corner


Recommended Posts

There's a constant problem in the video editing world with timecode: there are several different types and it's complicated to convert one format to another. It certainly can't be done in your head, the algorithm is insanely complicated. There are calculators online that can do it, but, they're all a bit different and sometimes do the conversion in a slightly different way into a format that's close but not exactly the one you need. 

Last week, I needed to convert some timecode and couldn't find an online calculator that matched the formats in and out that I wanted. But then I found an Excel file someone posted online that did the conversion for you: type in the timecode you want in A and the converted format appears in B. And vice-versa, type it into B in the alternate format, and it'll give you the matching code in A.

I was curious how it worked, so I looked at the formula in the cell, hoping to break it down. But it just was a reference to a hidden sheet. So I un-hid the sheet and took a look.

Was it the algorithm I expected? Did it need 8-20 helper cells and placeholders to keep it on track as it stepped through the process? Nope. Instead, column A was just numbers. But not just any numbers.... it was every possible number of 24 frames-per-second timecode. From 00:00:00.00 to 00:00:00.01 to 00:00:00.02 to 00:00:00.03, etc., down all of Column A. All 1,040,000 rows or whatever. Column B? Every corresponding number in 29.97 frames-per-second timecode, all million-plus possible answers.

The magic formula was just a LOOKUP. :lmao:

  • Laughing 1
Link to post
Share on other sites
  • Replies 995
  • Created
  • Last Reply

Top Posters In This Topic

Top Posters In This Topic

Popular Posts

You still need to spell out all of the cells with concat (or concatenate or just the & sign). That is: =CONCATENATE(A1, A2, A3... etc up to A-whatever. Textjoin lets you just enter a range: =

Also sometimes where that doesn't turn out well, I've pasted into Notepad then copy and pasted that to Excel and it's done better.

I'm an Excel nerd to the hilt and enjoy helping others when it comes to Excel problems, so I figured I'd start a thread where y'all can ask questions on how to do certain things in Excel. I'm game fo

In layman's terms, they basically wrote a "formula" that converts metric to standard units by writing out every single increment of centimeters, from 1 to 10.5 kilometers, in column A, then wrote out every equivalent in inches in column B, then just did a LOOKUP for the matching value.

A                B

1cm = 0.393701 inches

2 cm = 0.787402 inches

3 cm = 1.1811 inches

.... 1 million, 40 thousand rows later...

1,040,000 cm = 409448.8189 inches

 

:headexplode:

Edited by shades
Link to post
Share on other sites
22 hours ago, Thorpe said:

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

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

Thank you! 

One other question related to this question. How would I update the existing  formula to account for a value of 'N' in A1 or B1? I didn't realize this until just now, but a value of 'N' is possible in any of these values and would make the calculation impossible. 

In addition to the existing formula, I need to to also output a '-' if/when A1 or B1 has a value of 'N'.

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

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

Edited by cubd8
Link to post
Share on other sites

Thank you again!

One other question!

When Column A has the same value and Column B contains "Grass", aggregate the values in Column C in the Expected Value. 

How do I write this ?

Column A:                    Column B:              Column c                 Expected Value:

111                                Grass                        100                             250

111                                Weed                          50                             250

222                                WeedGrass                25                              25

222                                "              "                    0                                25

111                                Grass                        100                             250

333                                Grass                        100                              100

444                                Weed                         50                                 0

555                                "          "                       0                                   0

 

Edited by cubd8
Link to post
Share on other sites
6 hours ago, cubd8 said:

Thank you again!

One other question!

When Column A has the same value and Column B contains "Grass", aggregate the values in Column C in the Expected Value. 

How do I write this ?

Column A:                    Column B:              Column c                 Expected Value:

111                                Grass                        100                             250

111                                Weed                          50                             250

222                                WeedGrass                25                              25

222                                "              "                    0                                25

111                                Grass                        100                             250

333                                Grass                        100                              100

444                                Weed                         50                                 0

555                                "          "                       0                                   0

 

I would probably use SUMIFS for this.  In Column D, row 1: 

=SUMIFS($C$1:$C$100, $A$1:$A$100, A1) and copy down.  For Row 1, this adds up all of the occurences of 111 in Column A.  I can't tell if your example is contingent on what is in Column B.  If it is, $B$1:$B$100, B1 to the end of your SUMIFS.  

Link to post
Share on other sites

Can somebody assist with this logic below?

(IF Column A = "Down" OR Column B > 0) AND Column C > 0 then subtract Column D - Column E.
- If Column E = N OR the subtraction cannot be performed, output a '-' in Column F.

Column A:                   Column B:                     Column c                 Column d                     Column E                     Expected Calculation:

Down                           -                                      1                                763,979                       90,121                          673,858

Down                          3                                      -                                 145,566                      145,566                        -

Up                               3                                     -                                    -                                 N                                   -

Up                               2                                     -                                   -                                  70,384                         -

Down                         -                                       1                                100                             N                                   -

Up                              1                                      -                                  -                                 29,228                          -

Down                        -                                       -                                  -                                 N                                   -

Down                       2                                      1                                40                               20                               20

Down                      1                                       1                               10                               4                                  6

 

Link to post
Share on other sites
  • 2 weeks later...
13 minutes ago, Chemical X said:

here's one;

wife inserts an excel chart into a powerpoint presentation, then the chart rows lose their spacing......any idea how to respace an excel chart that is in powerpoint?

 

thx

Paste Special as an image is the easiest in my opinion, assuming you aren't trying to link it/update it in Powerpoint.

  • Like 1
Link to post
Share on other sites
34 minutes ago, acarey50 said:

Paste Special as an image is the easiest in my opinion, assuming you aren't trying to link it/update it in Powerpoint.

thx.  not so easy, she just mentioned the chart is created by vba code.  so i have no idea anymore.

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

thx.  not so easy, she just mentioned the chart is created by vba code.  so i have no idea anymore.

Even so, if it's a chart, you should still be able to copy/paste it as an image.

If the Powerpoint is something that will be updated regularly, you can paste as a link to the original, and the powerpoint should update when the Excel file updates, but I know in practice it is not always that simple.

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

I haven't tested this, but when you paste into powerpoint there is an option to "Keep Source formatting and Link Data" which sounds like what you need.

True, however, if this is a file that will be emailed, shared, etc. you will get the fun popup that the source/linked file could not be found, etc., and it will not be linked to the source data. In theory it could be simple, in practice it often is not.

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

thx.  not so easy, she just mentioned the chart is created by vba code.  so i have no idea anymore.

Even so, if it's a chart, you should still be able to copy/paste it as an image.

If the Powerpoint is something that will be updated regularly, you can paste as a link to the original, and the powerpoint should update when the Excel file updates, but I know in practice it is not always that simple.

agreed - how it's created shouldn't matter.

Link to post
Share on other sites
  • 2 weeks later...
1 hour ago, heckmanm said:

No question, just a pet peeve: People who put blank rows or columns in large spreadsheets that make it IMPOSSIBLE to use the "Filter" function.

That also stymies CTRL-SHIFT-8 to select the whole table. 

It's right up there with the people who people who use Merge and Center across columns.  

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

That also stymies CTRL-SHIFT-8 to select the whole table. 

It's right up there with the people who people who use Merge and Center across columns.  

I do wish they'd make Center Across Selection the default instead of merge - it is much more useful in my opinion and essentially accomplishes the same desired formatting

  • Thanks 1
  • Love 1
Link to post
Share on other sites
15 hours ago, heckmanm said:

No question, just a pet peeve: People who put blank rows or columns in large spreadsheets that make it IMPOSSIBLE to use the "Filter" function.

I know for me if I click the kind of half square looking thing to the left of the A (first column) it highlights the whole excel and lets you filter.

Link to post
Share on other sites
  • 4 weeks later...

Help

I have a number that must be formatted to two digits, i.e. 5 = 05. I can easily format a cell to display this.  ok.

Now I want to concatentate this number into a string and need it to also be "05" in the string.  

how?

 

Edit, answer was basically : ="G4-"&D16& "-" &TEXT(D12,"#00")

Edited by culdeus
Link to post
Share on other sites
2 minutes ago, culdeus said:

Help

I have a number that must be formatted to two digits, i.e. 5 = 05. I can easily format a cell to display this.  ok.

Now I want to concatentate this number into a string and need it to also be "05" in the string.  

how?

This will combine A1 and B1.

=IF(A1<10,"0"&A1&B1,A1&B1)

or you can use CONCATENATE.

=IF(A1<10,CONCATENATE("0",A1,B1),CONCATENATE(A1&B1))

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

This will combine A1 and B1.

=IF(A1<10,"0"&A1&B1,A1&B1)

or you can use CONCATENATE.

=IF(A1<10,CONCATENATE("0",A1,B1),CONCATENATE(A1&B1))

I figured out another way to do it, see the edit.  

Link to post
Share on other sites
  • 1 month later...

As near as I can tell, the Developer tab tools (i.e., the Form Controls tools such as option buttons, scroll bars, combo boxes) are not available in Google Sheets.  Can someone confirm?

Link to post
Share on other sites
  • 2 weeks later...

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.

Link to post
Share on other sites
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 post
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 post
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 post
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 post
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 post
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 post
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 post
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 post
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 post
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 post
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 post
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 post
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 post
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 post
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 post
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 post
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 post
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 post
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 post
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 post
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 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.


×
×
  • Create New...