***Official*** Excel Help Corner (1 Viewer)

[MattFancy]

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.

 

[ragincajun]

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

 

[Brony]

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?

 

[ragincajun]

Essentially any other ideas other than fuzzy or another approach to fuzzy?

 

[ragincajun]

Okay so maybe I am barking up the wrong tree. Maybe I need to utilize Access?

 

[Long Ball Larry]

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.

 

[slackjawedyokel]

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?

 

[Mr. Pickles]

With VBA, anything is possible.

 

[Mr. Landry]

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

 

[Ned]

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.

 

[Ned]

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.

 

[Ignoramus]

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

 

[BroadwayG]

=value(left(A1,find(" ", A1,1)-1))

 

[Ignoramus]

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)

 

[Ignoramus]

=value(left(A1,find(" ", A1,1)-1))

This is better than mine. Thanks!

 

[Herc]

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.

 

[Brony]

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.

 

[Herc]

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.

 

[Ned]

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.

 

[Herc]

Thanks Ned I do greatly appreciate the assist!!

 

[Gawain]

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 $

 

[Village Idiot]

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.

 

[Spin]

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.

 

[Spin]

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

 

[ragincajun]

With VBA, anything is possible.

See my issue above.......

 

[Herc]

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

 

[Spin]

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

 

[Herc]

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.

 

[Spin]

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.

 

[Ned]

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.

 

[Spin]

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.

 

[Herc]

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.

 

[Ned]

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.

 

[Smarge]

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?

 

[Spin]

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

 

[Spin]

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

 

[Ned]

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.

 

[Smarge]

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.

 

[Smarge]

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?

 

[Smarge]

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

 

[Herc]

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

 

[Ned]

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

 

[Herc]

currently is automated but not splitting out enough information for corporate.

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

What I would like to do for each company

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

 

[Spin]

Herc,

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

 

[Herc]

correct

 

[Herc]

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

 

[Ned]

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.

 

[Herc]

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

 

[Ned]

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

 

[Herc]

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