array formula

[gakiss2]

I have used array formulas successfully but only when the result is a single value. It was my impression that excel could list out the results when there are multiple resuls. I don't see where I can attach an excel file. =IFERROR(INDEX('All GK Data'!I:I, MATCH(0,COUNTIF($M$19:M19, 'All GK Data'!I:I), 0)),"")

 

[Marcelo Branco]

Welcome to Mr Excel forum

Take a look at
https://www.myonlinetraininghub.com/excel-extract-a-unique-list

Hope this helps

M.

 

[gakiss2]

update. I had some success with getting the results to cascade but I'm looking for unique distinct but got all the results exactly the same. Also tried a different formula I got from a google search: =LOOKUP(2, 1/((COUNTIF($L$19:L19, 'All GK Data'!I:I)=0)*('All GK Data'!I:I<>"")), 'All GK Data'!I:I)

 

[Marcelo Branco]

A small data sample along with expected results would be helpful.

M.

 

[gakiss2]

the results would be a list of the items from the category column which contain only one instance of each category. Later will will use sumifs to addup the total reject qty for each of these categories.

IDX	Supplier	RejNum	DateIssu	QtyRej	Ship	Item	Descr	Category
1	Bobalee	109016	7/3/2018	1		S2J1014	CYL CAB TILT	Function
2	Bobalee	109180	7/10/2018	1		S3J0193	CYL ASSY BEAM TOP	Dimensional
3	Bobalee	179	9/26/2018	1		T2J0087	CYL CAB TILT	Missing
4	Bobalee	382	10/16/2018	1		S2J1014	CYL CAB TILT	Orifice

<colgroup><col width="64" style="width: 48pt;"> <col width="81" style="width: 61pt; mso-width-source: userset; mso-width-alt: 2872;"> <col width="70" style="width: 52pt; mso-width-source: userset; mso-width-alt: 2474;"> <col width="78" style="width: 59pt; mso-width-source: userset; mso-width-alt: 2787;"> <col width="64" style="width: 48pt;" span="2"> <col width="64" style="width: 48pt;" span="2"> <col width="75" style="width: 56pt; mso-width-source: userset; mso-width-alt: 2673;"> <tbody> </tbody>

 

[Marcelo Branco]

Something like this

	A​	B​	C​	D​	E​	F​	G​	H​	I​	J​	K​
1​	IDX​	Supplier​	RejNum​	DateIssu​	QtyRej​	Ship​	Item​	Descr​	Category​		List​
2​	1​	Bobalee​	109016​	07/03/2018​	1​		S2J1014​	CYL CAB TILT​	Function​		Function​
3​	2​	Bobalee​	109180​	07/10/2018​	1​		S3J0193​	CYL ASSY BEAM TOP​	Dimensional​		Dimensional​
4​	3​	Bobalee​	179​	09/26/2018​	1​		T2J0087​	CYL CAB TILT​	Missing​		Missing​
5​	4​	Bobalee​	382​	10/16/2018​	1​		S2J1014​	CYL CAB TILT​	Orifice​		Orifice​
6​	5​	Bobalee​	111​	10/18/2018​	1​		S2J1014​	CYL CAB TILT​	Function​
7​	6​	Bobalee​	222​	11/02/2018​	2​		S3J0193​	CYL ASSY BEAM TOP​	Dimensional​

Array formula in K2 copied down
=IFERROR(INDEX($I$2:$I$7,MATCH(0,COUNTIF(K$1:K1,I$2:I$7),0)),"")
Ctrl+Shift+Enter

M.

 

[gakiss2]

Something like this

	A​	B​	C​	D​	E​	F​	G​	H​	I​	J​	K​
1​	IDX​	Supplier​	RejNum​	DateIssu​	QtyRej​	Ship​	Item​	Descr​	Category​		List​
2​	1​	Bobalee​	109016​	07/03/2018​	1​		S2J1014​	CYL CAB TILT​	Function​		Function​
3​	2​	Bobalee​	109180​	07/10/2018​	1​		S3J0193​	CYL ASSY BEAM TOP​	Dimensional​		Dimensional​
4​	3​	Bobalee​	179​	09/26/2018​	1​		T2J0087​	CYL CAB TILT​	Missing​		Missing​
5​	4​	Bobalee​	382​	10/16/2018​	1​		S2J1014​	CYL CAB TILT​	Orifice​		Orifice​
6​	5​	Bobalee​	111​	10/18/2018​	1​		S2J1014​	CYL CAB TILT​	Function​
7​	6​	Bobalee​	222​	11/02/2018​	2​		S3J0193​	CYL ASSY BEAM TOP​	Dimensional​

<tbody>
</tbody>

Array formula in K2 copied down
=IFERROR(INDEX($I$2:$I$7,MATCH(0,COUNTIF(K$1:K1,I$2:I$7),0)),"")
Ctrl+Shift+Enter

M.

Thank You. still having trouble. almost works but still just repeats the first category item over and over. Also range is on another tab. I don't think that matters, seems to be pulling the right data but thought I would mention it just in case.

 

[gakiss2]

Thank You. still having trouble. almost works but still just repeats the first category item over and over. Also range is on another tab. I don't think that matters, seems to be pulling the right data but thought I would mention it just in case.

And when I put it in I forgot to change K$1:K1 to the correc address but I worked, albeit not correctly, repeat s first category over and over. Sorr for the typos, this site is actinweird andnotaknkstrok correctly.

 

[gakiss2]

And when I put it in I forgot to change K$1:K1 to the correc address but I worked, albeit not correctly, repeat s first category over and over. Sorr for the typos, this site is actinweird andnotaknkstrok correctly.

I tried it on the same tab but the same result, Function, function,....

 

[Marcelo Branco]

I do not know why the formula is not working for you. This is a well-known and tested formula.
One last question
Did you confirm the formula with Ctrl+Shift+Enter simultaneously?



M.