Copying select data from one worksheet to the other.....

[Pincer360]

Hello,

Need help with the following -

I have the following information in one sheet <Sheet1> in Column A. This list is very long (1000s of names). I need this information to move to <Sheet2> in this format - Name (Column A), Designation (Column B), Company Name (Column C). The <Useless Info> can be totally ignored.

<Sheet1, Column A>
[TABLE="width: 158"]
<colgroup><col></colgroup><tbody>[TR]
[TD]Name[/TD]
[/TR]
[TR]
[TD]Designation[/TD]
[/TR]
[TR]
[TD]Company Name[/TD]
[/TR]
[TR]
[TD]<Useless Info#1>[/TD]
[/TR]
[TR]
[TD]<Useless Info#2>[/TD]
[/TR]
[TR]
[TD]Name1[/TD]
[/TR]
[TR]
[TD]Designation1[/TD]
[/TR]
[TR]
[TD]Company Name1[/TD]
[/TR]
[TR]
[TD]<Useless Info#1>[/TD]
[/TR]
[TR]
[TD]<Useless Info#2>[/TD]
[/TR]
[TR]
[TD]Name2[/TD]
[/TR]
[TR]
[TD]Designation2[/TD]
[/TR]
[TR]
[TD]Company Name2[/TD]
[/TR]
[TR]
[TD]<Useless Info#1>[/TD]
[/TR]
[TR]
[TD]<Useless Info#2>
.......[/TD]
[/TR]
</tbody>[/TABLE]

Thanks in advance for your help.

Regards,
-P360

 

[Peter_SSs]

Welcome to the MrExcel board!

Your sample data did not post very well. You have to be careful using < and > signs as the forum software tends to interpret them as HTML tags. See my signature block below for better ways to post sample data.

Assuming there are exactly 5 rows for each section try these formulas, copied down. I have done it on a single sheet but post back if you have difficulty applying it across 2 sheets.

Excel Workbook
	A	B	C	D	E
1	Name		Name	Designation	Company Name
2	Designation		Name1	Designation1	Company Name1
3	Company Name		Name2	Designation2	Company Name2
4	Useless Info#1
5	Useless Info#2
6	Name1
7	Designation1
8	Company Name1
9	Useless Info#1
10	Useless Info#2
11	Name2
12	Designation2
13	Company Name2
14	Useless Info#1
15	Useless Info#2
List

If the number of rows per section varies, you would need to give us some way to logically work out where the start of each section is.

 

[dave2018]

or you can just copy this into C2 and drag to the right and down...

=IF(INDEX($A$1:$A$1000,ROWS($C$2:C2)*5-4+COLUMNS($C$2:C2)-1)="","",INDEX($A$1:$A$1000,ROWS($C$2:C2)*5-4+COLUMNS($C$2:C2)-1))

 

[Pincer360]

Thank you both. Your inputs really helped solve the problem. Very much appreciate your help.

 

[Peter_SSs]

Thank you both. Your inputs really helped solve the problem. Very much appreciate your help.

No problem. Glad to help.