• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Sorting data in worksheet separately

VDS

Member
@ Dear All,

Wishing all of you a very happy new year 2014. I attach herewith a printscreen file. Here, Column J gives two options. Active & Closed.

What I require is data to be sorted in two worksheets according to category "Active" & "Closed" each time when new record is entered / modified /deleted. But data entry is done only in the worksheet BG Bull" and completely display. This will avoid cut copy up to a great extent.


VDS
 

Attachments

  • upload_2013-12-30_14-17-8.png
    upload_2013-12-30_14-17-8.png
    60.9 KB · Views: 52
@ Huli Sir,

Thanks for your reply. The answer provided is matching with my requirement. But I am not expert in VB and the data being modified by multiple users in network. So how can macro be applied ? Sir, is it possible to do any other function ?


VDS
 
Hi VDS,

I see two options:
  1. Using PivotTable (dynamic table)
  2. Using non-dynamic table (requires manually adjusting its size each time)
A sample file would be helpful. By the way, what Excel version are you using?
 
@ Xiq,

I am using excel 2003 version. (11.8169.8172) SP3

Sir, file uploading is not possible as the server settings does not allow me to do so.

However, I will try to do through different source shortly

VDS
 
@VDS,

Happy New Year !

Hey I tried for your problem and came up with this macro code.

Code:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Dim wsd As Worksheet
Dim wsa As Worksheet
Dim wsc As Worksheet
Application.ScreenUpdating = False
Set wsd = Sheets("Data")
Set wsa = Sheets("Active")
Set wsc = Sheets("Closed")
If Not Intersect(Target, Range("D2:D65536")) Is Nothing Then
  If Target.Value = "Active" Or Target.Value = "active" Then
  erow = Target.Row
  MsgBox erow
  
  numberofrows = wsa.Range("A65536").End(xlUp).Row
  
  For i = 1 To numberofrows
  If wsd.Cells(erow, 1) = wsa.Cells(i, 1) Then
  Exit Sub
  End If
  Next
  
  wsd.Range("A" & erow & ":A" & erow).EntireRow.Copy wsa.Range("A" & numberofrows + 1)
  wsa.Activate
  ActiveSheet.Range("A2:D" & numberofrows + 1).Select
  ActiveWorkbook.Worksheets("Active").Sort.SortFields.Clear
  ActiveWorkbook.Worksheets("Active").Sort.SortFields.Add Key:=Range("C2:C" & numberofrows + 1), _
  SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
  With ActiveWorkbook.Worksheets("Active").Sort
  .SetRange Range("A2:D" & numberofrows + 1)
  .Header = xlYes
  .MatchCase = False
  .Orientation = xlTopToBottom
  .SortMethod = xlPinYin
  .Apply
  End With
  wsd.Activate
  
  Else
  If Target.Value = "Closed" Or Target.Value = "closed" Then
  erow = Target.Row
  numberofrow = wsc.Range("A65536").End(xlUp).Row
  For i = 1 To numberofrow
  If wsd.Cells(erow, 1) = wsc.Cells(i, 1) Then
  Exit Sub
  End If
  Next
  
  wsd.Range("A" & erow & ":A" & erow).EntireRow.Copy wsc.Range("A" & numberofrow + 1)
  wsc.Activate
  ActiveSheet.Range("A2:D" & numberofrow + 1).Select
  ActiveWorkbook.Worksheets("Closed").Sort.SortFields.Clear
  ActiveWorkbook.Worksheets("Closed").Sort.SortFields.Add Key:=Range("C2:C" & numberofrow + 1), _
  SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
  With ActiveWorkbook.Worksheets("Closed").Sort
  .SetRange Range("A2:D" & numberofrow + 1)
  .Header = xlYes
  .MatchCase = False
  .Orientation = xlTopToBottom
  .SortMethod = xlPinYin
  .Apply
  End With
  wsd.Activate
End If
End If
  
  
  End If
End Sub

Place this code in your data sheet window. Do not make any module.
It has a limitation. After entering Active/closed you have to select the cells once.

Just try it.

Regards!
 
@Hai Somendra

Wish you a very happy and prosperous new year to you and all your members.

I think both of us started this in forum during the same period but you are much ahead of me.

