Looking for a specific number in a cell that can have multiple numbers separated by a comma (Using Mac Excel)

[HansM]

I have two data set sheets, (Sheet1) contains invoice data and the other has service data. Unfortunately, the (Sheet2) service sheet does not show the invoice used to pay for the service and the Invoice sheet will often combine services that were paid by one invoice (example: 3062, 3060, 3061, 3058, 3139.) So I have created a third sheet (Sheet3) to look for the data and combine the two data sheets; however, I am having an issue when trying to search the status of a job if it is associated with other jobs. I want the invoice number that is associated with the job number listed in the (B Column) and the paid status in the (F Column).

Sheet # 1

Book1
	A	B	C	D	E	F	G	H	I	J	K
1	Invoice #	Client name	Client email	Client phone	Service street	Service city	Service province	Service ZIP	Marked paid date	Job #s	Status
2	2545	Client 1	Client 1 EMAIL	Client 1 PHONE #	Client 1 ADDRESS	Boise	ID	83706	31-Jan-18	3161	Paid
3	2536	Client 2	Client 2 EMAIL	Client 2 PHONE #	Client 2 ADDRESS	Park City	Utah	84060	26-Jan-18	3163	Paid
4	2537	Client 3	Client 3 EMAIL	Client 3 PHONE #	Client 3 ADDRESS	Park City	Utah	84060	29-Jan-18	3164	Paid
5	2547	Client 4	Client 4 EMAIL	Client 4 PHONE #	Client 4 ADDRESS	Tacoma	Washington	98447	1-Feb-18	3167	Paid
6	2546	Client 5	Client 5 EMAIL	Client 5 PHONE #	Client 5 ADDRESS	Kirkland	Washington	98034	31-Jan-18	3169	Paid
7	2544	Client 6	Client 6 EMAIL	Client 6 PHONE #	Client 6 ADDRESS	Wylie	Texas	75098	31-Jan-18	3170	Paid
8	2553	Client 7	Client 7 EMAIL	Client 7 PHONE #	Client 7 ADDRESS	Star	Idaho	83669	1-Feb-18	3176	Paid
9	2521	Client 8	Client 8 EMAIL	Client 8 PHONE #	Client 8 ADDRESS	Evanston	WY	82930	22-Jan-18	3043, 3084, 3083, 3085, 3086, 3088, 3087, 3090, 3092, 3089, 3091, 3094, 3096, 3093, 3095	Paid
10	2514	Client 9	Client 9 EMAIL	Client 9 PHONE #	Client 9 ADDRESS	Kent	Washington	98030	5-Mar-18	3046, 3047	Paid
11	2512	Client 10	Client 10 EMAIL	Client 10 PHONE #	Client 10 ADDRESS	Sedro-Woolley	Washington	98284	27-Mar-18	3048, 3039	Paid
12	2526	Client 11	Client 11 EMAIL	Client 11 PHONE #	Client 11 ADDRESS	Shoreline	Washington	98133	20-Feb-18	3062, 3060, 3061, 3058, 3139	Paid
13	2511	Client 12	Client 12 EMAIL	Client 12 PHONE #	Client 12 ADDRESS	Portland	Oregon	97209	12-Feb-18	3077, 3076	Paid
14	2520	Client 13	Client 13 EMAIL	Client 13 PHONE #	Client 13 ADDRESS	Provo	UT	84601	16-Jan-18	3102, 3101	Paid
15	2551	Client 14	Client 14 EMAIL	Client 14 PHONE #	Client 14 ADDRESS	Seattle	Washington	98108	20-Feb-18	3111, 3110, 3109, 3112	Paid
16	2524	Client 15	Client 15 EMAIL	Client 15 PHONE #	Client 15 ADDRESS	Renton	Washington	98056	19-Jan-18	3119, 3120	Paid
17	2542	Client 16	Client 16 EMAIL	Client 16 PHONE #	Client 16 ADDRESS	Tukwila	Washington	98168	20-Feb-18	3132, 3131, 3116, 3125, 3129, 3115, 3140	Paid
18	2527	Client 17	Client 17 EMAIL	Client 17 PHONE #	Client 17 ADDRESS	Longview	WA	98632	27-Mar-18	3134, 3136, 3135, 3137	Paid
19	2541	Client 18	Client 18 EMAIL	Client 18 PHONE #	Client 18 ADDRESS	Issaquah	Washington	98027	26-Feb-18	3143, 3147	Paid
20	2548	Client 19	Client 19 EMAIL	Client 19 PHONE #	Client 19 ADDRESS	Kent	Washington	98030	26-Feb-18	3157, 3159, 3158	Paid
Sheet 1 (Invoice Sheet)

