CODEWITHSUNDEEP
google-sheets-formula
Normalitie
Normalitie

Reputation: 69

Google Sheets query order by row ends with

I have table of data which looks like:

J2150 IMPAC-BRIGA (RH)
J2283 BAYWA-FERGU (NK)
J2284 BAYWA-DIAPR (NK)
J2320 BOSCH-OWNER (ML)
J2475 GIPPS-GIPWF (NK)
J2568 GWFLD-CASTL-002 (PW)
J2663 AUSTRA-BARHA-001 (NK)
J2690 PHOTO-NEWAT (KT)
J2692 TETRI-MANGA (NK)

I'm using a Google Sheets query but I want to order the table by the project manager ie the initials at the end eg (CM).

I've been trying to use 'ends with' and 'order by' but this doesn't do what I want; eg

select Col2 where Col2 ends with '(CM)' group by Col2 order by Col2

I could separate out the initials into a new column in the original data, select and sort on that but is there an elegant way of sorting by the end of the row rather than the start?

Upvotes: 0

Views: 225

Answers (3)

Normalitie
Normalitie

Reputation: 69

I realised my original question wasn't precise enough: the rows are one record and not separate entries

J2150 IMPAC-BRIGA (RH)
J2283 BAYWA-FERGU (NK)
J2284 BAYWA-DIAPR (NK)
J2320 BOSCH-OWNER (ML)
J2475 GIPPS-GIPWF (NK)
J2568 GWFLD-CASTL-002 (PW)
J2663 AUSTRA-BARHA-001 (NK)
J2690 PHOTO-NEWAT (KT)
J2692 TETRI-MANGA (NK)

But I did want to sort on the two-letter suffix in the () at the end.

This is what I came up with:

query({'Project Overview'!$B$1:$B$373, 
    arrayformula(REGEXEXTRACT('Project Overview'!$B$1:$B$373,"\(([A-Z]{2})\)")), 
    'Project Overview'!$c$1:$d$373},
    "select Col1, Col2, Col3, Col4 where Col3 is not null order by Col2 
         label Col1 'Project', Col2 'Project Manager'", 1)

This extracts the first column, 'B' and then uses REGEXTRACT to pull the 2-letter code from the same data as the second column.

Project Project Manager 4-Oct-21 11-Oct-21
Kinley (CM) CM 1
Kinley (CM) CM 1
J1911 KINEL-KENT (CM) CM 4 8
J2741 SIGNL-DARLP (DD) DD 2
J2745 MANGO-FEASB (DD) DD 1
J2754 CPENG-WANG (DD) DD 16 8
J2754 CPENG-WANG (DD) DD 20 16
J2754 CPENG-WANG (DD) DD 4
DARETON O&M (JM) JM 0.5 0.5
DARETON O&M (JM) JM 2 4

Upvotes: 0

idfurw
idfurw

Reputation: 5852

=ArrayFormula(QUERY(REGEXEXTRACT(A:A,"^(.+?) (.+)$"),"select * where Col2 ends with '(NK)' order by Col2"))

Upvotes: 0

Rocky
Rocky

Reputation: 990

try the below formula:

Assuming your data range is A2:B, if not then change your data range accordingly

=Query({A2:A,B2:B,Arrayformula(SPLIT(B2:B," "))},"Select Col1,Col2 where Col4 <>'' order by Col4")

Upvotes: 1

Related Questions