Automatic sorting or rows based on the value
Closed
SAM
-
Oct 4, 2010 at 06:47 PM
venkat1926 Posts 1863 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 - Oct 16, 2010 at 08:41 AM
venkat1926 Posts 1863 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 - Oct 16, 2010 at 08:41 AM
Related:
- Automatic sorting or rows based on the value
- Nvidia automatic driver detection tool - Guide
- Automatic redial samsung - Guide
- Based on the values in cells b77 b81 c77 - Excel Forum
- We couldn't find an account matching the login info you entered, but found an account that closely matches based on your login history. - Facebook Forum
- Excel macro to create new sheet based on value in cells - Guide
6 responses
venkat1926
Posts
1863
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
811
Oct 4, 2010 at 09:10 PM
Oct 4, 2010 at 09:10 PM
quote
ransactions of that amount should be grouped..
unquote
this is not clear . suppose you enter 1000 in column B you want to group all the 100s or the name (column A) of this 1000 figure.. how do you want it to be grouped. do you want just sorting according to names or amounts
or
do you want filter the data of that name and show it in another sheet without disturbing the original data.
ransactions of that amount should be grouped..
unquote
this is not clear . suppose you enter 1000 in column B you want to group all the 100s or the name (column A) of this 1000 figure.. how do you want it to be grouped. do you want just sorting according to names or amounts
or
do you want filter the data of that name and show it in another sheet without disturbing the original data.
Thank you for the reply Venkat.
Sorry for not being clear. First let me explain wat I do: the excel sheet which i work on has the following data,
Customer name, Ordering bank, Amount, Processor number, Transaction number
Here is wat i do, I need to sort the transaction of a particular amount, say for example $5000. When i select $5000 from the drop-down list, all the transactions with that amount will be selected.
Looking for:
I want to create a field so that once i enter the amount in the field, (ex. $5000) then all the transactions of that amount should be selected.
Thank you in advance..
Sorry for not being clear. First let me explain wat I do: the excel sheet which i work on has the following data,
Customer name, Ordering bank, Amount, Processor number, Transaction number
Here is wat i do, I need to sort the transaction of a particular amount, say for example $5000. When i select $5000 from the drop-down list, all the transactions with that amount will be selected.
Looking for:
I want to create a field so that once i enter the amount in the field, (ex. $5000) then all the transactions of that amount should be selected.
Thank you in advance..
venkat1926
Posts
1863
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
811
Oct 5, 2010 at 06:11 AM
Oct 5, 2010 at 06:11 AM
I am suggesting following. see whether it serves your prupose
Your data is like this (a1 down and right )
Customer Name Amount
abcd 122 - - - - -
baad 1144 - - - - -
nndd 1255 - - - - -
kllai 125654 - - - - -
kkdi 44 - - - - -
kjeu 2545 - - - - -
jasdi 1442 - - - - -
suppose in the next row to the last row in column A you type a name
in column B of same row you type 122 then you want only those rows containing 122 should be available. I have suggested you get this in sheset 2.
Please do this experiment
you right click sheet 1 tab and click view code. in the resulting window you copy this event code
now you go to the row next to sample data (your data consists of 8 rows including heading row). go to A9 and type some name
go to B9 and type 122
see sheet 2 now. will this serve your purpose
next time you add some data in row no. 10 the sheet 2 will be cleared and the new filtered data corresponding to B10 will be available in sheet 2.
based on this you are free to rephrase your question.
Your data is like this (a1 down and right )
Customer Name Amount
abcd 122 - - - - -
baad 1144 - - - - -
nndd 1255 - - - - -
kllai 125654 - - - - -
kkdi 44 - - - - -
kjeu 2545 - - - - -
jasdi 1442 - - - - -
suppose in the next row to the last row in column A you type a name
in column B of same row you type 122 then you want only those rows containing 122 should be available. I have suggested you get this in sheset 2.
Please do this experiment
you right click sheet 1 tab and click view code. in the resulting window you copy this event code
Private Sub Worksheet_Change(ByVal Target As Range)
Dim r As Range, j As Double
Worksheets("sheet2").Cells.Clear
If Target.Column <> Range("B1").Column Then Exit Sub
Set r = Range("a1").CurrentRegion
j = Target.Value
r.AutoFilter field:=Range("B1").Column, Criteria1:=j
r.Cells.SpecialCells(xlCellTypeVisible).Copy
Worksheets("sheet2").Range("A1").PasteSpecial
Me.AutoFilterMode = False
Application.CutCopyMode = False
End Sub
now you go to the row next to sample data (your data consists of 8 rows including heading row). go to A9 and type some name
go to B9 and type 122
see sheet 2 now. will this serve your purpose
next time you add some data in row no. 10 the sheet 2 will be cleared and the new filtered data corresponding to B10 will be available in sheet 2.
based on this you are free to rephrase your question.
Hello Venkat.
Please excuse me for the delay. I Sincerely thank you for the code u suggested me.
It was very help ful.
But the purpose was not served.
I wanted to create a field (for eg. on the top of the amount column, so that once I enter the amount in the field, all the enteries corresponding to the amount should be filtered. and this should be in the same sheet.
Could you please provide me your e-mail address so that I can send you a sample sheet.
Thank you in advance.
Please excuse me for the delay. I Sincerely thank you for the code u suggested me.
It was very help ful.
But the purpose was not served.
I wanted to create a field (for eg. on the top of the amount column, so that once I enter the amount in the field, all the enteries corresponding to the amount should be filtered. and this should be in the same sheet.
Could you please provide me your e-mail address so that I can send you a sample sheet.
Thank you in advance.
venkat1926
Posts
1863
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
811
Oct 15, 2010 at 08:48 PM
Oct 15, 2010 at 08:48 PM
giving my eamil address will not be permitted by the Administrator. You have to explain in detail
your main data is in sheet 1
in sheet 2 you have A1 as amount
suppose in A2 you type (or choose from dropdown box)
122
what do you want and where
i suppose it will be in the second row (I used comma as delimiter)
122,abcd,-,-,-,-
is this what you want
give one or two examples
your main data is in sheet 1
in sheet 2 you have A1 as amount
suppose in A2 you type (or choose from dropdown box)
122
what do you want and where
i suppose it will be in the second row (I used comma as delimiter)
122,abcd,-,-,-,-
is this what you want
give one or two examples
Didn't find the answer you are looking for?
Ask a question
Thank you Venkat,
For example in sheet 1 i have 10 columns (A to J) titled as Customer, bank, amount, transaction no., Obligation number and so-on.... Correspondingly I have entries for each Column. Lets say I have 50 entries.
Customer Name Bank Amount Transaction number Obligation #
Sam SBI $500 652118 116ss571
John JP Morgan $300 26581 2511fgr3
Venkat Wells Fargo $200 223654 21544rw
Henry Barclays Bank $150 542264 512d135
Mishel SBH $150 641281 54125se1
Vijay HSBC 550 623548 5415dw1
Now I want to create a Rectangular Field on top of Amount Column in Sheet 1 such that when I enter the amount in the field which we have created, should automatically sort the related entries.
For example if I enter 150 in the field, the rows corresponding to that amount should be sorted.. i.e. :
Henry Barclays Bank $150 542264 512d135
Mishel SBH $150 641281 54125se1
Note Everything should be in sheet 1
Thanks in advance.
For example in sheet 1 i have 10 columns (A to J) titled as Customer, bank, amount, transaction no., Obligation number and so-on.... Correspondingly I have entries for each Column. Lets say I have 50 entries.
Customer Name Bank Amount Transaction number Obligation #
Sam SBI $500 652118 116ss571
John JP Morgan $300 26581 2511fgr3
Venkat Wells Fargo $200 223654 21544rw
Henry Barclays Bank $150 542264 512d135
Mishel SBH $150 641281 54125se1
Vijay HSBC 550 623548 5415dw1
Now I want to create a Rectangular Field on top of Amount Column in Sheet 1 such that when I enter the amount in the field which we have created, should automatically sort the related entries.
For example if I enter 150 in the field, the rows corresponding to that amount should be sorted.. i.e. :
Henry Barclays Bank $150 542264 512d135
Mishel SBH $150 641281 54125se1
Note Everything should be in sheet 1
Thanks in advance.
venkat1926
Posts
1863
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
811
Oct 16, 2010 at 08:41 AM
Oct 16, 2010 at 08:41 AM
quote
Now I want to create a Rectangular Field on top of Amount Column in Sheet
unquote
this is not clear to me
where will you enter the amount you require to find . t his is not explicit
I am giving you a sample file with sheet 1 only. download this file from
http://www.speedyshare.com/files/24719773/sam_101016.xls
do not type numbers like $150. just type 150 and you can format as you like
the basic entry will remain as 150
enter any number in I1. see what happens. just as number e.g 150
type another number in I1 see what happens
The EVENT CODE which does this you can see rightlcick sheet1 tab ad click view code.
if you want to undo this run the macro "undo" which is in the standard module.
your comments please
the event code is
the code undo in the standard module is
Now I want to create a Rectangular Field on top of Amount Column in Sheet
unquote
this is not clear to me
where will you enter the amount you require to find . t his is not explicit
I am giving you a sample file with sheet 1 only. download this file from
http://www.speedyshare.com/files/24719773/sam_101016.xls
do not type numbers like $150. just type 150 and you can format as you like
the basic entry will remain as 150
enter any number in I1. see what happens. just as number e.g 150
type another number in I1 see what happens
The EVENT CODE which does this you can see rightlcick sheet1 tab ad click view code.
if you want to undo this run the macro "undo" which is in the standard module.
your comments please
the event code is
Private Sub Worksheet_Change(ByVal Target As Range)
Dim j As Double, cfind As Range, r As Range
Dim add As String
If Target.Address <> "$I$1" Then Exit Sub
j = Target.Value
Set r = Range(Range("C2"), Range("C2").End(xlDown))
Set cfind = r.Cells.Find(what:=j, lookat:=xlWhole)
If Not cfind Is Nothing Then
add = cfind.Address
cfind.EntireRow.Copy
Cells(Rows.Count, "A").End(xlUp).Offset(1, 0).PasteSpecial
Else
MsgBox "that number is not available"
GoTo eend
End If
Do
Set cfind = r.Cells.FindNext(cfind)
If cfind Is Nothing Then Exit Do
'MsgBox cfind.Address
'MsgBox add
If cfind.Address = add Then Exit Do
cfind.EntireRow.Copy
Cells(Rows.Count, "A").End(xlUp).Offset(1, 0).PasteSpecial
Loop
eend:
Application.CutCopyMode = False
End Sub
the code undo in the standard module is
Sub undo()
Dim r As Range
Set r = Range(Range("A14"), Cells(Rows.Count, "A"))
r.EntireRow.Delete
End Sub