Sheet # 2

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	Job #	Date	Visit title	Client name	Client email	Client phone	Service street	Service city	Service province	Service ZIP	Visit completed	Assigned to	Line items	One-off job ($)
2	3043	22-Jan-18	Client 8 - Thermal	Client 8	Client 8 EMAIL	Client 8 PHONE #	Client 8 ADDRESS	Evanston		82930	Yes	Nelly Tone	Canine Inspection	1000
3	3084	22-Jan-18	Client 8 - Thermal	Client 8	Client 8 EMAIL	Client 8 PHONE #	Client 8 ADDRESS	Evanston		82930	Yes	Nelly Tone	Canine Inspection	1000
4	3083	22-Jan-18	Client 8 - Thermal	Client 8	Client 8 EMAIL	Client 8 PHONE #	Client 8 ADDRESS	Evanston		82930	Yes	Nelly Tone	Thermal Remediation	0
5	3085	22-Jan-18	Client 8 - Thermal	Client 8	Client 8 EMAIL	Client 8 PHONE #	Client 8 ADDRESS	Evanston		82930	Yes	Nelly Tone	Canine Inspection	1620
6	3086	22-Jan-18	Client 8 - Thermal	Client 8	Client 8 EMAIL	Client 8 PHONE #	Client 8 ADDRESS	Evanston		82930	Yes	Talbert Walkin	Thermal Remediation	1200
7	3088	22-Jan-18	Client 8 - Thermal	Client 8	Client 8 EMAIL	Client 8 PHONE #	Client 8 ADDRESS	Evanston		82930	Yes	Talbert Walkin	Thermal Remediation	1100
8	3087	22-Jan-18	Client 8 - Thermal	Client 8	Client 8 EMAIL	Client 8 PHONE #	Client 8 ADDRESS	Evanston		82930	Yes	Nelly Tone	Thermal Remediation	1100
9	3090	22-Jan-18	Client 8 - Thermal	Client 8	Client 8 EMAIL	Client 8 PHONE #	Client 8 ADDRESS	Evanston		82930	Yes	Nelly Tone	Thermal Remediation	1200
10	3092	22-Jan-18	Client 8 - Thermal	Client 8	Client 8 EMAIL	Client 8 PHONE #	Client 8 ADDRESS	Evanston		82930	Yes	Talbert Walkin	Canine Inspection	0
11	3089	22-Jan-18	Client 8 - Thermal	Client 8	Client 8 EMAIL	Client 8 PHONE #	Client 8 ADDRESS	Evanston		82930	Yes	Nelly Tone	Thermal Remediation	1200
12	3091	22-Jan-18	Client 8 - Thermal	Client 8	Client 8 EMAIL	Client 8 PHONE #	Client 8 ADDRESS	Evanston		82930	Yes	Talbert Walkin	Thermal Remediation	1100
13	3094	22-Jan-18	Client 8 - Thermal	Client 8	Client 8 EMAIL	Client 8 PHONE #	Client 8 ADDRESS	Evanston		82930	Yes	Nelly Tone	Thermal Remediation	1000
14	3096	22-Jan-18	Client 8 - Thermal	Client 8	Client 8 EMAIL	Client 8 PHONE #	Client 8 ADDRESS	Evanston		82930	Yes	Nelly Tone	Canine Inspection	1000
15	3093	22-Jan-18	Client 8 - Thermal	Client 8	Client 8 EMAIL	Client 8 PHONE #	Client 8 ADDRESS	Evanston		82930	Yes	Talbert Walkin	Canine Inspection	1000
16	3095	22-Jan-18	Client 8 - Thermal	Client 8	Client 8 EMAIL	Client 8 PHONE #	Client 8 ADDRESS	Evanston		82930	Yes	Talbert Walkin	Canine Inspection	1000
17	3161	31-Jan-18	Client 1 - Thermal	Client 1	Client 1 EMAIL	Client 1 PHONE #	Client 1 ADDRESS	Boise		83706	Yes	Nelly Tone	Thermal Remediation	1200
18	3163	26-Jan-18	Client 2 - Thermal	Client 2	Client 2 EMAIL	Client 2 PHONE #	Client 2 ADDRESS	Park City		84060	Yes	Talbert Walkin	Canine Inspection	1200
19	3164	29-Jan-18	Client 3 - Thermal	Client 3	Client 3 EMAIL	Client 3 PHONE #	Client 3 ADDRESS	Park City		84060	Yes	Nelly Tone	Canine Inspection	900
20	3167	1-Feb-18	Client 4 - Thermal	Client 4	Client 4 EMAIL	Client 4 PHONE #	Client 4 ADDRESS	Tacoma		98447	Yes	Nelly Tone	Thermal Remediation	900
21	3169	31-Jan-18	Client 5 - Thermal	Client 5	Client 5 EMAIL	Client 5 PHONE #	Client 5 ADDRESS	Kirkland		98034	Yes	Talbert Walkin	Canine Inspection	350
22	3170	31-Jan-18	Client 6 - Thermal	Client 6	Client 6 EMAIL	Client 6 PHONE #	Client 6 ADDRESS	Wylie		75098	Yes	Nelly Tone	Canine Inspection	1100
23	3176	1-Feb-18	Client 7 - Thermal	Client 7	Client 7 EMAIL	Client 7 PHONE #	Client 7 ADDRESS	Star		83669	Yes	Talbert Walkin	Thermal Remediation	1100
24	3046	5-Mar-18	Client 9 - Thermal	Client 9	Client 9 EMAIL	Client 9 PHONE #	Client 9 ADDRESS	Kent		98030	Yes	Talbert Walkin	Canine Inspection	1440
25	3047	5-Mar-18	Client 9 - Thermal	Client 9	Client 9 EMAIL	Client 9 PHONE #	Client 9 ADDRESS	Kent		98030	Yes	Talbert Walkin	Thermal Remediation	2240
26
Sheet 2 (Service Sheet)

