missing item find out

[majidsiddique]

hi all,
kindly help me to find out missing item below mention data.
i use formula, Vlookup(B2&"*",sheet2!A2:E45,5,0) to pick KIP No exact match item + invoice BUT formula break and give #N/A because item is not match. so i have to find out actual item from sheet2 criteria match invoice and qty then pick right item in next column sheet1. you can see missing item.

ITEM	Merge Inv + Item	INVOICE NO	QUANTITY	Kip-01	Missing Item
MD-ENSP27530	A2370MD-ENSP27530	A2370	1	KIP-A00104
MD-ENSP30018	A2370MD-ENSP30018	A2370	1	#N/A	A2370MD-INT30018X
MD-INT30026X	A2370MD-INT30026X	A2370	1	KIP-A00045
MD-NCSP3009X	A2370MD-NCSP3009X	A2370	1	KIP-A00010
MD-SPL20020X	A2370MD-SPL20020X	A2370	1	KIP/A00198
MD-SPL25015X	A2370MD-SPL25015X	A2370	1	KIP-A00148
				KIP-A00104
MD-DRSP30018	A2392MD-DRSP30018	A2392	1	#N/A	A2392MD-DRSP25014
MD-DRSP30030	A2392MD-DRSP30030	A2392	1	#N/A	A2392MD-DRSP25018
MD-DRSP35015	A2392MD-DRSP35015	A2392	1	#N/A	A2392MD-DRSP25024
MD-DRSP35018	A2392MD-DRSP35018	A2392	1	#N/A	A2392MD-DRSP27514
MD-INT25014X	A2392MD-INT25014X	A2392	1	#N/A	A2392MD-DRSP27518
				KIP-A00104
MD-BB841000	A2453MD-BB841000	A2453	4	KIP-A00194
MD-M110706C	A2453MD-M110706C	A2453	4	#N/A	BI-300894210
MD-RCL841000	A2453MD-RCL841000	A2453	4	KIP-A00194

<colgroup><col><col><col><col><col><col></colgroup><tbody>
</tbody>

Sheet2 DATA.

Merge Inv + Item	INVOICE NO	ITEM	QUANTITY	KIP
A2370MD-ENSP27530	A2370	MD-ENSP27530	1	KIP-A00104
A2370MD-INT30018X	A2370	MD-INT30018X	1	KIP-A00075
A2370MD-INT30026X	A2370	MD-INT30026X	1	KIP-A00045
A2370MD-NCSP3009X	A2370	MD-NCSP3009X	1	KIP-A00010
A2370MD-SPL20020X	A2370	MD-SPL20020X	1	KIP/A00198
A2370MD-SPL25015X	A2370	MD-SPL25015X	1	KIP-A00148
A2392MD-DRSP25014	A2392	MD-DRSP25014	1	KIP-2073
A2392MD-DRSP25018	A2392	MD-DRSP25018	1	KIP-2073
A2392MD-DRSP25024	A2392	MD-DRSP25024	1	KIP-2073
A2392MD-DRSP27514	A2392	MD-DRSP27514	1	KIP-2086
A2392MD-DRSP27518	A2392	MD-DRSP27518	1	KIP-2073
A2453BI-300894210	A2453	BI-300894210	4	KIP-A00218
A2453MD-BB8410000	A2453	MD-BB8410000	4	KIP-A00194
A2453MD-RCL841000	A2453	MD-RCL841000	4	KIP-A00194

<colgroup><col><col><col><col><col></colgroup><tbody>
</tbody>

 

[oldbrewer]

in your bottom table add a helper column that shows 1 if a match with the top table and 0 if no match, then in another helper put invoice number&0 ie it will show A23700

then search bottom table for A23700

 

[majidsiddique]

Hi oldbrewer,
can you do this? like a sample.

 

[oldbrewer]

just find A23700 in helper column with offset - say helper is in column K from K3 down

=offset($k$2,match("A23700",$k$4:$k$100,0),-6)

this gives you whatever is in column C

 

[majidsiddique]

hi oldbrewer,
kindly explain more, helper column values. i want find sheet2 and paste it sheet1 match invoice no A2370 sheet1.

 

[oldbrewer]

your 5th column in the top table uses a formula to pull the kip number. Where this formula brings back #na you need to pull the kip number in a different way. The helper1 column sees if rows in the lower table match the top table and if no match returns a 0 - then helper2 wherever there is a 0 in helper 1 returns the invoice number - then helper3 where helper2 is not blank returns the kip number

