VBA Code Required

[hrayani]

Hello Friends,

I want to copy specific data in specific format from one worksheet to another (both worksheet in the same workbook)

Here is the short sample data

Running Orders New Style.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V
2	PO #	REF #	PO DATE	Customer	Supplier	Article	Quality	Dyed or Printed	Fiber Content	Construction	SIZE	QTY	UNIT	PO SHIP DATE	ACTUAL SHIP DATE	REMARKS	SAMPLING	PO # CONCAT	ARTICLE CONCAT	EXTRA COLUMN	VALUE	STATUS
3	377421	633	29-Dec-21	TEX 1	Mills	Bed Set	Microfiber Satin - 100 Gsm	Disperse Print	100% Polyester	75D X 200D 107X66	Normal	143,760	Set(s)	2-May-22			OCT - 2021	377421	Bed Set		$699,576	In Process
4	377422	633	29-Dec-21	TEX 1	Mills	Pillow Pair	Microfiber Satin - 100 Gsm	Disperse Dyed + Dispers Print	100% Polyester	75D X 200D 107X66	Multiple	233,544	Pair(s)	2-May-22			OCT - 2021	377421 - 377422	Bed Set - Pillow Pair		$452,181	In Process
5	377423	633	29-Dec-21	TEX 1	Mills	Bed Set	Microfiber Satin - 100 Gsm	Disperse Print	100% Polyester	75D X 200D 107X66	Over	94,240	Set(s)	2-May-22			OCT - 2021	377421 - 377422 - 377423	Bed Set - Pillow Pair		$673,612	In Process
6	377424	633	29-Dec-21	TEX 1	Mills	Bed Set	Microfiber Satin - 100 Gsm	Disperse Print	100% Polyester	75D X 200D 107X66	King	37,110	Set(s)	2-May-22			OCT - 2021	377421 - 377422 - 377423 - 377424	Bed Set - Pillow Pair		$309,762	In Process
7	377425	633	29-Dec-21	TEX 1	Mills	Side Pillow	Microfiber Satin - 100 Gsm	Disperse Dyed + Dispers Print	100% Polyester	75D X 200D 107X66	40x145	176,532	Pc(s)	2-May-22			OCT - 2021	377421 - 377422 - 377423 - 377424 - 377425	Bed Set - Pillow Pair - Side Pillow		$307,166	In Process
8	980154	634	10-Dec-21	TEX 1	Mills	Bed Set	Microfiber Satin - 100 Gsm	Disperse Print	100% Polyester	75D X 200D 107X66	Normal	59,976	Set(s)	6-Jun-22			OCT - 2021	980154	Bed Set		$249,122	In Process
9	980155	634	10-Dec-21	TEX 1	Mills	Bed Set	Microfiber Satin - 100 Gsm	Disperse Print	100% Polyester	75D X 200D 107X66	King	25,410	Set(s)	6-Jun-22			OCT - 2021	980154 - 980155	Bed Set		$170,777	In Process
10	980156	634	10-Dec-21	TEX 1	Mills	Bed Set	Microfiber Satin - 100 Gsm	Disperse Print	100% Polyester	75D X 200D 107X66	Over	47,976	Set(s)	6-Jun-22			OCT - 2021	980154 - 980155 - 980156	Bed Set		$254,075	In Process
11	980157	634	10-Dec-21	TEX 1	Mills	Pillow Pair	Microfiber Satin - 100 Gsm	Disperse Print	100% Polyester	75D X 200D 107X66	Multiple	51,240	Pair(s)	6-Jun-22			OCT - 2021	980154 - 980155 - 980156 - 980157	Bed Set - Pillow Pair		$ 41,313	In Process
12	123456	635	10-Dec-21	ROS 2	Usman	Fitted Sheet	CTN Jersey - 135 Gsm	Reactive Dyed	100% Cotton	30'S Cotton	100x200	3,920	Pc(s)	13-Feb-22		Best Price & Easy Sleep - Contract # 21176967		123456	Fitted Sheet		$ 14,896	Shipped
13	123457	635	10-Dec-21	ROS 2	Usman	Fitted Sheet	CTN Jersey - 135 Gsm	Reactive Dyed	100% Cotton	30'S Cotton	150x200	2,400	Pc(s)	13-Feb-22		Best Price & Easy Sleep - Contract # 21176967		123456 - 123457	Fitted Sheet		$ 12,960	Shipped
14	229988	636	14-Dec-21	LOS	Fabrics	Sheet Set	CTN Renforce - 100 Gsm	Pigment Printed Rotary	100% Cotton	30X30 / 76X50	Single	1,416	Set(s)	28-Feb-22				229988	Sheet Set		$ 14,168	In Process
15	229900	636	14-Dec-21	LOS	Fabrics	Sheet Set	CTN Renforce - 100 Gsm	Pigment Printed Rotary	100% Cotton	30X30 / 76X50	Single	3,456	Set(s)	28-Feb-22				229988 - 229900	Sheet Set		$ 41,437	In Process
16	403175	637	21-Dec-21	OFF	Towel	Terry Beach Towel	CTN 320 GSM	Reactive Print	100% Cotton		70x150 cm	60,160	Pc(s)	18-Apr-22		Amount in euro increased to 13%		403175	Terry Beach Towel		$205,982	In Process
17	403176	637	21-Dec-21	OFF	Towel	Poncho	CTN 320 GSM	Reactive Print	100% Cotton		60x120 cm	56,092	Pc(s)	18-Apr-22		Amount in euro increased to 13%		403175 - 403176	Terry Beach Towel - Poncho		$205,998	In Process
18	310088	638	7-Jan-22	OFF	Towel	Terry Towel	CTN 385 GSM	Reactive Dyed	100% Cotton		50x100 cm (2 Pc Set)	142,566	Set(s)	10-Jun-22		Young Living ▬ Amount in euro increased to 14%		310088	Terry Towel		$367,307	Shipped
19	310089	638	7-Jan-22	OFF	Towel	Terry Towel	CTN 385 GSM	Reactive Dyed	100% Cotton		70x140 cm	124,382	Pc(s)	10-Jun-22		Young Living ▬ Amount in euro increased to 14%		310088 - 310089	Terry Towel		$314,786	Shipped
20	567891	639	12-Jan-22	ROS 2	Usman	Fitted Sheet	CTN Jersey - 135 Gsm	Reactive Dyed	100% Cotton	30'S Cotton	100x200	4,080	Pc(s)	13-May-22		Best Price & Easy Sleep - Contract # 21176967		567891	Fitted Sheet		$ 15,504	Shipped
21	567892	639	12-Jan-22	ROS 2	Usman	Fitted Sheet	CTN Jersey - 135 Gsm	Reactive Dyed	100% Cotton	30'S Cotton	150x200	2,220	Pc(s)	13-May-22		Best Price & Easy Sleep - Contract # 21176967		567891 - 567892	Fitted Sheet		$ 11,988	Shipped
22	129875	640	12-Jan-22	ROS 2	Usman	Fitted Sheet	CTN Jersey - 135 Gsm	Reactive Dyed	100% Cotton	30'S Cotton	100x200	3,840	Pc(s)	1-Jun-22		Best Price & Easy Sleep - Contract # 21176967		129875	Fitted Sheet		$ 14,592	Shipped
23	129876	640	12-Jan-22	ROS 2	Usman	Fitted Sheet	CTN Jersey - 135 Gsm	Reactive Dyed	100% Cotton	30'S Cotton	150x200	2,400	Pc(s)	1-Jun-22		Best Price & Easy Sleep - Contract # 21176967		129875 - 129876	Fitted Sheet		$ 12,960	Shipped
ORDERS

