Selecting data from cells that are 5 rows apart and groung in 1 co
 

Hi, I would like to know if you have any idea to do the following:

I have a set of data organized in the following fashion:

Column A
Row1: Name
Row2: Company
Row 3: Phone
Row 4: Address
Row 5: City
Row 6: State
Row 7: Country
Row 8: Name2
Row 9: Company2
Row 10: Phone2
Row 11: Address2
Row 12: City2
Row 13: State2
Row 14: Country2

Now, I want to regroup this information differently and just use some of it
in a different sheet. I want to organize it in columns as follows:

Column A Column B Column C Column D
Name 1 Company 1 Phone 1 City 1
Name 2 Company 2 Phone 2 City 2
Name 3 Company 3 Phone 3 City 3
Name 4 Company 4 Phone 4 City 4

Any ideas?

Thank you

Selecting data from cells that are 5 rows apart and groung in 1 co
 

Sub rearrangedata()
lr = Cells(Rows.Count, "a").End(xlUp).Row
j = 1
For i = 1 To lr Step 7
Cells(j, 2) = Cells(i, 1)
Cells(j, 3) = Cells(i + 1, 1)
Cells(j, 4) = Cells(i + 2, 1)
Cells(j, 5) = Cells(i + 4, 1)
j = j + 1
Next i
End Sub

Sub rearrangedataothersheet()
lr = Cells(Rows.Count, "a").End(xlUp).Row
j = 2
With Sheets("sheet9")
For i = 1 To lr Step 7
.Cells(j, 1) = Cells(i, 1)
.Cells(j, 2) = Cells(i + 1, 1)
.Cells(j, 3) = Cells(i + 2, 1)
.Cells(j, 4) = Cells(i + 4, 1)
j = j + 1
Next i
End With
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Jorge" wrote in message
...
Hi, I would like to know if you have any idea to do the following:

I have a set of data organized in the following fashion:

Column A
Row1: Name
Row2: Company
Row 3: Phone
Row 4: Address
Row 5: City
Row 6: State
Row 7: Country
Row 8: Name2
Row 9: Company2
Row 10: Phone2
Row 11: Address2
Row 12: City2
Row 13: State2
Row 14: Country2

Now, I want to regroup this information differently and just use some of
it
in a different sheet. I want to organize it in columns as follows:

Column A Column B Column C Column D
Name 1 Company 1 Phone 1 City 1
Name 2 Company 2 Phone 2 City 2
Name 3 Company 3 Phone 3 City 3
Name 4 Company 4 Phone 4 City 4

Any ideas?

Thank you

Selecting data from cells that are 5 rows apart and groung in 1 co
 

In Sheet 2

A1: =INDEX(Sheet1!$A$1:$A$14,(COLUMNS($A:A)=4)+COLUMNS ($A:A)+(ROWS($1:1)-1)*7)

Adjust your range to suit
Copy across from A1 to D1 and select A1:D1 copy down as far as needed


"Jorge" wrote:

Hi, I would like to know if you have any idea to do the following:

I have a set of data organized in the following fashion:

Column A
Row1: Name
Row2: Company
Row 3: Phone
Row 4: Address
Row 5: City
Row 6: State
Row 7: Country
Row 8: Name2
Row 9: Company2
Row 10: Phone2
Row 11: Address2
Row 12: City2
Row 13: State2
Row 14: Country2

Now, I want to regroup this information differently and just use some of it
in a different sheet. I want to organize it in columns as follows:

Column A Column B Column C Column D
Name 1 Company 1 Phone 1 City 1
Name 2 Company 2 Phone 2 City 2
Name 3 Company 3 Phone 3 City 3
Name 4 Company 4 Phone 4 City 4

Any ideas?

Thank you

Selecting data from cells that are 5 rows apart and groung in 1 co
 

If data is consistently sets of 7 cells as your example.

In B1 enter this formula =INDEX($A:$A,(ROWS($1:1)-1)*7+COLUMNS($A:B)-1)

Copy across to H1

Select B1:H1 and drag/copy down until zeros show up.

Select columns B:E and copypaste specialvaluesokesc

Delete original column A


Gord Dibben MS Excel MVP

On Tue, 15 Jan 2008 14:34:04 -0800, Jorge
wrote:

Hi, I would like to know if you have any idea to do the following:

I have a set of data organized in the following fashion:

Column A
Row1: Name
Row2: Company
Row 3: Phone
Row 4: Address
Row 5: City
Row 6: State
Row 7: Country
Row 8: Name2
Row 9: Company2
Row 10: Phone2
Row 11: Address2
Row 12: City2
Row 13: State2
Row 14: Country2

Now, I want to regroup this information differently and just use some of it
in a different sheet. I want to organize it in columns as follows:

Column A Column B Column C Column D
Name 1 Company 1 Phone 1 City 1
Name 2 Company 2 Phone 2 City 2
Name 3 Company 3 Phone 3 City 3
Name 4 Company 4 Phone 4 City 4

Any ideas?

Thank you

Selecting data from cells that are 5 rows apart and groung in 1 co
 

One of these I will learn to read "everything in the post"

500 times on the blackboard.



On Tue, 15 Jan 2008 15:54:57 -0800, Gord Dibben <gorddibbATshawDOTca wrote:

If data is consistently sets of 7 cells as your example.

