Excel to return a value in a column, if multiple criteria meets

[Ann Ooi]

I tried to return a value in sheet1, column A (vertical), to sheet 2 (Horizontal format), if the "Process_Step" and "WO" is matched. As the data is huge and there're many columns, I do not know how to specifically to use the index and match or vlookup, to return the column A value (Date) if the criteria are meet. The INDEX formula I written, is not working as I understand it's not making sense to Excel. [=IF(INDEX(WIP!$A$1:$W$8835,MATCH($B3,WIP!$U:$U,0),MATCH(C$2,WIP!$L:$L,0)),C3=WIP!$A:A,"")]

 

[alansidman]

1. We cannot manipulate data in a picture. Please upload same data using XL2BB. See my signature for link to understand
2. Also upload a mocked up result based upon the data you have presented. It is nice to know how you want your solution presented and also to understand exactly what you are asking.

 

[Ann Ooi]

I corrected the index/match formula as =INDEX(WIP!$L:$L,MATCH(Sheet2!C$2&$B3,INDEX(WIP!$A:$A&WIP!$U:$U,0),0)), and it works now.
I changed the table format to below, but there are few matching that is duplicates, which means the value to return is more than 1, is there anyway, I can return all the duplicate lines in worksheet "WIP"/ column L, to the cell below at 2nd sheet? e.g. FVI/Marking

 

[alansidman]

Still cannot manipulate data in a picture. Please read post #2

 

[Ann Ooi]

HI Alan, I copied as below. There're 3 worksheets in my file, and I'm having hard time with this file, as it's keeps on calculating, I have to manual calculate the formula and row by row copy paste the formula to every cells. even I left only one formula for reference, it's still keeps on calculating, which I have no idea what is the problem. Is there anyway to have VBA to paste the value offer instead?

Worksheet {WIP_Flow} is the final result I'm expecting
worksheet {Pivot} is I do pivot to get the range of date (a month data), and copy paste over to WIP_Flow
Worksheet {WIP} is the range of data for 1 month, highlighted columns are all the information I used to populate the final result.

WIP Table_test.xlsx
	E
12	20191107
WIP

WIP Table_test.xlsx
	E
21
Pivot

WIP Table_test.xlsx
	D
2	TTST
WIP_Flow

Cell Formulas
Range	Formula
D2	D2	=IF(ISNA(INDEX(WIP!$G:$G,MATCH(WIP_Flow!D$1&$B2,INDEX(WIP!$A:$A&WIP!$K:$K,0),0))),"",INDEX(WIP!$G:$G,MATCH(WIP_Flow!D$1&$B2,INDEX(WIP!$A:$A&WIP!$K:$K,0),0)))

Named Ranges
Name	Refers To	Cells
WIP!_FilterDatabase	=WIP!$A$1:$K$58294	D2

 

[alansidman]

There is no data that resembles your post #1 in what you have provided in post #5. You will need to highlight the entire range to copy to XL2BB. It appears that you may have only highlighted and copied single cells.

 

[Ann Ooi]

Hi Alan, Hopefully I do it right this time.

