Find First Number Within Two Ranges

[ScabbyDog]

In cells YR3 to ABS3 there may be numbers (they might all be empty but if there is data it'll be a number). Let's call this range "H". The same applies for ABT3 to AEU3 but we'll call this range "A". That is 80 cells for each of those ranges.

In cell ES4 I need a formula that looks for the first bit of data across both these ranges and when found pastes the number it finds and whether it was found in range "H" or range "A".

So if there is '3' in cell YZ3 but there is a '5' in ABU3 then cell ES4 should return 'A5'. This is because the first instance of data was found in the 'A' range and the actual number found was '5'.

[candybg]

I may be confused - it seems as though you are not consistent in your range names, from Paragraph 1 vs Paragraph 3 (Range A vs Range H).

I'm not able to think of a formula to do this, though. Maybe a macro?

[ScabbyDog]

YR3 to ABS3 can be called range 'H'

ABT3 to AEU3 can be called range 'A'

Perhaps I may need a macro. I'll post my question on the VBA forum.

[FDibbins]

Perhaps I may need a macro. I'll post my question on the VBA forum.

No, please do not post duplicate threads, it goes against our rules. If you need to move this thread, let a mod or admin know

[Beamernsw]

no posting?

Edit: for some reason I keep getting access denied...trying again

This seems awfully long but it does seem to work, if I've read the question right that is

ok..fine...it seems to think my formula is html.

I'll add my test file instead.

[newdoverman]

Your post does not comply with Rule 1 of our Forum RULES. Your post title should accurately and concisely describe your problem, not your anticipated solution.

Use terms appropriate to a Google search. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will be addressed according to the OP's experience in the forum: If you have less than 10 posts, expect (and respond to) a request to change your thread title. If you have 10 or more posts, expect your post to be locked, so you can start a new thread with an appropriate title.

To change a Title go to your first post, click EDIT then Go Advanced and change your title, if 2 days have passed ask a moderator to do it for you.

(This thread should receive no further responses until this moderation request is fulfilled, as per Forum Rule 7)

[Beamernsw]

Yes, sorry newdoverman, I was going to mention that to ScabbyDog myself and forgot... was going to suggest something like "Find first number within 2 ranges" or something along those lines.

[ScabbyDog]

Perfect Beamernsw! Super work.

[Beamernsw]

I'm glad I could help.....but please do change the Post Title so others can search for this thread and get answers as well.

[Beamernsw]

I think a good title that might yield search results could be: "Find first occurrence of number within multiple ranges"

[newdoverman]

This is a bit late but you might want to consider it.
Enter this in ES4

=IFERROR("H"&INDEX(YR3:ABS3,MATCH(TRUE,YR3:ABS3<>"",0)),"A"&INDEX(ABT3:AEU3,MATCH(TRUE,ABT3:AEU3<>"",0)))

...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.

[ScabbyDog]

This is a bit late but you might want to consider it.
Enter this in ES4

=IFERROR("H"&INDEX(YR3:ABS3,MATCH(TRUE,YR3:ABS3<>"",0)),"A"&INDEX(ABT3:AEU3,MATCH(TRUE,ABT3:AEU3<>"",0)))

...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.

This works great BUT it needs to know what the first number found is. So if for example if there is '3' in cell YZ3 but there is a '5' in ABU3 then cell ES4 should return 'A5'. This is because the first instance of data was found in the 'A' range and the actual number found was '5'.

I've had to re-open this as I've been trying to use Beamersnw's code in a macro but since it's more than 255 characters it's having trouble running it so I need a solution that is 255 characters or less!

[newdoverman]

This will return the values that you specify if entered in the cells that you mention. This was simply a reversal of the terms of the formula

=IFERROR("A"&INDEX(ABT3:AEU3,MATCH(TRUE,ABT3:AEU3<>"",0)),"H"&INDEX(YR3:ABS3,MATCH(TRUE,YR3:ABS3<>"",0)))

...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.

[ScabbyDog]

This will return the values that you specify if entered in the cells that you mention. This was simply a reversal of the terms of the formula

=IFERROR("A"&INDEX(ABT3:AEU3,MATCH(TRUE,ABT3:AEU3<>"",0)),"H"&INDEX(YR3:ABS3,MATCH(TRUE,YR3:ABS3<>"",0)))

...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.

Thanks but I'm in the same situation then if the first number is found in the other range. The formula will need to know which range the first number is found in.