Help needed! Formula writing...

[will596]

Hi!

I have a query and am hoping one of you geniuses can help! I don't even know if it can be done but thought it would be worth asking...

So, I have columns with titles at the top, and with numbers in the below cells.

What I would like is a formula for a column seperate to these that firstly, looks if there is a quantity in the relevant cell on that row, secondly takes the title (top cell) of that column, and thirdly puts the number in the cell on that row after the title:

A_____B_____ C_____ D_____ E______ F
1_____UK____ Ger___ M/E___ Rus_____ Nor
2_____80_____250___ 300___ 350_____200
3____ 60_____200____350____350____ 150

So in cell A1, I would like a formula that reads the subsequent cells and their titles, and gives a one-cell summary of the data seperated by commas. So in A1 it would say:

UK 80, Ger 250, M/E 300, Rus 350, Now 200

And in A2:
UK 60, Ger 200, M/E 350, Rus 350, Now 150

Please help!

Thanks
Will

 

[Peter_SSs]

... is this done with Visual Basic ....Editor?

Yes

How would I go about writing the macro,...

In this case it is already written (apart from any minor tweaks it might need) so all you have to do is implement it. Here's one way ...

1. Right click the sheet name tab and choose 'View Code'

2. In the VB window use the menus to Insert|Module

3. Copy my code from the earlier post and Paste into the main right hand pane that opened at step 2

4. Close the VB window.

5. From Excel's normal menus ...
Tools|Macro|Macros...|select the 'CombineText' macro in the list|Run

The macro can also be attached to a toolbar button for easier launching and/or have a key combination set up to launch.

The formula will get quite unwieldy with 20 columns however.

Basically the same unit repeats for each column - IF(B2="","",$B$1 & " " & B2 & ", ") (mind the $ signs).

Yet to fix the trailing comma.

I definitely wouldn't use a formula for 20 columns, but if you were using a formula approach, the extra comma is easily dealt with by putting it before each term rather than after then stripping out the first occurrance of this. Like this:

=REPLACE(IF(B2="","",", "&B$1&" "&B2)&IF(C2="","",", "&C$1&" "&C2)&IF(D2="","",", "&D$1&" "&D2)&IF(E2="","",", "&E$1&" "&E2)&IF(F2="","",", "&F$1&" "&F2),1,2,"")

 

[drsarao]

Thank you Peter for the insight.

I was building on MID() to remove the trailing comma and then gave up.