Jump to content
workinglikeadog

Microsoft Excel help

Recommended Posts

i know there are some real data wizards on the site, so i thought i'd put this out there....

 

i want to merge the text data in four columns into one, can this be done ?

 

for example:

 

column a = big / column b = brown / column c = bear

 

and i'd like to get that to : big brown bear , in a single column............

Share this post


Link to post
Share on other sites

concatenate is the formula you require, hit the fx button at left of the bar that the data appears in while on column d in the above example

 

Don't forget to include spaces if you require spaces between the words

Share this post


Link to post
Share on other sites

Edit: cottoneyes beat me to the punch

 

concatenate is your friend.

http://office.microsoft.com/en-au/excel-help/concatenate-function-HP010062562.aspx

For example, if your worksheet contains a person's first name in cell A1 and the person's last name in cell B1, you can combine the two values in another cell by using the following formula:

=CONCATENATE(A1," ",B1)

The second argument in this example (" ") is a space character. You must specify any spaces or punctuation that you want to appear in the results as an argument that is enclosed in quotation marks.

Edited by Tyno

Share this post


Link to post
Share on other sites

You can also use &. Eg

=a1&b1&c&1&d1.

 

But I find it clunky for too many cells. Concatenate will work on a range. However, if you don't have spaces in your data and you have to add them, you can preferably use Concatenate twice. Firstly, create new data fields using concatenate(a1," ") etc, then Concatenate those new fields.

 

Or, messier, use "&" thus,

 

=a1&" "&b1&" "&c1&" "......etc

 

"&" is fantastic for creating index fields to use later in complicated lookups. Like if you want to lookup by both date and some other attribute, just create a new variable.

Share this post


Link to post
Share on other sites

If you want to add a carriage return between the merged cols you can add CHAR(10) to tortoise's solution above.

Ooh, I love it when I find a new excel trick!

  • Like 1

Share this post


Link to post
Share on other sites

If you want to add a carriage return between the merged cols you can add CHAR(10) to tortoise's solution above.

What's a carriage return, Grandad? :smartarse:

Share this post


Link to post
Share on other sites

Ah very good. I have a question too. I am looking for a formula to count shaded cells. Specifically I have a row where each cell is half an hour. I want to be able to shade individual cells in green manually but have them added into a separate cell to give a total as a number. For example if I shade 5 in the row I want it to show me the number 2.5 in a stand alone cell. I googled and found a formula but it does not auto update with changes.

Share this post


Link to post
Share on other sites

My advice is don't use shading for real calculation stuff. It's good for troubleshooting and tarting up stuff for an end user.

 

Add another column where you flag the rows you are interested in using a formula that returns 1 for the ones you want to sum, 0 for the rest. Then do a sumproduct of your data col and the flag col. Simple. Will always refresh.

 

That assumes that your shading has some numerical basis, ie < 3.5 or > average of the range, etc. If your green shading is manual, then also just enter a 1 in your flag col at the same time, but that's pretty crude and not sustainable in a big data set.

Edited by tortoise

Share this post


Link to post
Share on other sites

Ah very good. I have a question too. I am looking for a formula to count shaded cells. Specifically I have a row where each cell is half an hour. I want to be able to shade individual cells in green manually but have them added into a separate cell to give a total as a number. For example if I shade 5 in the row I want it to show me the number 2.5 in a stand alone cell. I googled and found a formula but it does not auto update with changes.

 

As I read that... a function that returns the number of shaded cells in a range?

 

Perhaps this can get you started in the right direction.

 

Modify it to suit your specific needs.

 

1. Create a function macro

-------------------------------

 

Function ShadedCells(a As Range) As Integer

ShadedCells = 0

For Each c In a

If c.Interior.ColorIndex <> xlColorIndexNone Then

ShadedCells = ShadedCells + 1

End If

Next c

End Function

 

 

2. Use the function in the cell where you want the answer

--------------------------------------------------------------------

 

=ShadedCells(cellref:cellref)

Share this post


Link to post
Share on other sites

Interesting option yogib.

 

