ttc526
SandeepMarwal:
Thanks for share the attachment.
sql:
create temp table aa as
select f01,f02,f03||f04||f05||f06||f07||f08||f09||f10||f11||f12||f13||f14||f15||f16||f17||f18||f19||f20||f21||f22||f23||f24||f25 other from consolidateSheet;
select f01,f02,max(regexp2('Location\s+(\w+)',other,1)) Location,max(regexp2('Reference\s+(\w+)',other,1)) Reference,* from aa group by f02;
f01 f02 Location Reference other
Book1 (2).xlsx | Sheet1 | ABC | 123 | Reference 123---------------------- |
Book1 (2).xlsx | Sheet10 | | | Location ------------------------- |
Book1 (2).xlsx | Sheet2 | DEF | 456 | Reference 456---------------------- |
Book1 (2).xlsx | Sheet3 | PQR | 000 | Reference 000---------------------- |
Book1 (2).xlsx | Sheet4 | GGG | 007 | Location GGG Reference 007---------------------- |
Book1 (2).xlsx | Sheet5 | SOS | 111 | Location SOS Reference 111---------------------- |
Book1 (2).xlsx | Sheet6 | RRR | 555 | Location RRR Reference 555---------------------- |
Book1 (2).xlsx | Sheet9 | XXX | | Location XXX Reference ###---------------------- |