MattFancy 2,197 Posted June 30, 2015 Share Posted June 30, 2015 Trying to combine 6 bars in a bar chart to make one bar using a secondary axis. HELP! Quote Link to post Share on other sites
Ned 10,585 Posted June 30, 2015 Author Share Posted June 30, 2015 Trying to combine 6 bars in a bar chart to make one bar using a secondary axis. HELP!Make a total column in your data and include it in your series data for the chart.Right click the new total bar - Format Data Series...change it to a secondary axis (how this is displayed is different in 2010, IIRC). 1 Quote Link to post Share on other sites
MattFancy 2,197 Posted June 30, 2015 Share Posted June 30, 2015 Trying to combine 6 bars in a bar chart to make one bar using a secondary axis. HELP!Make a total column in your data and include it in your series data for the chart.Right click the new total bar - Format Data Series...change it to a secondary axis (how this is displayed is different in 2010, IIRC).I figured it out. The person that did it before me didn't leave instructions, but I ended up just needed to overlap the bars and then reordering the data sets until it looked like how I wanted. Quote Link to post Share on other sites
ragincajun 904 Posted June 30, 2015 Share Posted June 30, 2015 Let's talk Fuzzy Lookup.I have several warehouses with say 100k items. The warehouses may have the same item but when it was originally uploaded into SAP the description might have been different.Example:Whse 1: pen, red, bicWhse 2: bic penWhse 3: pen model #abcdThe goal is to identify all of the similar items across the different warehouses. Very watered down but a fuzzy lookup will lookup similar items based on percentage similarity. Let's say I have 100k items amongst 10 warehouses. I can perform a fuzzy lookup with a 90% accuracy and then with conditional formatting highlight the same words. At this point it would become more of a manual process to review and find the like items.thoughts?Any thoughts on this? I am still stumped.Also http://chandoo.org Quote Link to post Share on other sites
Brony 7,618 Posted June 30, 2015 Share Posted June 30, 2015 Let's talk Fuzzy Lookup.I have several warehouses with say 100k items. The warehouses may have the same item but when it was originally uploaded into SAP the description might have been different.Example:Whse 1: pen, red, bicWhse 2: bic penWhse 3: pen model #abcdThe goal is to identify all of the similar items across the different warehouses. Very watered down but a fuzzy lookup will lookup similar items based on percentage similarity. Let's say I have 100k items amongst 10 warehouses. I can perform a fuzzy lookup with a 90% accuracy and then with conditional formatting highlight the same words. At this point it would become more of a manual process to review and find the like items.thoughts?Any thoughts on this? I am still stumped.Also http://chandoo.orgWhat are you looking for? Fuzzy match methods? Quote Link to post Share on other sites
ragincajun 904 Posted June 30, 2015 Share Posted June 30, 2015 Essentially any other ideas other than fuzzy or another approach to fuzzy? Quote Link to post Share on other sites
ragincajun 904 Posted July 13, 2015 Share Posted July 13, 2015 Okay so maybe I am barking up the wrong tree. Maybe I need to utilize Access? Quote Link to post Share on other sites
Long Ball Larry 14,295 Posted August 2, 2015 Share Posted August 2, 2015 (edited) Anyone up in here know R too?If so, any idea why this isn't working?h = nrow(Electric)Electric$Month <- 0 for(i in 1:12){ k=i for(j in 1:h){ if (j==k){ Electric$Month[j] <- i k=k+12 }}} ETA: after messing around with it I think the problem is in the 5th row -- where I'm trying to assign values for the column Electric$Month. ETA2: Fixed.I have no answer (though I think maybe you figured it out), but I have an r question.I am creating code in Excel, because I have many lines that are similar, just with different numbers, so I am using formulas in Excel and concatenating.However, when I copy and paste the code into r, it seems like it does not read the quotation marks correctly. They look different on the screen (slanted, instead of vertical) and it is throwing me an error.Any insight on how to get the quotation marks to paste correctly?Edit; I figured this out. You have to adjust the smartquotes option in the original source program (Excel, well, actually LibreOffice in this case) so that it leaves the quotes in ASCII. Edited August 3, 2015 by Long Ball Larry Quote Link to post Share on other sites
slackjawedyokel 147 Posted August 3, 2015 Share Posted August 3, 2015 I'm fairly competent with excel but I have a major wish list item... Perhaps it exists.I'd like to automatically have columns or rows hide based on the contents of a cell. For instance: on Sheet1, cell B2 says "Yes", then Sheet 2, row 20 becomes hidden. Random example for the sake of simplicity but I have reports that I always have to manually adjust that I would love to automate. Does such a feature exist? Quote Link to post Share on other sites
Mr. Pickles 4,621 Posted August 3, 2015 Share Posted August 3, 2015 With VBA, anything is possible. 1 Quote Link to post Share on other sites
Mr. Landry 74 Posted August 3, 2015 Share Posted August 3, 2015 I need to do this: Repeating formulasWhen you're using a formula against a data population that resides in multiple rows, you can make Excel do the work for you. First, enter the formula you want to use next to your data. While still selected on that cell with the formula, take your mouse and double click the tiny box that's in the lower right hand corner of the cell. Excel will autofill that formula all the way down until it finds a gap in your data. But with columns instead of rows. I've got a formula in E2 and I need to copy it all the way down to E60. Copy and paste doesn't seem to work (it copies the value in E2, not the formula). TIA Quote Link to post Share on other sites
Ned 10,585 Posted August 3, 2015 Author Share Posted August 3, 2015 I'm fairly competent with excel but I have a major wish list item... Perhaps it exists.I'd like to automatically have columns or rows hide based on the contents of a cell. For instance: on Sheet1, cell B2 says "Yes", then Sheet 2, row 20 becomes hidden. Random example for the sake of simplicity but I have reports that I always have to manually adjust that I would love to automate. Does such a feature exist?Pickles hinted at it - you have to macro it. You can invoke a macro when a cell value changes. You can code it to hide the row when B2 flips to Yes. Quote Link to post Share on other sites
Ned 10,585 Posted August 3, 2015 Author Share Posted August 3, 2015 I need to do this: Repeating formulasWhen you're using a formula against a data population that resides in multiple rows, you can make Excel do the work for you. First, enter the formula you want to use next to your data. While still selected on that cell with the formula, take your mouse and double click the tiny box that's in the lower right hand corner of the cell. Excel will autofill that formula all the way down until it finds a gap in your data. But with columns instead of rows. I've got a formula in E2 and I need to copy it all the way down to E60. Copy and paste doesn't seem to work (it copies the value in E2, not the formula). TIA Is it a protected spreadsheet? Otherwise, I have no idea why its doing that. Quote Link to post Share on other sites
Ignoramus 5,794 Posted August 5, 2015 Share Posted August 5, 2015 How would I pull out just the number from a column containing these values? I don't want to use a find & replace... would prefer a formula, if possible. 8.625 IN OD1.66 IN OD16 IN OD6.625 IN OD.84 IN OD3.5 IN OD Quote Link to post Share on other sites
Walking Boot 8,296 Posted August 5, 2015 Share Posted August 5, 2015 (edited) How would I pull out just the number from a column containing these values? I don't want to use a find & replace... would prefer a formula, if possible. 8.625 IN OD1.66 IN OD16 IN OD6.625 IN OD.84 IN OD3.5 IN ODIf you can get ".84" to display as "0.84", google says this will work:=LOOKUP(99^99,--("0"&MID(A1,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A1&"0123456789")),ROW($1:$10000)))) Edited August 5, 2015 by Walking Boot 1 Quote Link to post Share on other sites
BroadwayG 963 Posted August 5, 2015 Share Posted August 5, 2015 =value(left(A1,find(" ", A1,1)-1)) 2 Quote Link to post Share on other sites
Ignoramus 5,794 Posted August 5, 2015 Share Posted August 5, 2015 How would I pull out just the number from a column containing these values? I don't want to use a find & replace... would prefer a formula, if possible. 8.625 IN OD1.66 IN OD16 IN OD6.625 IN OD.84 IN OD3.5 IN ODIf you can get ".84" to display as "0.84", google says this will work:=LOOKUP(99^99,--("0"&MID(A1,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A1&"0123456789")),ROW($1:$10000))))Thanks, but that's not an option.I figured it out: =LEFT(A1,LEN(A1)-6) Quote Link to post Share on other sites
Walking Boot 8,296 Posted August 5, 2015 Share Posted August 5, 2015 (edited) How would I pull out just the number from a column containing these values? I don't want to use a find & replace... would prefer a formula, if possible. 8.625 IN OD1.66 IN OD16 IN OD6.625 IN OD.84 IN OD3.5 IN ODIf you can get ".84" to display as "0.84", google says this will work:=LOOKUP(99^99,--("0"&MID(A1,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A1&"0123456789")),ROW($1:$10000))))Thanks, but that's not an option.I figured it out: =LEFT(A1,LEN(A1)-6)Yeah, just figured that out. =LEFT(A1,(FIND(" ",A1)-1)) if it's always leftmost and has a space after the number.Broadway was smart to realize VALUE is a helpful add. Edited August 5, 2015 by Walking Boot 1 Quote Link to post Share on other sites
Ignoramus 5,794 Posted August 5, 2015 Share Posted August 5, 2015 =value(left(A1,find(" ", A1,1)-1))This is better than mine. Thanks! Quote Link to post Share on other sites
Herc 10 Posted August 20, 2015 Share Posted August 20, 2015 Looking for some help again.Many months ago I needed to create a spreadsheet for work and have it grab values. I used vlookup to accomplish this task. Now I need to be able to find if my vlookup has a second, third or fourth entry and tabulate it on another sheet.Example table:Equipment/Cost code/hoursCar/1/10truck/1/10car/2/10boat/1/10plane/1/10How can I use vlookup to find the second car in the list above. I would then need to pull that cost code? This is how I have been finding the first occurrence if there is one=IF(ISNA(VLOOKUP("*"&"F"&"*"&"150"&"*",'Daily Report Wed'!$A$53:$G$63,6,FALSE)),0,VLOOKUP("*"&"F"&"*"&"150"&"*",'Daily Report Wed'!$A$53:$G$63,6,FALSE))Any help is greatly appreciated. Quote Link to post Share on other sites
Brony 7,618 Posted August 20, 2015 Share Posted August 20, 2015 If the cost codes are unique and in nice order like above, can you concatenate the Equipment and Cost Code columns e.g cell D2 is =A2&B2? Then do VLOOKUP on concatenated column. Otherwise, you can do a RANKIF type formula on the Equipment and Cost code columns to get unique values for the cost code column. 1 Quote Link to post Share on other sites
Herc 10 Posted August 21, 2015 Share Posted August 21, 2015 If the cost codes are unique and in nice order like above, can you concatenate the Equipment and Cost Code columns e.g cell D2 is =A2&B2? Then do VLOOKUP on concatenated column. Otherwise, you can do a RANKIF type formula on the Equipment and Cost code columns to get unique values for the cost code column.no the cost codes are numbers like 101-1 or 460-6-18 and they are not unique. Cars truck boats and planes may all have the same cost code for the day. I was just hoping that there was an easier way than to concatenate. I have read about how to do it I guess I'll start messing with it. Quote Link to post Share on other sites
Ned 10,585 Posted August 21, 2015 Author Share Posted August 21, 2015 If the cost codes are unique and in nice order like above, can you concatenate the Equipment and Cost Code columns e.g cell D2 is =A2&B2? Then do VLOOKUP on concatenated column. Otherwise, you can do a RANKIF type formula on the Equipment and Cost code columns to get unique values for the cost code column.no the cost codes are numbers like 101-1 or 460-6-18 and they are not unique. Cars truck boats and planes may all have the same cost code for the day. I was just hoping that there was an easier way than to concatenate. I have read about how to do it I guess I'll start messing with it.I know you don't want to concatenate, but there's no way around it IMO. You can concatenate the equipment/cost code in one column and then concatenate the new equip/code column with a COUNTIF in a separate column. Then do your VLOOKUP...In column E -> =B2&C2In column A (since you're using VLOOKUP) -> =E2&COUNTIF($E$2:E2,E2)Fill those down, and then VLOOKUP -> =VLOOKUP("*"&"CAR"&"*"&2,A2:E10,4,FALSE) where the &2 is the instance you want to find. 1 Quote Link to post Share on other sites
Herc 10 Posted August 21, 2015 Share Posted August 21, 2015 Thanks Ned I do greatly appreciate the assist!! Quote Link to post Share on other sites
Gawain 2,250 Posted August 21, 2015 Share Posted August 21, 2015 I need to do this: Repeating formulasWhen you're using a formula against a data population that resides in multiple rows, you can make Excel do the work for you. First, enter the formula you want to use next to your data. While still selected on that cell with the formula, take your mouse and double click the tiny box that's in the lower right hand corner of the cell. Excel will autofill that formula all the way down until it finds a gap in your data. But with columns instead of rows. I've got a formula in E2 and I need to copy it all the way down to E60. Copy and paste doesn't seem to work (it copies the value in E2, not the formula). TIA Is it a protected spreadsheet? Otherwise, I have no idea why its doing that.$ (dollar signs) in the formula you're trying to copy? That locks the value to the cell given with the $ Quote Link to post Share on other sites
Village Idiot 173 Posted August 21, 2015 Share Posted August 21, 2015 As an aside, what is the difference between SEARCH and FIND?FIND is case sensitive, SEARCH is not.FIND is not case sensitive, at least in Excel 2010. Quote Link to post Share on other sites
Spin 1,014 Posted August 21, 2015 Share Posted August 21, 2015 Looking for some help again.Many months ago I needed to create a spreadsheet for work and have it grab values. I used vlookup to accomplish this task. Now I need to be able to find if my vlookup has a second, third or fourth entry and tabulate it on another sheet.Example table:Equipment/Cost code/hoursCar/1/10truck/1/10car/2/10boat/1/10plane/1/10How can I use vlookup to find the second car in the list above. I would then need to pull that cost code? This is how I have been finding the first occurrence if there is one=IF(ISNA(VLOOKUP("*"&"F"&"*"&"150"&"*",'Daily Report Wed'!$A$53:$G$63,6,FALSE)),0,VLOOKUP("*"&"F"&"*"&"150"&"*",'Daily Report Wed'!$A$53:$G$63,6,FALSE))Any help is greatly appreciated.Here's a way to do it without the concatenating:{=INDEX($B$2:$B$7,SMALL(IF($G$1=$A$2:$A$7,ROW($A$2:$A$7)-ROW($A$2)+1),1))}Make sure you press Ctrl + Shift + Enter when entering it, to add the curly brackets, as it's an array formula.Here's what my test sheet looks like:http://puu.sh/jJkMf/3c348011fa.pngTo get the 2nd, 3rd, nth value in the list, you'll have to change that last value to be which value you're looking for:{=INDEX($B$2:$B$7,SMALL(IF($G$1=$A$2:$A$7,ROW($A$2:$A$7)-ROW($A$2)+1),1))} -> Returns the 1st occurrence{=INDEX($B$2:$B$7,SMALL(IF($G$1=$A$2:$A$7,ROW($A$2:$A$7)-ROW($A$2)+1),2))} -> returns the 2nd occurrence...{=INDEX($B$2:$B$7,SMALL(IF($G$1=$A$2:$A$7,ROW($A$2:$A$7)-ROW($A$2)+1),n))} -> returns the nth occurrencehope that helps.Oh, and for future purposes, IFERROR is a quick and easy way to handle vlookup errors. For example:=IFERROR(VLOOKUP("*"&"F"&"*"&"150"&"*",'Daily Report Wed'!$A$53:$G$63,6,FALSE),0)Instead of your if statement above, returns the same results, but is much easier to modify/change as you only have to change one vlookup instead of 2 when making changes. Quote Link to post Share on other sites
Spin 1,014 Posted August 21, 2015 Share Posted August 21, 2015 As an aside, what is the difference between SEARCH and FIND?FIND is case sensitive, SEARCH is not.FIND is not case sensitive, at least in Excel 2010./shrug:It seems to be in my 2010... Same formula entered into E21, one with FIND, one with SEARCH, checking against G21. G21 has lowercase "test" entered. I used "TEST" to try to find "test". Search found it, Find did not. http://puu.sh/jJlgB/ef7b047dce.pnghttp://puu.sh/jJliB/9d918c0c4a.png Quote Link to post Share on other sites
ragincajun 904 Posted August 22, 2015 Share Posted August 22, 2015 With VBA, anything is possible.See my issue above....... Quote Link to post Share on other sites
Herc 10 Posted August 24, 2015 Share Posted August 24, 2015 I have a feeling I have a syntax error. I am not grabbing any information. Table is A53 to G63, A is the equipment and column B is the cost code.{=IFERROR(INDEX('Daily Report Wed'!$B$53:$B$63,SMALL(IF("*"&"F"&"*"&"150"&"*"=$A$53:$A$63,ROW($A$53:$A$63)-ROW($A$53)+1),1)),"")} Quote Link to post Share on other sites
Spin 1,014 Posted August 24, 2015 Share Posted August 24, 2015 Are you adding the brackets yourself? Enter it like a normal formula without the brackets and hold ctrl and shift when you press enter Quote Link to post Share on other sites
Herc 10 Posted August 24, 2015 Share Posted August 24, 2015 (edited) I added the brackets in my example above, but on the sheet I used Cont+Shift+EnterI figured it out I was calling the wrong sheet after the if statement. But I cant use "*"&"F"&"*"&"150"&"*" I have to use a direct cell reference like $a$183. Edited August 24, 2015 by Herc Quote Link to post Share on other sites
Spin 1,014 Posted August 24, 2015 Share Posted August 24, 2015 Ah, sorry I was on my phone, do you have to use the wild card characters? I don't think Index handles wildcards like Vlookup does. 1 Quote Link to post Share on other sites
Ned 10,585 Posted August 24, 2015 Author Share Posted August 24, 2015 I added the brackets in my example above, but on the sheet I used Cont+Shift+EnterI figured it out I was calling the wrong sheet after the if statement. But I cant use "*"&"F"&"*"&"150"&"*" I have to use a direct cell reference like $a$183.Instead of hard coding the model into the code, why not just put it in a separate cell so you can reference it? Makes it a lot cleaner if you ever have to change things around, too. 2 Quote Link to post Share on other sites
Spin 1,014 Posted August 24, 2015 Share Posted August 24, 2015 I added the brackets in my example above, but on the sheet I used Cont+Shift+EnterI figured it out I was calling the wrong sheet after the if statement. But I cant use "*"&"F"&"*"&"150"&"*" I have to use a direct cell reference like $a$183.Instead of hard coding the model into the code, why not just put it in a separate cell so you can reference it? Makes it a lot cleaner if you ever have to change things around, too.Yah, this is the way to do it. 1 Quote Link to post Share on other sites
Herc 10 Posted August 24, 2015 Share Posted August 24, 2015 I had the wild cards because some of our employees are not known for their accuracy so I made the sheet to find entries based on common sense things. like ford f-150 pickup, some of the guys call it a f150 truck some an f-150 pickup and some f 150 truck. I think they will just have to copy and paste the info into the cell. Problem is I don't have faith they can actually do that.Thanks for the help guys have it working great. Quote Link to post Share on other sites
Ned 10,585 Posted August 24, 2015 Author Share Posted August 24, 2015 If your models never have certain characters like spaces, dashes, etc... you can use the SUBSTITUTE formula to clean up data entry errors.So instead of indexing based on a single cell (say A63), you can normalize the data and then index on that.Example: SUBSTITUTE(A63,"-",""). That instance will remove all dashes from the cell. You can nest them to do multiple substitutions. 1 Quote Link to post Share on other sites
Smarge 21 Posted August 24, 2015 Share Posted August 24, 2015 I could use some help ----I have two sheets with data. On one sheet there's a column with data containing letters and numbers (i.e. ABC123, EFG456, etc.). On the second sheet, there's a similar column intended to represent the same data, but it only includes the numbers, not the letters. I need the second sheet to match the first, as there's a third data source that has the letter/number column and I need to tie the three sources together. So I need to do some sort of lookup between the two sheets, with the first sheet's column of data "containing" the numbers from the second sheet. I've played around with =if(isnumber(search...... type formulas, but haven't had any luck returning an expected outcome. Help? Quote Link to post Share on other sites
Spin 1,014 Posted August 24, 2015 Share Posted August 24, 2015 Will you ever have instances where on sheet 1 you have ABC123 DEF123 and then on sheet 2 you have 123 listed twice? Quote Link to post Share on other sites
Spin 1,014 Posted August 24, 2015 Share Posted August 24, 2015 Follow up question, is it always in the same format? IE 3 letters then 3 numbers? and then only 3 numbers on sheet 2? Quote Link to post Share on other sites
Ned 10,585 Posted August 24, 2015 Author Share Posted August 24, 2015 Follow up question, is it always in the same format? IE 3 letters then 3 numbers? and then only 3 numbers on sheet 2?Will you ever have instances where on sheet 1 you have ABC123 DEF123 and then on sheet 2 you have 123 listed twice? thinking the same thing.If it's always the same xxxnnn, you can use left/right formulas to parse out the combos. Assuming each parsed xxx and nnn are always unique. Quote Link to post Share on other sites
Smarge 21 Posted August 24, 2015 Share Posted August 24, 2015 The bulk of the data is 4 letters and 6 numbers, and there are only unique instances on both sheets. There are some other cats and dogs, but if I can handle the 4 letters, 6 numbers that covers the bulk. Quote Link to post Share on other sites
Smarge 21 Posted August 24, 2015 Share Posted August 24, 2015 Ok, I used a =right(1st tab, 5) formula, to get sheet 1 to "equal" sheet 2. Then did a vlookup to match the 2nd sheet to the 1st sheet. While the numbers are the same, "12345", I'm getting an #N/A returned. Perhaps the format is inconsistent? Quote Link to post Share on other sites
Smarge 21 Posted August 24, 2015 Share Posted August 24, 2015 I got it working --- thanks for the reminder on the right/left, that ultimately got it done. 1 Quote Link to post Share on other sites
Herc 10 Posted September 2, 2015 Share Posted September 2, 2015 http://s271.photobucket.com/user/knarfii/media/Capture_zps8hwf5kei.jpg.htmlThe photo is a capture of a table on my spreadsheet. I am not sure it can be done but I am going to ask anyways. We use our own employees as well as day laborers from multiple sources. Each employee may be listed several times as they change jobs on the site and we don't always get the same employees everyday.Thanks to the guys here I am much better with arrays and vlookup, but I don't really know how to approach this problem. I pull this information from this table and put in on a payroll sheet. As of now I have only been tracking our own employees so pulling the employee and the cost code and hours on a daily basis was pretty simple once I knew the syntax to use, but now I need to pull the information and put it on different tables for each company we are using. Basically I need to match the company name first and put it in the correct table, then I need to match the employees name and pull in the cost codes and hours for each cost code and add that to the table. Obviously I am not an excel guru. is it even possible to match multiple cells or to sort with multiple cells.Does that make any sense at all? I feel like I have too many variables to do anything with company name, employee name, cost code and hours all may change and they may change several times/day. Pivot tables are not an option until its been sorted to the proper company any ideas?. Quote Link to post Share on other sites
Ned 10,585 Posted September 2, 2015 Author Share Posted September 2, 2015 Not enough details to go on. Can you screen shot what your final product looks like today (the one you're manually doing)? 1 Quote Link to post Share on other sites
Herc 10 Posted September 2, 2015 Share Posted September 2, 2015 (edited) currently is automated but not splitting out enough information for corporate.http://s271.photobucket.com/user/knarfii/media/current_zpsskyqtopn.jpg.htmlWhat I would like to do for each companyhttp://s271.photobucket.com/user/knarfii/media/proposed_zps3d23zmhh.jpg.html Edited September 2, 2015 by Herc Quote Link to post Share on other sites
Spin 1,014 Posted September 2, 2015 Share Posted September 2, 2015 Herc,So the information you're pulling from looks like the first screenshot and you need to fill out the proposed screenshot? Quote Link to post Share on other sites
Herc 10 Posted September 2, 2015 Share Posted September 2, 2015 correct Quote Link to post Share on other sites
Herc 10 Posted September 2, 2015 Share Posted September 2, 2015 Would using arrays and matching the employee name work? Obviously the employees name would need entered on the payroll sheet but then I think I could pull all the information in. Just trying to find an easy solution for construction workers who can even break locked sheets 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.