Sign in to follow this  
Followers 0
workinglikeadog

Microsoft Excel help

74 posts in this topic

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.

1 person likes this

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!

1 person likes this

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

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

LoL! Buy Grandad a beer and I'll recount tales of \r yore.

1 person likes this

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

If you want a template for the above, which you can then work from, let me know Plazbot and I'll email it to you.

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
Sign in to follow this  
Followers 0