Please see which formula needs here v look up or if not match

[akshay6s]

HI EVERYONE,
i have an excel file. in which i have some names of the product.column B is named as "in", it means all the manufactured products we take as in. column h named as processing. after manufacturing we send the products on processing. my small requirement is that which products are not sent on processing should be shown up in the column k

we can see the total products are 21 and we have sent only 15 products on processing. so the rest of 6 products name should be shown up in the column k.


i know that this is nothing for the excel forum experts because they have solved up my very hard queries earlier. so thanks in advance. just waiting for the answer.

[Special-K]

in K2
=IFERROR(INDEX($B$2:$B$22,AGGREGATE(15,6,ROW($B$2:$B$22)-ROW($A$2)+1/(NOT(ISNUMBER(MATCH($B$2:$B$22,$H$2:$H$22,0)))),ROWS($A$2:A2))),"")
and copy down the column

Credit to CARACALLA, post #6 in this thread
https://www.excelforum.com/excel-gen...ml#post5194869

[akshay6s]

thanks for the effort but this is not working. showing nothing in column k. and i want simple thing that whatever quality is not sent on processing, should be shown up in column k if they are 6, if they are 4,5 whatever.

[modytrane]

The formula in #2 is an Array formula.

Make sure you enter it with ctrl+shft+ent.

Excel will put {} around the formula. Don't enter these brackets manually, it won't work.

[Special-K]

thanks for the effort but this is not working. showing nothing in column k. and i want simple thing that whatever quality is not sent on processing, should be shown up in column k if they are 6, if they are 4,5 whatever.

1. Open the test file you posted.
2. Copy and paste the formula in K2

=IFERROR(INDEX($B$2:$B$22,AGGREGATE(15,6,ROW($B$2:$B$22)-ROW($A$2)+1/(NOT(ISNUMBER(MATCH($B$2:$B$22,$H$2:$H$22,0)))),ROWS($A$2:A2))),"")

ARC shows in K2.

3. Copy the formula down the column

Works fine.

4. You've already got bad data.
SAMPLE appears in column H but not in columns B or E.
There are 21 entries in column B and column E.
Column H contains 15 entries
Column K contains 7 entries which totals 22, so something was amiss with your data.

Had the formula NOT worked I wouldn't have been able to note your error.

Try it, it works fine.

[Special-K]

The formula in #2 is an Array formula.

Make sure you enter it with ctrl+shft+ent.

Excel will put {} around the formula. Don't enter these brackets manually, it won't work.

The formula in post #2 is not an array formula, it uses AGGREGATE, therefore Ctrl-Shift enter is not needed.

[akshay6s]

this is not working at all.

[akshay6s]

and we don't consider sample as a product. and if this is present in column b this will not show up and if not(sample is not currently in column b). this doesn't matters for me should also not be matter for the formula because we needs that which is not in column h should be shown up in column k

[FDibbins]

It worked fine for me too...

	A	B	C	D	E	F	G	H	I	J	K	L
1	SR.NO.	IN		SR.NO.	FINAL STOCK		SR.NO.	PROCESSING			NOT SENT ON PROCESSING
2	1	ARC		1	ARC		1	BREEZE		1	ARC
3	2	BREEZE		2	BREEZE		2	CALVIN		2	DIAMOND
4	3	CALVIN		3	CALVIN		3	CELTIC		3	FEATHER
5	4	CELTIC		4	CELTIC		4	DIVINE		4	GEO
6	5	DIAMOND		5	DIAMOND		5	GREEN VALLEY		5	NEW ARC
7	6	DIVINE		6	DIVINE		6	JAZZ		6	PAISLEY
8	7	FEATHER		7	FEATHER		7	Matelasse			ROYAL FEATHER
9	8	GEO		8	GEO		8	NECTAR
10	9	GREEN VALLEY		9	GREEN VALLEY		9	NEW ROSE
11	10	JAZZ		10	JAZZ		10	RIO
12	11	MATELASSE		11	MATELASSE		11	ROMAN
13	12	NECTAR		12	NECTAR		12	ROSE
14	13	NEW ARC		13	NEW ARC		13	SAMPLE
15	14	NEW ROSE		14	NEW ROSE		14	SPRING
16	15	PAISLEY		15	PAISLEY		15	VISCOUSE
17	16	RIO		16	RIO
18	17	ROMAN		17	ROMAN
19	18	ROSE		18	ROSE
20	19	ROYAL FEATHER		19	ROYAL FEATHER
21	20	SPRING		20	SPRING
22	21	VISCOUSE		21	VISCOUSE

K2=IFERROR(INDEX($B$2:$B$22,AGGREGATE(15,6,ROW($B$2:$B$22)-ROW($A$2)+1/(NOT(ISNUMBER(MATCH($B$2:$B$22,$H$2:$H$22,0)))),ROWS($A$2:A2))),"")
Regular formula, copied down

[Special-K]

this is not working at all.

Post your file where you attempted to copy the formula in.
The fact two experts agree it is working would suggest you have something wrong in your spreadsheet.

[modytrane]

Sorry about that.
I posted too quickly.
You are correct. It's a regular formula and I tried it, it works fine.

[akshay6s]

here i have posted my spreadsheet. please check why this is not working.

[akshay6s]

and not working here also. https://docs.google.com/spreadsheets...gid=2117005142

[Glenn Kennedy]

Here is another formula:

=IFERROR(INDEX($B:$B,SMALL(IF(ISNA(MATCH($B$2:$B$22,$H$2:$H$16,0)),ROW($B$2:$B$22)),ROWS(K$2:K2))),"")

You will see that it returns 7 values not six. This is because sample (H14) does not occur in column B... so column H has only 14/21 values in common with column H.

This is an array formula. Array Formulae are a little different from ordinary formulae in that they MUST be confirmed in the FIRST CELL ONLY by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. After that, the array can be dragged down as normal, to cover the desired range.

You will know the array is active when you see curly brackets { } - or "curly braces" for those of you in the USA, or "flower brackets" for those of you in India - appear around the outside of your formula. If you do not use CTRL+SHIFT+ENTER you will (almost always) get an error message or an incorrect answer. Press F2 on that cell and try again.

Don't type the curly brackets yourself - it won't work...

[Glenn Kennedy]

For info. The previous formulae all included AGGREGATE, which is available only from Excel 2010 onwards. Your profile states that you are using Excel 2007.

[akshay6s]

i can't say how pleased i am and you are really an excel expert. its working fine and yes i am using excel 2007. you are really a genius and you have helped me before also. so again thanks a lot really really thanks. and thanks to special-k also who tried his/her best. now the formula provided by expert Mr. Glenn Kennedy is working perfectly. i am happy and thanks to the genius person again.

[Glenn Kennedy]

Laughs!! Too many nice words!!


You're welcome.

---

If that takes care of your original question, please select "Thread Tools" from the menu link above and mark this thread as SOLVED.

It'd also be appreciated if you were to click the Add Reputation button at the foot of any of the posts of all members who helped you reach a solution.

[akshay6s]

yes sir. i have already marked as solved and it feels pleasent that u laughs because of me and this is the only best thing i can give you from here. and this shows that you are a very kind hearted person. god bless u sir.