Fantasy Football - Footballguys Forums
Ned

***Official*** Excel Help Corner

Recommended Posts

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).

  • Like 1

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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, bic

Whse 2: bic pen

Whse 3: pen model #abcd

The 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

Share this post


Link to post
Share on other sites

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, bic

Whse 2: bic pen

Whse 3: pen model #abcd

The 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

What are you looking for? Fuzzy match methods?

Share this post


Link to post
Share on other sites

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 by Long Ball Larry

Share this post


Link to post
Share on other sites

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?

Share this post


Link to post
Share on other sites

I need to do this:



Repeating formulas


When 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



Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

I need to do this:

Repeating formulas

When 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.

Share this post


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

1.66 IN OD


16 IN OD


6.625 IN OD

.84 IN OD

3.5 IN OD

Share this post


Link to post
Share on other sites
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 OD
1.66 IN OD
16 IN OD
6.625 IN OD
.84 IN OD
3.5 IN OD

If 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 by Walking Boot
  • Like 1

Share this post


Link to post
Share on other sites
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 OD
1.66 IN OD
16 IN OD
6.625 IN OD
.84 IN OD
3.5 IN OD

If 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)

Share this post


Link to post
Share on other sites
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 OD
1.66 IN OD
16 IN OD
6.625 IN OD
.84 IN OD
3.5 IN OD

If 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 by Walking Boot
  • Like 1

Share this post


Link to post
Share on other sites

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/hours

Car/1/10

truck/1/10

car/2/10

boat/1/10

plane/1/10

How 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.

Share this post


Link to post
Share on other sites

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.

  • Like 1

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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&C2

In 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.

  • Like 1

Share this post


Link to post
Share on other sites

I need to do this:

Repeating formulas

When 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 $

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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/hours

Car/1/10

truck/1/10

car/2/10

boat/1/10

plane/1/10

How 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.png

To 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 occurrence

hope 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.

Share this post


Link to post
Share on other sites

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.png

http://puu.sh/jJliB/9d918c0c4a.png

Share this post


Link to post
Share on other sites

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)),"")}

Share this post


Link to post
Share on other sites

Are you adding the brackets yourself? Enter it like a normal formula without the brackets and hold ctrl and shift when you press enter

Share this post


Link to post
Share on other sites

I added the brackets in my example above, but on the sheet I used Cont+Shift+Enter

I 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 by Herc

Share this post


Link to post
Share on other sites

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.

  • Like 1

Share this post


Link to post
Share on other sites

I added the brackets in my example above, but on the sheet I used Cont+Shift+Enter

I 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.

  • Like 2

Share this post


Link to post
Share on other sites

I added the brackets in my example above, but on the sheet I used Cont+Shift+Enter

I 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.

  • Like 1

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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.

  • Like 1

Share this post


Link to post
Share on other sites

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?

Share this post


Link to post
Share on other sites

Will you ever have instances where on sheet 1 you have ABC123 DEF123 and then on sheet 2 you have 123 listed twice?

Share this post


Link to post
Share on other sites

Follow up question, is it always in the same format? IE 3 letters then 3 numbers? and then only 3 numbers on sheet 2?

Share this post


Link to post
Share on other sites

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?

:lol: 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.

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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?

Share this post


Link to post
Share on other sites

I got it working --- thanks for the reminder on the right/left, that ultimately got it done.

  • Like 1

Share this post


Link to post
Share on other sites

http://s271.photobucket.com/user/knarfii/media/Capture_zps8hwf5kei.jpg.html

The 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?.

Share this post


Link to post
Share on other sites

Not enough details to go on. Can you screen shot what your final product looks like today (the one you're manually doing)?

  • Like 1

Share this post


Link to post
Share on other sites

Herc,

So the information you're pulling from looks like the first screenshot and you need to fill out the proposed screenshot?

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

Join the conversation

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

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

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

×   Your previous content has been restored.   Clear editor

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


  • Recently Browsing   0 members

    No registered users viewing this page.