VBA Code required - copy data from one sheet to another

[hrayani]

Hello Friends,

I need a VBA code to copy specific data from worksheet DATABASE to another worksheet PO Wise

Here is the DATABASE sheet. All values are stored as values. No formulas, validation, conditional formats etc... nothing. Just simple values

mr excel query.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V
3	P.O #	REF	P.O.DATE	Customer	Suppliers	Article	Quality	Dyed or Printed	Fiber Content	Construction	Size	Quantity	UNIT	P.O. SHIPMENT DATE	Actual Ship DATE	Remarks	SAMPLING	HELPER 2	HELPER 3	HELPER 4	Values	STATUS
4	95822	123	date	Customer 3	Gul Ahmed	Bed Set	CVC Renforce - 90 Gsm	some text	some text	some text	some text	676,830		date	date	some text	some text	some text	some text	some text	3,393,385	Shipped
5	95825	123	date	Customer 3	Gul Ahmed	Bed Set	CVC Renforce - 90 Gsm	some text	some text	some text	some text	201,520		date	date	some text	some text	some text	some text	some text	1,338,316	Shipped
6	95828	123	date	Customer 3	Gul Ahmed	Bed Set	CVC Renforce - 90 Gsm	some text	some text	some text	some text	15,420		date	date	some text	some text	some text	some text	some text	134,154	Shipped
7	95830	123	date	Customer 3	Gul Ahmed	Side Pillow	CVC Renforce - 90 Gsm	some text	some text	some text	some text	179,712		date	date	some text	some text	some text	some text	some text	242,611	Shipped
8	95831	123	date	Customer 3	Gul Ahmed	Pillow Pair	CVC Renforce - 90 Gsm	some text	some text	some text	some text	436,032		date	date	some text	some text	some text	some text	some text	639,784	Shipped
9	40041541	127	date	Customer 4	Eastern	Terry Towel	CTN 400 Gsm PUNCH	some text	some text	some text	some text	58,000		date	date	some text	some text	some text	some text	some text	72,325	Shipped
10	40043227	127	date	Customer 4	Eastern	Terry Towel	CTN 400 Gsm PUNCH	some text	some text	some text	some text	1,887		date	date	some text	some text	some text	some text	some text	953	Shipped
11	40042045	129	date	Customer 4	Union Fabrics	Bed Set	CTN Renforce - 115 Gsm	some text	some text	some text	some text	2,400		date	date	some text	some text	some text	some text	some text	12,840	Shipped
12	12943122 A	130	date	Customer 4	Union Fabrics	Pillow Pc	CTN Renforce - 115 Gsm	some text	some text	some text	some text	19,400		date	date	some text	some text	some text	some text	some text	45,142	Shipped
13	12943122 B	130	date	Customer 4	Union Fabrics	Duvet Cover	CTN Renforce - 115 Gsm	some text	some text	some text	some text	6,000		date	date	some text	some text	some text	some text	some text	57,600	Shipped
14	672571	131	date	Customer 5	Usman & Sons	Fitted Sheet	Microfiber Interlock - 135 Gsm	some text	some text	some text	some text	2,500		date	date	some text	some text	some text	some text	some text	9,604	Shipped
15	78118	132	date	Customer 3	Gul Ahmed	Bed Set	CVC Renforce - 90 Gsm	some text	some text	some text	some text	372,472		date	date	some text	some text	some text	some text	some text	1,896,309	Shipped
16	78186	132	date	Customer 3	Gul Ahmed	Bed Set	CVC Renforce - 90 Gsm	some text	some text	some text	some text	176,976		date	date	some text	some text	some text	some text	some text	1,259,929	Shipped
17	78581	132	date	Customer 3	Gul Ahmed	Bed Set	CVC Renforce - 90 Gsm	some text	some text	some text	some text	26,088		date	date	some text	some text	some text	some text	some text	254,796	Shipped
18	76424	133	date	Customer 3	Gul Ahmed	Bed Set	CTN Renforce - 115 Gsm	some text	some text	some text	some text	524,320		date	date	some text	some text	some text	some text	some text	3,514,886	Shipped
19	76535	133	date	Customer 3	Gul Ahmed	Bed Set	CTN Renforce - 115 Gsm	some text	some text	some text	some text	192,840		date	date	some text	some text	some text	some text	some text	1,775,107	Shipped
20	76546	133	date	Customer 3	Gul Ahmed	Bed Set	CTN Renforce - 115 Gsm	some text	some text	some text	some text	48,360		date	date	some text	some text	some text	some text	some text	573,377	Shipped
21	76627	133	date	Customer 3	Gul Ahmed	Pillow Pair	CTN Renforce - 115 Gsm	some text	some text	some text	some text	372,552		date	date	some text	some text	some text	some text	some text	755,222	Shipped
22	76634	133	date	Customer 3	Gul Ahmed	Side Pillow	CTN Renforce - 115 Gsm	some text	some text	some text	some text	224,364		date	date	some text	some text	some text	some text	some text	406,099	Shipped
23	675098	134	date	Customer 5	Usman & Sons	Fitted Sheet	Combed Jersey 135 GSM	some text	some text	some text	some text	500		date	date	some text	some text	some text	some text	some text	1,689	Shipped
24	40043141	135	date	Customer 4	Usman & Sons	Fitted Sheet	CTN Jersey - 140 Gsm	some text	some text	some text	some text	9,250		date	date	some text	some text	some text	some text	some text	69,005	Shipped
25	40043137	136	date	Customer 4	Usman & Sons	Fitted Sheet	CTN Jersey - 140 Gsm	some text	some text	some text	some text	14,400		date	date	some text	some text	some text	some text	some text	55,248	Shipped
26	40043145	137	date	Customer 4	Union Fabrics	Bed Set	CTN Renforce - 115 Gsm	some text	some text	some text	some text	1,000		date	date	some text	some text	some text	some text	some text	6,100	Shipped
27	40043138	138	date	Customer 4	Usman & Sons	Fitted Sheet	CTN Jersey - 140 Gsm	some text	some text	some text	some text	14,400		date	date	some text	some text	some text	some text	some text	55,248	Shipped
28	40043139	139	date	Customer 4	Usman & Sons	Fitted Sheet	CTN Jersey - 140 Gsm	some text	some text	some text	some text	14,400		date	date	some text	some text	some text	some text	some text	55,248	Shipped
29	40043150	140	date	Customer 4	Usman & Sons	Fitted Sheet	CTN Jersey - 140 Gsm	some text	some text	some text	some text	14,400		date	date	some text	some text	some text	some text	some text	55,248	Shipped
30	40043241 A	141	date	Customer 4	Union Fabrics	Bed Set	CTN Renforce - 115 Gsm	some text	some text	some text	some text	3,900		date	date	some text	some text	some text	some text	some text	51,870	Shipped
31	40043241 B	141	date	Customer 4	Union Fabrics	Bed Set	CTN Renforce - 115 Gsm	some text	some text	some text	some text	1,000		date	date	some text	some text	some text	some text	some text	8,850	Shipped
32	40043241 C	141	date	Customer 4	Union Fabrics	Pillow Pc	CTN Satin - T200	some text	some text	some text	some text	1,400		date	date	some text	some text	some text	some text	some text	4,047	Shipped
33	40043241 D	141	date	Customer 4	Union Fabrics	Duvet Cover	CTN Satin - T200	some text	some text	some text	some text	1,200		date	date	some text	some text	some text	some text	some text	15,680	Shipped
34	40043241 E	141	date	Customer 4	Union Fabrics	Pillow Pc	CTN Satin - T200	some text	some text	some text	some text	2,420		date	date	some text	some text	some text	some text	some text	7,082	Shipped
35	40043241 F	141	date	Customer 4	Union Fabrics	Duvet Cover	CTN Satin - T200	some text	some text	some text	some text	1,670		date	date	some text	some text	some text	some text	some text	21,951	Shipped
36	40043241 G	141	date	Customer 4	Union Fabrics	Pillow Pc	CTN Satin - T200	some text	some text	some text	some text	4,850		date	date	some text	some text	some text	some text	some text	11,938	Shipped
37	40043241 H	141	date	Customer 4	Union Fabrics	Duvet Cover	CTN Satin - T200	some text	some text	some text	some text	650		date	date	some text	some text	some text	some text	some text	6,338	Shipped
38	40043201 A	142	date	Customer 4	Union Fabrics	Bed Set	CTN Percale - T200	some text	some text	some text	some text	1,150		date	date	some text	some text	some text	some text	some text	16,388	Shipped
39	40043201 B	142	date	Customer 4	Union Fabrics	Pillow Pc	CTN Percale - T200	some text	some text	some text	some text	1,680		date	date	some text	some text	some text	some text	some text	4,183	Shipped
40	40043201 C	142	date	Customer 4	Union Fabrics	Duvet Cover	CTN Percale - T200	some text	some text	some text	some text	1,070		date	date	some text	some text	some text	some text	some text	13,425	Shipped
41	40043201 D	142	date	Customer 4	Union Fabrics	Bed Set	CTN Renforce - 115 Gsm	some text	some text	some text	some text	1,700		date	date	some text	some text	some text	some text	some text	17,850	Shipped
42	40043201 E	142	date	Customer 4	Union Fabrics	Pillow Pc	CTN Satin - T200	some text	some text	some text	some text	15,100		date	date	some text	some text	some text	some text	some text	36,349	Shipped
43	40043201 F	142	date	Customer 4	Union Fabrics	Duvet Cover	CTN Satin - T200	some text	some text	some text	some text	3,935		date	date	some text	some text	some text	some text	some text	38,366	Shipped
44	133018	143	date	Customer 1	Eastern	Terry Towel	CTN 360 GSM	some text	some text	some text	some text	4,500		date	date	some text	some text	some text	some text	some text	24,300	Shipped
45	19022014/JR	144	date	Customer 2	Usman & Sons	Fitted Sheet	CTN Jersey - 125 Gsm	some text	some text	some text	some text	14,680		date	date	some text	some text	some text	some text	some text	45,371	Shipped
46	40043826 A	145	date	Customer 4	Union Fabrics	Bed Set	CTN Renforce - 115 Gsm	some text	some text	some text	some text	1,200		date	date	some text	some text	some text	some text	some text	7,140	Shipped
47	40043826 B	145	date	Customer 4	Union Fabrics	Bed Set	CTN Renforce - 115 Gsm	some text	some text	some text	some text	1,300		date	date	some text	some text	some text	some text	some text	13,650	Shipped
48	40043826 C	145	date	Customer 4	Union Fabrics	Pillow Pc	CTN Satin - T200	some text	some text	some text	some text	4,700		date	date	some text	some text	some text	some text	some text	11,328	Shipped
49	40043826 D	145	date	Customer 4	Union Fabrics	Duvet Cover	CTN Satin - T200	some text	some text	some text	some text	650		date	date	some text	some text	some text	some text	some text	6,338	Shipped
50	101031	146	date	Customer 3	Liberty Mills	Bed Set	CVC Renforce - 90 Gsm	some text	some text	some text	some text	107,040		date	date	some text	some text	some text	some text	some text	544,137	Shipped
51	101032	146	date	Customer 3	Liberty Mills	Bed Set	CVC Renforce - 90 Gsm	some text	some text	some text	some text	115,222		date	date	some text	some text	some text	some text	some text	945,015	Shipped
52	101033	146	date	Customer 3	Liberty Mills	Bed Set	CVC Renforce - 90 Gsm	some text	some text	some text	some text	31,860		date	date	some text	some text	some text	some text	some text	287,099	Shipped
53	348	147	date	Customer 3	Gul Ahmed	Bed Set	CTN Flannel - 140 Gsm	some text	some text	some text	some text	3,840		date	date	some text	some text	some text	some text	some text	51,149	Shipped
54	349	147	date	Customer 3	Gul Ahmed	Bed Set	CTN Flannel - 140 Gsm	some text	some text	some text	some text	1,920		date	date	some text	some text	some text	some text	some text	33,062	Shipped
55	350	147	date	Customer 3	Gul Ahmed	Bed Set	CTN Flannel - 140 Gsm	some text	some text	some text	some text	960		date	date	some text	some text	some text	some text	some text	19,613	Shipped
56	351	147	date	Customer 3	Gul Ahmed	Pillow Pair	CTN Flannel - 140 Gsm	some text	some text	some text	some text	1,616		date	date	some text	some text	some text	some text	some text	6,432	Shipped
57	13059310	148	date	Customer 4	Tex World	Terry Towel	CTN 400 Gsm PUNCH	some text	some text	some text	some text	42,400		date	date	some text	some text	some text	some text	some text	42,588	Shipped
58	13059311	149	date	Customer 4	Tex World	Terry Towel	CTN 400 Gsm PUNCH	some text	some text	some text	some text	50,310		date	date	some text	some text	some text	some text	some text	59,729	Shipped
59	40044281 A	150	date	Customer 4	Usman & Sons	Fitted Sheet	CTN Jersey - 140 Gsm	some text	some text	some text	some text	5,200		date	date	some text	some text	some text	some text	some text	34,840	Shipped
60	40044281 B	150	date	Customer 4	Usman & Sons	Fitted Sheet	CTN Jersey - 140 Gsm	some text	some text	some text	some text	1,890		date	date	some text	some text	some text	some text	some text	17,766	Shipped
61	40044322 A	151	date	Customer 4	Union Fabrics	Bed Set	CTN Renforce - 115 Gsm	some text	some text	some text	some text	1,000		date	date	some text	some text	some text	some text	some text	6,350	Shipped
62	40044322 B	151	date	Customer 4	Union Fabrics	Bed Set	CTN Renforce - 115 Gsm	some text	some text	some text	some text	1,000		date	date	some text	some text	some text	some text	some text	10,920	Shipped
63	102755	153	date	Customer 3	Gul Ahmed	Pillow Pair	CTN Flannel - 140 Gsm	some text	some text	some text	some text	88,416		date	date	some text	some text	some text	some text	some text	265,248	Shipped
64	102756	153	date	Customer 3	Gul Ahmed	Bed Set	CTN Flannel - 140 Gsm	some text	some text	some text	some text	65,504		date	date	some text	some text	some text	some text	some text	736,920	Shipped
65	102757	153	date	Customer 3	Gul Ahmed	Bed Set	CTN Flannel - 140 Gsm	some text	some text	some text	some text	38,604		date	date	some text	some text	some text	some text	some text	526,945	Shipped
66	40044489 A	154	date	Customer 4	Union Fabrics	Bed Set	PC Renforce - 115 Gsm	some text	some text	some text	some text	700		date	date	some text	some text	some text	some text	some text	6,195	Shipped
67	40044489 B	154	date	Customer 4	Union Fabrics	Pillow Pc	CTN Renforce - 115 Gsm	some text	some text	some text	some text	11,900		date	date	some text	some text	some text	some text	some text	27,710	Shipped
68	40044489 C	154	date	Customer 4	Union Fabrics	Duvet Cover	CTN Renforce - 115 Gsm	some text	some text	some text	some text	1,800		date	date	some text	some text	some text	some text	some text	17,280	Shipped
69	687558	155	date	Customer 5	Usman & Sons	Fitted Sheet	Combed Jersey 135 GSM	some text	some text	some text	some text	2,500		date	date	some text	some text	some text	some text	some text	8,791	Shipped
70	40044896 A	156	date	Customer 4	Union Fabrics	Pillow Pc	CTN Satin - T200	some text	some text	some text	some text	4,500		date	date	some text	some text	some text	some text	some text	11,061	Shipped
71	40044896 B	156	date	Customer 4	Union Fabrics	Duvet Cover	CTN Satin - T200	some text	some text	some text	some text	600		date	date	some text	some text	some text	some text	some text	5,850	Shipped
72	40045226 A	157	date	Customer 4	Union Fabrics	Pillow Pc	CTN Renforce - 115 Gsm	some text	some text	some text	some text	5,000		date	date	some text	some text	some text	some text	some text	11,750	Shipped
73	40045226 B	157	date	Customer 4	Union Fabrics	Duvet Cover	CTN Renforce - 115 Gsm	some text	some text	some text	some text	500		date	date	some text	some text	some text	some text	some text	4,800	Shipped
74	40045226 C	157	date	Customer 4	Union Fabrics	Pillow Pc	CTN Satin - T200	some text	some text	some text	some text	5,000		date	date	some text	some text	some text	some text	some text	12,482	Shipped
75	40045226 D	157	date	Customer 4	Union Fabrics	Duvet Cover	CTN Satin - T200	some text	some text	some text	some text	400		date	date	some text	some text	some text	some text	some text	3,900	Shipped
76	13119634 A	158	date	Customer 4	Union Fabrics	Duvet Cover	CTN Satin - T200	some text	some text	some text	some text	465		date	date	some text	some text	some text	some text	some text	2,037	Shipped
77	13119634 B	158	date	Customer 4	Union Fabrics	Duvet Cover	CTN Satin - T200	some text	some text	some text	some text	578		date	date	some text	some text	some text	some text	some text	8,269	Shipped
DATABASE

