Data Sorting

[gimran]

Dear,

I have a data which looks like below:



Now in a next sheet I want to sort all the data which are answered (E column). using which formula i get all the information which are answered.

Note: the data range is dynamic.

Below is a sample of the after sorting.

 

[S.H.A.D.O.]

Hi gimran,

Do you mean something like this...

Sub Srt_On_2_Cols()
    ActiveSheet.Range("A2").CurrentRegion.Select
    ActiveSheet.Sort.SortFields.Clear
    ActiveSheet.Range("A2" & CurrentRegion).Sort Key1:=Range("E2"), Order1:=xlAscending, Header:=True, _
        Key2:=Range("C2"), Order2:=xlAscending, Header:=True, Orientation:=xlTopToBottom
    ActiveSheet.Range("A1").Select
End Sub

I hope this helps!

 

[XLearner]

Hi,

Now in a next sheet I want to sort all the data which are answered (E column). using which formula i get all the information which are answered.

Not really clear to me. Is the task to :

1. Extract all records in Table 1 where Call Status = Answered
2. "Copy" the extracted records in a 2nd Table (next Sheet) sorted by Date/Time

If YES to #1 and #2, do you really want to "copy" all columns (you seem to have a lot) from Table 1 to Table 2?

Note: the data range is dynamic

Looking at your pictures you seem to use Tables where ranges (i.e. [Call Status]) are dynamically adjusted to the size of the Table. So I wonder if I missed something or if you use formulas (w/ Named variables) somewhere else to adjust the size of some ranges. Could you clarify this as well please?

FYI: I have a couple of array formulas doing #1 and #2. Not sure this is the best option with a large data set though. If you could give us a sense re. how many rows are/will be involved maybe someone could suggest something more efficient.

Regards
XLearner

 

[gimran]

Thank you very much for the response. I want to extract all data in a separate sheet which are answered. Now all the data are sorted in three label answered, abandon and noans. I want to extract all data which are answered and dates has to be sorted.

The number of rows can go as high as 600K.

Hi,



Not really clear to me. Is the task to :

1. Extract all records in Table 1 where Call Status = Answered
2. "Copy" the extracted records in a 2nd Table (next Sheet) sorted by Date/Time

If YES to #1 and #2, do you really want to "copy" all columns (you seem to have a lot) from Table 1 to Table 2?


Looking at your pictures you seem to use Tables where ranges (i.e. [Call Status]) are dynamically adjusted to the size of the Table. So I wonder if I missed something or if you use formulas (w/ Named variables) somewhere else to adjust the size of some ranges. Could you clarify this as well please?

FYI: I have a couple of array formulas doing #1 and #2. Not sure this is the best option with a large data set though. If you could give us a sense re. how many rows are/will be involved maybe someone could suggest something more efficient.

Regards
XLearner

 

[XLearner]

Hi,

