Could someone give me a formula or funtion for the following

J

John

Could you provide more info? i can't follow what exsactly
it is you're trying to do?

do you have 40 cols at the beforehand with 16 rows of data?

if so, then use copy - paste special to transpose the data
into 16 rows and 40 cols.

Select the data range
Menu Edit - Copy
Select an empty cell elsewhere on the sheet (or a new
sheet)
Menu Edit - Paste Special
tick the "Transpose" check-box and press OK
HTH,
John.
 
B

Bill in Verona, WI

Aaahh, if you are starting with 16 columns x 40 rows, wouldn't the
transposed output area be 40 columns x 16 rows and not 16 columns x 40 rows?
 
N

No

I'm really not sure what funtion will work best I just thought "transpose"

My Goal is to enter data in the cells A1 - P1 and have it show up at the
bottom od the spreadsheet so I can copy and paste into my project.


the format has to be as follows
f_style|v_255_5|Style||menu|||||||Yes|Yes|No|Yes|

So when I add data to the cells the funtion would take that data and add it
to the | | | | | | | | | | |
So id A1 data was "test" it would look like this | test| | | | | | | | | |
if A2 was test2, it would look like this
| test|test2 | | | | | | | | |

At the end I would have A1-P1 X 40 rows, down below the 40th row I would
have 40 | | | | | | | | | | |
filled in with the data from above

Make sence????
 
T

tomhaddock

Hi

Not sure if this is what you want but we can build from that...

Go to Visual Basic Editor by clicking Alt-F11 then go to Insert>Module

Paste the following code in the blank module...
********************************************
Public Function Seperate(ByRef rng As Range) As String
Dim rowString As String
Dim cell As Range

rowString = "|"

Set cell = rng.Cells

For Each cell In rng
rowString = rowString & cell.Value & "|"
Next

Seperate = rowString

End Function
********************************************
Enter in a cell:

=Seperate(A1:A16)

Then drag the formula downwards for 40 or whatever rows.

Let me know if this works! (did in my case!)

Tom
 
T

tomhaddock

After re-reading your messages I realised the function may need to be:

************************************************
Public Function Seperate(ByRef rng As Range) As String
Dim rowString As String
Dim totalRows As Integer
Dim totalColumns As Integer

rowString = "|"
totalRows = rng.Rows.count
totalColumns = rng.Columns.count

For i = 1 To totalRows

If Not rng(i, 1) = "" Then

For j = 1 To totalColumns
If j = 1 Then
rowString = "|"
End If
rowString = rowString & rng(i, j).Value & "|"
Next j
End If

Next i

Seperate = rowString

End Function
************************************************

So the formula should be:

=Seperate(A1:p40)

The function is flexible with ranges so you can select a range with
different rows and columns.

Hope this helps further!

Tom
 
Top