And I want some specific data on sheet PO wise like this

Starting from Cell B4
Column B: All unique ref # from DATABASE sheet
Column C: All PO # against Ref # (all PO # concatenated into one cell - will be unique - no need to remove duplicates)
Column D: Sample text against the Ref # (just look at the first instance as all sample text will be same against a REF #)
Column E: Customer against Ref # (just look at the first instance as all customers will be same against a REF #)
Column F: Supplier against Ref # (just look at the first instance as all suppliers will be same against a REF #)
Column G: Articles against Ref # (all articles against REF # concatenated into one cell - remove duplicates if any)
Column H: Quality against Ref # (just look at the first instance as all quality will be same against a REF #)
Column I: Quantity aginst Ref # (sum of quantity in one cell against REF #)
Column J: Value against Ref # (sum of value in one cell aginst REF #)

The result should look like this ?

mr excel query.xlsx
	B	C	D	E	F	G	H	I	J
10	Ref #	PO #	Sampling	Customer	Supplier	Article	Quality	Qty	Value
11	123	95822, 95825, 95828, 95830, 95831	some text	Customer 3	Gul Ahmed	Bed Set, Side Pillow, Pillow Pair	CVC Renforce - 90 Gsm	1,509,514	5,748,250
12	127	40041541, 40043227	some text	Customer 4	Eastern	Terry Towel	CTN 400 Gsm PUNCH	59,887	73,278
13	129	40042045	some text	Customer 4	Union Fabrics	Bed Set	CTN Renforce - 115 Gsm	2,400	12,840
14	130	12943122 A, 12943122 B	some text	Customer 4	Union Fabrics	Pillow Pc, Duvet Cover	CTN Renforce - 115 Gsm	25,400	102,742
15	131	672571	some text	Customer 5	Usman & Sons	Fitted Sheet	Microfiber Interlock - 135 Gsm	2,500	9,604
16	132	78118, 78186, 78581	some text	Customer 3	Gul Ahmed	Bed Set	CVC Renforce - 90 Gsm	575,536	3,411,034
17	133	76424, 76535, 76546, 76627, 76634	some text	Customer 3	Gul Ahmed	Bed Set, Pillow Pair, Side Pillow	CTN Renforce - 115 Gsm	1,362,436	7,024,692
18	134	675098	some text	Customer 5	Usman & Sons	Fitted Sheet	Combed Jersey 135 GSM	500	1,689
19	135	40043141	some text	Customer 4	Usman & Sons	Fitted Sheet	CTN Jersey - 140 Gsm	9,250	69,005
20	136	40043137	some text	Customer 4	Usman & Sons	Fitted Sheet	CTN Jersey - 140 Gsm	14,400	55,248
21	137	40043145	some text	Customer 4	Union Fabrics	Bed Set	CTN Renforce - 115 Gsm	1,000	6,100
22	138	40043138	some text	Customer 4	Usman & Sons	Fitted Sheet	CTN Jersey - 140 Gsm	14,400	55,248
23	139	40043139	some text	Customer 4	Usman & Sons	Fitted Sheet	CTN Jersey - 140 Gsm	14,400	55,248
24	140	40043150	some text	Customer 4	Usman & Sons	Fitted Sheet	CTN Jersey - 140 Gsm	14,400	55,248
25	141	40043241 A, 40043241 B, 40043241 C, 40043241 D, 40043241 E, 40043241 F, 40043241 G, 40043241 H	some text	Customer 4	Union Fabrics	Bed Set, Pillow Pc, Duvet Cover	CTN Renforce - 115 Gsm	17,090	127,755
26	142	40043201 A, 40043201 B, 40043201 C, 40043201 D, 40043201 E, 40043201 F	some text	Customer 4	Union Fabrics	Bed Set, Pillow Pc, Duvet Cover	CTN Percale - T200	24,635	126,560
27	143	133018	some text	Customer 1	Eastern	Terry Towel	CTN 360 GSM	4,500	24,300
28	144	19022014/JR	some text	Customer 2	Usman & Sons	Fitted Sheet	CTN Jersey - 125 Gsm	14,680	45,371
29	145	40043826 A, 40043826 B, 40043826 C, 40043826 D	some text	Customer 4	Union Fabrics	Bed Set, Pillow Pc, Duvet Cover	CTN Renforce - 115 Gsm	7,850	38,456
30	146	101031, 101032, 101033	some text	Customer 3	Liberty Mills	Bed Set	CVC Renforce - 90 Gsm	254,122	1,776,251
31	147	348, 349, 350, 351	some text	Customer 3	Gul Ahmed	Bed Set, Pillow Pair	CTN Flannel - 140 Gsm	8,336	110,256
32	148	13059310	some text	Customer 4	Tex World	Terry Towel	CTN 400 Gsm PUNCH	42,400	42,588
33	149	13059311	some text	Customer 4	Tex World	Terry Towel	CTN 400 Gsm PUNCH	50,310	59,729
34	150	40044281 A, 40044281 B	some text	Customer 4	Usman & Sons	Fitted Sheet	CTN Jersey - 140 Gsm	7,090	52,606
35	151	40044322 A, 40044322 B	some text	Customer 4	Union Fabrics	Bed Set	CTN Renforce - 115 Gsm	2,000	17,270
36	153	102755, 102756, 102757	some text	Customer 3	Gul Ahmed	Pillow Pair, Bed Set	CTN Flannel - 140 Gsm	192,524	1,529,113
37	154	40044489 A, 40044489 B, 40044489 C	some text	Customer 4	Union Fabrics	Bed Set, Pillow Pc, Duvet Cover	PC Renforce - 115 Gsm	14,400	51,185
38	155	687558	some text	Customer 5	Usman & Sons	Fitted Sheet	Combed Jersey 135 GSM	2,500	8,791
39	156	40044896 A, 40044896 B	some text	Customer 4	Union Fabrics	Pillow Pc, Duvet Cover	CTN Satin - T200	5,100	16,911
40	157	40045226 A, 40045226 B, 40045226 C, 40045226 D	some text	Customer 4	Union Fabrics	Pillow Pc, Duvet Cover	CTN Renforce - 115 Gsm	10,900	32,932
41	158	13119634 A, 13119634 B	some text	Customer 4	Union Fabrics	Duvet Cover	CTN Satin - T200	1,043	10,306
PO Wise

