Could someone give me a formula or funtion for the following

  • Thread starter Thread starter John
  • Start date Start date
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.
 
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?
 
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????
 
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
 
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
 
Back
Top