Automatic sorting or rows based on the value Closed

Question

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...					


Configuration: Windows XP / Safari 534.3

venkat1926 · Answer

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.

SAM · Answer

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..

venkat1926 · Answer

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.

venkat1926 · Answer

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

sam · Answer

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.

venkat1926 · Answer

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

Automatic sorting or rows based on the value

6 responses

Similar discussions

Subscribe To Our Newsletter!