Any help would be appreciated..

Regards,

Humayun

 

[JLGWhiz]

Give this a try. It might need some tweaking for the starting rows.

Sub t()
Dim sh1 As Worksheet, sh2 As Worksheet, lr As Long, fn As Range, c As Range, r As Range, rng1 As Range, rng2 As Range, adr As String
Set sh1 = ActiveSheet
Set sh2 = Sheets.Add(After:=sh1)
lr = sh1.Cells.Find("*", , xlFormulas, xlPart, xlByRows, xlPrevious).Row
    With sh1
        .Range("B3", .Cells(Rows.Count, 2)).AdvancedFilter xlFilterCopy, , .Cells(lr + 2, 1), True
        For Each c In .Cells(Rows.Count, 1).End(xlUp).CurrentRegion.Offset(1)
            If c <> "" Then
                Set fn = .Range("B:B").Find(c.Value, , xlValues, xlWhole)
                If Not fn Is Nothing Then
                        sh2.Cells(Rows.Count, 2).End(xlUp)(2) = c.Value
                        .UsedRange.AutoFilter 2, fn.Value
                        Set rng1 = sh2.Cells(Rows.Count, 2).End(xlUp).Offset(, 1)
                        For Each r In .Range("A2:A" & lr).SpecialCells(xlCellTypeVisible)
                            rng1 = rng1.Value & r.Value & ", "
                        Next
                        Columns(rng1.Column).AutoFit
                        rng1 = Left(rng1.Value, Len(rng1) - 2)
                        rng1.Offset(, 1) = fn.Offset(, 15).Value
                        rng1.Offset(, 2) = fn.Offset(, 2).Value
                        rng1.Offset(, 3) = fn.Offset(, 3).Value
                        Set rng2 = rng1.Offset(, 4)
                        For Each r In .Range("F2:F" & lr).SpecialCells(xlCellTypeVisible)
                            If InStr(rng2.Value, Trim(r.Value)) = 0 Then
                                rng2 = rng2.Value & r.Value & ", "
                            End If
                        Next
                        Columns(rng2.Column).AutoFit
                        rng2 = Left(rng2.Value, Len(rng2) - 2)
                        rng2.Offset(, 1) = fn.Offset(, 5).Value
                        rng2.Offset(, 2) = Application.SumIf(.Range("B2:B" & lr), c.Value, .Range("L2:L" & lr))
                        rng2.Offset(, 3) = Application.SumIf(.Range("B2:B" & lr), c.Value, .Range("U2:U" & lr))
                    .AutoFilterMode = False
                End If
            End If
        Next
        .Cells(lr + 2, 1).CurrentRegion.ClearContents
    End With
