how to put data from rows to a column

  • Thread starter Thread starter Evgen
  • Start date Start date
E

Evgen

Hi all!
I have a sheet, where some information is put in multiple rows. This
information is either "1" or "blank" cell. I need to creat a single column
in the other sheet, which puts addresses of all cells, containing ones,
omittings "blank" cells.
For example, A1, A3, A4, B2, B3, B5, C3, C5 contain "1". The column must
be:
A1
A3
A4
B2
B3
B5
C3
C5

How can I do automatically? Is there any chance to do it in such way that if
I change, for example, A2 to "1" the column will also change like
A1
A2
A3
.....
C5

Many thanks in advance for yuor help!

Kind regards,

Evgen
 
Evgen,

One possible solution would be to use Autofilter on this sheet, filtering
for 1 in that column. Now the sheet will look like the other one would
have. You can even copy/paste the rows to another sheet. Be careful about
keeping multiple copies of your data, though.
 
Hi Evgen,
You would certainly need a programming solution (macro)
There is a programming newsgroup better suited to such questions
if you know beforehand that you need a programming solution.

Gathering the 1's is simple, but I don't know the range to get them
from. Other sheet is rather vague. Whether you just have one
sheet that this is to done for is not clear.

Possible enhancement of the other sheet with
VLOOK to give descriptions to the Cell addresses keys on the other sheet.

To have something done automatically if you change something
in a sheet would require an Event macro.

Can't help but think that this is homework

If Not familiar with install and use of macros see
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Option Explicit
Dim arrCopyto()
Sub Questionaire()
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
'D.McRitchie, public.excel, 2004-09-12
' http://google.com/groups?threadm=#[email protected]
Dim cell As Range, n As Long, OrigSheetName As String
ReDim arrCopyto(0 To 1) 'no Preserve so starts fresh
On Error Resume Next 'In case no such cells in selection
For Each cell In Cells.SpecialCells(xlConstants, xlNumbers)
n = n + 1 '-- Note use of Preserve --
ReDim Preserve arrCopyto(0 To n)
arrCopyto(n) = cell.Address(0, 0)
Next cell
'Make another sheet
OrigSheetName = ActiveSheet.Name
Application.DisplayAlerts = False
Sheets(OrigSheetName & "_addrs").Delete
Application.DisplayAlerts = True
'Create New Sheet
Sheets.Add After:=ActiveSheet
'Rename current Sheet
ActiveSheet.Name = OrigSheetName & "_addrs"
For n = 1 To UBound(arrCopyto)
Cells(n, 1) = arrCopyto(n)
Next n
'Return to Original Sheet
Sheets(OrigSheetName).Activate
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End Sub


To make the above macro run automatically when you make a change to the
sheet you need an Event macro -- installation differs from the above.
To install the following Event macro, right click on the worksheet name, View Code,...

Option Explicit
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Application.EnableEvents = False 'should be part of Change macro
Application.Run "pesonal.xls!Questionaire"
Application.EnableEvents = True 'should be part of Change macro
End Sub
 
Dear David,
Many thanks for you great help! Your code made just what I wanted. I've made
just some small variations to correspond to my requirements (i.e. instead of
Cells I put a specific range, do jot use a new sheet, but existing one, copy
not the names themselves, but property codes, built using cell names).
Again many thanks!
Best regards,
Evgen
 
Back
Top