Need to define a search or define instruction to extract parts of data from cell

[Dawn Clark]

Hello, I normally tend to shy away from VBA because it seems to tend to corrupt or bog down my spreadsheets. However I am not sure if there is a solution with formulas alone.



I have Excel 2019 MSO.

In the attachment there are two tabs, one named "dashboard" and one named "Jobs"

Summary of result needed:
In the JOB tab I need to pull (from dashboard tab) all applicable S/O (order of list doesn't matter) for corresponding job (into column D) and earliest Ship date of all applicable ship dates for corresponding job (into column E).
Must search all job columns (columns G, H & I) on DASHBOARD.

Specific Examples of result needed:
On the job tab, I need Cell D18 to populate S/O with "17897-1-22" and E18 with Ship date "2/5/2020"
On the job tab, I need Cell D14 to populate S/O in with "16953-1-12, 17897-1-22, 17366-1-13" (in this format) and Ship date with "1/25/2020" (earliest ship date of the 3)

Note: the reason that I need all applicate S/O is because I need to be able to filter a long list of jobs per S/O.


Constants:

• All S/O will have one of the following formats and only one S/O entry per cell in Dashboard column C.
  xxxxx-x-x
  xxxxx-x-xx
  xxxxx-xx-x
  xxxxx-xx-xx
  
• Despite what you see in Dashboard columns G-I, I only need (and want) the function to search (identify) or extract jobs (extraction can be done separately if needed) numbers which are defined as:
  xxxx (numeric digits only)
  xxxxx (numeric digits only)

I suppose I envision a index/match with text join and some kind of special character removal functions but I am not sure what is possible let alone the best way to get the result needed.


Thanks so much for your assistance.



Dawn Clark

[sintek]

I normally tend to shy away from VBA because it seems to tend to corrupt or bog down my spreadsheets

No, cannot be...

One unorthodox way I guess...Try this...Unless I misread your expectation...

Please Login or Register  to view this content.

[Dawn Clark]

Forgive me but I am not sure what the next step is. i have the code now and have inserted it into my sheet and saved as macro-enabled but I do not know how to apply it to get the result that i was trying to get.

[sintek]

Hi Dawn

See attached...