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

 

[Titian]

Hi Will, welcome to the Board

I presume you want the information in A2 and A3 etc?

Enter this into A2 and copy down

=$B$5&" "&$B6&", "&$C$5&" "&$C6&", "&$D$5&" "&$D6&", "&$E$5&" "&$E6&", "&$F$5&" "&$F6

The best way is to copy and paste my formula to be sure of getting the "$" signs in the correct places.

There may be other ways to achieve the same results.

 

[will596]

Great, that worked! Thank you very much. And from so close to my Yorkshire home of Harrogate

Though I have over twenty headed columns, so now the A2 cell has the headers of each column making it a very wordy cell.

Can the formula be worked so that it only picks up a header and the relevant quantity if there is a value in the quantity cell in the first place?

Thanks
Will

 

[Titian]

Small world isn't it!

Yes, anything can be done but the possibilities that you are now looking for may be beyond what a formula can reasonably handle - though someone may jump in here with their offering.

Otherwise it may need a User Defined Function.

Regards

 

[will596]

Okey doke, thanks for your help.

Anyone else able to advise?

 

[will596]

Can anyone help on this?! Thanks...

 

[Peter_SSs]

With that many columns I would be using a macro to do this. Here's a suggestion.

<font face=Courier New><br><SPAN style="color:#00007F">Sub</SPAN> CombineText()<br>    <SPAN style="color:#00007F">Dim</SPAN> Result(), rRow(), Headings()<br>    <SPAN style="color:#00007F">Dim</SPAN> Cols <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>, lr <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>, i <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>, r <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN><br>    <br>    Cols = Cells(1, Columns.Count).End(xlToLeft).Column - 1<br>    lr = Range("B:B").Resize(, Cols).Find(What:="?*", After:=Cells(1, 2), _<br>        LookIn:=xlValues, SearchOrder:=xlByRows, SearchDirection:=xlPrevious, _<br>        SearchFormat:=False).Row<br>    <SPAN style="color:#00007F">ReDim</SPAN> Headings(1 <SPAN style="color:#00007F">To</SPAN> Cols)<br>    <SPAN style="color:#00007F">ReDim</SPAN> rRow(1 <SPAN style="color:#00007F">To</SPAN> Cols)<br>    <SPAN style="color:#00007F">ReDim</SPAN> Result(1 <SPAN style="color:#00007F">To</SPAN> lr)<br>    Headings = Cells(1, 2).Resize(, Cols).Value<br>    <SPAN style="color:#00007F">For</SPAN> r = 2 <SPAN style="color:#00007F">To</SPAN> lr<br>        rRow = Cells(r, 2).Resize(, Cols).Value<br>        <SPAN style="color:#00007F">For</SPAN> i = 1 <SPAN style="color:#00007F">To</SPAN> Cols<br>            <SPAN style="color:#00007F">If</SPAN> rRow(1, i) <> "" <SPAN style="color:#00007F">Then</SPAN><br>                Result(r) = Result(r) & ", " & Headings(1, i) & " " & rRow(1, i)<br>            <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>        <SPAN style="color:#00007F">Next</SPAN> i<br>        <SPAN style="color:#00007F">If</SPAN> Result(r) <> "" <SPAN style="color:#00007F">Then</SPAN><br>            Result(r) = Replace(Result(r), ", ", "", 1, 1)<br>        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>    <SPAN style="color:#00007F">Next</SPAN> r<br>    Cells(1, 1).Resize(lr).Value = Application.Transpose(Result)<br>    Columns("A").AutoFit<br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br></FONT>


Here is the result of running the code with the data shown in cols B:F

Excel Workbook
	A	B	C	D	E	F
1		UK	Ger	M/E	Rus	Nor
2	UK 80, Ger 250, M/E 300, Rus 350, Nor 200	80	250	300	350	200
3	UK 60, Ger 200, M/E 350, Rus 350, Nor 150	60	200	350	350	150
4	UK 25, Nor 3	25				3
5	M/E 75.2, Nor 90			75.2		90
6
7	Ger 22		22
Combine text

 

[drsarao]

Something like this with formula:

Excel Workbook
	A	B	C	D	E	F
1	Summary	UK	Ger	M/E	Rus	Nor
2	UK 80, Ger 250, M/E 300, Rus 350, Nor 200,	80	250	300	350	200
3	Ger 200, Rus 350, Nor 150,		200		350	150
4	UK 80, M/E 300, Rus 350, Nor 200,	80		300	350	200
5	UK 60, M/E 350, Nor 150,	60		350		150
6	Ger 250, Rus 350,		250		350
7	UK 60, Ger 200, M/E 350, Rus 350, Nor 150,	60	200	350	350	150
Sheet8

Excel 2003

Cell Formulas
Range	Formula
A2	=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 & ", ")

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.

 

[will596]

Hi Peter,

Thank you so much for your support. The result is exactly what I am after!

How would I go about writing the macro, is this done with Visual Basic or Microsoft Script Editor?

Best regards
Will

 

[will596]

Hi Dr Sarao

That worked a treat - cheers
No worries about the trailing comma....

Will