Cell Formulas
Range	Formula
R3:R23	R3	=IF(ISBLANK(B3),"",IF(B3<>B2,A3,IF(ISNUMBER(SEARCH(A3,R2)),R2,R2&" - "&A3)))
S3:S23	S3	=IF(ISBLANK(B3),"",IF(B3<>B2,F3,IF(ISNUMBER(SEARCH(F3,S2)),S2,S2&" - "&F3)))

Sheet 1: Copy from Sheet ORDERS
Sheet2: Copy to Sheet RUNNING ORDER STATUS

I want the data to copied to sheet 2 with following conditions

1) Copy all rows showing "In Process" in Column V from sheet 1 to sheet 2 starting from cell B4
Columns to copy would be A,B,C,D,E,F,G,K,L,M,N & P (Total 12 Columns)

2) Sort Data - 1st by Customer in Alphabetical order then by Ascending order PO ship date

3) Insert a Line when a Ref # changes
a) On the inserted Line: Show 1st instance for columns PO #, REF #, PO Date, Customer, Supplier, Article, Quality, PO Shape Date Columns
b) On the inserted Line: Show "Multiple" if more than one entry is found for Size, Unit & Remarks Columns else show 1st Instance
c) On the inserted Line: Show Total for the Qty Column

4) In Column A add value 1 to all the copied rows & value 2 to all the inserted rows (This is because I will be filtering records further)

5) Content Banding When a Ref # Changes

6) Dark grey color to the Inserted Rows

So, the final look will be like this

