Forum Discussion
Need to return col A if another col contains the text in col B (array) anywhere
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.
=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.
Thank you so much for this. This works like a charm and, yes, I'll definitely use column AND row references.
Now I just have to lookup all these functions I never knew existed so I can play around with them elsewhere. Thank you, again.