Excel Please please HELP going insane!

[geforce]

I need the answer to a simple problem but cant find it anywhere.
I have 2 work books (wb1 and wb2)
I have a column with 20 names in wb1 and I would like a formula that will put every odd name (eg name 1,name 3,name 5, etc) into a column in wb2(work book 2).
In short I need a formula to copy every alternative cell in a column into another column.

[crimlet]

Is the task only to copy every alternate cell, or one to copy a single name when duplicated ?

[geforce]

just to make a copy of every alternate name no other conditions, a straight copy and paste of name 1 name 3 name 5 name 7 etc

eg if I have a colum in wb1 that looks like this:
cathy
ben
bob
fred
mary

Then wb2 colum should look like this:
cathy
bob
mary

[paelinck]

eg if I have a colum in wb1 that looks like this:
cathy
ben
bob
fred
mary

Then wb2 colum should look like this:
cathy
bob
mary

You might try this.

1. copy all the names from WB1 to WB2. Put a column title in the topmost cell (e.g. "Name")
2. in the next column (let's say column B), put TRUE next to the first name or FALSE if you don't want to keep it (let's suppose this is cell B2). You can add a title "Keep" in cell B1.
3. In B3 put the formula =NOT(B2) and copy it down as far as the name list goes.
4. Use the Autofilter to show only the names that have FALSE beside them. Select these rows and delete them (Edit - Delete).

Marc

[Colo]

Microsoft Excel - Book1_______________Running: xl97 : OS = Windows (32-bit) 5.00
(F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp
D17		=
*	A	B	C	D
1	cathy	:alert("=MOD(ROW(),2)")>1	*	*
2	ben	:alert("=MOD(ROW(),2)")>0	*	*
3	bob	:alert("=MOD(ROW(),2)")>1	*	*
4	fred	:alert("=MOD(ROW(),2)")>0	*	*
5	mary	:alert("=MOD(ROW(),2)")>1	*	*
Sheet1

To see the formula in the cells just click on the cells hyperlink

The above image was automatically generated by [HtmlMaker V1.23]
If you want this code, click here and Colo will email the file to you.
This code was graciously allowed to be modified: by Ivan F Moala All credit to Colo


Hi geforce,

You could use a autofilter and mod and row formula.
Input formulas into column B, and using autofilter, and select 1 or 0 on autofilter.

After this, copy the filter range and paste it into another sheet.

[DZarlengo]

This is a simple matter for a loop. Remember that you can use "Step" in loops. For example, to copy every other cell:

For X = 1 to 100 Step 2
Cells (X,1).Copy
Next X

You could insert a line to load them into an array and then later paste them all at once. That would be fastest. You could also paste each one as soon as you copy it, but it would take a nested loop (not too hard), and run slower.

[Mark O'Brien]

Or, you could follow my bestest good mate Chris D's advice on this similar thread:

http://www.mrexcel.com/board/viewtop...c=5620&forum=2

(or you could follow mine, but Chris says his is more generic and I believe him)

[Jack in the UK]

one day CHris assure me you tell him Celtic will win the cup!

[Mark O'Brien]

Thought you'd like that one Jack.

[asramasarma]

Hai i think this will workout for you

For i = 1 To 21 Step 2
Cells(i, 1).Select
Selection.Copy
Windows("dest.xls").Activate
Cells(i, 1).Select
ActiveSheet.Paste
Windows("sourse.xls").Activate
Next

[ This Message was edited by: asramasarma on 2002-05-16 23:58 ]