WIP Flow Table_Sample.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L
1	Cut Off Date	LOTID	LOT NO	DEVICE	PT ISSUE DATE	CURRENT QTY	STEP_LOCATION_NEW	PROCESS_STEP	PACKAGE	WO #	Type	Concatenate(LotID&WO #)
2	6/1/2020	018SD06E	018SD06E	54-90-53327-016G	20200430	5153	T100	Top Mark	SLGA	11235420	54-90	018SD06E11235420
3	6/1/2020	018SD07E	018SD07E	54-90-53327-016G	20200430	9571	T475	Elec Test	SLGA	11235421	54-90	018SD07E11235421
4	6/2/2020	018SD07E	018SD07E	54-90-53327-016G	20200430	9571	T100	Elec Test	SLGA	11235421	54-90	018SD07E11235421
5	6/3/2020	018SD07E	018SD07E	54-90-53327-016G	20200430	3723	A165	Top Mark	SLGA	11235421	54-90	018SD07E11235421
6	6/1/2020	018SD15B01	018SD15	54-62-74913-128G	20200504	4291	A080	FVI	SLGA	11192861	54-62	018SD15B0111192861
7	6/2/2020	018SD15B01	018SD15	54-62-74913-128G	20200504	4291	T450	FVI	SLGA	11192861	54-62	018SD15B0111192861
8	6/7/2020	020SD11E	020SD11E	54-90-36803-016G	20200519	5640	A165	Elec Test	SLGA	11264206	54-90	020SD11E11264206
9	6/8/2020	020SD11E	020SD11E	54-90-36803-016G	20200519	2519	A080	T470	SLGA	11264206	54-90	020SD11E11264206
10	6/7/2020	020SD13E	020SD13E	54-90-36803-016G	20200519	5228	T085	Elec Test	SLGA	11262279	54-90	020SD13E11262279
11	6/8/2020	020SD13E	020SD13E	54-90-36803-016G	20200519	5215	A080	LeadScan	SLGA	11262279	54-90	020SD13E11262279
12	6/1/2020	022SD03E	022SD03E	54-90-63803-032G	20200527	6811	A080	Elec Test	SLGA	11250922	54-90	022SD03E11250922
13	6/4/2020	022SD05E	022SD05E	54-90-63796-032G	20200529	14881	T100	Elec Test	SLGA	11262266	54-90	022SD05E11262266
14	6/5/2020	022SD05E	022SD05E	54-90-63796-032G	20200529	7434	T100	Elec Test	SLGA	11262266	54-90	022SD05E11262266
15	6/4/2020	022SD06E	022SD06E	54-90-63796-032G	20200530	14628	T100	Elec Test	SLGA	11263279	54-90	022SD06E11263279
16	6/5/2020	022SD06E	022SD06E	54-90-63796-032G	20200530	7408	A080	Elec Test	SLGA	11263279	54-90	022SD06E11263279
17	6/2/2020	022SD0EE	022SD0EE	54-90-64087-128G	20200527	3928	T085	Elec Test	SLGA	11258913	54-90	022SD0EE11258913
18	6/1/2020	022SD69B01	022SD69	54-62-14515-016GA	20200528	4012	A080	FVI	TSOP	11240418	54-62	022SD69B0111240418
19	6/1/2020	022SD69B02	022SD69	54-62-14515-016GA	20200528	2877	T450	Marking	TSOP	11240418	54-62	022SD69B0211240418
20	6/1/2020	022SD70B01	022SD70	54-62-14515-016GA	20200528	2879	T450	Plating	TSOP	11240419	54-62	022SD70B0111240419
21	6/1/2020	022SD70B02	022SD70	54-62-14515-016GA	20200528	4130	A080	Marking	TSOP	11240419	54-62	022SD70B0211240419
22	6/1/2020	022SD71B01	022SD71	54-62-14515-016GA	20200529	2875	A080	Plating	TSOP	11240420	54-62	022SD71B0111240420
23	6/2/2020	022SD71B01	022SD71	54-62-14515-016GA	20200529	2875	T100	FVI	TSOP	11240420	54-62	022SD71B0111240420
24	6/1/2020	022SD71B02	022SD71	54-62-14515-016GA	20200529	5289	T475	PMC	TSOP	11240420	54-62	022SD71B0211240420
25	6/2/2020	022SD71B02	022SD71	54-62-14515-016GA	20200529	5289	T100	Forming	TSOP	11240420	54-62	022SD71B0211240420
26	6/1/2020	022SD72B01	022SD72	54-62-14515-016GA	20200529	5617	A080	Dejunk	TSOP	11240421	54-62	022SD72B0111240421
27	6/2/2020	022SD72B01	022SD72	54-62-14515-016GA	20200529	5617	T450	Forming	TSOP	11240421	54-62	022SD72B0111240421
28	6/3/2020	022SDL1E	022SDL1E	54-90-64087-128G	20200526	7480	A160	Elec Test	SLGA	11258905	54-90	022SDL1E11258905
29	6/4/2020	022SDL1E	022SDL1E	54-90-64087-128G	20200526	7267	T300	Elec Test	SLGA	11258905	54-90	022SDL1E11258905
30	6/1/2020	022SDL2E	022SDL2E	54-90-64086-128G	20200527	2857	T450	Elec Test	SLGA	11250892	54-90	022SDL2E11250892
31	6/5/2020	023SD01B02	023SD01	54-62-64223-064G	20200605	3771	A160	Die Attach	SLGA	11253133	54-62	023SD01B0211253133
32	6/6/2020	023SD01B02	023SD01	54-62-64223-064G	20200605	3771	A080	Wire Bond	SLGA	11253133	54-62	023SD01B0211253133
33	6/7/2020	023SD01B02	023SD01	54-62-64223-064G	20200605	3764	T450	PMC	SLGA	11253133	54-62	023SD01B0211253133
34	6/13/2020	023SD01E	023SD01E	54-90-64095-064G	20200605	10057	T450	Elec Test	SLGA	11281691	54-90	023SD01E11281691
35	6/14/2020	023SD01E	023SD01E	54-90-64095-064G	20200605	10057	T450	Elec Test	SLGA	11281691	54-90	023SD01E11281691
36	6/14/2020	023SD02E	023SD02E	54-90-64095-064G	20200606	18784	T460	Elec Test	SLGA	11281692	54-90	023SD02E11281692
37	6/17/2020	023SD02E	023SD02E	54-90-64095-064G	20200606	11476	T100	Elec Test	SLGA	11287698	54-90	023SD02E11287698
38	6/13/2020	023SD03E	023SD03E	54-90-64095-064G	20200607	10415	A060	Elec Test	SLGA	11281695	54-90	023SD03E11281695
39	6/14/2020	023SD03E	023SD03E	54-90-64095-064G	20200607	10415	T100	Elec Test	SLGA	11281695	54-90	023SD03E11281695
40	6/3/2020	023SD04B02	023SD04	54-62-66281-032G	20200604	2499	T100	Wire Bond	SLGA	11253128	54-62	023SD04B0211253128
41	6/4/2020	023SD04B02	023SD04	54-62-66281-032G	20200604	2499	T450	Wire Bond	SLGA	11253128	54-62	023SD04B0211253128
42	6/5/2020	023SD04B02	023SD04	54-62-66281-032G	20200604	2495	T100	Mold	SLGA	11253128	54-62	023SD04B0211253128
43	6/6/2020	023SD04B02	023SD04	54-62-66281-032G	20200604	2495	A080	Laser Cut	SLGA	11253128	54-62	023SD04B0211253128
44	6/10/2020	023SD04E	023SD04E	54-90-63574-032G	20200604	8331	A080	Elec Test	SLGA	11267158	54-90	023SD04E11267158
45	6/5/2020	023SD10B01	023SD10	54-62-75015-032G	20200606	6216	T450	Wire Bond	SLGA	11253138	54-62	023SD10B0111253138
46	6/6/2020	023SD10B01	023SD10	54-62-75015-032G	20200606	6206	T450	PMC	SLGA	11253138	54-62	023SD10B0111253138
47	6/8/2020	023SD10B01	023SD10	54-62-75015-032G	20200606	6206	S020	FVI	SLGA	11253138	54-62	023SD10B0111253138
48	6/3/2020	023SD14B02	023SD14	54-62-75015-032G	20200604	6189	A230	Wire Bond	SLGA	11253130	54-62	023SD14B0211253130
49	6/4/2020	023SD14B02	023SD14	54-62-75015-032G	20200604	6189	T100	Wire Bond	SLGA	11253130	54-62	023SD14B0211253130
50	6/5/2020	023SD14B02	023SD14	54-62-75015-032G	20200604	6186	A060	PMC	SLGA	11253130	54-62	023SD14B0211253130
51	6/6/2020	023SD14B02	023SD14	54-62-75015-032G	20200604	6186	SCHD	Pkg Saw	SLGA	11253130	54-62	023SD14B0211253130
52	6/11/2020	023SD19E	023SD19E	54-90-52473-016G	20200605	10756	T100	Top Mark	SLGA	11275631	54-90	023SD19E11275631
53	6/12/2020	023SD19E	023SD19E	54-90-52473-016G	20200605	10756	T475	Top Mark	SLGA	11275631	54-90	023SD19E11275631
54	6/13/2020	023SD19E	023SD19E	54-90-52473-016G	20200605	10756	T300	Elec Test	SLGA	11275631	54-90	023SD19E11275631
55	6/10/2020	023SD20E	023SD20E	54-90-53181-032G	20200604	5914	T475	Elec Test	SLGA	11267555	54-90	023SD20E11267555
56	6/11/2020	023SD20E	023SD20E	54-90-53181-032G	20200604	5847	T475	Elec Test	SLGA	11267555	54-90	023SD20E11267555
57	6/12/2020	023SD21E	023SD21E	54-90-53181-064G	20200606	1690	T100	Top Mark	SLGA	11279630	54-90	023SD21E11279630
58	6/13/2020	023SD21E	023SD21E	54-90-53181-064G	20200606	1690	T100	Elec Test	SLGA	11279630	54-90	023SD21E11279630
59	6/12/2020	023SD21F	023SD21F	54-90-53062-064G	20200606	2504	T475	Top Mark	SLGA	11279631	54-90	023SD21F11279631
60	6/13/2020	023SD21F	023SD21F	54-90-53062-064G	20200606	2504	T100	Elec Test	SLGA	11279631	54-90	023SD21F11279631
61	6/13/2020	023SD22E	023SD22E	54-90-53181-128G	20200607	1572	A120	Elec Test	SLGA	11281694	54-90	023SD22E11281694
62	6/14/2020	023SD22E	023SD22E	54-90-53181-128G	20200607	1550	A120	Elec Test	SLGA	11281694	54-90	023SD22E11281694
63	6/3/2020	023SD24B03	023SD24	54-62-75015-032G	20200603	7704	T300	Wire Bond	SLGA	11253126	54-62	023SD24B0311253126
64	6/4/2020	023SD24B03	023SD24	54-62-75015-032G	20200603	7695	T300	PMC	SLGA	11253126	54-62	023SD24B0311253126
65	6/5/2020	023SD24B03	023SD24	54-62-75015-032G	20200603	7695	A060	Pkg Saw	SLGA	11253126	54-62	023SD24B0311253126
66	6/5/2020	023SD25B03	023SD25	54-62-75015-032G	20200605	3311	A060	Wire Bond	SLGA	11253135	54-62	023SD25B0311253135
67	6/5/2020	023SD26B01	023SD26	54-62-75015-032G	20200606	6153	T450	Wire Bond	SLGA	11253139	54-62	023SD26B0111253139
68	6/6/2020	023SD26B01	023SD26	54-62-75015-032G	20200606	6149	T100	PMC	SLGA	11253139	54-62	023SD26B0111253139
69	6/8/2020	023SD26B01	023SD26	54-62-75015-032G	20200606	6149	T100	Pkg Saw	SLGA	11253139	54-62	023SD26B0111253139
70	6/5/2020	023SD26B02	023SD26	54-62-75015-032G	20200606	6224	T300	Wire Bond	SLGA	11253139	54-62	023SD26B0211253139
71	6/6/2020	023SD26B02	023SD26	54-62-75015-032G	20200606	6224	A212	Wire Bond	SLGA	11253139	54-62	023SD26B0211253139
72	6/7/2020	023SD26B02	023SD26	54-62-75015-032G	20200606	6221	A165	PMC	SLGA	11253139	54-62	023SD26B0211253139
73	6/8/2020	023SD26B02	023SD26	54-62-75015-032G	20200606	6221	A080	Pkg Saw	SLGA	11253139	54-62	023SD26B0211253139
74	6/5/2020	023SD26B03	023SD26	54-62-75015-032G	20200606	5956	A080	Wire Bond	SLGA	11253139	54-62	023SD26B0311253139
75	6/6/2020	023SD26B03	023SD26	54-62-75015-032G	20200606	5956	T100	Wire Bond	SLGA	11253139	54-62	023SD26B0311253139
76	6/7/2020	023SD26B03	023SD26	54-62-75015-032G	20200606	5955	A080	PMC	SLGA	11253139	54-62	023SD26B0311253139
77	6/18/2020	023SD27E	023SD27E	54-90-64095-064G	20200607	11177	A080	Elec Test	SLGA	11281693	54-90	023SD27E11281693
78	6/19/2020	023SD27E	023SD27E	54-90-64095-064G	20200607	6919	A080	Elec Test	SLGA	11281693	54-90	023SD27E11281693
79	6/1/2020	023SD61	023SD61	54-62-21613-016GA	20200530	2302	A080	Die Attach	TSOP	11252478	54-62	023SD6111252478
80	6/1/2020	023SD61B02	023SD61	54-62-21613-016GA	20200530	2880	S045	Wire Bond	TSOP	11252478	54-62	023SD61B0211252478
81	6/2/2020	023SD61B02	023SD61	54-62-21613-016GA	20200530	2879	T100	PMC	TSOP	11252478	54-62	023SD61B0211252478
82	6/3/2020	023SD61B02	023SD61	54-62-21613-016GA	20200530	2879	A120	Plating	TSOP	11252478	54-62	023SD61B0211252478
83	6/1/2020	023SD61B03	023SD61	54-62-21613-016GA	20200530	2880	A165	Wire Bond	TSOP	11252478	54-62	023SD61B0311252478
84	6/2/2020	023SD61B03	023SD61	54-62-21613-016GA	20200530	5749	T100	PMC	TSOP	11252478	54-62	023SD61B0311252478
85	6/3/2020	023SD61B03	023SD61	54-62-21613-016GA	20200530	5749	A080	Plating	TSOP	11252478	54-62	023SD61B0311252478
86	6/1/2020	023SD61B04	023SD61	54-62-21613-016GA	20200530	2880	A265	Wire Bond	TSOP	11252478	54-62	023SD61B0411252478
87	6/2/2020	023SD61B05	023SD61	54-62-21613-016GA	20200530	2302	T450	Wire Bond	TSOP	11252478	54-62	023SD61B0511252478
88	6/3/2020	023SD61B05	023SD61	54-62-21613-016GA	20200530	2294	T450	PMC	TSOP	11252478	54-62	023SD61B0511252478
89	6/4/2020	023SD61B05	023SD61	54-62-21613-016GA	20200530	2294	T500	Plating	TSOP	11252478	54-62	023SD61B0511252478
90	6/2/2020	023SD62B01	023SD62	54-62-29275-016GA	20200531	1268	A080	Marking	TSOP	11252479	54-62	023SD62B0111252479
91	6/3/2020	023SD62B01	023SD62	54-62-29275-016GA	20200531	1268	A080	SVI	TSOP	11252479	54-62	023SD62B0111252479
92	6/2/2020	023SD63B01	023SD63	54-62-21613-016GA	20200531	2878	A080	Mold	TSOP	11252480	54-62	023SD63B0111252480
93	6/3/2020	023SD63B01	023SD63	54-62-21613-016GA	20200531	2878	A120	Plating	TSOP	11252480	54-62	023SD63B0111252480
94	6/4/2020	023SD63B01	023SD63	54-62-21613-016GA	20200531	2878	A080	FVI	TSOP	11252480	54-62	023SD63B0111252480
95	6/2/2020	023SD63B02	023SD63	54-62-21613-016GA	20200531	2875	T100	Mold	TSOP	11252480	54-62	023SD63B0211252480
96	6/3/2020	023SD63B02	023SD63	54-62-21613-016GA	20200531	2875	T100	PMC	TSOP	11252480	54-62	023SD63B0211252480
97	6/4/2020	023SD63B02	023SD63	54-62-21613-016GA	20200531	2875	T500	Plating	TSOP	11252480	54-62	023SD63B0211252480
98	6/2/2020	023SD63B03	023SD63	54-62-21613-016GA	20200531	2880	T500	Wire Bond	TSOP	11252480	54-62	023SD63B0311252480
99	6/3/2020	023SD63B03	023SD63	54-62-21613-016GA	20200531	5252	A080	PMC	TSOP	11252480	54-62	023SD63B0311252480
100	6/4/2020	023SD63B03	023SD63	54-62-21613-016GA	20200531	5252	A080	Plating	TSOP	11252480	54-62	023SD63B0311252480
101	6/2/2020	023SD63B04	023SD63	54-62-21613-016GA	20200531	2377	A080	Wire Bond	TSOP	11252480	54-62	023SD63B0411252480
102	6/3/2020	023SD64B01	023SD64	54-62-14514-016GA	20200601	2873	A080	Mold	TSOP	11252482	54-62	023SD64B0111252482
103	6/4/2020	023SD64B01	023SD64	54-62-14514-016GA	20200601	5752	A080	Plating	TSOP	11252482	54-62	023SD64B0111252482
104	6/3/2020	023SD64B02	023SD64	54-62-14514-016GA	20200601	2879	A080	Mold	TSOP	11252482	54-62	023SD64B0211252482
105	6/3/2020	023SD64B03	023SD64	54-62-14514-016GA	20200601	1227	A080	Mold	TSOP	11252482	54-62	023SD64B0311252482
106	6/4/2020	023SD64B03	023SD64	54-62-14514-016GA	20200601	1227	T475	Marking	TSOP	11252482	54-62	023SD64B0311252482
107	6/3/2020	023SD65B01	023SD65	54-62-14514-016GA	20200601	2875	T475	Mold	TSOP	11252483	54-62	023SD65B0111252483
108	6/4/2020	023SD65B01	023SD65	54-62-14514-016GA	20200601	6283	T100	PMC	TSOP	11252483	54-62	023SD65B0111252483
109	6/3/2020	023SD65B02	023SD65	54-62-14514-016GA	20200601	2880	T100	Wire Bond	TSOP	11252483	54-62	023SD65B0211252483
110	6/3/2020	023SD65B03	023SD65	54-62-14514-016GA	20200601	528	T100	Mold	TSOP	11252483	54-62	023SD65B0311252483
111	6/5/2020	023SD67B01	023SD67	54-62-14514-016GA	20200602	4490	T460	Dejunk	TSOP	11252485	54-62	023SD67B0111252485
112	6/4/2020	023SDP4E	023SDP4E	54-99-26546-016G		2626	T460	Top Mark	SLGA	11261251	54-99	023SDP4E11261251
113	6/2/2020	023SDT1E	023SDT1E	54-99-26626-032G		5000	A230	Elec Test	SLGA	5536632	54-99	023SDT1E5536632
114	6/3/2020	023SDT1E	023SDT1E	54-99-26626-032G		4826	A265	Top Mark	SLGA	5536632	54-99	023SDT1E5536632
115	6/4/2020	023SDT1E	023SDT1E	54-99-26626-032G		4826	A120	Top Mark	SLGA	5536632	54-99	023SDT1E5536632
116	6/5/2020	023SDT1E	023SDT1E	54-99-26626-032G		4826	T460	Elec Test	SLGA	5536632	54-99	023SDT1E5536632
117	6/6/2020	023SDT1E	023SDT1E	54-99-26626-032G		4816	T475	Top Mark	SLGA	5536632	54-99	023SDT1E5536632
118	6/6/2020	024SD01	024SD01	54-62-75785-032G	20200608	10346	T460	SCHD	SLGA	11267802	54-62	024SD0111267802
119	6/7/2020	024SD01B03	024SD01	54-62-75785-032G	20200608	1281	A080	Wire Bond	SLGA	11267802	54-62	024SD01B0311267802
120	6/8/2020	024SD01B03	024SD01	54-62-75785-032G	20200608	1281	A080	Mold	SLGA	11267802	54-62	024SD01B0311267802
121	6/9/2020	024SD01B03	024SD01	54-62-75785-032G	20200608	1281	T450	Pkg Saw	SLGA	11267802	54-62	024SD01B0311267802
122	6/6/2020	024SD02	024SD02	54-62-75785-032G	20200609	11759	A165	SCHD	SLGA	11267803	54-62	024SD0211267803
123	6/7/2020	024SD02	024SD02	54-62-75785-032G	20200609	11759	A160	SCHD	SLGA	11267803	54-62	024SD0211267803
124	6/6/2020	024SD03	024SD03	54-62-75785-032G	20200610	8339	A120	SCHD	SLGA	11267804	54-62	024SD0311267804
125	6/7/2020	024SD03	024SD03	54-62-75785-032G	20200610	8339	A160	SCHD	SLGA	11267804	54-62	024SD0311267804
126	6/11/2020	024SD05	024SD05	54-62-73386-032G	20200612	8382	A120	SCHD	SLGA	11267806	54-62	024SD0511267806
127	6/12/2020	024SD05	024SD05	54-62-73386-032G	20200612	8382	A080	Die Attach	SLGA	11267806	54-62	024SD0511267806
128	6/13/2020	024SD05B02	024SD05	54-62-73386-032G	20200612	2110	T300	Wire Bond	SLGA	11267806	54-62	024SD05B0211267806
129	6/14/2020	024SD05B02	024SD05	54-62-73386-032G	20200612	2109	T100	PMC	SLGA	11267806	54-62	024SD05B0211267806
130	6/15/2020	024SD05B02	024SD05	54-62-73386-032G	20200612	2109	A230	FVI	SLGA	11267806	54-62	024SD05B0211267806
131	6/6/2020	024SD07	024SD07	54-62-75015-032G	20200609	14510	A160	SCHD	SLGA	11267808	54-62	024SD0711267808
132	6/6/2020	024SD08	024SD08	54-62-75015-032G	20200610	14611	A080	SCHD	SLGA	11267809	54-62	024SD0811267809
133	6/7/2020	024SD08B03	024SD08	54-62-75015-032G	20200610	1933	A080	Die Attach	SLGA	11267809	54-62	024SD08B0311267809
134	6/8/2020	024SD08B03	024SD08	54-62-75015-032G	20200610	1933	A080	Wire Bond	SLGA	11267809	54-62	024SD08B0311267809
135	6/9/2020	024SD08B03	024SD08	54-62-75015-032G	20200610	1930	A080	PMC	SLGA	11267809	54-62	024SD08B0311267809
136	6/10/2020	024SD09B02	024SD09	54-62-75015-032G	20200611	6300	A080	Wire Bond	SLGA	11267811	54-62	024SD09B0211267811
137	6/11/2020	024SD09B02	024SD09	54-62-75015-032G	20200611	6255	A080	Wire Bond	SLGA	11267811	54-62	024SD09B0211267811
138	6/12/2020	024SD09B02	024SD09	54-62-75015-032G	20200611	6255	A080	PMC	SLGA	11267811	54-62	024SD09B0211267811
139	6/17/2020	024SD13E	024SD13E	54-90-63769-032G	20200608	5767	A080	Elec Test	SLGA	11290064	54-90	024SD13E11290064
140	6/17/2020	024SD14E	024SD14E	54-90-63769-032G	20200609	4183	A120	Elec Test	SLGA	11290063	54-90	024SD14E11290063
141	6/17/2020	024SD15E	024SD15E	54-90-63769-032G	20200609	3078	T100	Elec Test	SLGA	11290065	54-90	024SD15E11290065
142	6/6/2020	024SD16B01	024SD16	54-62-75784-032G	20200609	4588	T300	Die Attach	SLGA	11267815	54-62	024SD16B0111267815
143	6/7/2020	024SD16B01	024SD16	54-62-75784-032G	20200609	4587	T300	Wire Bond	SLGA	11267815	54-62	024SD16B0111267815
144	6/8/2020	024SD16B01	024SD16	54-62-75784-032G	20200609	4587	T100	Wire Bond	SLGA	11267815	54-62	024SD16B0111267815
145	6/9/2020	024SD16B01	024SD16	54-62-75784-032G	20200609	4587	T475	Wire Bond	SLGA	11267815	54-62	024SD16B0111267815
146	6/10/2020	024SD16B01	024SD16	54-62-75784-032G	20200609	4577	T475	Wire Bond	SLGA	11267815	54-62	024SD16B0111267815
147	6/11/2020	024SD16B01	024SD16	54-62-75784-032G	20200609	4577	T475	PMC	SLGA	11267815	54-62	024SD16B0111267815
148	6/12/2020	024SD16B01	024SD16	54-62-75784-032G	20200609	4577	T475	FVI	SLGA	11267815	54-62	024SD16B0111267815
149	6/6/2020	024SD16B03	024SD16	54-62-75784-032G	20200609	1801	A160	Die Attach	SLGA	11267815	54-62	024SD16B0311267815
150	6/7/2020	024SD16B03	024SD16	54-62-75784-032G	20200609	1801	A265	Die Attach	SLGA	11267815	54-62	024SD16B0311267815
151	6/8/2020	024SD16B03	024SD16	54-62-75784-032G	20200609	1801	A080	Wire Bond	SLGA	11267815	54-62	024SD16B0311267815
152	6/9/2020	024SD16B03	024SD16	54-62-75784-032G	20200609	1799	A080	Mold	SLGA	11267815	54-62	024SD16B0311267815
153	6/10/2020	024SD16B03	024SD16	54-62-75784-032G	20200609	1799	A080	Pkg Saw	SLGA	11267815	54-62	024SD16B0311267815
154	6/11/2020	024SD16B03	024SD16	54-62-75784-032G	20200609	1794	A160	FVI	SLGA	11267815	54-62	024SD16B0311267815
155	6/12/2020	024SD16B03	024SD16	54-62-75784-032G	20200609	1794	A080	FVI	SLGA	11267815	54-62	024SD16B0311267815
156	6/10/2020	024SD17B03	024SD17	54-62-75784-032G	20200611	1310	A080	Wire Bond	SLGA	11267816	54-62	024SD17B0311267816
157	6/11/2020	024SD17B03	024SD17	54-62-75784-032G	20200611	1309	A265	PMC	SLGA	11267816	54-62	024SD17B0311267816
158	6/12/2020	024SD18B03	024SD18	54-62-75784-032G	20200613	2131	A160	Wire Bond	SLGA	11267817	54-62	024SD18B0311267817
159	6/13/2020	024SD18B03	024SD18	54-62-75784-032G	20200613	2127	A170	Mold	SLGA	11267817	54-62	024SD18B0311267817
160	6/14/2020	024SD18B03	024SD18	54-62-75784-032G	20200613	2127	A165	FVI	SLGA	11267817	54-62	024SD18B0311267817
161	6/6/2020	024SD20B03	024SD20	54-62-75784-032G	20200609	1525	A165	Die Attach	SLGA	11267819	54-62	024SD20B0311267819
162	6/7/2020	024SD20B03	024SD20	54-62-75784-032G	20200609	1525	T100	Die Attach	SLGA	11267819	54-62	024SD20B0311267819
163	6/8/2020	024SD20B03	024SD20	54-62-75784-032G	20200609	1525	T100	Wire Bond	SLGA	11267819	54-62	024SD20B0311267819
164	6/9/2020	024SD20B03	024SD20	54-62-75784-032G	20200609	1524	A265	PMC	SLGA	11267819	54-62	024SD20B0311267819
165	6/6/2020	024SD21	024SD21	54-62-75784-032G	20200609	11806	A212	SCHD	SLGA	11267820	54-62	024SD2111267820
166	6/7/2020	024SD21	024SD21	54-62-75784-032G	20200609	11806	A120	SCHD	SLGA	11267820	54-62	024SD2111267820
167	6/8/2020	024SD21	024SD21	54-62-75784-032G	20200609	11806	A120	SCHD	SLGA	11267820	54-62	024SD2111267820
168	6/9/2020	024SD21	024SD21	54-62-75784-032G	20200609	7312	A290	Die Attach	SLGA	11267820	54-62	024SD2111267820
169	6/10/2020	024SD21B02	024SD21	54-62-75784-032G	20200609	4653	A080	Wire Bond	SLGA	11267820	54-62	024SD21B0211267820
170	6/11/2020	024SD21B02	024SD21	54-62-75784-032G	20200609	4644	A080	Mold	SLGA	11267820	54-62	024SD21B0211267820
171	6/12/2020	024SD21B02	024SD21	54-62-75784-032G	20200609	4644	A080	Pkg Saw	SLGA	11267820	54-62	024SD21B0211267820
172	6/10/2020	024SD21B03	024SD21	54-62-75784-032G	20200609	2656	A080	Wire Bond	SLGA	11267820	54-62	024SD21B0311267820
173	6/11/2020	024SD21B03	024SD21	54-62-75784-032G	20200609	2654	A080	Mold	SLGA	11267820	54-62	024SD21B0311267820
174	6/12/2020	024SD21B03	024SD21	54-62-75784-032G	20200609	2654	A080	Pkg Saw	SLGA	11267820	54-62	024SD21B0311267820
175	6/12/2020	024SD24B02	024SD24	54-62-75784-032G	20200613	4509	A080	Wire Bond	SLGA	11267823	54-62	024SD24B0211267823
176	6/13/2020	024SD24B02	024SD24	54-62-75784-032G	20200613	4506	A080	Mold	SLGA	11267823	54-62	024SD24B0211267823
177	6/9/2020	024SDF1	024SDF1	54-62-64224-064G	20200610	5776	A080	SCHD	SLGA	11267824	54-62	024SDF111267824
178	6/10/2020	024SDF1	024SDF1	54-62-64224-064G	20200610	5776	A160	Die Attach	SLGA	11267824	54-62	024SDF111267824
179	6/19/2020	024SDF1E	024SDF1E	54-90-64095-064G	20200610	5761	T100	Elec Test	SLGA	11294520	54-90	024SDF1E11294520
180	6/20/2020	024SDF1E	024SDF1E	54-90-64095-064G	20200610	3439	T100	Elec Test	SLGA	11294520	54-90	024SDF1E11294520
181	6/10/2020	024SDF2	024SDF2	54-62-64224-064G	20200611	11530	A160	SCHD	SLGA	11267825	54-62	024SDF211267825
182	6/20/2020	024SDF2E	024SDF2E	54-90-64095-064G	20200611	11511	A160	Elec Test	SLGA	11294521	54-90	024SDF2E11294521
183	6/21/2020	024SDF2E	024SDF2E	54-90-64095-064G	20200611	11511	T300	Elec Test	SLGA	11294521	54-90	024SDF2E11294521
184	6/19/2020	024SDF3E	024SDF3E	54-90-64088-064G	20200612	11708	T100	Elec Test	SLGA	11294519	54-90	024SDF3E11294519
185	6/20/2020	024SDF3E	024SDF3E	54-90-64088-064G	20200612	11708	A265	Elec Test	SLGA	11294519	54-90	024SDF3E11294519
186	6/21/2020	024SDF3E	024SDF3E	54-90-64088-064G	20200612	11708	A265	Elec Test	SLGA	11294519	54-90	024SDF3E11294519
187	6/22/2020	024SDF3E	024SDF3E	54-90-64088-064G	20200612	11708	A265	Elec Test	SLGA	11294519	54-90	024SDF3E11294519
188	6/23/2020	024SDF4E	024SDF4E	54-90-64088-064G	20200613	11820	A265	Elec Test	SLGA	11298889	54-90	024SDF4E11298889
189	6/9/2020	024SDF6	024SDF6	54-62-84442-064G	20200610	7299	A265	SCHD	SLGA	11267829	54-62	024SDF611267829
190	6/10/2020	024SDF6	024SDF6	54-62-84442-064G	20200610	7299	T475	SCHD	SLGA	11267829	54-62	024SDF611267829
191	6/11/2020	024SDF6B02	024SDF6	54-62-84442-064G	20200610	2612	A290	Die Attach	SLGA	11267829	54-62	024SDF6B0211267829
192	6/12/2020	024SDF6B02	024SDF6	54-62-84442-064G	20200610	2610	A165	Wire Bond	SLGA	11267829	54-62	024SDF6B0211267829
193	6/13/2020	024SDF6B02	024SDF6	54-62-84442-064G	20200610	2610	A290	Wire Bond	SLGA	11267829	54-62	024SDF6B0211267829
WIP

 