End Sub

 

[hrayani]

Hi JLGWhiz,

First of all thanks very much for the code... I appreciate

I wanted the the data to extracted to the sheet called "PO Wise". So I have changed
From this
Set sh2 = Sheets.Add(After:=sh1)
To This
Set sh2 = Sheets("PO Wise")

Well that's the only thing I was able to do with my very little knowledge of VBA

There are some minor issues mentioned below:

1) As you pointed out that I want the data to be started at row # 11, whereas as of now it is starting at row # 2
2) If the mother source file has some hidden columns then it does not work
3) In PO and article column its written PO # and article in every cell... which is not required. pls see screen shot

4) The code takes about 6 to 8 seconds to complete the task. So we can do this to cut the load of the VB code

These below points under red box can be ignored if they can save some time for the code to run

I will type the Ref # once and it will remain there on the sheet

Columns D,E,F & H can be called with an index match formula in VBA - which can be done under 1 second

So now all the code has to do is this

Starting from cell B11
Column C: Look at cell B11 and find all PO's against that ref # from the database sheet and return concatenated into one single cell
Column G: Look at cell B11 and find all articles against ref # from the database sheet and return concatenated value - removing duplicates
Column I: Look at cell B11 and sum all quantities from the database sheet
Column J: Look at cell B11 and sum all values from the database sheet

