Sep 09 2019 04:21 AM
Dear community,
I am trying to set up a formula to do the following:
I have a list of customers with a certain number of work orders.
I have a list of invoices for each customer, listing a subset of work orders, i.e. not all work orders have been billed.
The Question: I want to find out which work orders have been billed.
Problem: in the invoices list, the work orders are all listed in one cell, separated by "|". The customer / work order list ist one item per line.
We are talking about 22000 work orders and 3000 invoices comprising one or several work orders.
The attached sample file gives an indication of the problem.
Thank you very much for any insight you might have.
Sep 09 2019 04:35 AM - edited Sep 09 2019 04:36 AM
SolutionYou may try something like this...
On Workorders Sheet,
In C2
=IF(ISNUMBER(SEARCH("| "&TEXT(B2,"000")&" |"," | "&INDEX(Invoices!B:B,MATCH(A2,Invoices!A:A,0))&" |")),"Billed","Not Billed")
and then copy it down.
Sep 09 2019 04:53 AM
Thank you for your speedy reply. After transposing your formula to the German Excel-Version, I only get "not billed" as a result. hmmm... I suspect, there might be a problem with the "|", since the first value does not have a leading "|".
Whoever desigend this output...
Cheers
Ben
Sep 09 2019 05:13 AM
Sep 09 2019 05:42 AM
Great! Glad it worked as desired.
Please take a minute to accept the post with the proposed formula as an Answer to mark your question as Solved. Also, you may hit the Like button if you liked the proposed answer.
Sep 09 2019 04:35 AM - edited Sep 09 2019 04:36 AM
SolutionYou may try something like this...
On Workorders Sheet,
In C2
=IF(ISNUMBER(SEARCH("| "&TEXT(B2,"000")&" |"," | "&INDEX(Invoices!B:B,MATCH(A2,Invoices!A:A,0))&" |")),"Billed","Not Billed")
and then copy it down.