Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
What to do if the worksheet is locked on selecting rows or cells? | Excel Discussion (Misc queries) | |||
Selecting All Rows with Certain Data | Excel Discussion (Misc queries) | |||
Selecting All Rows with Certain Data | Excel Discussion (Misc queries) | |||
after selecting 50 rows of a column i can't reference the cells in the rows | New Users to Excel | |||
Selecting rows from cells | Excel Discussion (Misc queries) |