-1

Wondering if there is a better way to pull multiple partial texts from a string in one column to define a type for each line item in a separate column.

string examples:

  • HEAVY HEX BOLT ASME B18.2.1 ASTM A320/A320M GR.B8M CL 2 PL UNC CLASS 2A SIZE: 5/8"" UNC X 2 1/2"" -
  • VALVE TURNING DEVICE * MFR STD BRACKET AS PER DATA SHEET NO. : USSP-CBE-00-LT-000-T01-0713 -

my formula:

=IF(COUNTIF(W1292,"*support*"),"SUPPORT",
IF(COUNTIF(W1292,"*pipe*"),"SPOOL/PIPE",
IF(COUNTIF(W1292,"*bolt*"),"BOLT/NUT/WASHER",
IF(COUNTIF(W1292,"*washer*"),"BOLT/NUT/WASHER",
IF(COUNTIF(W1292,"*nut*"),"BOLT/NUT/WASHER",
IF(COUNTIF(W1292,"*valve*"),"FITTING",
IF(COUNTIF(W1292,""),"BLANK",
IF(COUNTIF(W1292,"*flange*"),"FITTING",
IF(COUNTIF(W1292,"*coupling*"),"ACCESSORIES",no)))))))))

I get an error every few hundred rows that says #NAME? Even though it works 95% throughout my spreadsheet.

I get an error every few hundred rows that says #NAME? Even though it works 95% throughout my spreadsheet. The partial text is valid in the column I'm pulling the data from so it should label the type but it doesn't.

Mayukh Bhattacharya
  • 12,541
  • 5
  • 21
  • 32
laserhawkeye
  • 1
  • 1

1 Answers1

1

enter image description here

You may benefit from SUMPRODUCT:

=IF(A1="";"BLANK";IFERROR(INDEX($E$2:$E$9;SUMPRODUCT(COUNTI(A1;"*"&$D$2:$D$9&"*")*ROW($D$2:$D$9))-1);"NO"))
Foxfire And Burns And Burns
  • 10,665
  • 2
  • 17
  • 38
  • So now I have some instances where there are two keywords in the same string. How can I make one take priority if both partial strings are in the line item? – laserhawkeye Jul 19 '23 at 11:33
  • Also if I use SUMPRODUCT wouldn't I have to make a table with the thousands of line item tags to sort the data? The spread sheet has over 2 million line items. – laserhawkeye Jul 19 '23 at 11:35
  • 1
    *I have some instances where there are two keywords in the same string* That's a different question and you should first make sure this solution works and then try to modify it with your needs. *wouldn't I have to make a table with the thousands of line item tags to sort the data?* Yes you should. *The spread sheet has over 2 million line items* if you got 2 million records then Excel **is not** the tool you need because it can handle only around 1 million records. Try designing a database and cross data there. – Foxfire And Burns And Burns Jul 19 '23 at 12:31