Automation

[Gotchaman]

I have an imported data looks like below. Two rows contain records with same ID, so I'm trying to move each person's name to F column and delete the second row. See the second example as after clean-up. There are more than 13000 rows of records so there's no way I'd do this manually. If anyone can help me create an automated job I'd appreciate it. Thank you.

Excel Workbook
	A	B	C	D	E
1	W0000027	1108970	5/5/2011	402251	6/22/1961
2	W0000027	Abagin, Adrian
3	W0000037	1108970	5/5/2011	3826	4/13/1985
4	W0000037	Abbassy,Heba
5	W0000046	1577352	6/6/2011	277755	5/22/1987
6	W0000046	Albalbisi, Zakiah
Before

Excel 2007

Excel Workbook
	A	B	C	D	E	F
1	W0000027	1108970	5/5/2011	402251	6/22/1961	Abagin, Adrian
2	W0000037	1108970	5/5/2011	3826	4/13/1985	Abbassy,Heba
3	W0000046	1577352	6/6/2011	277755	5/22/1987	Albalbisi, Zakiah
After

Excel 2007

 

[dcardno]

If this format is consistent, then...

1) Select Column B (for the relevant range), and then ctrl-drag and drop it on column F to create a copy of the values in Column B.
2) Drag and drop that copy upwards by one row (not a copy - move it) - this will leave you with the first and every second row after that looking like you want, with the other rows containing the Key value and the related name.
3) Sort the whole range by Column B - the numeric values will move to the top or bottom (depending on whether you are sorting ascending or descending - it really doesn't matter) , with the alphabetic values going the other way - you want to keep the rows with numeric values.
4) Go to the top or bottom of the sorted range, select the rows with alphabetic entries in Column B, and delete them.
5) Re-sort the remaining list in whatever order is best suited for your task.
6) If you want to return to the order shown, before you perform the sort in step 3 add a sequence number to each record, and then run the step 5 sort on that sequence number.

 

[Smitty]

Welcome to the Board!

See if this does what you need:

<font face=Calibri><SPAN style="color:#00007F">Sub</SPAN> foo()<br>    <SPAN style="color:#00007F">Dim</SPAN> i <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN><br>        <SPAN style="color:#00007F">For</SPAN> i = Cells(Rows.Count, "A").End(xlUp).Row <SPAN style="color:#00007F">To</SPAN> 1 <SPAN style="color:#00007F">Step</SPAN> -1<br>            <SPAN style="color:#00007F">If</SPAN> <SPAN style="color:#00007F">Not</SPAN> IsNumeric(Cells(i, "B").Value) <SPAN style="color:#00007F">Then</SPAN><br>                Cells(i, "B").Cut Cells(i - 1, "F")<br>                Cells(i, "B").EntireRow.Delete<br>            <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>        <SPAN style="color:#00007F">Next</SPAN> i<br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

HTH,

 

[Gotchaman]

dcardno thanks your method seems the simplest.

Smitty, the scrip didn't do anything... It appeared running but nothing changed.
thanks for trying though.

 

[Smitty]

Smitty, the scrip didn't do anything... It appeared running but nothing changed.

It works fine for me on your sample.