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>

Name

Designation

Company Name

<Useless Info#1>

<Useless Info#2>

Name1

Designation1

Company Name1

<Useless Info#1>

<Useless Info#2>

Name2

Designation2

Company Name2

<Useless Info#1>

<Useless Info#2> .......

<colgroup><col></colgroup><tbody>
</tbody>

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.