Column C & G are the most important thing

Regards,

Humayun

 

[hrayani]

Hi,

This is what I have come up with and it does all what I want in 1 second - except 2 things

The PO # & articles

1) ALL PO # against Ref # needs to concatenated in single cell - there wont be any duplicates
2) All Article agianst Ref # needs to be concatenated in single cell - remove duplicates if any

as of now I am able to pull only the first instance

[/B]
Sub humayun()

Range("C11:C583").Formula = "=iferror(index(po,match($B11,ref,0)),"""")"
Range("D11:D583").Formula = "=iferror(index(sampling,match($B11,ref,0)),"""")"
Range("E11:E583").Formula = "=iferror(index(customer,match($B11,ref,0)),"""")"
Range("F11:F583").Formula = "=iferror(index(supplier,match($B11,ref,0)),"""")"
Range("G11:G583").Formula = "=iferror(index(article,match($B11,ref,0)),"""")"
Range("H11:H583").Formula = "=iferror(index(quality,match($B11,ref,0)),"""")"
Range("I11:I583").Formula = "=SUMIF(ref,B11,quantity)"
Range("J11:J583").Formula = "=SUMIF(ref,B11,value)"

Range("C11:J583").Value = Range("C11:J583").Value
End Sub

[B]

Any idea ??

