Need help extracting single data from cell with multi data

[Dawn Clark]

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

In the JOB tab I need to pull all applicable S/O (order doesn't matter) for corresponding job and earliest Ship date of all applicable ship dates for corresponding job.
Must search all job columns (columns G, H & I) on DASHBOARD.

Examples:
On the job tab, I need Cell D18 to populate S/O with "17897" and Ship date with "2/5/2020"
On the job tab, I need Cell D14 to populate S/O with "16953, 17897, 17366-1-1" and Ship date with "1/25/2020"


Thanks so much!

Dawn

[hrlngrv]

First, Windows 7 Professional isn't an Office version. I'll assume you're using Excel 2000.

On one hand, Jobs!D18:E18 are simple because Jobs!A18 appears only once in Dashboard!G2:I25, in G25. Better to use another column for row indices common to both columns in the same row.

Jobs!K18:

Please Login or Register  to view this content.

Then use that in INDEX formulas in columns D and E.

Jobs!D18:

Please Login or Register  to view this content.

Jobs!E18:

Please Login or Register  to view this content.

On the other hand, Jobs!D14:E14 aren't simple at all. The best approach depends on your actual Excel version, which you haven't provided. In Excel 2000, given everything it lacks compared to more recent versions, you'd be best off doing the entire lookup and consolidation in VBA. In Excel 365, you could almost avoid VBA entirely; almost because you have job numbers in Jobs!A2:A20 of varying sizes and formats, and Dashboard!G2:I25 essentially contains free-form text. If you had a job number 3456, you'd need additional logic to prevent matching that as a substring of 23456, 34567 or 234567. That requires regular expressions (if you want this to be efficient), which requires VBA.

Tangent: your data is perfectly suited to give you headaches and nightmares going forward even if you get a working solution to this particular problem. If you CAN clean up/normalize your data, you'd be far better off doing so.

[Dawn Clark]

I have Excel 2019 MSO.

Also, would it help if i remove all but job number, 1 comma and 1 space for each job in Columns G - I on the dashboard tab? (cannot get around using both 4 and 5 digit job numbers)

I can also update all S/O numbers on dashboard tab to following format variants only:
xxxxx-x-x
xxxxx-x-xx
xxxxx-xx-x
xxxxx-xx-xx

Lastly, the formulas that were already in the spreadsheet do not need to apply. They were left there by mistake.

Sorry for the ambiguity. Im unsure of the possible search type formulas are available in excel

[JeteMc]

Please upload an Excel sheet that has the change to the job number column described in post #3.
Let us know if you have any questions.

[kvsrinivasamurthy]

Try this
In D2

=IFERROR(INDEX(Dashboard!$C:$C,AGGREGATE(15,6,ROW(Dashboard!$A$2:$A$25)/(ISNUMBER(SEARCH($A2,Dashboard!$G$2:$I$25))),1)),"")

In F2

=IFERROR(INDEX(Dashboard!$D:$D,AGGREGATE(15,6,ROW(Dashboard!$A$2:$A$25)/(ISNUMBER(SEARCH($A2,Dashboard!$G$2:$I$25))),1)),"")

Copied down.

[kvsrinivasamurthy]

I have revised formula for D2

Please Login or Register  to view this content.

For F2

Please Login or Register  to view this content.