Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Data sorting to a new sheet
I'm not even sure this can be done. What am trying to do is sort data from
Sheet 1 and place the sorted data on sheet 2 & 3. Here is what the data would look like: 1 BLOCKNAME ADDRESS1 STROBE_CIRCUIT_INFO SPEAKER_CIRCUIT_INFO 2 INIT_SD 5.5.001 3 NAC_110 V2-01 A4-03 4 INIT_SD 5.5.021 5 INIT_SD 5.6.001 6 NAC_110 V3-01 A1-23 7 NAC_110 V1-01 A1-01 What I need is all Rows with something in Address1 column goes to sheet1 (in order) and all Rows with something in column Strobe_circuit_info goes to sheet 3(in order). I also need them to be grouped on the other sheet (2 & 3). ex. 3 NAC_110 V1-01 A1-01 30 NAC_110 V2-01 A4-03 57 NAC_110 V3-01 A1-23 so if the if there is a device V1-02 it would go in row 4 and so on, same for sheet 2 Is this possible or am i really reaching Thanks for your input |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Data sorting to a new sheet
This doesn't sound too difficult. I just confused by the sheet numbers in
your description 1) what sheet is th original data located on? 2) I think you incorrectly said the data goes on sheet 1 instead of sheet 2. 3) You said to put the data inorder on sheet 1, but then said to sort data. Which is correct? 4) Are yuor columns A toc D? "Novice Lee" wrote: I'm not even sure this can be done. What am trying to do is sort data from Sheet 1 and place the sorted data on sheet 2 & 3. Here is what the data would look like: 1 BLOCKNAME ADDRESS1 STROBE_CIRCUIT_INFO SPEAKER_CIRCUIT_INFO 2 INIT_SD 5.5.001 3 NAC_110 V2-01 A4-03 4 INIT_SD 5.5.021 5 INIT_SD 5.6.001 6 NAC_110 V3-01 A1-23 7 NAC_110 V1-01 A1-01 What I need is all Rows with something in Address1 column goes to sheet1 (in order) and all Rows with something in column Strobe_circuit_info goes to sheet 3(in order). I also need them to be grouped on the other sheet (2 & 3). ex. 3 NAC_110 V1-01 A1-01 30 NAC_110 V2-01 A4-03 57 NAC_110 V3-01 A1-23 so if the if there is a device V1-02 it would go in row 4 and so on, same for sheet 2 Is this possible or am i really reaching Thanks for your input |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Data sorting to a new sheet
Morning
In regards to your questions Joel: 1)the original data is imported to sheet 1 2)you are correct it should be sheet 2 3)both, the data needs to be in order on the sheets 2 & 3 but it also needs to be put in groups. 4) there are more than four columns, but column b and column c are the main sorting column. Every Init device will have something in the Address1 column and every Nac device will have something in the Strobe_Circuit_Info Column. A B C D 1 BLOCKNAME ADDRESS1 STROBE_CIRCUIT_INFO SPEAKER_CIRCUIT_INFO 2 INIT_SD 5.5.001 3 NAC_110 V2-01 A4-03 4 INIT_SD 5.5.021 5 INIT_SD 5.6.001 6 NAC_110 V3-01 A1-23 7 NAC_110 V1-01 A1-01 this is what it was suppose to look like Thanks "Joel" wrote: This doesn't sound too difficult. I just confused by the sheet numbers in your description 1) what sheet is th original data located on? 2) I think you incorrectly said the data goes on sheet 1 instead of sheet 2. 3) You said to put the data inorder on sheet 1, but then said to sort data. Which is correct? 4) Are yuor columns A toc D? "Novice Lee" wrote: I'm not even sure this can be done. What am trying to do is sort data from Sheet 1 and place the sorted data on sheet 2 & 3. Here is what the data would look like: 1 BLOCKNAME ADDRESS1 STROBE_CIRCUIT_INFO SPEAKER_CIRCUIT_INFO 2 INIT_SD 5.5.001 3 NAC_110 V2-01 A4-03 4 INIT_SD 5.5.021 5 INIT_SD 5.6.001 6 NAC_110 V3-01 A1-23 7 NAC_110 V1-01 A1-01 What I need is all Rows with something in Address1 column goes to sheet1 (in order) and all Rows with something in column Strobe_circuit_info goes to sheet 3(in order). I also need them to be grouped on the other sheet (2 & 3). ex. 3 NAC_110 V1-01 A1-01 30 NAC_110 V2-01 A4-03 57 NAC_110 V3-01 A1-23 so if the if there is a device V1-02 it would go in row 4 and so on, same for sheet 2 Is this possible or am i really reaching Thanks for your input |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Data sorting to a new sheet
Sub MoveData()
'clear sheet 2 and copy header row With Sheets(2) Cells.ClearContents Sheets(1).Rows(1).Copy Destination:=.Rows(1) End With 'clear sheet 3 and copy header row With Sheets(3) Cells.ClearContents Sheets(1).Rows(1).Copy Destination:=.Rows(1) End With Sh1RowCount = 2 Sh2RowCount = 2 Sh3RowCount = 2 With Sheets(1) Do While .Range("A" & Sh1RowCount) < "" If .Range("B" & Sh1RowCount) < "" Then .Row(Sh1RowCount).Copy _ Destination:=Sheets(2).Row(Sh2RowCount) Sh2RowCount = Sh2RowCount + 1 End If If .Range("C" & Sh1RowCount) < "" Then .Row(Sh1RowCount).Copy _ Destination:=Sheets(3).Row(Sh3RowCount) Sh3RowCount = Sh3RowCount + 1 End If Sh1RowCount = Sh1RowCount + 1 Loop End With 'Sort sheet 3 With Sheets(3) LastRow = .Range("A" & Rows.Count).End(xlUp).Row Set SortRange = .Rows("2:" & LastRow) SortRange.Sort _ Key1:=Range("A2"), _ Order1:=xlAscending, _ Key2:=Range("C2"), _ Order2:=xlAscending, _ Header:=xlNo End With End Sub "Novice Lee" wrote: Morning In regards to your questions Joel: 1)the original data is imported to sheet 1 2)you are correct it should be sheet 2 3)both, the data needs to be in order on the sheets 2 & 3 but it also needs to be put in groups. 4) there are more than four columns, but column b and column c are the main sorting column. Every Init device will have something in the Address1 column and every Nac device will have something in the Strobe_Circuit_Info Column. A B C D 1 BLOCKNAME ADDRESS1 STROBE_CIRCUIT_INFO SPEAKER_CIRCUIT_INFO 2 INIT_SD 5.5.001 3 NAC_110 V2-01 A4-03 4 INIT_SD 5.5.021 5 INIT_SD 5.6.001 6 NAC_110 V3-01 A1-23 7 NAC_110 V1-01 A1-01 this is what it was suppose to look like Thanks "Joel" wrote: This doesn't sound too difficult. I just confused by the sheet numbers in your description 1) what sheet is th original data located on? 2) I think you incorrectly said the data goes on sheet 1 instead of sheet 2. 3) You said to put the data inorder on sheet 1, but then said to sort data. Which is correct? 4) Are yuor columns A toc D? "Novice Lee" wrote: I'm not even sure this can be done. What am trying to do is sort data from Sheet 1 and place the sorted data on sheet 2 & 3. Here is what the data would look like: 1 BLOCKNAME ADDRESS1 STROBE_CIRCUIT_INFO SPEAKER_CIRCUIT_INFO 2 INIT_SD 5.5.001 3 NAC_110 V2-01 A4-03 4 INIT_SD 5.5.021 5 INIT_SD 5.6.001 6 NAC_110 V3-01 A1-23 7 NAC_110 V1-01 A1-01 What I need is all Rows with something in Address1 column goes to sheet1 (in order) and all Rows with something in column Strobe_circuit_info goes to sheet 3(in order). I also need them to be grouped on the other sheet (2 & 3). ex. 3 NAC_110 V1-01 A1-01 30 NAC_110 V2-01 A4-03 57 NAC_110 V3-01 A1-23 so if the if there is a device V1-02 it would go in row 4 and so on, same for sheet 2 Is this possible or am i really reaching Thanks for your input |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Data sorting to a new sheet
If I change the Sheet name (sheet 2) to ALD Would I change Sheets(2) to ALD?
"Joel" wrote: Sub MoveData() 'clear sheet 2 and copy header row With Sheets(2) Cells.ClearContents Sheets(1).Rows(1).Copy Destination:=.Rows(1) End With 'clear sheet 3 and copy header row With Sheets(3) Cells.ClearContents Sheets(1).Rows(1).Copy Destination:=.Rows(1) End With Sh1RowCount = 2 Sh2RowCount = 2 Sh3RowCount = 2 With Sheets(1) Do While .Range("A" & Sh1RowCount) < "" If .Range("B" & Sh1RowCount) < "" Then .Row(Sh1RowCount).Copy _ Destination:=Sheets(2).Row(Sh2RowCount) Sh2RowCount = Sh2RowCount + 1 End If If .Range("C" & Sh1RowCount) < "" Then .Row(Sh1RowCount).Copy _ Destination:=Sheets(3).Row(Sh3RowCount) Sh3RowCount = Sh3RowCount + 1 End If Sh1RowCount = Sh1RowCount + 1 Loop End With 'Sort sheet 3 With Sheets(3) LastRow = .Range("A" & Rows.Count).End(xlUp).Row Set SortRange = .Rows("2:" & LastRow) SortRange.Sort _ Key1:=Range("A2"), _ Order1:=xlAscending, _ Key2:=Range("C2"), _ Order2:=xlAscending, _ Header:=xlNo End With End Sub |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Data sorting to a new sheet
I tried it on my break. cleared my sheet 1 and then crashed I did the debug
button and it highlighted this section With Sheets(3) LastRow = .Range("A" & Rows.Count).End(xlUp).Row Set SortRange = .Rows("2:" & LastRow) SortRange.Sort _ Key1:=Range("A2"), _ Order1:=xlAscending, _ Key2:=Range("C2"), _ Order2:=xlAscending, _ Header:=xlNo End With "Joel" wrote: Sub MoveData() 'clear sheet 2 and copy header row With Sheets(2) Cells.ClearContents Sheets(1).Rows(1).Copy Destination:=.Rows(1) End With 'clear sheet 3 and copy header row With Sheets(3) Cells.ClearContents Sheets(1).Rows(1).Copy Destination:=.Rows(1) End With Sh1RowCount = 2 Sh2RowCount = 2 Sh3RowCount = 2 With Sheets(1) Do While .Range("A" & Sh1RowCount) < "" If .Range("B" & Sh1RowCount) < "" Then .Row(Sh1RowCount).Copy _ Destination:=Sheets(2).Row(Sh2RowCount) Sh2RowCount = Sh2RowCount + 1 End If If .Range("C" & Sh1RowCount) < "" Then .Row(Sh1RowCount).Copy _ Destination:=Sheets(3).Row(Sh3RowCount) Sh3RowCount = Sh3RowCount + 1 End If Sh1RowCount = Sh1RowCount + 1 Loop End With 'Sort sheet 3 With Sheets(3) LastRow = .Range("A" & Rows.Count).End(xlUp).Row Set SortRange = .Rows("2:" & LastRow) SortRange.Sort _ Key1:=Range("A2"), _ Order1:=xlAscending, _ Key2:=Range("C2"), _ Order2:=xlAscending, _ Header:=xlNo End With End Sub "Novice Lee" wrote: Morning In regards to your questions Joel: 1)the original data is imported to sheet 1 2)you are correct it should be sheet 2 3)both, the data needs to be in order on the sheets 2 & 3 but it also needs to be put in groups. 4) there are more than four columns, but column b and column c are the main sorting column. Every Init device will have something in the Address1 column and every Nac device will have something in the Strobe_Circuit_Info Column. A B C D 1 BLOCKNAME ADDRESS1 STROBE_CIRCUIT_INFO SPEAKER_CIRCUIT_INFO 2 INIT_SD 5.5.001 3 NAC_110 V2-01 A4-03 4 INIT_SD 5.5.021 5 INIT_SD 5.6.001 6 NAC_110 V3-01 A1-23 7 NAC_110 V1-01 A1-01 this is what it was suppose to look like Thanks "Joel" wrote: This doesn't sound too difficult. I just confused by the sheet numbers in your description 1) what sheet is th original data located on? 2) I think you incorrectly said the data goes on sheet 1 instead of sheet 2. 3) You said to put the data inorder on sheet 1, but then said to sort data. Which is correct? 4) Are yuor columns A toc D? "Novice Lee" wrote: I'm not even sure this can be done. What am trying to do is sort data from Sheet 1 and place the sorted data on sheet 2 & 3. Here is what the data would look like: 1 BLOCKNAME ADDRESS1 STROBE_CIRCUIT_INFO SPEAKER_CIRCUIT_INFO 2 INIT_SD 5.5.001 3 NAC_110 V2-01 A4-03 4 INIT_SD 5.5.021 5 INIT_SD 5.6.001 6 NAC_110 V3-01 A1-23 7 NAC_110 V1-01 A1-01 What I need is all Rows with something in Address1 column goes to sheet1 (in order) and all Rows with something in column Strobe_circuit_info goes to sheet 3(in order). I also need them to be grouped on the other sheet (2 & 3). ex. 3 NAC_110 V1-01 A1-01 30 NAC_110 V2-01 A4-03 57 NAC_110 V3-01 A1-23 so if the if there is a device V1-02 it would go in row 4 and so on, same for sheet 2 Is this possible or am i really reaching Thanks for your input |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Data sorting to a new sheet
the sheet names can be iether of the following
Sheets(1) - the first sheet in order in the workbook. the 2nd sheet would be sheets(2) Sheets("Sheet1") - the sheet name in double quotes. can be "ALD" sht = "ALD" - using a varible with the sheet name in double quotes. sheets(sht) - no double quote No sure why it crashed unless it has to do something with you changing sheet names. You may not have any data in column A in the first sheet. "Novice Lee" wrote: If I change the Sheet name (sheet 2) to ALD Would I change Sheets(2) to ALD? "Joel" wrote: Sub MoveData() 'clear sheet 2 and copy header row With Sheets(2) Cells.ClearContents Sheets(1).Rows(1).Copy Destination:=.Rows(1) End With 'clear sheet 3 and copy header row With Sheets(3) Cells.ClearContents Sheets(1).Rows(1).Copy Destination:=.Rows(1) End With Sh1RowCount = 2 Sh2RowCount = 2 Sh3RowCount = 2 With Sheets(1) Do While .Range("A" & Sh1RowCount) < "" If .Range("B" & Sh1RowCount) < "" Then .Row(Sh1RowCount).Copy _ Destination:=Sheets(2).Row(Sh2RowCount) Sh2RowCount = Sh2RowCount + 1 End If If .Range("C" & Sh1RowCount) < "" Then .Row(Sh1RowCount).Copy _ Destination:=Sheets(3).Row(Sh3RowCount) Sh3RowCount = Sh3RowCount + 1 End If Sh1RowCount = Sh1RowCount + 1 Loop End With 'Sort sheet 3 With Sheets(3) LastRow = .Range("A" & Rows.Count).End(xlUp).Row Set SortRange = .Rows("2:" & LastRow) SortRange.Sort _ Key1:=Range("A2"), _ Order1:=xlAscending, _ Key2:=Range("C2"), _ Order2:=xlAscending, _ Header:=xlNo End With End Sub |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Data sorting to a new sheet
Thanks for your help I will fiddle around with it this week end
"Joel" wrote: the sheet names can be iether of the following Sheets(1) - the first sheet in order in the workbook. the 2nd sheet would be sheets(2) Sheets("Sheet1") - the sheet name in double quotes. can be "ALD" sht = "ALD" - using a varible with the sheet name in double quotes. sheets(sht) - no double quote No sure why it crashed unless it has to do something with you changing sheet names. You may not have any data in column A in the first sheet. "Novice Lee" wrote: If I change the Sheet name (sheet 2) to ALD Would I change Sheets(2) to ALD? "Joel" wrote: Sub MoveData() 'clear sheet 2 and copy header row With Sheets(2) Cells.ClearContents Sheets(1).Rows(1).Copy Destination:=.Rows(1) End With 'clear sheet 3 and copy header row With Sheets(3) Cells.ClearContents Sheets(1).Rows(1).Copy Destination:=.Rows(1) End With Sh1RowCount = 2 Sh2RowCount = 2 Sh3RowCount = 2 With Sheets(1) Do While .Range("A" & Sh1RowCount) < "" If .Range("B" & Sh1RowCount) < "" Then .Row(Sh1RowCount).Copy _ Destination:=Sheets(2).Row(Sh2RowCount) Sh2RowCount = Sh2RowCount + 1 End If If .Range("C" & Sh1RowCount) < "" Then .Row(Sh1RowCount).Copy _ Destination:=Sheets(3).Row(Sh3RowCount) Sh3RowCount = Sh3RowCount + 1 End If Sh1RowCount = Sh1RowCount + 1 Loop End With 'Sort sheet 3 With Sheets(3) LastRow = .Range("A" & Rows.Count).End(xlUp).Row Set SortRange = .Rows("2:" & LastRow) SortRange.Sort _ Key1:=Range("A2"), _ Order1:=xlAscending, _ Key2:=Range("C2"), _ Order2:=xlAscending, _ Header:=xlNo End With End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Sorting data in one sheet | Excel Worksheet Functions | |||
Sorting Data from a different sheet | Excel Worksheet Functions | |||
sorting data from one sheet to another | Excel Worksheet Functions | |||
SORTING DATA TO ANOTHER SHEET | Excel Worksheet Functions | |||
SORTING DATA TO ANOTHER SHEET | Excel Worksheet Functions |