so your formula needs to be .......=if(iserror(yourformula)),go find the kip number in helper3, your formula)

use offset(match) to match the invoice number to helper2 column offsetting one column to the right ie to helper3

 

[majidsiddique]

hi oldbrewer,
please do it on a sheet. i am doing but not get result. please do it on excel sheet. where value put and how to apply formula with helper?

 

[oldbrewer]

ITEM	Merge Inv + Item	INVOICE NO	QUANTITY	Kip-01
MD-ENSP27530	A2370MD-ENSP27530	A2370	1	KIP-A00104
MD-ENSP30018	A2370MD-ENSP30018	A2370	1	KIP-A00075	correct
MD-INT30026X	A2370MD-INT30026X	A2370	1	KIP-A00045
MD-NCSP3009X	A2370MD-NCSP3009X	A2370	1	KIP-A00010
MD-SPL20020X	A2370MD-SPL20020X	A2370	1	KIP/A00198
MD-SPL25015X	A2370MD-SPL25015X	A2370	1	KIP-A00148
MD-DRSP30018	A2392MD-DRSP30018	A2392	1	KIP-2073	where there are consecutive errors my approach fails
MD-DRSP30030	A2392MD-DRSP30030	A2392	1	KIP-2073	I will thik on….
MD-DRSP35015	A2392MD-DRSP35015	A2392	1	KIP-2073
MD-DRSP35018	A2392MD-DRSP35018	A2392	1	KIP-2073	col % top table now formula driven
MD-INT25014X	A2392MD-INT25014X	A2392	1	KIP-2073
KIP-A00104				#N/A
MD-BB841000	A2453MD-BB841000	A2453	4	KIP-A00218
MD-M110706C	A2453MD-M110706C	A2453	4	KIP-A00218
MD-RCL841000	A2453MD-RCL841000	A2453	4	KIP-A00194
					(if not return inv num)
Sheet2 DATA.
Merge Inv + Item	INVOICE NO	ITEM	QUANTITY	KIP	does col A match to col B table 1	if inv num ret kip num
A2370MD-ENSP27530	A2370	MD-ENSP27530	1	KIP-A00104
A2370MD-INT30018X	A2370	MD-INT30018X	1	KIP-A00075	A2370	KIP-A00075
A2370MD-INT30026X	A2370	MD-INT30026X	1	KIP-A00045
A2370MD-NCSP3009X	A2370	MD-NCSP3009X	1	KIP-A00010
A2370MD-SPL20020X	A2370	MD-SPL20020X	1	KIP/A00198
A2370MD-SPL25015X	A2370	MD-SPL25015X	1	KIP-A00148
A2392MD-DRSP25014	A2392	MD-DRSP25014	1	KIP-2073	A2392	KIP-2073
A2392MD-DRSP25018	A2392	MD-DRSP25018	1	KIP-2073	A2392	KIP-2073
A2392MD-DRSP25024	A2392	MD-DRSP25024	1	KIP-2073	A2392	KIP-2073
A2392MD-DRSP27514	A2392	MD-DRSP27514	1	KIP-2086	A2392	KIP-2086
A2392MD-DRSP27518	A2392	MD-DRSP27518	1	KIP-2073	A2392	KIP-2073
A2453BI-300894210	A2453	BI-300894210	4	KIP-A00218	A2453	KIP-A00218
A2453MD-BB8410000	A2453	MD-BB8410000	4	KIP-A00194	A2453	KIP-A00194
A2453MD-RCL841000	A2453	MD-RCL841000	4	KIP-A00194

<colgroup><col><col><col><col><col><col><col><col span="2"></colgroup><tbody>
</tbody>

 

[oldbrewer]

ITEM	Merge Inv + Item	INVOICE NO	QUANTITY	Kip-01	target row num
MD-ENSP27530	A2370MD-ENSP27530	A2370	1	KIP-A00104	23
MD-ENSP30018	A2370MD-ENSP30018	A2370	1	KIP-A00075	24
MD-INT30026X	A2370MD-INT30026X	A2370	1	KIP-A00045	25
MD-NCSP3009X	A2370MD-NCSP3009X	A2370	1	KIP-A00010	26
MD-SPL20020X	A2370MD-SPL20020X	A2370	1	KIP/A00198	27
MD-SPL25015X	A2370MD-SPL25015X	A2370	1	KIP-A00148	28
MD-DRSP30018	A2392MD-DRSP30018	A2392	1	KIP-2073	29
MD-DRSP30030	A2392MD-DRSP30030	A2392	1	KIP-2073	30
MD-DRSP35015	A2392MD-DRSP35015	A2392	1	KIP-2073	31
MD-DRSP35018	A2392MD-DRSP35018	A2392	1	KIP-2086	32
MD-INT25014X	A2392MD-INT25014X	A2392	1	KIP-2073	33
KIP-A00104				KIP-A00218	34
MD-BB841000	A2453MD-BB841000	A2453	4	KIP-A00194	35
MD-M110706C	A2453MD-M110706C	A2453	4	KIP-A00194	36
				col E		col G