[Ann Ooi]

HI Alan, This is the worksheet I would like the result to populated. I only put the formula at D2 for reference, as it keeps on calculating and not working smoothly. Is there anyway to write a VBA to populate the result? As there's limit of data to copy over, I'm only random selected the range to paste over here.

WIP Flow Table_Sample.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA
1	Type	Concatenate	WO #	6/1/2020	6/2/2020	6/3/2020	6/4/2020	6/5/2020	6/6/2020	6/7/2020	6/8/2020	6/9/2020	6/10/2020	6/11/2020	6/12/2020	6/13/2020	6/14/2020	6/15/2020	6/16/2020	6/17/2020	6/18/2020	6/19/2020	6/20/2020	6/21/2020	6/22/2020	6/23/2020	7/6/2020
2	54-62	018SD15B0111192861	11192861	FVI	FVI
3	54-62	022SD69B0111240418	11240418	FVI
4	54-62	022SD69B0211240418	11240418	Marking
5	54-62	022SD70B0111240419	11240419	Plating
6	54-62	022SD70B0211240419	11240419	Marking
7	54-62	022SD71B0111240420	11240420	Plating	FVI
8	54-62	022SD71B0211240420	11240420	PMC	Forming
9	54-62	022SD72B0111240421	11240421	Dejunk	Forming
10	54-62	023SD01B0211253133	11253133					Die Attach	Wire Bond	PMC
11	54-62	023SD04B0211253128	11253128			Wire Bond	Wire Bond	Mold	Laser Cut
12	54-62	023SD10B0111253138	11253138					Wire Bond	PMC		FVI
13	54-62	023SD14B0211253130	11253130			Wire Bond	Wire Bond	PMC	Pkg Saw
14	54-62	023SD24B0311253126	11253126			Wire Bond	PMC	Pkg Saw
15	54-62	023SD25B0311253135	11253135					Wire Bond
16	54-62	023SD26B0111253139	11253139					Wire Bond	PMC		Pkg Saw
17	54-62	023SD26B0211253139	11253139					Wire Bond	Wire Bond	PMC	Pkg Saw
18	54-62	023SD26B0311253139	11253139					Wire Bond	Wire Bond	PMC
19	54-62	023SD6111252478	11252478	Die Attach
20	54-62	023SD61B0211252478	11252478	Wire Bond	PMC	Plating
21	54-62	023SD61B0311252478	11252478	Wire Bond	PMC	Plating
22	54-62	023SD61B0411252478	11252478	Wire Bond
23	54-62	023SD61B0511252478	11252478		Wire Bond	PMC	Plating
24	54-62	023SD62B0111252479	11252479		Marking	SVI
25	54-62	023SD63B0111252480	11252480		Mold	Plating	FVI
26	54-62	023SD63B0211252480	11252480		Mold	PMC	Plating
27	54-62	023SD63B0311252480	11252480		Wire Bond	PMC	Plating
28	54-62	023SD63B0411252480	11252480		Wire Bond
29	54-62	023SD64B0111252482	11252482			Mold	Plating
30	54-62	023SD64B0211252482	11252482			Mold
31	54-62	023SD64B0311252482	11252482			Mold	Marking
32	54-62	023SD65B0111252483	11252483			Mold	PMC
33	54-62	023SD65B0211252483	11252483			Wire Bond
34	54-62	023SD65B0311252483	11252483			Mold
35	54-62	023SD67B0111252485	11252485					Dejunk
36	54-62	024SD0111267802	11267802						SCHD
37	54-62	024SD01B0311267802	11267802							Wire Bond	Mold	Pkg Saw
38	54-62	024SD0211267803	11267803						SCHD	SCHD
39	54-62	024SD0311267804	11267804						SCHD	SCHD
40	54-62	024SD0511267806	11267806											SCHD	Die Attach
41	54-62	024SD05B0211267806	11267806													Wire Bond	PMC	FVI
42	54-62	024SD0711267808	11267808						SCHD
43	54-62	024SD0811267809	11267809						SCHD
44	54-62	024SD08B0311267809	11267809							Die Attach	Wire Bond	PMC
45	54-62	024SD09B0211267811	11267811										Wire Bond	Wire Bond	PMC
46	54-62	024SD16B0111267815	11267815						Die Attach	Wire Bond	Wire Bond	Wire Bond	Wire Bond	PMC	FVI
47	54-62	024SD16B0311267815	11267815						Die Attach	Die Attach	Wire Bond	Mold	Pkg Saw	FVI	FVI
48	54-62	024SD17B0311267816	11267816										Wire Bond	PMC
49	54-62	024SD18B0311267817	11267817												Wire Bond	Mold	FVI
50	54-62	024SD20B0311267819	11267819						Die Attach	Die Attach	Wire Bond	PMC
51	54-62	024SD2111267820	11267820						SCHD	SCHD	SCHD	Die Attach
52	54-62	024SD21B0211267820	11267820										Wire Bond	Mold	Pkg Saw
53	54-62	024SD21B0311267820	11267820										Wire Bond	Mold	Pkg Saw
54	54-62	024SD24B0211267823	11267823												Wire Bond	Mold
55	54-62	024SDF111267824	11267824									SCHD	Die Attach
56	54-62	024SDF211267825	11267825										SCHD
57	54-62	024SDF611267829	11267829									SCHD	SCHD
58	54-62	024SDF6B0211267829	11267829											Die Attach	Wire Bond	Wire Bond	PMC
59	54-62	024SDF711267830	11267830										SCHD
60	54-62	024SDF811267831	11267831											SCHD
61	54-62	024SDF9B0211267832	11267832												Die Attach	Wire Bond	Wire Bond	Wire Bond	PMC
62	54-62	024SDFAB0111267833	11267833													Wire Bond	Wire Bond	Wire Bond	Wire Bond	Mold	Mold	Mold	FVI
63	54-62	024SDFAB0311267833	11267833													Wire Bond	Wire Bond	Wire Bond	Wire Bond	Mold	Mold	Mold
64	54-62	025SD0211280116	11280116													SCHD
65	54-62	025SD03B0311280117	11280117																Wire Bond	Mold	Mold	Mold	FVI
66	54-62	025SD0411280118	11280118																	Die Attach
67	54-62	025SD04B0111280118	11280118																		Die Attach	Wire Bond	PMC	FVI
68	54-62	025SD04B0211280118	11280118																		Die Attach	Wire Bond	Mold	FVI
69	54-62	025SD04B0311280118	11280118																		Die Attach	Wire Bond	Wire Bond	FVI
70	54-62	025SD05B0111280119	11280119																		Die Attach	Wire Bond	Mold	FVI
71	54-62	025SD05B0211280119	11280119																		Die Attach	Wire Bond	Mold	Marking
72	54-62	025SD0611280120	11280120													Die Attach
WIP_Flow