"The number of rows can go as high as 600K". I doubt array formulas can manage this efficiently given that you want to extract (in addition) all columns (that's what I understand when you say "I want to extract all data").

Which version of Excel do you run?

Regards
XLearner

 

[gimran]

I use Excel 2010 in windows 10.

Hi,

Which version of Excel do you run?

Regards
XLearner

 

[XLearner]

Excel 2010 is fine re. the idea I had earlier to update the formulas I have. I won't have time to work on this in the next hours though... Expect an update laterrrr today

 

[XLearner]

Hi,

With the AGGREGATE function (Excel >/= 2010). I doubt this is a viable option with potentially 600K rows. Let us know please...

Assumptions

• Source Table is named tblSource<strike></strike>
• Source Table is already filtered. In your case on [Call Status]=Answered
• Answered calls have valid dates (blanks/invalid dates not checked - doable if necessary) in Source Table
• Column titles are sticktly the same in Source and Destination tables.The order of the columns can be different as the formulas in the Destination Table match the column number in the Source Table - something to look at it term of optimization...
• Destination Table is large enough (# rows) to "receive" all the records to extract from the Source Table

Recommandations

• Disable Excel auto calc.
• Copy the 1st formula in the Destination Table
• Force the calc. (F9) and see how it goes with the 1st formula before copying the others (still w/auto. calc. disabled)

Source Table (tblSource<strike></strike>) sample

	A	B	C	D	E
1	Cust Ph No<strike></strike>	Cust Name<strike></strike>	Date Time<strike></strike>	Duration<strike></strike>	Call Status<strike></strike>
2	880010101012<strike></strike>	Customer 2<strike></strike>	05/01/2016 00:20:00<strike></strike>	00:10:40<strike></strike>	Answered<strike></strike>
4	880010101014<strike></strike>	Customer 4	05/01/2016 00:18:00<strike></strike>	00:07:10<strike></strike>	Answered<strike></strike>
6	880010101016<strike></strike>	Customer 6	05/01/2016 00:16:00<strike></strike>	00:09:20<strike></strike>	Answered<strike></strike>
8	880010101018<strike></strike>	Customer 8	05/01/2016 00:14:00<strike></strike>	00:08:50<strike></strike>	Answered<strike></strike>

<tbody>
</tbody>

Destination Tablesample

	A	B	C	D	E
1	Cust Ph No<strike></strike><strike></strike>	Cust Name<strike></strike><strike></strike>	Date Time<strike></strike><strike></strike>	Duration<strike></strike><strike></strike>	Call Status<strike></strike><strike></strike>
2	8800101010122<strike></strike>	Customer 22<strike></strike>	04/01/2016 00:05:00<strike></strike>	00:10:10<strike></strike>	Answered<strike></strike>
3	8800101010124<strike></strike>	Customer 24<strike></strike>	05/01/2016 00:01:00<strike></strike>	00:08:24<strike></strike>	Answered<strike></strike>
4	8800101010121<strike></strike>	Customer 21	05/01/2016 00:01:03<strike></strike>	00:08:12<strike></strike>	Answered<strike></strike>
5	8800101010125<strike></strike>	Customer 25<strike></strike>	05/01/2016 00:01:07<strike></strike>	00:08:25<strike></strike>	Answered<strike></strike>

<tbody>
</tbody>

Named variable
vNbRecordsToExtract with formula

=AGGREGATE(3,5,([COLOR=#0000cd][B][I]tblSource[/I][/B][/COLOR][Call Status]))

Formulas in Destination Table (tblDestination)
in C2:

=IF(ROWS($C$2:$C2) > [B][I][COLOR=#0000cd]vNbRecordsToExtract[/COLOR][/I][/B], "",
AGGREGATE(15,5,[COLOR=#0000cd][B][I]tblSource[/I][/B][/COLOR][Date Time],ROWS($C$2:$C2)))

validate with Enter only

in A2 (to Copy/Paste in B2, D2, E2...):

=IF(ROWS(A$2:A2) > [B][I][COLOR=#0000cd]vNbRecordsToExtract[/COLOR][/I][/B], "",
INDEX([B][I][COLOR=#0000cd]tblSource[/COLOR][/I][/B], MATCH(AGGREGATE(15,5,IF([B][I][COLOR=#0000cd]tblSource[/COLOR][/I][/B][Date Time]=[@[Date Time]],ROW([B][I][COLOR=#0000cd]tblSource[/COLOR][/I][/B][Date Time])),
                                 COUNTIF($C$1:$C1,[@[Date Time]])+1),
                       ROW([B][I][COLOR=#0000cd]tblSource[/COLOR][/I][/B]),0), MATCH(A$1,[B][I][COLOR=#0000cd]tblSource[/COLOR][/I][/B][#Headers],0)))

validate with Ctrl+Shift+Enter (array formula)

See SAMPLE_AGGREG_ExtractRecordsWithOneCriteriaAndSortByDateTime.xlsx if necessary

Regards
XLearner