Conditional multi result lookup with multiple criteria

[Greveller]

Added attachment

[AliGW]

Welcome to the forum!

Will you please attach a sample Excel workbook? We are not able to work with or manipulate a picture of one.

1. Make sure that your sample data are REPRESENTATIVE of your real data. 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 is also shown (mock up the results manually).

3. Make sure that all confidential data is removed or replaced with dummy data first (e.g. names, addresses, E-mails, etc.).

4. Try to avoid using merged cells as they cause lots of problems.

Unfortunately the attachment icon doesn't work at the moment, so to attach an Excel file you have to do the following: just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.

Please pay particular attention to point 2 (above): without an idea of your intended outcomes, it is often very difficult to offer appropriate advice.

[Greveller]

Hi,
I am trying to create a spreadsheet for work to record missing paperwork and display it in a table that can easily be emailed to staff each week.

It uses up multiple criteria lookups to fill the table (Data, Name, and Code) from the database and displays the results.

I want it to do two things:
• Lookup whether the paperwork has been returned and if so not display it
• Either concatenate the results or place multiple results on subsequent lines
Any help would be appreciated.

Thanks!

[AliGW]

I'm sorry - in what way is the workbook not working for you? You seem to have managed to do what you are asking for already. What am I missing here?

[Greveller]

It doesn't currently display multiple results with matching criteria. For example in the database there are two instances of 'Tristan Crittall', 'JC', '14/12/16', and formula only displays the first result - 25787 and not the 99999 result.

Also I have no idea how to not display the results that have been marked as 'returned'

Thanks

[AliGW]

Ah, I see. OK - thanks for clarifying.

[FlameRetired]

It sounds like you wish to concatenate the 25787; 99999 in cell D20 of 'Email Template' just as E24 has 25789; 299999.

If that is the case:

• Are you OK with VBA? I don't know of a way to concatenate multiple values without it.
• Are you OK with a helper column in 'Missing Paperwork'?

Additionally do you wish to have corrected results in column L ... ie, 4 in L19?

[Greveller]

That is exactly what I want to do. I don't use excel much and have had to look up the formulas that I have used.

Is a VBA a Macro?

I have never used VBA's, Macro's, or Helper Columns before but am willing to if they help.

And yes, I'd rather have the correct number of instances in column L - didn't know that that was possible!!

Thanks for any help that you can offer

[FlameRetired]

This has a user defined function called Concatall. Although the code is included in the attached if you are interested you can find it here on the forum. The link is:

http://www.excelforum.com/tips-and-t...ml#post3096647

That is where the VBA comes in. The file must be saved as a macro file ... extension .xlsm

The helper column formula is array entered in G3 of 'Missing Paperwork' and filled down until you get blanks. If you aren’t familiar with array-entered formulas array enter means the formula must be committed from edit mode by simultaneously pressing and holding down Ctrl and Shift while hitting Enter.

=IF(D3 <> "",Concatall(IF((A3=$A$3:$A3)*(C3=$C$3:$C3)*($E$3:E3 <> "Yes"),$D$3:$D3,""),"; "),D3&"")

That will concatenate the 25787; 99999 and import the other Job Numbers / Info.

Then in D12 'Email Template' ... again array entered ...

=IFERROR(INDEX('Missing Paperwork'!$G$3:$G$100,MATCH(2,1/(('Email Template'!$B$11='Missing Paperwork'!$A$3:$A$100)*('Email Template'!D$11='Missing Paperwork'!$C$3:$C$100)*('Email Template'!$B12='Missing Paperwork'!$B$3:$B$100)),1)),"")

Copy that cell and paste successively into each of the other date cells (Monday-Sunday / JC RA Photos Yard Card). These must be pasted individually ... (already done in the attached).

Then in L13 and filled down this non-array formula filled down.

=COUNTIFS('Missing Paperwork'!A:A,$K13,'Missing Paperwork'!B:B,">="&$B$12,'Missing Paperwork'!B:B,"<="&$B$36,'Missing Paperwork'!D:D,"<>")+COUNTIFS('Missing Paperwork'!A:A,$K13,'Missing Paperwork'!D:D,"*;*")-COUNTIFS('Missing Paperwork'!A:A,$K13,'Missing Paperwork'!E:E,"Yes")

Try changing the E3 drop down cell to "Yes" and see the values in the helper, the calendar and column L change.

In column L:
Jaymie Bennett has no Job Numbers and the formula in column L returns 0 which is what I believe you want.

Douglas Cotton is flagged red and I couldn't read your intentions. I note that there is one Job Number and "no PPE" as text under Job Number / Info.

Tristan Crittall returns 4 unless the drop down is "Yes". Then Tristan is 3.

[Greveller]

Hi FlameRetired,

This is exactly what I wanted. I do not understand exactly how it works (I'm now going to have to look into the 'Concatall' rule to try and get my head around it).

The next step for me is to try and see how to get it into Google Sheets (I know it's not as feature based but it's what work uses and is great for collaboration).

Thanks for this!

[FlameRetired]

Good deal. You're welcome and thanks for the feedback.

RE: Google Sheets

While I am not that well acquainted with GS I have encountered this in another thread (link not found). I recall that GS has a function that does what Concatall does. Unfortunately I do not recall what that is either. Search GS ... for example the JOIN function. That should put you "in the neighborhood".

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.