Hi, I am new to this forum and this is my 1st post for help.


I have outlined the problem below and in the attached Word document.

I have a data table with 4 columns of data:
Job number (Column A), visit date Column B), Job type (Column C), Status (Column D).

Also an answer table with columns E to K.

I want to return the date occurrences in 1st, 2nd, 3rd etc order, that relate to aaprticular Job number, that also meet certain Job type and Status criteria.
This is described further in the attached document.

I have tried the function 'SMALL' with multiple 'IF' statements, but can't get it to work.

This example is an extract of a larger spreadsheet with much more data variations in the descriptions in columns C and D.

If someone could give me the formula I need to enter in cells G3:K4, that gives me the date occurrences I need, I would be most grateful.

Forgive me if I have not got the forum etiquette or format correct. I am new to this. Any advice would be welcome.
Thank you.
garymon9AX

Attached Files

Excel Forum Query.docx (15.4 KB, 10 views)

Welcome!

Please press Go Advanced and upload your Excel file. It is cumbersome to enter all your data.

Thanks xor.
My apologies.
I have now attached the spreadsheet.

Regards,
garymon9AX

Attached Files

Forum Query.xlsx (10.4 KB, 11 views)

What about Job number in A8?

Maybe like the attached. Be aware that it is array formulas.

Attached Files

Job_Number.xlsx (11.1 KB, 11 views)

Ah yes sorry xor, I forgot to mention that.
There are some jobs with an 's' prefix which would also need to be ignored (as with the suffix hyphen and it's following numbers).
We need to match only the part of the job numbers after any 's' prefix and before the hyphen.
Thanks for your patience with the newbie

Regards,
garymon9AX

Thanks xor, that is great.
I am not familiar with those functions so could never have got there. It took me hours of fumbling before joining this forum and posting the problem. So I am impressed with the speed of your response.

At the risk of being cheeky - is there a way of making the formula remove the 's' prefix on the job number at 'A8' rather than manually removing it?
I say this because the data table is populated from a query run separately for different customers, so the data will change frequently and the 's' prefixes are a common factor that will crop up often.
This extra part of the solution would make it perfect.

Regards,
garymon

I attach a new file where I have made helper cells in N2:N15. You can hide this column if you want or move it (not copy) to another place out of sight.

Are you aware that array formulas like thos in G3:K4 must be entered in a special way. Normally you just press Enter to complete a formula. Array formulas must be entered by holding down Ctrl and Shift before pressing Enter. If you do it correctly Excel will automatically put braces {} around the formula. When you have entered one array formula correctly you can copy it to other cells in the normal way. Don't try to enter the {} manually. If you edit an array formula you must again hold down Ctrl and Shift before pressing Enter.

Attached Files

Job_Number_2.xlsx (11.3 KB, 11 views)

Hi xor,

That is excellent. Thank you so much.

I can now work with this to produce the report I need, to keep my customer happy.
Thanks for the advice about entering array formula, I'll take care with that.
Much appreciated. Have a good evening.

Regards,
garymon9AX

That's fine.

If you extend your data below row 15 remember to extend the formula in the helper column (column N) similarly (and of course the references in all formulas in G3:K4).