0

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. PFAenter image description here

1
  • please consider attaching a link to a sheet showing the data and what you have tried so far using the Blank sheet Maker and edit in to your question. Jun 6, 2023 at 12:10

1 Answer 1

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().

2
  • It almost got the answer I was looking for. But when I put this formula in cell I1, L3 and M3 shows the same value "KOLLAM" and L4 and m4 shows "600301". Value not removed from the original cell. pls check..
    – Anand
    Jun 7, 2023 at 5:32
  • Edited the answer. I cannot "check" the answer because you have not shared sample data nor desired results. Screenshots are not enough. Please see the comment to your question. Jun 7, 2023 at 7:31

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.