Running Orders New Style.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M
4		PO #	REF #	PO DATE	Customer	Supplier	Article	Quality	SIZE	QTY	UNIT	PO SHIP DATE	REMARKS
5	1	377421	633	29-Dec-21	TEX 1	Mills	Bed Set	Microfiber Satin - 100 Gsm	Normal	143,760	Set(s)	2-May-22
6	1	377422	633	29-Dec-21	TEX 1	Mills	Pillow Pair	Microfiber Satin - 100 Gsm	Multiple	233,544	Pair(s)	2-May-22
7	1	377423	633	29-Dec-21	TEX 1	Mills	Bed Set	Microfiber Satin - 100 Gsm	Over	94,240	Set(s)	2-May-22
8	1	377424	633	29-Dec-21	TEX 1	Mills	Bed Set	Microfiber Satin - 100 Gsm	King	37,110	Set(s)	2-May-22
9	1	377425	633	29-Dec-21	TEX 1	Mills	Side Pillow	Microfiber Satin - 100 Gsm	40x145	176,532	Pc(s)	2-May-22
10	2	377421	633	29-Dec-22	TEX 1	Mills	Bed Set	Microfiber Satin - 100 Gsm	Multiple	685,186	Multiple	2-May-22
11	1	980154	634	10-Dec-21	TEX 1	Mills	Bed Set	Microfiber Satin - 100 Gsm	Normal	59,976	Set(s)	6-Jun-22
12	1	980155	634	10-Dec-21	TEX 1	Mills	Bed Set	Microfiber Satin - 100 Gsm	King	25,410	Set(s)	6-Jun-22
13	1	980156	634	10-Dec-21	TEX 1	Mills	Bed Set	Microfiber Satin - 100 Gsm	Over	47,976	Set(s)	6-Jun-22
14	1	980157	634	10-Dec-21	TEX 1	Mills	Pillow Pair	Microfiber Satin - 100 Gsm	Multiple	51,240	Pair(s)	6-Jun-22
15	2	980154	634	10-Dec-22	TEX 1	Mills	Bed Set	Microfiber Satin - 100 Gsm	Multiple	184,602	Multiple	6-Jun-22
16	1	229988	636	14-Dec-21	LOS	Fabrics	Sheet Set	CTN Renforce - 100 Gsm	Single	1,416	Set(s)	28-Feb-22
17	1	229900	636	14-Dec-21	LOS	Fabrics	Sheet Set	CTN Renforce - 100 Gsm	Single	3,456	Set(s)	28-Feb-22
18	2	229988	636	14-Dec-22	LOS	Fabrics	Sheet Set	CTN Renforce - 100 Gsm	Single	4,872	Set(s)	28-Feb-22
19	1	403175	637	21-Dec-21	OFF	Towel	Terry Beach Towel	CTN 320 GSM	70x150 cm	60,160	Pc(s)	18-Apr-22	Amount in euro increased to 13%
20	1	403176	637	21-Dec-21	OFF	Towel	Poncho	CTN 320 GSM	60x120 cm	56,092	Pc(s)	18-Apr-22	Amount in euro increased to 13%
21	2	403175	637	21-Dec-22	OFF	Towel	Terry Beach Towel	CTN 320 GSM	Multiple	116,252	Pc(s)	18-Apr-22
22	1	377421	633	29-Dec-21	TEX 1	Mills	Bed Set	Microfiber Satin - 100 Gsm	Normal	143,760	Set(s)	2-May-22
23	1	377422	633	29-Dec-21	TEX 1	Mills	Pillow Pair	Microfiber Satin - 100 Gsm	Multiple	233,544	Pair(s)	2-May-22
24	1	377423	633	29-Dec-21	TEX 1	Mills	Bed Set	Microfiber Satin - 100 Gsm	Over	94,240	Set(s)	2-May-22
25	1	377424	633	29-Dec-21	TEX 1	Mills	Bed Set	Microfiber Satin - 100 Gsm	King	37,110	Set(s)	2-May-22
26	1	377425	633	29-Dec-21	TEX 1	Mills	Side Pillow	Microfiber Satin - 100 Gsm	40x145	176,532	Pc(s)	2-May-22
27	2	377421	633	29-Dec-22	TEX 1	Mills	Bed Set	Microfiber Satin - 100 Gsm	Multiple	685,186	Multiple	2-May-22
28	1	980154	634	10-Dec-21	TEX 1	Mills	Bed Set	Microfiber Satin - 100 Gsm	Normal	59,976	Set(s)	6-Jun-22
29	1	980155	634	10-Dec-21	TEX 1	Mills	Bed Set	Microfiber Satin - 100 Gsm	King	25,410	Set(s)	6-Jun-22
30	1	980156	634	10-Dec-21	TEX 1	Mills	Bed Set	Microfiber Satin - 100 Gsm	Over	47,976	Set(s)	6-Jun-22
31	1	980157	634	10-Dec-21	TEX 1	Mills	Pillow Pair	Microfiber Satin - 100 Gsm	Multiple	51,240	Pair(s)	6-Jun-22
32	2	980154	634	10-Dec-22	TEX 1	Mills	Bed Set	Microfiber Satin - 100 Gsm	Multiple	184,602	Multiple	6-Jun-22
RUNNING ORDER STATUS

Any help would be appreciated,

Regards,

Humayun

 

[hrayani]

If it works, then the approach is right.

Thanks

Please!, one last question & then I am done with the project

Is it possible to concatenate (join with comma separated) unique values ?
Currently this is the part of last code which you have sent me for count unique which works PERFECTLY FINE

Set rng = .Range("K" & fRow & ":K" & lRow)
                Set dic = CreateObject("Scripting.Dictionary")
                For Each cel In rng
                    If Not dic.exists(cel.Value) Then
                        dic.Add cel.Value, Nothing
                    End If
                Next cel
                .Range("K" & lRow + 1) = dic.Count

 

[mumps]

Try:

.Range("K" & lRow + 1) = Join(dic.keys, ", ")

 

[hrayani]

Try:

.Range("K" & lRow + 1) = Join(dic.keys, ", ")

Bingo!

Bundle of thanks!

 

[mumps]

My pleasure.