CODEWITHSUNDEEP
google-sheets-formula
DarthBusiness
DarthBusiness

Reputation: 153

Google Sheets formula to sort range of data with multiple conditions for sorting assigned to one column

I am trying to sort a range of data that I collect on daily basis. The data that I collect would approximately look something like this:

Location    Ticket  Part    Quantity
B           1003    Thing 2 3
B           8001    Thing 4 5
A           4002    Thing 3 1
A           1001    Thing 1 1
C           8003    Thing 1 7
B           4003    Thing 5 2
A           1002    Thing 1 4
B           8002    Thing 1 3
C           4001    Thing 1 1

Currently I manually sort the data so that it ends up looking like this

Location    Ticket  Part    Quantity
A           1002    Thing 1 1
A           1001    Thing 1 4
A           4002    Thing 3 1
B           1003    Thing 2 3
B           4003    Thing 5 2
B           8001    Thing 4 5
B           8002    Thing 1 3
C           4001    Thing 1 1
C           8003    Thing 1 7

To be clear I am looking to sort the original data so that it is arranged as follows

Column Location descending then column Ticket descending by its left most number, 1,4, and 8, but in such a way that column Part will be in alphabetical order by the left most ticket number, 1,4,8, and the sort order of Location will remain sort ascending

I am unaware of a way to sort column Location ascending and then sort column Ticket by two sets of criteria. I want to sort Ticket ascending by Locating without changing the order of Location and then sort column part ascending without changing column Location sort ascending or the sort ascending of the LEFT most number in column Ticket.

To summarize here is the order I need to get from a formula once more

Column Location ascending Column Ticket ascending by 1,4, and 8 with respect to its location Column Part ascending by column Ticket in a way that the 1,4,8 order is preserved and remains organized by it's location

The key is column Part must be sorted alphabetically by ticket group, 1,4,8 and not alphabetically with respect to the complete ticket number. Here is one more example of what I mean:

Location    Ticket  Part
B           1999    Thing 1
B           1002    Thing 2
B           4002    Thing 2
B           4001    Thing 3
B           8888    Thing 1
B           8000    Thing 2
B           8999    Thing 3
C           1003    Thing 1

Part is in alphabetical order by left most ticket number despite the fact that row 1 ticket 1999 is greater than row 2 1002

Can anyone help?

Upvotes: 0

Views: 2113

Answers (2)

kirkg13
kirkg13

Reputation: 3010

By temporary column, I meant it is only created and used on the fly, not physically in the sheet.

Try this formula, anywhere on row 2 of your sheet.

=QUERY(SORT({A2:D,ARRAYFORMULA(LEFT(B2:B,1))},1,1,5,1,3,1),"select Col1, Col2, Col3, Col4",0)

This creates an array with your data, from A2:D, adds a temporary column created from the left most character in column B, sorts by column 1 of the array (Location), then by the temp column (column '5'), then by the Part (column 3), and finally does a query of the result to only keep the first four sorted columns, discarding the temporary column.

This approach should make it easy to modify if you have other specific sorting requirements.

Does this do what you needed? If not, let us know what the issue is, or if I've misunderstood.

enter image description here

Upvotes: 1

OneInAMillion
OneInAMillion

Reputation: 552

If understand your question correctly, then I would sort the array based on the column order you've defined. This formula will sort by columns 1, 2, and 3.

Sort Explained: https://support.google.com/docs/answer/3093150?hl=en

=Sort(ArrayFormula(A2:D10),1,true,2,true,3,true)

If you need to sort the existing data without a formula then this script should do the trick:

function sortRange() {

var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  // change 'A2:D' to whatever your A1 Notation range is.  
  spreadsheet.getRange('A2:D').sort([{column: 1, ascending: true}, {column: 2, ascending: true}, {column: 3, ascending: true}]);

}

More info on this can be found here: https://developers.google.com/apps-script/reference/spreadsheet/range#sort(Object)

Upvotes: 0

Related Questions