Sheet2 DATA.
Merge Inv + Item	INVOICE NO	ITEM	QUANTITY	KIP		row num
A2370MD-ENSP27530	A2370	MD-ENSP27530	1	KIP-A00104		23
A2370MD-INT30018X	A2370	MD-INT30018X	1	KIP-A00075		24
A2370MD-INT30026X	A2370	MD-INT30026X	1	KIP-A00045		25
A2370MD-NCSP3009X	A2370	MD-NCSP3009X	1	KIP-A00010		26
A2370MD-SPL20020X	A2370	MD-SPL20020X	1	KIP/A00198		27
A2370MD-SPL25015X	A2370	MD-SPL25015X	1	KIP-A00148		28
A2392MD-DRSP25014	A2392	MD-DRSP25014	1	KIP-2073		29
A2392MD-DRSP25018	A2392	MD-DRSP25018	1	KIP-2073		30
A2392MD-DRSP25024	A2392	MD-DRSP25024	1	KIP-2073		31
A2392MD-DRSP27514	A2392	MD-DRSP27514	1	KIP-2086		32
A2392MD-DRSP27518	A2392	MD-DRSP27518	1	KIP-2073		33
A2453BI-300894210	A2453	BI-300894210	4	KIP-A00218		34
A2453MD-BB8410000	A2453	MD-BB8410000	4	KIP-A00194		35
A2453MD-RCL841000	A2453	MD-RCL841000	4	KIP-A00194		36
	formula in cell E2
	=IF(ISERROR(OFFSET($A$22,MATCH(B2,$A$23:$A$36,0),4)),OFFSET($G$22,MATCH(F2,$G$23:$G$36,0),-2),OFFSET($A$22,MATCH(B2,$A$23:$A$36,0),4))
	this is a much simpler approach requiring only one helper cell in col G of lower table
	check accuracy please

<colgroup><col><col><col><col><col><col><col><col span="2"></colgroup><tbody>
</tbody>

 

[majidsiddique]

Hi Oldbrewer,
its really a good and fantastic approach, But tell me one thing, whatabout -2 show column no where to to item pick or anything else?

=IF(ISERROR(OFFSET($A$22,MATCH(B2,$A$23:$A$36,0),4)),OFFSET($G$22,MATCH(F2,$G$23:$G$36,0),-2),OFFSET($A$22,MATCH(B2,$A$23:$A$36,0),4))
OFFSET($G$22,MATCH(F2,$G$23:$G$36,0),-2)

i use this,
=IF(ISERROR(OFFSET(Sheet2!$A$1,MATCH(B2,Sheet2!$A$2:$A$15,0),4)),OFFSET(Sheet2!$F$1,MATCH(G2,Sheet2!$F$2:$F$15,0),-1),OFFSET(Sheet2!$A$1,MATCH(B2,Sheet2!$A$2:$A$15,0),4))
anwer if OK,

BUT i use this, answer is not OK,

=IF(ISERROR(OFFSET(Sheet2!$A$1,MATCH(B2,Sheet2!$A$2:$A$15,0),4)),OFFSET(Sheet2!$H$1,MATCH(G2,Sheet2!$H$2:$H$15,0),-1),OFFSET(Sheet2!$A$1,MATCH(B2,Sheet2!$A$2:$A$15,0),4))
can you tell me where is mistake.
first formula pick KIP No OK.

and second i want to pick item no. see the result.
sheet 2 H2 same copy 23 to 36 no and before one column G copy and paste item no.

KIP-A00104

MD-INT30018X

KIP-A00045

KIP-A00010

KIP/A00198

KIP-A00148

MD-DRSP25014

MD-DRSP25018

MD-DRSP25024

MD-DRSP27514

MD-RINT30018X

BI-300894210

MD-BB8410000

KIP-A00194

<colgroup><col></colgroup><tbody>
</tbody>