VBA to find additional matches based on first match

[Hillster]

I have attached a workbook that has some data in AS5 the number 2 currently occupies that cell. I need a way to have whatever number is in AS5 be compared to D5,E5 and F5. If in this example the number 2 (AS5) is also in either D5, E5 or F5, then vba needs to look down thru row 14 of D:F and if there are additional 2's found in any row, then extract the numbers next to (in this example) the number 2 and deliver the results back over to column AS where the number 2 (AS5) is currently occupying and display in AS8 and below, (AS9, AS10, AS11,etc). You hopefully will be clear on my expected result when you review the attached spread sheet. If that function can be accomplished, i would love for it to be able to perform the same analysis and extracted results across the entire column range of AR5:Bi5 and AR6:Bi6. You may ask why did I point direction to AS5 (#2)? simply because it has a number in the cell and the ones next to it are blank so just thought it would be easier to get a positive result.

Another anticipated question from you might be what is the status of D5:F14? those numbers will be changing every day.... Also the numbers in AR5:Bi5 & AR6:Bi6 will change daily as well. So at some point soon, the AS5 (#2) might be blank tomorrow so if the vba sees the cell does not have a number in it, then just ignore it until a number finally does occupy that cell again.
Gosh I hope at least some of that made a little sense.... I sure appreciate any help or suggestions!

[Kaper]

Try such code:

Please Login or Register  to view this content.

2 comments:
1) if you want to search not (as mentioned in description) D5:F14 but D6:F14 (as you did manualy, skipping value 2 in E5) just change range assigned to DFarr variable.
2) you have not mentioned how results for not-empty cells in AR6:BI6 shall be positioned, so I did only row5 listing above. But if they shall be listed below all values for row5 then such minor modification would do (test both codes):

Please Login or Register  to view this content.

[Hillster]

Kaper.... OMG the second one worked perfect!! the first one did to but the second one is spot on.. I did not realize how the results would display. Love it!! So as I review the results In column BE (for example), where the number 3 resides in BE5 and the number 1 resides in BE6, as i inspect the results displayed down the column (BE8:BE25), I can't help but wonder how to tell where the number 3 results end and the number 1 results begin....? Is there a way to possibly make all results coming from Row6 have Red colored font & BOLD and maybe everything related to Row 5 (results) just be the default (Black) color font but also BOLD? Just an observation and question. Sometimes we don't realize what our needs might be until we see an end result first... Excellent work Kaper, Thank you!!!

[Kaper]

Written description is nice, but will you please attach a sample Excel workbook.

1. Make sure that your sample data are truly REPRESENTATIVE of your real data. (If trere are typical cases like: all unique values/duplicates could occur, day/night, nobody present/several persons at once, before/on/past due, etc. - please show them all or at least indicate in text) The use of unrepresentative data is very frustrating and can lead to long delays in reaching a solution.

2. Make sure that your desired solution(s) is/are also shown (mock up the results manually).

Best,

Kaper

[Hillster]

Sorry for the lack of attention to your points. I have attached a spread sheet showing the Red colored numbers in Row 6 and the results pertaining to the Row 6 numbers in their perspective columns below, also Red color and BOLD. Hopefully this is clear enough for your resolution. please let me know... thanks again for all of your help with this matter