shades 272 Posted November 9, 2020 Share Posted November 9, 2020 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. 1 Quote Link to post Share on other sites
shades 272 Posted November 9, 2020 Share Posted November 9, 2020 (edited) 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 November 9, 2020 by shades Quote Link to post Share on other sites
cubd8 299 Posted November 10, 2020 Share Posted November 10, 2020 (edited) 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 November 10, 2020 by cubd8 Quote Link to post Share on other sites
Thorpe 221 Posted November 10, 2020 Share Posted November 10, 2020 I didn't test this in Excel, but this should work: =IF(C1 = "-" , "-", IF(A1 = "N" or B1= "N" , "-", IF(A1 <> "-" , A1-C1, B1-C1))) Quote Link to post Share on other sites
cubd8 299 Posted November 10, 2020 Share Posted November 10, 2020 (edited) 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 November 10, 2020 by cubd8 Quote Link to post Share on other sites
bryhamm 877 Posted November 10, 2020 Share Posted November 10, 2020 not really an easy/convenient way to do that with formulas. might be better doing a pivot table for that. Quote Link to post Share on other sites
Brony 7,669 Posted November 10, 2020 Share Posted November 10, 2020 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. Quote Link to post Share on other sites
cubd8 299 Posted November 11, 2020 Share Posted November 11, 2020 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 Quote Link to post Share on other sites
Chemical X 5,823 Posted November 19, 2020 Share Posted November 19, 2020 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 Quote Link to post Share on other sites
acarey50 634 Posted November 19, 2020 Share Posted November 19, 2020 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. 1 Quote Link to post Share on other sites
Chemical X 5,823 Posted November 19, 2020 Share Posted November 19, 2020 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. Quote Link to post Share on other sites
acarey50 634 Posted November 20, 2020 Share Posted November 20, 2020 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. Quote Link to post Share on other sites
Thorpe 221 Posted November 20, 2020 Share Posted November 20, 2020 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. 1 Quote Link to post Share on other sites
acarey50 634 Posted November 20, 2020 Share Posted November 20, 2020 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. Quote Link to post Share on other sites
Ned 10,620 Posted November 20, 2020 Author Share Posted November 20, 2020 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. Quote Link to post Share on other sites
heckmanm 4,434 Posted December 2, 2020 Share Posted December 2, 2020 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. 1 Quote Link to post Share on other sites
Brony 7,669 Posted December 2, 2020 Share Posted December 2, 2020 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. Quote Link to post Share on other sites
acarey50 634 Posted December 2, 2020 Share Posted December 2, 2020 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 1 1 Quote Link to post Share on other sites
Niles Standish 354 Posted December 3, 2020 Share Posted December 3, 2020 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. Quote Link to post Share on other sites
culdeus 7,527 Posted December 29, 2020 Share Posted December 29, 2020 (edited) 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 December 29, 2020 by culdeus Quote Link to post Share on other sites
Ned 10,620 Posted December 29, 2020 Author Share Posted December 29, 2020 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)) Quote Link to post Share on other sites
culdeus 7,527 Posted December 29, 2020 Share Posted December 29, 2020 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. Quote Link to post Share on other sites
tri-man 47 9,091 Posted February 8 Share Posted February 8 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? Quote Link to post Share on other sites
HaFo SaFo 110 Posted February 18 Share Posted February 18 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. Quote Link to post Share on other sites
Psychopav 1,172 Posted February 18 Share Posted February 18 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? Quote Link to post Share on other sites
acarey50 634 Posted February 18 Share Posted February 18 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? Quote Link to post Share on other sites
cubd8 299 Posted February 23 Share Posted February 23 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? Quote Link to post Share on other sites
SaintsInDome2006 47,490 Posted March 13 Share Posted March 13 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? Quote Link to post Share on other sites
Chemical X 5,823 Posted March 13 Share Posted March 13 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. Quote Link to post Share on other sites
acarey50 634 Posted March 15 Share Posted March 15 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. 1 Quote Link to post Share on other sites
acarey50 634 Posted March 15 Share Posted March 15 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 1 Quote Link to post Share on other sites
SaintsInDome2006 47,490 Posted March 15 Share Posted March 15 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. Quote Link to post Share on other sites
heckmanm 4,434 Posted March 15 Share Posted March 15 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. 1 Quote Link to post Share on other sites
acarey50 634 Posted March 15 Share Posted March 15 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) Quote Link to post Share on other sites
SaintsInDome2006 47,490 Posted March 15 Share Posted March 15 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. Quote Link to post Share on other sites
SaintsInDome2006 47,490 Posted March 15 Share Posted March 15 (edited) 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 March 15 by SaintsInDome2006 Quote Link to post Share on other sites
acarey50 634 Posted March 15 Share Posted March 15 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. 1 Quote Link to post Share on other sites
Chemical X 5,823 Posted March 16 Share Posted March 16 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 Quote Link to post Share on other sites
acarey50 634 Posted March 16 Share Posted March 16 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. Quote Link to post Share on other sites
Chemical X 5,823 Posted March 16 Share Posted March 16 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. Quote Link to post Share on other sites
Chemical X 5,823 Posted March 16 Share Posted March 16 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....? Quote Link to post Share on other sites
acarey50 634 Posted March 16 Share Posted March 16 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. Quote Link to post Share on other sites
Chemical X 5,823 Posted March 17 Share Posted March 17 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! Quote Link to post Share on other sites
acarey50 634 Posted March 17 Share Posted March 17 2 hours ago, Chemical X said: Very helpful says the boss....thx! No problem - glad I was able to help Quote Link to post Share on other sites
Chemical X 5,823 Posted March 27 Share Posted March 27 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 Quote Link to post Share on other sites
acarey50 634 Posted March 27 Share Posted March 27 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. Quote Link to post Share on other sites
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.