if cell value=3, lookup return 3 same repeat row

[Joeun]

Hi, I need help as title. I'm try to use array index and match but couldn't find a way to duplicate the repeat row.
Column B {=INDEX(orders!$B$2:$B$40,SMALL(IF(($C$1=orders!$A$2:$A$40),MATCH(ROW(orders!$A$2:$A$40),ROW(orders!$A$2:$A$40)),""),ROWS($A$1:A1)))}
Column C {=INDEX(orders!$C$2:$C$40,SMALL(IF(($C$1=orders!$A$2:$A$40),MATCH(ROW(orders!$A$2:$A$40),ROW(orders!$A$2:$A$40)),""),ROWS($A$1:A1)))}

2nd - help on Column E too. Where Column D and F need to be match. If Column D have 3 time repeat item. Column F also need to have 3 time scanned item.

Thanks in advance.

 

[jasonb75]

For the first part enter this into B5 (without Ctrl Shift Enter), drag right to C5, then copy both down as needed.

=IFERROR(INDEX(orders!B:B,AGGREGATE(15,6,ROW(orders!$A$2:$A$40)/($C$1=orders!$A$2:$A$40),ROWS(B$5:B5))),"")

Column D is a straightforward index match combination (or vlookup).

=IF(C5="","",INDEX(barcode!D:D,MATCH(B5&C5,barcode!A:A,0)))

Column E check

=IF(D5="","",IF(COUNTIF(F:F,D5)>=COUNTIF(D$5:D5,D5),"Done",""))

Note that this does will not check (for example) if 4 items are scanned but only 3 are required.

 

[Joeun]

Column B & C still not working. is there any thing that i missed?
- Turquoise,L need to duplicate 4 times as the quantity is 4
- Black,L need to duplicate 3 times as the quantity is 3

Column D im using vlookup. >>>> Thank you

Column E work amazing >>>> Thank you very much

 

[jasonb75]

is there any thing that i missed?
- Turquoise,L need to duplicate 4 times as the quantity is 4

No, that was something that I missed

Could you upload your test workbook on a sharing site then post a link to it? What you need will be a bit more complex so I will need something to test the formulas on.

Also, could you confirm which version of excel that you are using please. If you are using office 365, could you check to see if you have the =LET() and =FILTER() formulas available?

 

[Joeun]

Could you upload your test workbook on a sharing site then post a link to it? What you need will be a bit more complex so I will need something to test the formulas on.

Check.xlsx

drive.google.com

Also, could you confirm which version of excel that you are using please. If you are using office 365, could you check to see if you have the =LET() and =FILTER() formulas available?

MS Office 2016

=LET() and =FILTER()

not available


Thanks in advance ?

 

[Peter_SSs]

MS Office 2016

Please put that in your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[kvsrinivasamurthy]

For column B and C .
In B5
=IFERROR(INDEX(orders!$C:$C,AGGREGATE(15,6,ROW(orders!$B$2:$B$40)/(($C$1=orders!$A$2:$A$40)*ISERROR(MATCH($B$4:$B4,orders!$B$2:$B$40))),0)),"")
In C5
=IFERROR(INDEX(orders!$B:$B,AGGREGATE(15,6,ROW(orders!$B$2:$B$40)/(($C$1=orders!$A$2:$A$40)*ISERROR(MATCH($B$4:$B4,orders!$B$2:$B$40))),0)),"")
copied down.
Duplicate values are removed.

 

[jasonb75]

I tried the suggestion from @kvsrinivasamurthy before doing this but couldn't get it working.

As far as I can see, this does what is required. Maybe someone else can simplify it.

Check.xlsx
	A	B	C	D	E	F
1		Tracking Number :	PL177004039286	←Fill in
2
3
4	No.	Product Name	Variation Name	Barcode Number	Status	Scan Barcode
5	1	Collar Neck T-Shirt Cotton (Assorted Colors)	Pink,M	6543457895	Done	3452255691
6	2	Round Neck T-Shirt Cotton (Assorted Colors)	Smoke Grey,M	3452255691	Done	6543457895
7	3	Round Neck T-Shirt Cotton (Assorted Colors)	Smoke Grey,M	3452255691	Done	3457896856
8	4	Round Neck T-Shirt Cotton (Assorted Colors)	Smoke Grey,M	3452255691		3452255691
9	5	Round Neck T-Shirt Cotton (Assorted Colors)	Smoke Grey,M	3452255691
10	6	Round Neck T-Shirt Cotton (Assorted Colors)	Smoke Grey,M	3452255691
11	7	Round Neck T-Shirt Cotton (Assorted Colors)	Black,L	3457896856	Done
12
design

Cell Formulas
Range	Formula
A5:A12	A5	=IF(ROWS(A$5:A5)>SUMIF(orders!A:A,$C$1,orders!D:D),"",ROWS(A$5:A5))
B5:C12	B5	=IF($A5="","",INDEX(orders!B:B,AGGREGATE(15,6,ROW(orders!$A$2:$A$40)/(orders!$A$2:$A$40=$C$1),INT(SUM(IFERROR(1/SUMIFS(orders!$D$2:$D$40,orders!$B$2:$B$40,$B$4:$B4,orders!$C$2:$C$40,$C$4:$C4,orders!$A$2:$A$40,$C$1),0))+1))))
D5:D12	D5	=IF(C5="","",INDEX(barcode!D:D,MATCH(B5&C5,barcode!A:A,0)))
E5:E12	E5	=IF(D5="","",IF(COUNTIF(F$5:F$20,D5)>=COUNTIF(D$5:D5,D5),"Done",""))
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[Joeun]

Please put that in your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

big help for the future. done editing

Duplicate values are removed.

thank for try. but i dont wan the duplicate to be remove

@jasonb75 many thanks for the solution. It's make my day for tomorrow ?

Regards