In B1 enter this formula =INDEX($A:$A,(ROWS($1:1)-1)*7+COLUMNS($A:B)-1)

Copy across to H1

Select B1:H1 and drag/copy down until zeros show up.

Select columns B:E and copypaste specialvaluesokesc

Delete original column A


Gord Dibben MS Excel MVP

On Tue, 15 Jan 2008 14:34:04 -0800, Jorge
wrote:

Hi, I would like to know if you have any idea to do the following:

I have a set of data organized in the following fashion:

Column A
Row1: Name
Row2: Company
Row 3: Phone
Row 4: Address
Row 5: City
Row 6: State
Row 7: Country
Row 8: Name2
Row 9: Company2
Row 10: Phone2
Row 11: Address2
Row 12: City2
Row 13: State2
Row 14: Country2

Now, I want to regroup this information differently and just use some of it
in a different sheet. I want to organize it in columns as follows:

Column A Column B Column C Column D
Name 1 Company 1 Phone 1 City 1
Name 2 Company 2 Phone 2 City 2
Name 3 Company 3 Phone 3 City 3
Name 4 Company 4 Phone 4 City 4

Any ideas?

Thank you

Selecting data from cells that are 5 rows apart and groung in
 

Don,

What do I do with this code...sorry, I know Excel fairly ok but the
programing part I really don't know much, or doing macros for that matter.
Sorry to ask you something I'm sure is quite basic.

Thanks,


"Don Guillett" wrote:


Sub rearrangedata()
lr = Cells(Rows.Count, "a").End(xlUp).Row
j = 1
For i = 1 To lr Step 7
Cells(j, 2) = Cells(i, 1)
Cells(j, 3) = Cells(i + 1, 1)
Cells(j, 4) = Cells(i + 2, 1)
Cells(j, 5) = Cells(i + 4, 1)
j = j + 1
Next i
End Sub

Sub rearrangedataothersheet()
lr = Cells(Rows.Count, "a").End(xlUp).Row
j = 2
With Sheets("sheet9")
For i = 1 To lr Step 7
.Cells(j, 1) = Cells(i, 1)
.Cells(j, 2) = Cells(i + 1, 1)
.Cells(j, 3) = Cells(i + 2, 1)
.Cells(j, 4) = Cells(i + 4, 1)
j = j + 1
Next i
End With
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Jorge" wrote in message
...
Hi, I would like to know if you have any idea to do the following:

I have a set of data organized in the following fashion:

Column A
Row1: Name
Row2: Company
Row 3: Phone
Row 4: Address
Row 5: City
Row 6: State
Row 7: Country
Row 8: Name2
Row 9: Company2
Row 10: Phone2
Row 11: Address2
Row 12: City2
Row 13: State2
Row 14: Country2

Now, I want to regroup this information differently and just use some of
it
in a different sheet. I want to organize it in columns as follows:

Column A Column B Column C Column D
Name 1 Company 1 Phone 1 City 1
Name 2 Company 2 Phone 2 City 2
Name 3 Company 3 Phone 3 City 3
Name 4 Company 4 Phone 4 City 4

Any ideas?

Thank you

Selecting data from cells that are 5 rows apart and groung in
 

If you're new to macros, you may want to read David McRitchie's intro
at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Put it in a module change the destination sheet to where you want it to
gofire from the source sheet.
If all else fails after your attempts, send to my email below
--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Jorge" wrote in message
...
Don,

What do I do with this code...sorry, I know Excel fairly ok but the
programing part I really don't know much, or doing macros for that matter.
Sorry to ask you something I'm sure is quite basic.

Thanks,


"Don Guillett" wrote:


Sub rearrangedata()
lr = Cells(Rows.Count, "a").End(xlUp).Row
j = 1
For i = 1 To lr Step 7
Cells(j, 2) = Cells(i, 1)
Cells(j, 3) = Cells(i + 1, 1)
Cells(j, 4) = Cells(i + 2, 1)
Cells(j, 5) = Cells(i + 4, 1)
j = j + 1
Next i
End Sub

Sub rearrangedataothersheet()
lr = Cells(Rows.Count, "a").End(xlUp).Row
j = 2
With Sheets("sheet9")
For i = 1 To lr Step 7
.Cells(j, 1) = Cells(i, 1)
.Cells(j, 2) = Cells(i + 1, 1)
.Cells(j, 3) = Cells(i + 2, 1)
.Cells(j, 4) = Cells(i + 4, 1)
j = j + 1
Next i
End With
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Jorge" wrote in message
...
Hi, I would like to know if you have any idea to do the following:

I have a set of data organized in the following fashion:

Column A
Row1: Name
Row2: Company
Row 3: Phone
Row 4: Address
Row 5: City
Row 6: State
Row 7: Country
Row 8: Name2
Row 9: Company2
Row 10: Phone2
Row 11: Address2
Row 12: City2
Row 13: State2
Row 14: Country2

Now, I want to regroup this information differently and just use some
of
it
in a different sheet. I want to organize it in columns as follows:

Column A Column B Column C Column D
Name 1 Company 1 Phone 1 City
1
Name 2 Company 2 Phone 2 City
2
Name 3 Company 3 Phone 3 City
3
Name 4 Company 4 Phone 4 City
4

Any ideas?

Thank you