I've never been a macro lover so it's never the first option that springs to mind. In the workplace I've had one too many young turks who would write macros rather than using formulae, and then use them to stamp a whole lot of values into a data block. All very nice until they get it wrong and there is no-one capable of auditing and checking their work because the macros are so complex. This means either the mistakes are propagated if they are subtle or someone picks up that the results just don't look right. In a third party QA'd consulting environment, that is unsustainable. All work has to be checkable.

 

This means that sometimes my spreadsheets get big, but you can always backtrack where something came from.

Share this post


Link to post
Share on other sites

I've never been a macro lover so it's never the first option that springs to mind. In the workplace I've had one too many young turks who would write macros rather than using formulae, and then use them to stamp a whole lot of values into a data block. All very nice until they get it wrong and there is no-one capable of auditing and checking their work because the macros are so complex.

 

Yes, exactly as you say, there are risks involved with using macros, and standard programming practices should be applied to anything but the most trivial of functions. E.g. 'Comments should at least be used to document the author's intentions and hopefully be used to isolate any error.

 

Macros are a very powerful tool, as they essentially give you access to the full object model of the application, but I've seen some god-awful macros in my time, typically written by someone with just enough knowledge to get themselves into serious trouble and not enough experience to know when they are doing just that.

 

I worked as a programmer for 20+ years (I still dabble on the side) and so am perhaps more comfortable with the idea of macros. Nevertheless, I believe they should only be used when the built-in functions (essentially just macros written by Microsoft) can't do the job, and should be kept as simple as possible, so that they do exactly what they say on the tin. You can then manipulate or use the returned value in the excel function-bar, with standard functions and/or other custom functions, so that you can see the basic logic from the spreadsheet, and debug problems easier. i.e. Avoid writing monolithic "DoBlackMagic()" functions that require reading the macro to understand what the hell it does.

 

e.g. In this case, the requirement seems to be count the number of shaded cells in a range and divide by 2? Rather than creating a function that returns half the number of counted cells, I would rather create a function to simply count the number of shaded cells, and then divide that by two in the excel formula-bar [ =ShadedCells(range)/2 ] so that the logic for the cell value is viewable from the spreadsheet.

Share this post


Link to post
Share on other sites

 

As I read that... a function that returns the number of shaded cells in a range?

 

Perhaps this can get you started in the right direction.

 

Modify it to suit your specific needs.

 

1. Create a function macro

-------------------------------

 

Function ShadedCells(a As Range) As Integer

ShadedCells = 0

For Each c In a

If c.Interior.ColorIndex <> xlColorIndexNone Then

ShadedCells = ShadedCells + 1

End If

Next c

End Function

 

 

2. Use the function in the cell where you want the answer

--------------------------------------------------------------------

 

=ShadedCells(cellref:cellref)

thanks. That is a fair bit beyond my knowledge. Let's say the cells are a1-h1, exactly what do I gave to insert into the cell that I want the answer to appear?

Share this post


Link to post
Share on other sites

This is the other reason why I don't use macros, because for a mid-range excel user, there are too many leaps into the unkown.

 

Plaz, try the formula option if you've never done macros before. If you don't know how to write the formula, post back a word description of your criteria for shading the cells green and someone will be able to write it for you.

Share this post


Link to post
Share on other sites

Let's say the cells are a1-h1, exactly what do I gave to insert into the cell that I want the answer to appear?

 

That simple VBA function is expecting a cell range, so it would be =ShadedCells(A1:H1) or if you wanted to just check the entire row1... =ShadedCells(1:1)

 

However, keep in mind that Excel doesn't consider fomatting to be changing the value of a cell, and so doesn't trigger the event to auto-update cells... you will still need to 'touch' the cell to update it.

 

Of course, there are ways around this (off the top of my head... perhaps adding Application.Volatile to the function and adding a Calulate event to the sheet area) but it sounds like that may be too advanced for you.

 

In which case, Tortoise probably has the KISS solution.

Share this post


Link to post
Share on other sites

Yes, way too advanced. The specific thing in need is to count cells that I manually shade. Let's just say I have 10 cells in total a1-j1 and I want the total number of cells that I manually shaded by using fill divided by 2 to appear in cell m1. What do I put in m1?

Share this post


Link to post
Share on other sites

You could perhaps approach it from the other direction.

 

Manually enter a value (e.g. 1) into the cells, so you have a value that you can easily count and calculate with.

 

