Forum Discussion
Solved
Need to return col A if another col contains the text in col B (array) anywhere
So, here's my array in columns A and B:
Column L has a journal description that COULD contain one of col B descriptions. So, column L will have things like "3M LO(a)N 2024.12" OR "3M LO(a)N 2024.11", etc. But they could also have "TRU UP COLORADO INTEREST #3126" - basically, the description in col B could be anywhere (together or separated) in Col L.
What I want to do is, in Column C write a formula that will look at column L and compare it to the array in columns A & B, and then return column A if the value in col B is found. So, for "3M LO(a)N 2024.12", col C would return "1". For "TRU UP COLORADO INTEREST #3126", return "5". The difference being "3M LO(a)N" is found in the same sequence as it is in the array, but "COLORADO #3126", though contained in col L, the two parts are separated.
I'd like to leave these descriptions as they are to help in identifying if/when I need to change the array, but if it's too difficult, I could just use "#3126" as the description in col B and add a vehicle description in another column. The array is named "CATEGORIES" to ease formula creation.
Thanks for any help in advance.
*** apparently the full word LO AN is not allowed in the text. So, LO(a)N it is.
You are welcome. You are right, the formula i posted is the translation of the formula from the screenshot. It's actually the same formula one for english Excel and the other for german Excel. In my sample file i made an example for ~2000 rows in columns A and B and ~200 rows in column J and the formula calculates for ~2 minutes. When the calculation is finished you can copy the result and paste only the values in another column. Then you can remove the equal sign in the formula in order to prevent unintended recalculation. If the data in columns A, B and / or J changes you can add the equal sign to the formula to start the calculation.
Certainly this task requires massive calculation which takes its time at least with this solution. Perhaps another contributor can suggest a solution that works faster.
- Change J2:J10 to e.g. J2:J1000 according to the number of journal descriptions in that column. And change A2:A13 and B2:B13 (to e.g. A2:A1300 and B2:B1300) according to the number of company codes and descriptions in these columns.
- You don't need to copy the formula down. You can enter the formula in cell C2 and it spills down the results.
- You should always specify the number of rows. If you referenced i.e. "J:J" (which is equal to "J1:J1048576") the formula would calculate the result for 1048576 rows. Then the calculation would take ages for this task unless Excel would crash. Therefore it's essential to always reference the rows.
5 Replies
please share a sample file.
=IFERROR(
BYROW(J2:J10,LAMBDA(r,FILTER(A2:A13,
DROP(
REDUCE("",B2:B13,LAMBDA(u,v,
VSTACK(u,LET(_res,UNIQUE(ISNUMBER(SEARCH(TOCOL(TEXTSPLIT(v," ")),r))),
AND(COUNTA(_res)=1,_res=TRUE))))),1)))),
"not found")
With Excel for the web or Office 365 this formula returns the intended result in my sample sheet.
Thank you so much. That's a lot more involved than I hoped it would be. But, if it works, it works.
So, I entered the above formula (IFERROR one) and it seriously slowed down my Excel. Like, 5 minutes to calculate - I only entered it into C2. And, any time I attempted to change something it would take another few minutes to allow me to get into the cell to edit. So, we're not doing that one - I just closed without saving, having to do a hard shutdown of Excel.
The other formula (in your screenshot) is completely foreign to me. I'm assuming that's because it is a foreign version of Excel. But, I certainly do not know all the available functions. So, maybe it's just a bunch of functions I've never heard of.
The result is AWESOME, though! It's exactly what I was hoping for. But, I have a few questions due to my limited knowledge of Excel functions (as indicated above).
- How do I edit it to look through hundreds or thousands of rows?
- Is this a formula I need to copy down to all the rows, or does it calculate based on the number of rows in col J?
- Do I need to specify the number of rows, or can I just use a column reference (i.e. "J:J")?
You are welcome. You are right, the formula i posted is the translation of the formula from the screenshot. It's actually the same formula one for english Excel and the other for german Excel. In my sample file i made an example for ~2000 rows in columns A and B and ~200 rows in column J and the formula calculates for ~2 minutes. When the calculation is finished you can copy the result and paste only the values in another column. Then you can remove the equal sign in the formula in order to prevent unintended recalculation. If the data in columns A, B and / or J changes you can add the equal sign to the formula to start the calculation.
Certainly this task requires massive calculation which takes its time at least with this solution. Perhaps another contributor can suggest a solution that works faster.
- Change J2:J10 to e.g. J2:J1000 according to the number of journal descriptions in that column. And change A2:A13 and B2:B13 (to e.g. A2:A1300 and B2:B1300) according to the number of company codes and descriptions in these columns.
- You don't need to copy the formula down. You can enter the formula in cell C2 and it spills down the results.
- You should always specify the number of rows. If you referenced i.e. "J:J" (which is equal to "J1:J1048576") the formula would calculate the result for 1048576 rows. Then the calculation would take ages for this task unless Excel would crash. Therefore it's essential to always reference the rows.
