Help with formula

[punit83]

Hello,

pls guide me with formula with index&match with model # to reflect unique item in size (few items size are not sorted but don't want reflect duplicate size)
i have more than 20k thousand row data for which cannot go thru individual item.

Thanx in advance.
( i am newbie)

 

[Peter_SSs]

Is it something like this that you are after?

24 01 02.xlsm
	C	D	E	F	G	H
1
2
3	1mm					1mm
4	4mm					4mm
5	3mm					3mm
6	2mm					2mm
7	5mm					5mm
8	4mm
9	5mm
10	4mm
Unique

Cell Formulas
Range	Formula
H3:H10	H3	=IFERROR(INDEX(C$3:C$10,MATCH(0,INDEX(COUNTIF(H$2:H2,C$3:C$10),0),0)),"")

 

[punit83]

Is it something like this that you are after?

24 01 02.xlsm
	C	D	E	F	G	H
1
2
3	1mm					1mm
4	4mm					4mm
5	3mm					3mm
6	2mm					2mm
7	5mm					5mm
8	4mm
9	5mm
10	4mm
Unique

Cell Formulas
Range	Formula
H3:H10	H3	=IFERROR(INDEX(C$3:C$10,MATCH(0,INDEX(COUNTIF(H$2:H2,C$3:C$10),0),0)),"")

Hello,

Thanx for reply

formula worked half for me but need more specific, i am attaching excel for reference. Pls have a look for it
every model no need individual result output

24 01 02.xlsx
	A	B	C	D	E	F	G	H	I
1	Data						Output Result
2	Item #	Model #	Size				Item #	Model #	Size
3	1	PDB1033-1	1mm		1mm		1	PDB1033-1	1mm
4		PDB1033-1	4mm		4mm			PDB1033-1	4mm
5		PDB1033-1	3mm		3mm			PDB1033-1	3mm
6		PDB1033-1	2mm		2mm			PDB1033-1	2mm
7		PDB1033-1	5mm		5mm			PDB1033-1	5mm
8		PDB1033-1	4mm		0
9		PDB1033-1	5mm		6x4
10		PDB1033-1	4mm		5x4
11					4x6
12	2	PDB1033-2	1mm		3x6		2	PDB1033-2	1mm
13		PDB1033-2	2mm		6x3			PDB1033-2	2mm
14		PDB1033-2	3mm		#N/A			PDB1033-2	3mm
15		PDB1033-2	2mm		#N/A			PDB1033-2	4mm
16		PDB1033-2	3mm		#N/A			PDB1033-2	5mm
17		PDB1033-2	4mm		#N/A
18		PDB1033-2	5mm		#N/A
19		PDB1033-2	4mm		#N/A
20					#N/A
21	3	PDB1033-3	6x4		#N/A		3	PDB1033-3	6x4
22		PDB1033-3	5x4		#N/A			PDB1033-3	5x4
23		PDB1033-3	4x6		#N/A			PDB1033-3	4x6
24		PDB1033-3	4x6		#N/A			PDB1033-3	3x6
25		PDB1033-3	5x4		#N/A			PDB1033-3	6x3
26		PDB1033-3	6x4		#N/A
27		PDB1033-3	3x6		#N/A
28		PDB1033-3	6x3		#N/A
29
Sheet1

Cell Formulas
Range	Formula
E3:E28	E3	=INDEX(C$3:C$28,MATCH(0,INDEX(COUNTIF(E$2:E2,C$3:C$28),0),0))

 

[Peter_SSs]

1. For what you have shown in post #3, please state exactly which cells you require formula for.
   
   
2. If required, would it be acceptable to use helper columns?

 

[punit83]

1. For what you have shown in post #3, please state exactly which cells you require formula for.
   
   
2. If required, would it be acceptable to use helper columns?

In coloumn E i want to use the formula.
i want unique size based on all individual model number

yes sir helper columns is also acceptable

 

[Peter_SSs]

In coloumn E i want to use the formula.

But column E does not contain the results that you want does it? Are you sure that it isn't the results in column I that you want?
Are the values shown in columns G & H already on the sheet?

 

[punit83]

But column E does not contain the results that you want does it? Are you sure that it isn't the results in column I that you want?
Are the values shown in columns G & H already on the sheet?

Sorry sir

i think i am not manage to explain you, so sorry for inconvenience.