Shee1 3 Data Set

Book1
	A	B	C	D	E	F
1	Job #	Invoice #	Value	Service Date	Technician	Status
2	3043		1000	1/22/18	Nelly Tone
3	3084		1000	1/22/18	Nelly Tone
4	3083		0	1/22/18	Nelly Tone
5	3085		1620	1/22/18	Nelly Tone
6	3086		1200	1/22/18	Talbert Walkin
7	3088		1100	1/22/18	Talbert Walkin
8	3087		1100	1/22/18	Nelly Tone
9	3090		1200	1/22/18	Nelly Tone
10	3092		0	1/22/18	Talbert Walkin
11	3089		1200	1/22/18	Nelly Tone
12	3091		1100	1/22/18	Talbert Walkin
13	3094		1000	1/22/18	Nelly Tone
14	3096		1000	1/22/18	Nelly Tone
15	3093		1000	1/22/18	Talbert Walkin
16	3095		1000	1/22/18	Talbert Walkin
17	3161		1200	1/31/18	Nelly Tone
18	3163		1200	1/26/18	Talbert Walkin
19	3164		900	1/29/18	Nelly Tone
20	3167		900	2/1/18	Nelly Tone
21	3169		350	1/31/18	Talbert Walkin
22	3170		1100	1/31/18	Nelly Tone
23	3176		1100	2/1/18	Talbert Walkin
24	3046		1440	3/5/18	Talbert Walkin
25	3047		2240	3/5/18	Talbert Walkin
Sheet 3 Data Set

Cell Formulas
Range	Formula
C2:C25	C2	=IFERROR(VLOOKUP(A2,'Sheet 2 (Service Sheet)'!A:N,14,FALSE),0)
D2:D25	D2	=IFERROR(VLOOKUP(A2,'Sheet 2 (Service Sheet)'!A:N,2,FALSE),0)
E2:E25	E2	=IFERROR(VLOOKUP(A2,'Sheet 2 (Service Sheet)'!A:N,12,FALSE),0)

 

[Jeffrey Mahoney]

In Cell B2: =TEXTJOIN(", ",TRUE,FILTER('Sheet 1 (Invoice Sheet)'!$A$2:$A$100000,ISNUMBER(SEARCH("*"&A2&"*",'Sheet 1 (Invoice Sheet)'!$J$2:$J$100000)),""))

 

[HansM]

In Cell B2: =TEXTJOIN(", ",TRUE,FILTER('Sheet 1 (Invoice Sheet)'!$A$2:$A$100000,ISNUMBER(SEARCH("*"&A2&"*",'Sheet 1 (Invoice Sheet)'!$J$2:$J$100000)),""))

Jeffery, Thank you so much for the help. I am almost there; everything worked great except for invoices that were under the number sequence of 1,000, at invoice 999 and lower. It added a whole array of invoice numbers to each cell.

 

[alansidman]

an alternative is with Power Query

let
    T1 = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    SplitCol = Table.ExpandListColumn(Table.TransformColumns(Table.TransformColumnTypes(T1, {{"Job #s", type text}}, "en-US"), {{"Job #s", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Job #s"),
    Trim = Table.TransformColumns(SplitCol,{{"Job #s", Text.Trim, type text}}),
    T2 = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(T2,{{"Job #", type text}}),
    MQ = Table.NestedJoin(Trim, {"Job #s"}, #"Changed Type", {"Job #"}, "Table2", JoinKind.FullOuter),
    #"Expanded Table2" = Table.ExpandTableColumn(MQ, "Table2", {"Job #", "Date", "Assigned to", "One-off job ($)"}, {"Job #", "Date", "Assigned to", "One-off job ($)"}),
    #"Removed Other Columns" = Table.SelectColumns(#"Expanded Table2",{"Invoice #", "Job #s", "Status", "Date", "Assigned to", "One-off job ($)"}),
    #"Reordered Columns" = Table.ReorderColumns(#"Removed Other Columns",{"Job #s", "Invoice #", "Status", "One-off job ($)", "Date", "Assigned to"}),
    #"Changed Type2" = Table.TransformColumnTypes(#"Reordered Columns",{{"Date", type date}})
in
    #"Changed Type2"

 

[Fluff]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Also can a job numbe have more than one invoice number?

 

[Jeffrey Mahoney]

I can't duplicate the issue because I don't have your full data set.