App Script for getting data from one cell

Question

I have a large database with unstructured data. In the below pic column A contains the 14 districts of Kerala. Column C:G represents the customer details. If any of the cells from C2 to G2 contain a number then it should show it in cell F2 and remove the number from original cell. Also if any of the cells from C2 to G2 consist of any of the district that is given in the column A, it should show it in cell G2 and remove the District from the original cell. For example in the below sheet cell D3 contain a number so F3 should be the same number and remove the value from D3. Also E3 consist a district name mentioned in the column A. So G3 should be the district name and remove the value from E3 and so on. Number of rows may vary. Please find a solution for this issue. PFA

Answer 1

That should be doable with a plain vanilla spreadsheet formula without resorting to scripting. Put this formula in cell I1:

=let( 
  districts, A2:A, 
  headers, C1:G1, 
  data, C2:G, 
  base, choosecols(data, 1, 2, 3), 
  pinBase, choosecols(data, 4), 
  distBase, choosecols(data, 5), 
  ø, iferror(ø), 
  swapN_, lambda(a, b, if((istext(a) + isblank(a)) * isnumber(b), b, if(isnumber(a), a, ø))), 
  swapT_, lambda(a, b, if((isnumber(b) + isblank(b)) * istext(a), a, if(istext(b), b, ø))), 
  pinDef, map(pinBase, distBase, swapN_), 
  distDef, map(pinBase, distBase, swapT_), 
  clean, map(base, lambda(v, 
    ifs( 
      isnumber(v), ø, 
      ifna(match(v, districts, 0)), ø, 
      true, v 
    ) 
  )), 
  pinCand, byrow(base, lambda(row, 
    join(", ", 
      ifna(filter(row, isnumber(row))) 
    ) 
  )), 
  distCand, byrow(base, lambda(row, 
    join(", ", 
      ifna(filter(row, match(row, districts, 0))) 
    ) 
  )), 
  merge_, lambda(a, b, 
    ifs( 
      to_text(a) = to_text(b), a, 
      isblank(a), iferror(value(b), b), 
      not(len(b)), a, 
      true, join(", ", a, b) 
    ) 
  ), 
  pin, map(pinDef, pinCand, merge_), 
  dist, map(distDef, distCand, merge_), 
  vstack(headers, hstack(clean, pin, dist)) 
)

When the data is like this:

DISTRICT		NAME	HOUSE NAME	PLACE	PIN	DISTRICT
ALAPPUZHA		KAVITHA	KANJIRAKKAVU H	CHERUTHURUTHI	680200	THRISSUR
ERNAKULAM		RAJAN	680301	Kannur
IDUKKI		LATHA		KARUVARAM H	KOLLAM
KANNUR		MAHESH		KOLLAM		600301
KASARAGOD		SANOOB	PALAKKAD	CHERUVATHOOR
KOLLAM
PALAKKAD

...the results look like this:

NAME	HOUSE NAME	PLACE	PIN	DISTRICT
KAVITHA	KANJIRAKKAVU H	CHERUTHURUTHI	680200	THRISSUR
RAJAN			680301	Kannur
LATHA		KARUVARAM H		KOLLAM
MAHESH			600301	KOLLAM
SANOOB		CHERUVATHOOR		PALAKKAD

See let(), map(), byrow(), vstack() and hstack().