Best wishes for you to go ahead.

I am not able to understand this. Could you please explain so that the data should not be affected.


VDS
 
@VDS

Thank for your wishes.

First of all there is nothing as ahead or after in knowledge sharing. I am still learning Excel. Last few month I was free so utlized the time in learning by helping. As @Hui says
"Believe me that you'll learn more about Excel by contributing to answering questions here than you will by any other method."

Now coming to my code, since I dont have you data. I created a workbook with three sheets : Data, Active, Closed.
In Data I used 4 columns A1: Sr. No. B1: Name C1: Age D1: Status.
Now this code will do the shifting of Active rows and closed rows in respective sheets. It will check if the same data is already entered there or not with ref. to Sr. no. data and than sort it with Age data.

For applying this to your data, I would suggest you first to make a copy of your workbook and experiment on it, as the Changes made by a macro are irreversible.
Secondly, you have to change refrences made in my code and sheet name w.r.t. to your sorting and unique number for not allowing duplicacy of data.

Just advise if you need more explanation on this.

Regards!
 
@SOMENDRA,

Many thanks for your reply. The live data has already posted in my earlier post titled " How to Use Autofilter/ Sorting in Multiple files." However, the way of putting query was different, that may be reason why I am not able to present in the right way. Since you come with a solution, just go through it and request you to correct it with the code.

VDS
 
@VDS

[quote =VDS] What I require is data to be sorted in two worksheets according to category "Active" & "Closed" each time when new record is entered / modified /deleted. But data entry is done only in the worksheet BG Bull" and completely display. This will avoid cut copy up to a great extent.

/
As per your this requirement please find the attached file. No usage of macro only INDEX formula.

Just advise if you have any issue.

Regards!
 

Attachments

  • VDS.xlsm
    25.2 KB · Views: 57
@ Hai somendra,

Good morning. Can you convert the same into Excel 2003 ? In my house it opens. In my office, it don't have xlms converter. Server crashed and not able to open for 2-3 days.


VDS
 
@VDS

Hey just find the 2003 version of the file. Just check and advise if any issue. You may have to adjust range in the formulas to suit your requiremnt.

Regards!
 

Attachments

  • VDS(1).xls
    54.5 KB · Views: 41
@Somendra,

The file opens okay and formulas too. While saving, the entire data displays #Name?. In Master copy, data is same.

Can you suggest any reasons.

VDS
 
@ Somendra,

Not any named range, just a plain data. While going through the functions there is no "IFEROR" in Excel 2003. It is infact "ISERROR". So, is it the same command line ?


VDS
 
@VDS
Use below formula
In Active Sheet: in A2 :-> =IF(ISERROR(INDEX(Data!$A$2:$D$15,SMALL(IF(Data!$D$2:$D$15="Active",ROW(Data!$A$2:$A$15)-ROW(Data!$A$2)+1),ROWS(A$2:A2)),COLUMN())),"",INDEX(Data!$A$2:$D$15,SMALL(IF(Data!$D$2:$D$15="Active",ROW(Data!$A$2:$A$15)-ROW(Data!$A$2)+1),ROWS(A$2:A2)),COLUMN()))

In Closed Sheet: in A2 :-> =IF(ISERROR(INDEX(Data!$A$2:$D$15,SMALL(IF(Data!$D$2:$D$15="Closed",ROW(Data!$A$2:$A$15)-ROW(Data!$A$2)+1),ROWS(A$2:A2)),COLUMN())),"",INDEX(Data!$A$2:$D$15,SMALL(IF(Data!$D$2:$D$15="Closed",ROW(Data!$A$2:$A$15)-ROW(Data!$A$2)+1),ROWS(A$2:A2)),COLUMN()))

Regards!
 
@ Somendra,

Big hats off to you gentleman & this is Brilliant. This works perfectly in the test file. In fact this is even an array command. I will check the formula gradually in the live data before it become normal and let you know to clear all my doubts.


VDS
 
@Somendra,

In the test file, it was working okay upto 15 rows. When I try to add data for more than 15 rows, no result is coming. The actual data in my company is more than 150. I tried my level best, but not getting through. How can I give the live data ?

Could u pls help me out.

VDS
 
Back
Top