0

I'm following this post to categorize data in Google Sheets using regular expressions.

The person who wrote the answer was able to get the formula to automatically write down the category in column D.

I assume they used ArrayFormula to achieve it, but I can't seem to get it done. Or they just paste the formula to each cell of the column. I'm not sure.

The formula:

=IFERROR(INDEX(A$2:B,MATCH(TRUE,ARRAYFORMULA(REGEXMATCH(C2,INDIRECT("A"&2&":A"&3+COUNTA(B$2:B)))),0),2))

Here is the sample Sheet I made as an example.

What I am looking for is to categorize data in column C based on the keywords in column A. The category result is written in Column D

Does anyone know how to do it? Thanks so much.

enter image description here

Improve this question
7
  • There are many unknowns here. I suggest that you share a link to a sample spreadsheet with realistic data examples representing your actual data. For instance, will Col A always join only single words, or might some of them be expressions made up of multiple words? Will they be enclose in quotes as you show here? Why are you using periods in Col A? Are those supposed to represent regular expressions? Does case matter? Why do you have "small house" listed twice in Col C, and why does only one of them get labeled in Col D? Give more examples and explain everything in detail for best results. Commented Aug 10, 2022 at 9:56
  • @ErikTyler Thanks for answering. I just updated my question. I just updated the question. Here is the sample sheet I made. Thanks! Commented Aug 10, 2022 at 9:59
  • Sharing the spreadsheet is a start. However, you have not answered any of the many questions I asked above. You haven't added more data for testing purposes. And you haven't confirmed that this is realistic data that represents the actual data you will be working with. Commented Aug 10, 2022 at 10:03
  • Thanks for helping. At the beginning of the question, I added the post that I was following. Perhaps that can explain it better than I. 1) ColA contains the keywords that when detected, will write out the category in Col B. For example, the sentence "small house" has the word "small" in it, so the category of it is "Size". If the sentence contains "blue", then category is "Color", etc. 2) I fixed Col A into blue|white|red This seems to work for my case. I'm new in this so my apology. 3) Case matters. 4) ColC is where I put the different sentences to categorize them. Commented Aug 10, 2022 at 10:31
  • What I am looking for is when I type a link in ColC. The formula will detects keywords in ColA with appropriate category in Col B and write the answer to Col D Commented Aug 10, 2022 at 10:33

1 Answer 1

Reset to default
1

Use this formula
see this question with similar answer

=ARRAYFORMULA(IF(C2:C="",,TRIM(FLATTEN(QUERY(TRANSPOSE(IFERROR(
 IF(REGEXMATCH(C2:C&IFERROR(SEQUENCE(1, COUNTA(A2:A))/0), 
 IFERROR(SEQUENCE(COUNTA(A2:A))/0)&TRANSPOSE(A2:A)), B2:B, ))),,9^9)))))

enter image description here

Improve this answer
Sign up to request clarification or add additional context in comments.

5 Comments

your formula will only work for as many rows in Col C as there are entries in Cols A and B. Right now, you happen to have as many rows in Col C as in A and B. But if you added more entries to Col C without adding any more entries to Cols A and B, I think you will find that no results appear for the later listings in Col C. That is because Google Sheets will be trying to keep all array lengths (in rows) parallel. In fact, your formula will only return a match if the match is in the same row as the Col-C entry for this reason. But the OP should thank you for the time you put in!
Hi @ErikTyler thanks for your insight, I tested the scenario you Pointed out, and it work's as expected see This, no errors, and the formula will only work for as many rows in Col C as there are entries in Cols A and B, thats the point why he kept the categories in this form blue|white|red, if there is any thing i did'nt address please share a screenshot of your resut.
I may be misunderstanding the OP's goal, and you may be understanding it correctly. I thought the goal was to indicate per listing in Col C what the category (or categories) are for each individual listing in Col C. But it seems, per your assessment, that the goal is to simply list whether a category was used at all anywhere in Col C and, if so, to list it only once in Col D.
No no, let's keep it simple, |Unique Categories| Are Categories and |Join of Variables| Are sub Categories, and they need to be in the same row, and ... Phrases to Screen is changing every time, I think you want to do it this way follow the answer and you will find the OP is limited due to large categories and criteria for each category.
@ErikTyler To be clear, the approach chosen by the OP seems a little off to me. If there is a better way, post it as an answer to this question or as an article/question.

Your Answer

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

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.