INSERT AND ORDER THE COLUMN
Solved/Closed
ssmzulfikar
-
Feb 15, 2010 at 04:55 AM
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 - Feb 16, 2010 at 08:00 AM
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 - Feb 16, 2010 at 08:00 AM
Related:
- INSERT AND ORDER THE COLUMN
- Insert tick in word - Guide
- What does the insert key do - Guide
- How to insert @ on a laptop - Guide
- Disk boot failure insert system disk and press enter - Guide
- How to insert photo in word for resume - Guide
6 responses
venkat1926
Posts
1863
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
811
Feb 15, 2010 at 05:34 AM
Feb 15, 2010 at 05:34 AM
you have posed small amount of data . it is good. what should be the result data.
Thanks for your respond.
the result is the column want to reorder. the balance not equal with column "A" come under blank cell or down to end of the list.
Because if manually one by one do then it takes more time to finish.
please help me !
the result is the column want to reorder. the balance not equal with column "A" come under blank cell or down to end of the list.
Because if manually one by one do then it takes more time to finish.
please help me !
rizvisa1
Posts
4478
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
766
Feb 15, 2010 at 07:08 AM
Feb 15, 2010 at 07:08 AM
This is your data
A B C D E
1 U 3 10 X
2 G 11 5 Y
3 A 2 4 Z
4 B 4 4 L
5 C 1 3 M
6 F 7 20 N
7 H 10 18 P
After this re-ordering that you want, how would the data look like. Use the above example to show how data would look like once reorder is done
A B C D E
1 U 3 10 X
2 G 11 5 Y
3 A 2 4 Z
4 B 4 4 L
5 C 1 3 M
6 F 7 20 N
7 H 10 18 P
After this re-ordering that you want, how would the data look like. Use the above example to show how data would look like once reorder is done
venkat1926
Posts
1863
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
811
Feb 15, 2010 at 08:05 PM
Feb 15, 2010 at 08:05 PM
It would have been helpful if you had given the result data as suggested by rizvi.
any how I think you are familiar with macros vba etc
AS YOU MESSING UP WITH THE DATA BY USING THE MACRO SOME WHERE SAFELY.
ALSO COPY THE DATA IN SHEET 3 OF THIS FILE
I have added column heading in the first row.
the macro is
the reulting sheet1 will be
HDNG1 HDNG2 HDNG3 HDNG4 HDNG5
4 B 4 4 L
7 H 10 18 P
6 F 7 20 N
5 C 1 3 M
3 A 2 4 Z
2 G 11 5 Y
1 U 3 10 X
any how I think you are familiar with macros vba etc
AS YOU MESSING UP WITH THE DATA BY USING THE MACRO SOME WHERE SAFELY.
ALSO COPY THE DATA IN SHEET 3 OF THIS FILE
I have added column heading in the first row.
the macro is
Sub TEST()
Dim j As Integer, k As Integer
Worksheets("sheet1").Cells.Clear
Worksheets("sheet3").Cells.Copy Worksheets("sheet1").Range("A1")
Worksheets("sheet1").Activate
j = Range("A1").End(xlDown).Row
'j is the last row
For k = j To 2 Step -1
If Cells(k, "c") <> Cells(k, "a") Then
Cells(k, "a").EntireRow.Copy Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)
Cells(k, "a").EntireRow.Delete
End If
Next k
End Sub
the reulting sheet1 will be
HDNG1 HDNG2 HDNG3 HDNG4 HDNG5
4 B 4 4 L
7 H 10 18 P
6 F 7 20 N
5 C 1 3 M
3 A 2 4 Z
2 G 11 5 Y
1 U 3 10 X
Hello,
the data after reorder look like as follows
A B C D E
1 U 1 3 M
2 G 2 4 Z
3 A 3 10 X
4 B 4 4 L
5 C
6 F
7 H
10 18 P (Column "C","D","E")
11 5 Y
7 20 N
please help me.
the data after reorder look like as follows
A B C D E
1 U 1 3 M
2 G 2 4 Z
3 A 3 10 X
4 B 4 4 L
5 C
6 F
7 H
10 18 P (Column "C","D","E")
11 5 Y
7 20 N
please help me.
rizvisa1
Posts
4478
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
766
Feb 15, 2010 at 10:42 PM
Feb 15, 2010 at 10:42 PM
Is your sort correct or is it like this
does this line appear at the bottom or third from bottom
"7 20 N"
A ### B ### C ### D ### E 1 ### U ### 1 ### 3 ### M 2 ### G ### 2 ### 4 ### Z 3 ### A ### 3 ### 10 ### X 4 ### B ### 4 ### 4 ### L 5 ### C ### - ### - ### - 6 ### F ### - ### - ### - 7 ### H ### 7 ### 20 ### N - ### - ### 10 ### 18 ### P - ### - ### 11 ### 5 ### Y
does this line appear at the bottom or third from bottom
"7 20 N"
Didn't find the answer you are looking for?
Ask a question
yes rizvisa
that is want to come A-Z by column "C"
help me
that is want to come A-Z by column "C"
help me
rizvisa1
Posts
4478
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
766
Feb 16, 2010 at 08:00 AM
Feb 16, 2010 at 08:00 AM
Ok here is the macro that should do it
Assumptions.
1. Col A will have the max number of rows
2. Col A and Col C are numbers
3. Data range A:E can be sorted on A and data Range C:E can be sorted on C
4. Data starts at row 2
5. Here i have taken liberty from your requirement. I did not knew what you would have done if there is a missing value in A and a lower value in C. I am presuming that you want to push A and B down (as you were doing for C:E). If you dont want that remove the "else" clause that have a comment " 'cell a > cell c". Remove all lines starting from Else till a line above END IF (leave end if, but remove else)
As Venkat would wisely point every time, please make sure that you backup your data some where before testing this macro.
Assumptions.
1. Col A will have the max number of rows
2. Col A and Col C are numbers
3. Data range A:E can be sorted on A and data Range C:E can be sorted on C
4. Data starts at row 2
5. Here i have taken liberty from your requirement. I did not knew what you would have done if there is a missing value in A and a lower value in C. I am presuming that you want to push A and B down (as you were doing for C:E). If you dont want that remove the "else" clause that have a comment " 'cell a > cell c". Remove all lines starting from Else till a line above END IF (leave end if, but remove else)
As Venkat would wisely point every time, please make sure that you backup your data some where before testing this macro.
Sub arrangeData()
Dim lMaxRow As Long
Dim iMaxRowsInCol As Integer ' which col has max rows
Dim lRowCounter As Long
Dim lCellA, lCellC As Integer
iMaxRowsInCol = 1
lMaxRows = Cells(65536, iMaxRowsInCol).End(xlUp).Row
Range("A1:E" & lMaxRows).Select
Selection.Sort Key1:=Range("A2"), _
Order1:=xlAscending, _
Header:=xlYes, _
OrderCustom:=1, _
MatchCase:=False, _
Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
Range("C1:E" & lMaxRows).Select
Selection.Sort Key1:=Range("C2"), _
Order1:=xlAscending, _
Header:=xlYes, _
OrderCustom:=1, _
MatchCase:=False, _
Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
lRowCounter = 2
Do While (lRowCounter < lMaxRows)
lCellA = Cells(lRowCounter, 1)
lCellC = Cells(lRowCounter, 3)
If (lCellA = lCellC) Then
'nothing to see folks, move on
ElseIf (lCellA < lCellC) Then
'push down C:E
Range(Cells(lRowCounter, 3), Cells(lRowCounter, 5)).Select
Selection.Insert Shift:=xlDown
Else
'cell a > cell c
'push down A:B
Range(Cells(lRowCounter, 1), Cells(lRowCounter, 2)).Select
Selection.Insert Shift:=xlDown
'just added blank row in col A
lMaxRows = lMaxRows + 1
End If
lRowCounter = lRowCounter + 1
Loop
End Sub