Regards,

Humayun

 

[hrayani]

Hi JLGWhiz,

Any idea ??

 

[JLGWhiz]

Hi JLGWhiz,

Any idea ??

No, the code I posted was my idea.
Regards, JLG

 

[hrayani]

No, the code I posted was my idea.
Regards, JLG

Thanks...

As of now its extracting results in sh.2 starting from cell B2

Can you pls make one change to make it start from from cell B11 instead of cell B2

Regards,

Humayun

 

[JLGWhiz]

See if this works better.

Sub t2()
Dim sh1 As Worksheet, sh2 As Worksheet, lr As Long, fn As Range, c As Range, r As Range, rng1 As Range, rng2 As Range, adr As String
Set sh1 = ActiveSheet
Set sh2 = Sheets.Add(After:=sh1)
lr = sh1.Cells.Find("*", , xlFormulas, xlPart, xlByRows, xlPrevious).Row
    With sh1
        .Range("B10", .Cells(Rows.Count, 2)).AdvancedFilter xlFilterCopy, , .Cells(lr + 2, 1), True
        For Each c In .Cells(Rows.Count, 1).End(xlUp).CurrentRegion.Offset(1)
            If c <> "" Then
                Set fn = .Range("B:B").Find(c.Value, , xlValues, xlWhole)
                If Not fn Is Nothing Then
                    If sh2.Range("B11") = "" Then
                        sh2.Range("B11") = c.Value
                    Else
                        sh2.Cells(Rows.Count, 2).End(xlUp)(2) = c.Value
                    End If
                    .UsedRange.AutoFilter 2, fn.Value
                    Set rng1 = sh2.Cells(Rows.Count, 2).End(xlUp).Offset(, 1)
                    For Each r In .Range("A11:A" & lr).SpecialCells(xlCellTypeVisible)
                        rng1 = rng1.Value & r.Value & ", "
                    Next
                    Columns(rng1.Column).AutoFit
                    rng1 = Left(rng1.Value, Len(rng1) - 2)
                    rng1.Offset(, 1) = fn.Offset(, 15).Value
                    rng1.Offset(, 2) = fn.Offset(, 2).Value
                    rng1.Offset(, 3) = fn.Offset(, 3).Value
                    Set rng2 = rng1.Offset(, 4)
                    For Each r In .Range("F11:F" & lr).SpecialCells(xlCellTypeVisible)
                        If InStr(rng2.Value, Trim(r.Value)) = 0 Then
                            rng2 = rng2.Value & r.Value & ", "
                        End If
                    Next
                    Columns(rng2.Column).AutoFit
                    rng2 = Left(rng2.Value, Len(rng2) - 2)
                    rng2.Offset(, 1) = fn.Offset(, 5).Value
                    rng2.Offset(, 2) = Application.SumIf(.Range("B2:B" & lr), c.Value, .Range("L2:L" & lr))
                    rng2.Offset(, 3) = Application.SumIf(.Range("B2:B" & lr), c.Value, .Range("U2:U" & lr))
                    .AutoFilterMode = False
                End If
            End If
        Next
        .Cells(lr + 2, 1).CurrentRegion.ClearContents
    End With
End Sub

 

[hrayani]

Just tested the recent code..

I have changed the sh1 and sh2 names as required and it is working fine with that

From This
Set sh1 = ActiveSheet
Set sh2 = Sheets.Add(After:=sh1)

To This
Set sh1 = Sheets("DATABASE")
Set sh2 = Sheets("PO Wise")

But the problem is that it start looking at sh1 from Cell B11 whereas I want it to look at sh1 from Cell B4 and because of this some starting data when extracted on sh2.

I tried changing the range to B4 in this part

With sh1
.Range("B10", .Cells(Rows.Count, 2)).AdvancedFilter xlFilterCopy, , .Cells(lr + 2, 1), True

But I get an error 1004 (no cells were found) with this part of the code highlited ?

For Each r In .Range("A11:A" & lr).SpecialCells(xlCellTypeVisible)

 

[JLGWhiz]

You will have to get someone else to help you with this. I have spent too much time on it now.
Regards, JLG