SOLVED
Home

Find match in multiple numbers in one cell

%3CLINGO-SUB%20id%3D%22lingo-sub-843725%22%20slang%3D%22en-US%22%3EFind%20match%20in%20multiple%20numbers%20in%20one%20cell%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-843725%22%20slang%3D%22en-US%22%3E%3CP%3EDear%20community%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20am%20trying%20to%20set%20up%20a%20formula%20to%20do%20the%20following%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20a%20list%20of%20customers%20with%20a%20certain%20number%20of%20work%20orders.%3C%2FP%3E%3CP%3EI%20have%20a%20list%20of%20invoices%20for%20each%20customer%2C%20listing%20a%20subset%20of%20work%20orders%2C%20i.e.%20not%20all%20work%20orders%20have%20been%20billed.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20Question%3A%20I%20want%20to%20find%20out%20which%20work%20orders%20have%20been%20billed.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EProblem%3A%20in%20the%20invoices%20list%2C%20the%20work%20orders%20are%20all%20listed%20in%20one%20cell%2C%20separated%20by%20%22%7C%22.%20The%20customer%20%2F%20work%20order%20list%20ist%20one%20item%20per%20line.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWe%20are%20talking%20about%2022000%20work%20orders%20and%203000%20invoices%20comprising%20one%20or%20several%20work%20orders.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20attached%20sample%20file%20gives%20an%20indication%20of%20the%20problem.%3C%2FP%3E%3CP%3EThank%20you%20very%20much%20for%20any%20insight%20you%20might%20have.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-843725%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EBI%20%26amp%3B%20Data%20Analysis%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-843754%22%20slang%3D%22en-US%22%3ERe%3A%20Find%20match%20in%20multiple%20numbers%20in%20one%20cell%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-843754%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F405789%22%20target%3D%22_blank%22%3E%40BeRol%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYou%20may%20try%20something%20like%20this...%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EOn%20Workorders%20Sheet%2C%3C%2FP%3E%3CP%3EIn%20C2%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-markup%22%3E%3CCODE%3E%3DIF(ISNUMBER(SEARCH(%22%7C%20%22%26amp%3BTEXT(B2%2C%22000%22)%26amp%3B%22%20%7C%22%2C%22%20%7C%20%22%26amp%3BINDEX(Invoices!B%3AB%2CMATCH(A2%2CInvoices!A%3AA%2C0))%26amp%3B%22%20%7C%22))%2C%22Billed%22%2C%22Not%20Billed%22)%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Eand%20then%20copy%20it%20down.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-843795%22%20slang%3D%22en-US%22%3ERe%3A%20Find%20match%20in%20multiple%20numbers%20in%20one%20cell%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-843795%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F394231%22%20target%3D%22_blank%22%3E%40Subodh_Tiwari_sktneer%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%20for%20your%20speedy%20reply.%20After%20transposing%20your%20formula%20to%20the%20German%20Excel-Version%2C%20I%20only%20get%20%22not%20billed%22%20as%20a%20result.%20hmmm...%20I%20suspect%2C%20there%20might%20be%20a%20problem%20with%20the%20%22%7C%22%2C%20since%20the%20first%20value%20does%20not%20have%20a%20leading%20%22%7C%22.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWhoever%20desigend%20this%20output...%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ECheers%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EBen%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-843832%22%20slang%3D%22en-US%22%3ERe%3A%20Find%20match%20in%20multiple%20numbers%20in%20one%20cell%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-843832%22%20slang%3D%22en-US%22%3EEUREKA!%20I%20found%20it.%20I%20failed%20to%20translate%20one%20command%20key%20word.%3CBR%20%2F%3E%3CBR%20%2F%3EThanks%20again.%3CBR%20%2F%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-843853%22%20slang%3D%22en-US%22%3ERe%3A%20Find%20match%20in%20multiple%20numbers%20in%20one%20cell%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-843853%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F405789%22%20target%3D%22_blank%22%3E%40BeRol%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EGreat!%20Glad%20it%20worked%20as%20desired.%3C%2FP%3E%3CP%3EPlease%20take%20a%20minute%20to%20accept%20the%20post%20with%20the%20proposed%20formula%20as%20an%20Answer%20to%20mark%20your%20question%20as%20Solved.%20Also%2C%20you%20may%20hit%20the%20Like%20button%20if%20you%20liked%20the%20proposed%20answer.%3C%2FP%3E%3C%2FLINGO-BODY%3E
BeRol
New Contributor

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.

 

 

 

4 Replies
Solution

@BeRol 

You 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.

@Subodh_Tiwari_sktneer 

 

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

EUREKA! I found it. I failed to translate one command key word.

Thanks again.

@BeRol 

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.

Related Conversations
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies
Early preview of Microsoft Edge group policies
Sean Lyndersay in Discussions on
65 Replies
*Updated 9/3* Syncing in Microsoft Edge Preview Channels
Elliot Kirk in Articles on
201 Replies
Tabs and Dark Mode
cjc2112 in Discussions on
2 Replies