Jump to content
Fantasy Football - Footballguys Forums

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

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.

Link to post
Share on other sites
  • Replies 927
  • Created
  • Last Reply

Top Posters In This Topic

Top Posters In This Topic

Popular Posts

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

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

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

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?

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

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

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



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.

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.

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

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

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.

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

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

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.

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

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

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

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

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.

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?

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

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

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

Link to post
Share on other sites

A few off the cuff thoughts:

  • Your input sheet needs a date column
  • INDEX/MATCH the Classification, Cost Code, Hours using a concatenation of the employee's name and the date (from the header)
  • Depending on the depth of your employees list, its not wise to use names as a key (since there can be dups). I'd recommend assigning unique employee IDs.
  • Like 1
Link to post
Share on other sites

input sheet has a date I just did not include the top of the sheet I only posted the table I am trying to pull from.

There is that concatenate word again. I was thinking something like this

=IFERROR(INDEX('Daily Report Wed'!$E$18:$E$40,SMALL(IF($A$8='Daily Report Wed'!$C$18:$C$40,ROW($C$18:$C$40)-ROW($C$18)+1),1)),"") for classification

=IFERROR(INDEX('Daily Report Wed'!$B$18:$B$40,SMALL(IF($A$8='Daily Report Wed'!$C$18:$C$40,ROW($C$18:$C$40)-ROW($C$18)+1),1)),"") for cost code

=IFERROR(INDEX('Daily Report Wed'!$G$18:$G$40,SMALL(IF($A$8='Daily Report Wed'!$C$18:$C$40,ROW($C$18:$C$40)-ROW($C$18)+1),1)),"") for hours

Good point on the names I have to come up with a solution for that, but for now I am going to just search by trying to match a name from the payroll sheet to the daily report. Our company I can get employee numbers, but the payroll companies we use for rent a drunks may not be as easy.

edit for syntax error

Edited by Herc
Link to post
Share on other sites

"Daily Report Wed"

So you have daily input tabs - its not just one big tab? Maybe that's to keep it dumbed down for the guys inputting their time, but that's a clunky way to do it. It'll be a lot cleaner with a single input tab where they enter the date they worked.

Link to post
Share on other sites

"Daily Report Wed"

So you have daily input tabs - its not just one big tab? Maybe that's to keep it dumbed down for the guys inputting their time, but that's a clunky way to do it. It'll be a lot cleaner with a single input tab where they enter the date they worked.

There is more to the sheet than just payroll its a daily report of work(completed and billable), equipment on site, subcontractors, inspections and employees.

My thoughts are if they can fill out one report without screwing it up I can automate the rest, but it is definitely proving a challenge.

its a weekly workbook and its a blank template that can be used on any of our construction projects.

Edited by Herc
Link to post
Share on other sites

Join the conversation

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

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

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

×   Your previous content has been restored.   Clear editor

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

  • Recently Browsing   0 members

    No registered users viewing this page.


×
×
  • Create New...