Hello,
I am trying to copy the items (going down) in a column to a row going accross. Any formula that will do this? I have 20 items in the column that I need to move to a row with out lossing the links.
Thanks!
Hello,
I am trying to copy the items (going down) in a column to a row going accross. Any formula that will do this? I have 20 items in the column that I need to move to a row with out lossing the links.
Thanks!
Re: Copy items in column to a row
Try PasteSpecial then select Transpose.
Alastair
Re: Copy items in column to a row
Thanks, it try that and it works. However, it does not carry over the formula that the item in the column has. I need this function since this column is linked to other items. Any ideas?
Regards
Re: Copy items in column to a row
What do you mean by does not carry the formula over? Are you talking about the formula in the cells that you are copying/pasting, or formulas referencing the cells you are copying?
Alastair
Re: Copy items in column to a row
For your info there is a Web site that has about everthing you might want for this kind of spreadsheet work. Web address J-walk.com
Re: Copy items in column to a row
Hello,
Thanks. I am ataching a sample of what I am trying to do. Any suggestions?
Regards
Re: Copy items in column to a row
So I went and transposed the values as directed earlier. This swaped all just fine for me. A few format changes mine looked like what you want in just a few minutes. All values were moved just fine.
So do want the move to happen just one time, or the values to carry automatically as infomation is inputed?
If so you will just have to tell it what each column is equal to for each individual cell. ao2 =k12 and ao3 =l12 and so on. Hope this helps.
Re: Copy items in column to a row
Afraid I dont see why the Transpose does not work for you. As far as I can tell you have no formulas in your original data. You do have formulas in you output data...Are these the ones you wish to keep?
As horrible as it sounds I don't think there is a way to transpose the formulas int he way you wish, eg, to drag across and have the formula reference to go down.
You could do this automatically using VBA, but this will not leave you with the formulas.
Equally you could use VBA to create the formula for you, as a once off code.
If you need help setting up any of these code, then post back.
Hope this helps
Alastair
Re: Copy items in column to a row
Alastair,
Thanks. Yes that is what I am trying to do. Copy the formula to the right while copying the items on the column below.
Do you have a VBA code to do this?
Thanks
Re: Copy items in column to a row
Chris,
Thanks, I tried to use AO=2... but I get a circular reference?
Regards
Re: Copy items in column to a row
Have a look at this example.
Alastair
Re: Copy items in column to a row
Hi
try
Sub test()
Dim rng1 As Range, rngPaste As Range, a(), i As Long
On Error Resume Next
Set rng1 = Application.InputBox("select cells to copy", Type:=8)
Set rngPaste = Application.InputBox("select cell to paste", Type:=8)
ReDim a(1 To rng1.Count)
For i = 1 To rng1.Count
a(i) = rng1.Item(i).Address(0, 0)
Next
For i = 1 To rng1.Count
rngPaste.Range("a1").Offset(, i - 1).Formula = "=" & a(i)
Next
End Sub
Display More
Re: Copy items in column to a row
JINDON, Alastair
THANKS!!! It works, this is going to save me a lot of time. I appreciate your time and support!
I have another question, I am wondering if you can help me... on this post or if I need to start another post??? please let me know.
Attached is a file where, I am trying to match a country to a supervisor. I have the supervisor per country, now I need to add the supervisor to the country?
Thanks and Regards
Re: Copy items in column to a row
I don't think this is possible using standard Excel functions (Happy to be proved wrong!), though it may be possible to create a UDF.
Try posting a separate thread though, this is technically a new question and new topic.
Alastair
Re: Copy items in column to a row
This UDF works.
Public Function SearchArray(Crit As String, rng As Range) As String
With rng
Set findval = .Find(Crit, LookIn:=xlValues, lookat:=xlWhole)
End With
header_rw = rng.Cells([a1]).Row
find_col = findval.Column
SearchArray = Cells(header_rw, find_col).Value
End Function
Display More
rng includes the header, and assumes the header is at the top of the data.
crit in this case is country a etc.
Hope this helps
Alastair
Re: Copy items in column to a row
try
Sub test2()
Dim rng1 As Range, rngPaste As Range, a(), i As Long
Dim title, data, result(), x, ii, iii
On Error Resume Next
Set rng1 = Application.InputBox("select cells to copy", Type:=8)
Set rngPaste = Application.InputBox("select cell to paste", Type:=8)
title = rng1.Rows(1).Value
With rng1.Offset(1).Resize(rng1.Rows.Count - 1)
data = .Value
x = .Count
End With
ReDim result(1 To x, 1 To 2)
For i = 1 To UBound(data)
For ii = LBound(data, 2) To UBound(data, 2)
iii = iii + 1
result(iii, 1) = data(i, ii): result(iii, 2) = title(1, ii)
Next
Next
With rngPaste.Range("a1").Resize(UBound(result, 1), UBound(result, 2))
.Value = result
.Sort key1:=.Range("a1"), order1:=xlAscending, header:=xlNo
End With
End Sub
Display More
Re: Copy items in column to a row
Alastair
Thanks. I am not sure how to run this code? Can you advise?
Regards
Re: Copy items in column to a row
JINDON,
I am getting an error "Compile error" duplicate declaration on current scope
Dim title, data, result(), x, i, ii. I am not sure how to fix this.
Thanks and regards
Re: Copy items in column to a row
A UDF (User defined function) works in the same way as a standard Excel function like SUM.
Put the code into a new module (open VBE, and then go Insert>>Module)
Then you can use as a normal function. Enter it into a cell as
=SearchArray(criteria to search for, range to search in (including the headers))
Hope this helps
Alastair
Re: Copy items in column to a row
Sorry,
I have edited the code above.
Can you try to copy the code and replace?
Don’t have an account yet? Register yourself now and be a part of our community!