Cell Formulas
Range	Formula
D2	D2	=IF(ISNA(INDEX(WIP!$H:$H,MATCH(WIP_Flow!D$1&$B2,INDEX(WIP!$A:$A&WIP!$L:$L,0),0))),"",INDEX(WIP!$H:$H,MATCH(WIP_Flow!D$1&$B2,INDEX(WIP!$A:$A&WIP!$L:$L,0),0)))
A3:A72	A3	=A2

Named Ranges
Name	Refers To	Cells
WIP!_FilterDatabase	=WIP!$A$1:$L$609	D2

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
D2:AA267	Cell Value	="FGST"	text	NO

 

[alansidman]

Here is the Mcode from Power Query

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Cut Off Date", type date}, {"LOTID", type text}, {"LOT NO", type text}, {"DEVICE", type text}, {"PT ISSUE DATE", Int64.Type}, {"CURRENT QTY", Int64.Type}, {"STEP_LOCATION_NEW", type text}, {"PROCESS_STEP", type text}, {"PACKAGE", type text}, {"WO #", Int64.Type}, {"Type", type text}, {"Concatenate(LotID&WO #)", type text}}),
    #"Reordered Columns" = Table.ReorderColumns(#"Changed Type",{"Type", "Concatenate(LotID&WO #)", "WO #", "Cut Off Date", "LOTID", "LOT NO", "DEVICE", "PT ISSUE DATE", "CURRENT QTY", "STEP_LOCATION_NEW", "PROCESS_STEP", "PACKAGE"}),
    #"Pivoted Column" = Table.Pivot(Table.TransformColumnTypes(#"Reordered Columns", {{"Cut Off Date", type text}}, "en-US"), List.Distinct(Table.TransformColumnTypes(#"Reordered Columns", {{"Cut Off Date", type text}}, "en-US")[#"Cut Off Date"]), "Cut Off Date", "PROCESS_STEP")
