Automatic sorting or rows based on the value

Closed
-
Posts
1864
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
-
Hello,

I basically work on excel which has huge data.. I need to search a transactions based on the amount of the transaction. As of now I'm using the drop down list to select the transaction. But since the data is huge, I want to create a field so that when i enter the amount in the field all transactions of that amount should be grouped..

Example:
Customer Name Amount
abcd 122 - - - - -
baad 1144 - - - - -
nndd 1255 - - - - -
kllai 125654 - - - - -
kkdi 44 - - - - -
kjeu 2545 - - - - -
jasdi 1 442 - - - - -
so on... so on...


6 replies

Posts
1864
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
804
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.
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..
Posts
1864
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
804
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


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.
Posts
1864
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
804
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
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.
Posts
1864
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
804
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

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