Column A to C is my data.
Column E i want to use the formula and get result.

but when item #1 is over i want output result in column in E (as result highlighted column "I" in green)
when item #2 started and getting over i want result in column W only for item # 2, means for every item it extract its unique data in column E.
( for example if you see in E9 the result is from item # 3, Model # PDB1033-3 and i want result against pdb1033-3 between e21 to e28 )

 

[punit83]

Sorry sir

i think i am not manage to explain you, so sorry for inconvenience.

Column A to C is my data.
Column E i want to use the formula and get result.

but when item #1 is over i want output result in column in E (as result highlighted column "I" in green)
when item #2 started and getting over i want result in column W only for item # 2, means for every item it extract its unique data in column E.
( for example if you see in E9 the result is from item # 3, Model # PDB1033-3 and i want result against pdb1033-3 between e21 to e28 )

Loading Google Sheets

 

[Peter_SSs]

I think this does what you want then?

punit83.xlsm
	A	B	C	D	E
1	Data
2	Item #	Model #	Size		Result Size
3	1	PDB1033-1	1mm		1mm
4		PDB1033-1	4mm		4mm
5		PDB1033-1	3mm		3mm
6		PDB1033-1	2mm		2mm
7		PDB1033-1	5mm		5mm
8		PDB1033-1	4mm
9		PDB1033-1	5mm
10		PDB1033-1	4mm
11
12	2	PDB1033-2	1mm		1mm
13		PDB1033-2	2mm		2mm
14		PDB1033-2	3mm		3mm
15		PDB1033-2	2mm		4mm
16		PDB1033-2	3mm		5mm
17		PDB1033-2	4mm
18		PDB1033-2	5mm
19		PDB1033-2	4mm
20
21	3	PDB1033-3	6x4		6x4
22		PDB1033-3	5x4		5x4
23		PDB1033-3	4x6		4x6
24		PDB1033-3	4x6		3x6
25		PDB1033-3	5x4		6x3
26		PDB1033-3	6x4
27		PDB1033-3	3x6
28		PDB1033-3	6x3
Unique (2)

Cell Formulas
Range	Formula
E3:E28	E3	=IFERROR(INDEX(C3:INDEX(C3:C$100,MATCH(TRUE,C3:C$100="",0)),MATCH(0,INDEX(COUNTIF(INDEX(E:E,AGGREGATE(14,6,ROW(A$3:A3)/(A$3:A3<>""),1)-1):E2,C3:INDEX(C:C,AGGREGATE(15,6,ROW(C3:C$100)/(C3:C$100=""),1)-1)),0),0)),"")

 

[punit83]

I think this does what you want then?

punit83.xlsm
	A	B	C	D	E
1	Data
2	Item #	Model #	Size		Result Size
3	1	PDB1033-1	1mm		1mm
4		PDB1033-1	4mm		4mm
5		PDB1033-1	3mm		3mm
6		PDB1033-1	2mm		2mm
7		PDB1033-1	5mm		5mm
8		PDB1033-1	4mm
9		PDB1033-1	5mm
10		PDB1033-1	4mm
11
12	2	PDB1033-2	1mm		1mm
13		PDB1033-2	2mm		2mm
14		PDB1033-2	3mm		3mm
15		PDB1033-2	2mm		4mm
16		PDB1033-2	3mm		5mm
17		PDB1033-2	4mm
18		PDB1033-2	5mm
19		PDB1033-2	4mm
20
21	3	PDB1033-3	6x4		6x4
22		PDB1033-3	5x4		5x4
23		PDB1033-3	4x6		4x6
24		PDB1033-3	4x6		3x6
25		PDB1033-3	5x4		6x3
26		PDB1033-3	6x4
27		PDB1033-3	3x6
28		PDB1033-3	6x3
Unique (2)

Cell Formulas
Range	Formula
E3:E28	E3	=IFERROR(INDEX(C3:INDEX(C3:C$100,MATCH(TRUE,C3:C$100="",0)),MATCH(0,INDEX(COUNTIF(INDEX(E:E,AGGREGATE(14,6,ROW(A$3:A3)/(A$3:A3<>""),1)-1):E2,C3:INDEX(C:C,AGGREGATE(15,6,ROW(C3:C$100)/(C3:C$100=""),1)-1)),0),0)),"")

Thank you Sir, This exactly what i needed. Worked......