in
    #"Pivoted Column"

There are to many cells to upload the entire solution picture. Here are the first dozen and half lines.

Book1-1.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R
1	Type	Concatenate(LotID&WO #)	WO #	LOTID	LOT NO	DEVICE	PT ISSUE DATE	CURRENT QTY	STEP_LOCATION_NEW	PACKAGE	6/1/2020	6/2/2020	6/3/2020	6/7/2020	6/8/2020	6/4/2020	6/5/2020	6/6/2020
2	54-62	018SD15B0111192861	11192861	018SD15B01	018SD15	54-62-74913-128G	20200504	4291	A080	SLGA	FVI
3	54-62	018SD15B0111192861	11192861	018SD15B01	018SD15	54-62-74913-128G	20200504	4291	T450	SLGA		FVI
4	54-62	022SD69B0111240418	11240418	022SD69B01	022SD69	54-62-14515-016GA	20200528	4012	A080	TSOP	FVI
5	54-62	022SD69B0211240418	11240418	022SD69B02	022SD69	54-62-14515-016GA	20200528	2877	T450	TSOP	Marking
6	54-62	022SD70B0111240419	11240419	022SD70B01	022SD70	54-62-14515-016GA	20200528	2879	T450	TSOP	Plating
7	54-62	022SD70B0211240419	11240419	022SD70B02	022SD70	54-62-14515-016GA	20200528	4130	A080	TSOP	Marking
8	54-62	022SD71B0111240420	11240420	022SD71B01	022SD71	54-62-14515-016GA	20200529	2875	A080	TSOP	Plating
9	54-62	022SD71B0111240420	11240420	022SD71B01	022SD71	54-62-14515-016GA	20200529	2875	T100	TSOP		FVI
10	54-62	022SD71B0211240420	11240420	022SD71B02	022SD71	54-62-14515-016GA	20200529	5289	T100	TSOP		Forming
11	54-62	022SD71B0211240420	11240420	022SD71B02	022SD71	54-62-14515-016GA	20200529	5289	T475	TSOP	PMC
12	54-62	022SD72B0111240421	11240421	022SD72B01	022SD72	54-62-14515-016GA	20200529	5617	A080	TSOP	Dejunk
13	54-62	022SD72B0111240421	11240421	022SD72B01	022SD72	54-62-14515-016GA	20200529	5617	T450	TSOP		Forming
14	54-62	023SD01B0211253133	11253133	023SD01B02	023SD01	54-62-64223-064G	20200605	3764	T450	SLGA				PMC
15	54-62	023SD01B0211253133	11253133	023SD01B02	023SD01	54-62-64223-064G	20200605	3771	A080	SLGA								Wire Bond
16	54-62	023SD01B0211253133	11253133	023SD01B02	023SD01	54-62-64223-064G	20200605	3771	A160	SLGA							Die Attach
17	54-62	023SD04B0211253128	11253128	023SD04B02	023SD04	54-62-66281-032G	20200604	2495	A080	SLGA								Laser Cut
18	54-62	023SD04B0211253128	11253128	023SD04B02	023SD04	54-62-66281-032G	20200604	2495	T100	SLGA							Mold
Sheet2

 

[Ann Ooi]

Hi Alan,
Thank you for your guidance. But, I'm not an expert in VBA. May I know how to apply the coding? I put it into module, and it turns red font, and not able to run. Should I add Sub? For the result columns, if I only need the Type, Concatenate, WO # and cut off date on top, highlighted yellow, I should just take away? If I do not need pivot anymore, since you had helped to put the result in columns, I can just deleted the #"Pivoted Column"? Can I put "_" since it's long?