Apply a conditional format to all those cells, so that if a cell contains a value > 0 then it automatically shades it and sets the text colour to the same as the shade (so you can't see it).

 

Not perfect, but perhaps easier to manage.

Edited by yogib

Share this post


Link to post
Share on other sites

I'd go with something simple like Tortoise suggests and keep everything within a "default" excel install (the other solution is very smart, but could lead to issues if you try and play with things further in future without understanding the whole code segment). Without seeing the whole xls, and just off the top of my head, something using a "flag" as follows:

 

A row with a drop-down of YES/NO options (this can be adjusted to anything you want) above the 30 min time slots, and then selecting this to YES highlights the time and then does a calculation. This involves using the in-Excel option of Data Validation (making the drop-down), and then Conditional Formatting to adjust the color (doesn't impact calculations, but makes it easier to read). Then work the formula from the "YES" fields, something like: =COUNTIF(B2:F2,"YES") and then use the result of this to multiply by the 30 minute slots (note, adjust for hh:mm:ss appropriately).

 

B C D E F G

2 YES YES NO NO YES =COUNTIF(B2:F2,"YES") which produces the result 3

3 00:30 00:30 00:30 00:30 00:30 =G2*30

 

However, my preference would be remove the YES/NO row and make each time-slot a drop-down of either 00:00 or 00:30 (you can add more options if you like) as follows:

 

B C D E F G

2 00:30 00:30 00:00 00:00 00:30 =COUNTIF(B2:F2,"00:30")*30

 

Drop-down (just in case this isn't clear): when you click on the cell, it produces a vertical menu showing acceptable options to choose for the cell.

Share this post


Link to post
Share on other sites

Lol. I started reading this and it reminded me how much I have forgotten, but also how much I don't know. Trannies is a funny site .... Just when I'm bored and not training there is always something in Sandbox to amuse. ;).

Share this post


Link to post
Share on other sites

Hello fellow excel geeks......seeking some help. I have spent a few hours trying to work out this formula with no luck (doing my head in basically).....



Trying to use a date of birth to workout when the student would start Prep.



So, a date could be 04/10/12 (dates are entered as dd/mm/yy), and I need a formula for the following



If (DOB) if >= to 01/07/11 to <=31/06/12 = Prep 2016


If (DOB) if >= to 01/07/12 to <=31/06/13 = Prep 2017


If (DOB) if >= to 01/07/13 to <=31/06/14 = Prep 2018


If (DOB) if >= to 01/07/14 to <=31/06/15 = Prep 2017


Share this post


Link to post
Share on other sites

Skel, set up a lookup table starting in A2 in my example.

 

 

DOB

Enters

1/07/11

Prep 2016

1/07/12

Prep 2017

1/07/13

Prep 2018

1/07/14

Prep 2019

1/07/15

Prep 2020

1/07/16

Prep 2021

 

Then set up a formula that reads the DOB and looks up the DOB in A13:

 

=VLOOKUP(A13,$A$2:$B$7,2)

 

My little test results:

 

 

DOB

Prep entry

5/07/11

Prep 2016

5/06/12

Prep 2016

5/07/12

Prep 2017

5/06/13

Prep 2017

5/07/13

Prep 2018

5/06/14

Prep 2018

 

If it doesn't work, it's because the dates are being read as TXT inputs, not DATE inputs. PM me if this happens and I'll give you a fix. It involves one formula.

Edited:

AARRGHHH!!! formatting of my neat tables disappeared, but I hope you get the idea. The lookup table has the date in ColA, the entry year in ColB.

Edited by tortoise

Share this post


Link to post
Share on other sites

or perhaps something along these lines...

 

=CONCATENATE("Prep ",IF(A1>=DATEVALUE("1-JULY-2014"),"2019",IF(A1>=DATEVALUE("1-JULY-2013"),"2018",IF(A1>=DATEVALUE("1-JULY-2012"),"2017",IF(A1>=DATEVALUE("1-JULY-2011"),"2016","*UNKOWN*")))))

 

Assuming a column of DOB dates (formatted as date) and a column with 'Prep" calculation.

 

Where A1 is the cellref of the first date (cell must be formatted as a date) and the formula is entered into another cell on the same row.

 

Then you can just copy the formula down the column, and it will update the references.

 

Basically just IF() that falls back through the 4 start dates.

 

Note: I assumed "Prep 2019".

Share this post


Link to post
Share on other sites

Many thanks - did a copy an paste XCom and it worked perfectly - I will take it to work tomorrow and plug it into the larger spread sheet.

 

This new job is giving me an opportunity to extend my "limited" skills, but I like a challenge.

 

:-

Share this post


Link to post
Share on other sites

There is probably a more generic way to do it - as it's essentially looks like the financial-year of a date + 4 ?

 

Assuming that is the case, then perhaps something like this might work ??

 

Used the same way as the previous one.

 

=IF(ISERROR(DATEVALUE(TEXT(A1,"d/m/yyyy"))),"Unkown",CONCATENATE("Prep ", YEAR(A1) + IF(MONTH(A1)>=7,1,0) + 4))

 

Basically, says if it's a valid date, get the year, add 1 if the month is >= 7, and then add 4

Edited by XCom

Share this post


Link to post
Share on other sites

I hate auditing nested ifs and I avoid them like the plague because they can be so error prone. Like I hate auditing spreadsheets from peeps who don't know sumproduct.

Share this post


Link to post
Share on other sites

I hate auditing nested ifs and I avoid them like the plague because they can be so error prone. Like I hate auditing spreadsheets from peeps who don't know sumproduct.

 

Yes, the first solution was just a quick & dirty, which implemented the specified conditions - it's nasty, ugly, and would need to be updated as new years are added, but it does give the required answer.

 

The 2nd solution is better IMO, as (provided the logic is correct) it should work for any year... and the only purpose of the IF() in that one is for empty references.

Share this post


Link to post
Share on other sites

Yours is really obviously a programmer's solution to an excel problem - totally not a criticism, just an observation that it is code-think.

 

Having managed unsophisticated users for a long time, I prefer to teach them stuff that they can pick up and use for other purposes. Lookups are a fantastic and much under-rated spreadsheet tool, because they are simple (if you understand the principle of exact or inexact matches - for most applications I just tell people they must add FALSE and they eventually work out why), they are AUDITABLE by just about anyone, and very hard to get wrong. Plus if you add MATCH and INDIRECT to the toolkit, you are really humming.

 

Just personal preference, but I use IFERROR to return for nuls or invalid calculations rather than IF(ISERROR.....

 

I also reject spreadsheets that are given to me for audit where macros have been used to do stuff and stamp values. I don't mind that once the process has been vetted properly as it speeds up subequent analysis and reduces file size, but if you have to sign off on stuff that might land you in court, you need a rock solid audit trail.

 

Geek time is over, back to some real work with my 60MB spreadsheet.

Edited by tortoise

Share this post


Link to post
Share on other sites

Yours is really obviously a programmer's solution to an excel problem - totally not a criticism, just an observation that it is code-think.

 

Having managed unsophisticated users for a long time, I prefer to teach them stuff that they can pick up and use for other purposes. Lookups are a fantastic and much under-rated spreadsheet tool, because they are simple (if you understand the principle of exact or inexact matches - for most applications I just tell people they must add FALSE and they eventually work out why), they are AUDITABLE by just about anyone, and very hard to get wrong. Plus if you add MATCH and INDIRECT to the toolkit, you are really humming.

 

Yes, it's funny how a spreadsheet can have a difference in philosophical approach: accounting vs. programming.

 

I'm a programmer and always approach things from that direction, but I constantly have to 'negotiate' a middle ground between what seems perfectly logical and more elegant to me, and what is practical and usable for accounts/finance who requested a solution - more often than not, their 'clunky' solution gets implemented, because at least everyone can understand it - LOL.

 

Just personal preference, but I use IFERROR to return for nuls or invalid calculations rather than IF(ISERROR.....

 

I'm not sure that IFERROR would work in this case, as it only returns a single argument: IFERROR(value, return_if_error)

 

Basically, the 2nd solution is a True/False return based on the ISERROR test for a valid date-value

 

IF(ISERROR(value), return_if_error, return_if_ok) where the return_if_ok is the calculated value

 

IF(ISERROR(DATEVALUE(TEXT(A1,"d/m/yyyy"))), "Unkown", CONCATENATE("Prep ", YEAR(A1) + IF(MONTH(A1)>=7,1,0) + 4))

Edited by XCom

Share this post


Link to post
Share on other sites

Love excel - not quite the same proficiency as Xcom but I was using it recently to validate some maths for an android app This one will find the distance in km between two GPS coordinates (in lat/long)

 

=ACOS(COS(RADIANS(90-Latitude_1))*COS(RADIANS(90-Latitude_2))+SIN(RADIANS(90-Latitude_1))*SIN(RADIANS(90-Latitude_2))*COS(RADIANS(Longitude_1-Longitude_2)))*RadKilom (where RadKilom=6371)

 

and this one will give you the heading in degrees between the two

 

=DEGREES(ATAN2(COS(RADIANS(Latitude_1))*SIN(RADIANS(Latitude_2)) -SIN(RADIANS(Latitude_1))*COS(RADIANS(Latitude_2))*COS(RADIANS(Longitude_2-Longitude_1)),SIN(RADIANS(Longitude_2-Longitude_1))*COS(RADIANS(Latitude_2))))

 

 

Just perfect for a real time bike tracking algorithm or "find my car" type app. :)

Share this post


Link to post
Share on other sites

Love excel - not quite the same proficiency as Xcom

 

Proficient? - HAH! - tell him he's deamin'

 

I do consider myself to be proficient in multiple programming languages - all the usual imperative and declarative suspects, including the various Web 'languages' - but I use Excel (functions/macros) only when I get a request for an Excel solution, and otherwise avoid it like the plague.

 

However, because Excel uses what could be loosely described as a rudimentary functional programming language, I find it easy enough to initially write the problem out in pseudo-language, and then go looking for the required Excel functions to substitute - or write a macro if I need to.

Share this post


Link to post
Share on other sites

 

Proficient? - HAH! - tell him he's deamin'

 

I do consider myself to be proficient in multiple programming languages - all the usual imperative and declarative suspects, including the various Web 'languages' - but I use Excel (functions/macros) only when I get a request for an Excel solution, and otherwise avoid it like the plague.

 

However, because Excel uses what could be loosely described as a rudimentary functional programming language, I find it easy enough to initially write the problem out in pseudo-language, and then go looking for the required Excel functions to substitute - or write a macro if I need to.

I'd love to be proficient in ANY language LOL My current passion is embedded controllers and IOT so I am having a crack at a heap of languages. platforms and environments (all with varying degrees of success :( ) but I get hardware and maths pretty well at least :)

Share this post


Link to post
Share on other sites

Seeking yet more wisdom.....

 

Trying to calculate =IF formula for three options (using age as the pre-cursor decision). I can make it work with two options, but not a third J Just need the number of FTE not the wording FTE. So, looking at the following..........

 

If aged 2.5 to 3 = 3 (FTE)

 

If aged 3 to 4 = 4 (FTE)

 

If aged 4 = 5 (FTE)

Share this post


Link to post
Share on other sites

Basically, the way nested IF() functions work is:

 

=IF(test_condition, true_return, false_return)

 

or another way of saying it is:

 

=IF(condition, then, else)

 

So, to nest multiple conditions you simply substitute the else with another IF(condition, then, else) and so on and so on, and close all the nested IF functions at the end, Which excel conveniently colour-codes for you.

 

So.... something like this might work... but obviously you need to fine-tune the references and perhaps the logic.

 

=IF(age < 2.5, "error", IF(age < 3, 3, IF(age < 4, 4, 5)))

Edited by XCom

Share this post


Link to post
Share on other sites

Seeking yet more wisdom.....

 

Trying to calculate =IF formula for three options (using age as the pre-cursor decision). I can make it work with two options, but not a third J Just need the number of FTE not the wording FTE. So, looking at the following..........

 

If aged 2.5 to 3 = 3 (FTE)

 

If aged 3 to 4 = 4 (FTE)

 

If aged 4 = 5 (FTE)

 

Rather than nested IFs, could use a lookup table instead:

 

Screen%20Shot%202015-07-29%20at%204.23.0

 

 

You can also define range names, e.g. select all of column A and define it with than name "Age", and select the lookup table in D3:E6 and define it to be named "FTP_LookupTable".

 

Then the formula in column 2 can become a bit easier to read:

 

=VLOOKUP(Age,FTE_LookupTable,2,TRUE)

 

Just copy down the formula.

 

What this does is look at the age value in column A, check that value in the first column of the lookup table, then returns the corresponding value in the second column of the lookup table.

 

When an age value doesn't match exactly what's in the lookup table, it will choose the maximum value not exceeding the value being checked. In effect the table creates ranges.

 

It's much neater than nested IFs, and especially if later you need to adjust the lookup table with modified ranges. It's a simple table change rather than getting messed up with nested IFs.

Share this post


Link to post
Share on other sites

Alex, that was my solution to Skel's first query. I'm a BIG user of lookups for all the reasons you outline. Even lower-skilled users can adapt them once given an example. I steer clear of names ranges because they are harder to audit and can trap the inexperienced end user.

 

In this case CEILING would work as well, if real age was in decimals of years. It will depend on how the original data is structured. If you have DOB and a date on which you are measuring the age, just subtract DOB from the reference date and divide by 365, the formula will look something like this:

 

=CEILING((basedate-DOB)/365)

 

Will return an integer that matches Skel's list.

Share this post


Link to post
Share on other sites

OK, here's a query that's so technical it will BLOW YOUR MIND :shy:

 

MY WFH environment is a Win laptop with 2007 Office. I run a 2nd 21" monitor which is great for looking at contracts in detail and side by side comparos. I run everything from the laptop and use the 'Extend' function in Display to drag pretty much anything I want across to the external monitor. When I say nearly everything, except XLS that is. If I open with the laptop I cannot drag it across and even if open it from an application that is displayed on the external monitor (like Outlook for example) the XLS itself always 'jumps' back to laptop.

 

This doesn't happen PPT, Word or anything else.

Share this post


Link to post
Share on other sites

Can you resize the Excel window down and then drag it across FP?

Or does it not move, no matter what?

Share this post


Link to post
Share on other sites

Can you resize the Excel window down and then drag it across FP?

Or does it not move, no matter what?

 

 

That's how I used to do it Tyno, but it jumps back no matter nowadays, been doing it for about a week. I've probably hit some whacky setting when I've been rushing from one meeting to another. :shy:

Share this post


Link to post
Share on other sites

I can't do that between my excel in parallels on the Mac and the secondary screen either. But on some dual screen machines in my clients office I can, and on some I can't. Seeing as I play find the desk on a daily basis, I'm darned if I know why the difference.

 

Edit, because I re-read your problem. On my Mac I can place excel in either window, but not stretch it across both.

Edited by tortoise

Share this post


Link to post
Share on other sites

My turn to beg for help this time.

 

I have a large list of addresses that I need to break into street, Suburb & State. The formatting is consistent, with a comma between the fields I need, the only issue is that many have a sub-address as well which can have 1 or 2 commas in it. The sub-address is to be included in the street address. I can work out how to split the text string from the left using the commas, but if I do that, then some addresses will get broken into 4 or 5 fields rather than 3. Is there a way to do it from the right, so I can peel out State, then Suburb, then the rest.

 

eg:

 

 

17 CHURCH ST, PERTH, WA

3 PLAIN ST, EAST PERTH, WA

U1, 23 QUEENSBERRY ST, CARLTON, VIC

U1, 41-53 VICTORIA PDE, FITZROY, VIC

 

What I want from the list above is:

 

17 CHURCH ST PERTH WA

3 PLAIN ST EAST PERTH WA

U1, 23 QUEENSBERRY ST CARLTON VIC

U1, 41-53 VICTORIA PDE FITZROY VIC

Edited by Ex-Hasbeen

Share this post


Link to post
Share on other sites

Under data menu, text to columns from memory. Use delimited rather than fixed width, using comma as the delimiter

I'd thought of that, but with some data having more commas than others, how do I get around that?

 

I need the text after the last comma in the 3rd column, the text between the 2nd last and last comma in the 2nd column, and everything before the 2nd last comma in the first column.

Share this post


Link to post
Share on other sites

To be honest, I would be looking for some sort of sorting before hand. Sort and do a different text to columns on those starting with U in your example. Manual I know but not sure of any other way so would use sort and other methods to reduce the amount of manual work required

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

×