Find value in range, loop for each row

[anrichards22]

I am trying to write a formula or vba code to:

1. Find (L1) value in range(A2:K2) and place it into cell L2
2. Find (M1) value in range (A2:K2) and place it into cell M2
...... Find range (R1) value in range (A2:K2) and place it into cell R2

3. Then I need to copy down formula (or run code) down each row to the last row. Which could be 150,000 rows or 25 rows.
e.g. Find (L1) value in range A255:K255 and place it into cell L255

4. There aren't exact matches for the entire string
5. A match on the first 5 characters of the string will do the job.

Any assistance appreciated.

I have attached a sample of before and after for reference.

Thanks.

[TMS]

Find (L1) value in range A255:K255 and place it into cell L255

Place what in L255.

Don't really understand how you plan to run this across 150,000 rows.

And, even looking at your example, I have no idea how you get from where you start to wher you want to end up.


Regards, TMS

[anrichards22]

The value in L1 = First

So I need to look for "First" in the range A2:K2 and place the result in cell L2.

Something like...... Formula in (L2) = lookup(A2:K2,$L$1)...CANNOT use this formula because the match is NOT exact...the first five characters will match.


I understand that running it across 150000 rows will be time consuming which is why I would like to find a formula solution...but I will use VBA code if that is my only option

[HaHoBe]

Hi, anrichards22,

according to your description worksheet After is not showing what I was expecting from reading your post (in Row 2 Second and Third are not to be found).

Maybe this code may lead towards what you are searching for (since posted in VBA)

Please Login or Register  to view this content.

Ciao,
Holger

[TMS]

Try:

In cell L2: =IFERROR(INDEX($A2:$K2,MATCH(L$1&"*",$A2:$K2,0)),"")


Copy across and down.


Regards, TMS

[anrichards22]

Try:

In cell L2: =IFERROR(INDEX($A2:$K2,MATCH(L$1&"*",$A2:$K2,0)),"")


Copy across and down.


Regards, TMS

Thanks TMS
Works perfectly!

Hi, anrichards22,

according to your description worksheet After is not showing what I was expecting from reading your post (in Row 2 Second and Third are not to be found).

Maybe this code may lead towards what you are searching for (since posted in VBA)

Please Login or Register  to view this content.

Ciao,
Holger

Thanks to you too Holga!

The code works perfectly.


I tested both over 100,000+ rows and there was only a very marginal difference in performance.
If I am going to incorporate the formula or the code into some other VBA code which would be a better choice, VBA code or formula??


Thanks again to both of you.

[TMS]

You're welcome. Thanks for the rep.


Personally, I would not advocate doing something in VBA that I can do with a formula. If I did want to do it in VBA, I would probably just put the formula in every cell in the range and then convert the results to values.

You posted in the programming forum and Holger gave you the code. With all due respect, I'd be surprised if code looping through cells would be quicker than a relatively simple formula.

You said:

there was only a very marginal difference in performance

Out of interest, which was quicker and what was the difference?


Regards, TMS

[anrichards22]

TMS,

In my previous post I stated 100,000+ rows which was an error. It should have been 10,000+ rows.
Over 10,000 rows the formula and the code both complete within 2 seconds. The formula is marginally faster.

Over 100,000 rows the formula takes 3.6 seconds and the code takes 2min18 seconds
Over 200,000 rows the formula takes 9.9 seconds and the code takes 4min47 seconds


My knowledge of VBA is only basic, I asked the question (formula v code) because I am not sure which is more reliable.

Thanks again for your assistance.

[TMS]

which is more reliable

That surely depends on the formula, the code and the author(s). It is possible to make errors in formulae, as it is possible to make errors in code.

If the formula works on a relatively small sample of data, it is likely to work on a larger sample. As I said, I would expect a formula to be more efficient (faster) than code in most instances. However, an array formula dragged over 100s or 1000s of cells is very likely to kill performance. It is better suited to one-off calculations or a relatively small number of replications. Conversely, I have seen examples where an entire sheet is read into an array, processing done within the code, and the output written back to the sheet. I imagine that Holger's code could be adapted to use that approach and would be faster ... though I wouldn't bet money on it being faster than a formula.

In this instance, your timings demonstrate a huge increase in time for the code approach. So, I guess it depends on how long you want to wait.

When it comes down to it, it really depends on where your skills lie. Although you don't have to be, or have been, a programmer to use VBA, I suspect it helps a lot.


Regards, TMS

[snb]

or

Please Login or Register  to view this content.