Merging With Same Cell Value and Matching Other Column Value

[shah0101]

Hello Experts,

Following is actual data and the common column in both sheets is "Style No.".

The two sheets have thousands of records. Lets say first sheet is with prices and order quantity and the second sheet is with actual shipped quantity.




TABLE 1 WITH PRICES:

Style No.	Suppl. Style	brand	Color	Sex	M	L	XL	Quantity/Pcs	Unit price	Amount
14406146	MR-1	Mo	light navy	men	29	43	29	101	$ 2.50	$ 976.67
14406146	MR-1	Mo	black	men	29	43	29	101	$ 2.50	$ 976.67
14406146	MR-1	Mo	dark beige	men	29	43	29	101	$ 2.50	$ 976.67
14406146	MR-1	Mo	light olive	men	29	43	29	101	$ 2.50	$ 976.67
14406147	16203	Mo	light navy	men	29	43	29	101	$ 3.50	$ 1,139.28
14406147	16203	Mo	black	men	29	43	29	101	$ 3.50	$ 1,139.28
14406147	16203	Mo	dark beige	men	29	43	29	101	$ 3.50	$ 1,139.28
14406147	16203	Mo	light olive	men	29	43	29	101	$ 3.50	$ 1,139.28
14406148	3015	Mo	navy	men	29	43	29	101	$ 1.50	$ 1,374.61
14406148	3015	Mo	black	men	29	43	29	101	$ 1.50	$ 1,374.61
14406149	17323	Mo	navy	men	29	43	29	101	$ 2.30	$ 1,049.39
14406149	17323	Mo	black	men	29	43	29	101	$ 2.30	$ 1,049.39
14406150	19618	Mo	black	men	29	43	29	101	$ 2.21	$ 1,031.21
14406150	19618	Mo	navy	men	29	43	29	101	$ 2.21	$ 1,031.21

<colgroup><col><col><col><col><col><col span="3"><col><col><col></colgroup><tbody>
</tbody>

TABLE 2 WITH SHIPPED QUANTITIES:

Carton	Total	Style No.	Customs-No.	Colour	Lot	Column1	Column2	Column3	Pcs/Ctn	Total Pcs	G.W.	N.W.	Carton Size	Column4	Column5	CBM
						M	L	XL					Length	Width	Height
1	1	14406146		light navy	M/L/XL	10	10	10	30	30	13	12.2	60	40	32	0.0768
5	1	14406146		black	M/L/XL	10	10	10	30	30	13	12.2	60	40	32	0.0768
9	1	14406146		dark beige	M/L/XL	10	10	10	30	30	13	12.2	60	40	32	0.0768
13	1	14406146		light olive	M/L/XL	10	10	10	30	30	13	12.2	60	40	32	0.0768
1	1	14406147		light navy	M/L/XL	10	10	10	30	30	14.4	13.2	60	40	32	0.0768
5	1	14406147		black	M/L/XL	10	10	10	30	30	14.4	13.2	60	40	32	0.0768
9	1	14406147		dark beige	M/L/XL	10	10	10	30	30	14.4	13.2	60	40	32	0.0768
13	1	14406147		light olive	M/L/XL	10	10	10	30	30	14.4	13.2	60	40	32	0.0768
1	1	14406148		navy	M/L/XL	10	10	10	30	30	19	18.1	60	40	50	0.12
5	1	14406148		black	M/L/XL	10	10	10	30	30	19	18.1	60	40	50	0.12
1	1	14406149		navy	M/L/XL	10	10	10	30	30	10.9	10.1	60	40	30	0.072
5	1	14406149		black	M/L/XL	10	10	10	30	30	10.9	10.1	60	40	30	0.072
1	1	14406150		black	M/L/XL	10	10	10	30	30	11	10.2	60	40	30	0.072
5	1	14406150		navy	M/L/XL	10	10	10	30	30	11	10.2	60	40	30	0.072

<colgroup><col><col><col><col><col><col><col span="3"><col><col><col><col><col><col span="2"><col></colgroup><tbody>
</tbody>

Now what I want to achieve is:
1) merge the style no. to a single row of same kind on second sheet (removing colour/lot columns)
2) then relate/attach/bring the prices from the first sheet based on "Style No." right on the same row in front of it to calculate the value of actual shipped goods.

I dont know if it can be acieved with PivotTable() or Vlookup() or Match(). Can you please guide / advise.

I hope I am making sense.

Thanks,

 

[shah0101]

You can't have a single INDEX and/or MATCH using multiple ranges so you'll have to search each in turn.

I've put tables 1,3 and 4 on one sheet just to demonstrate (so you'll need to add sheet names to the arrays) and I'm showing Style Nos and Price in different columns just for fun.

The IFERROR searching tables 1 and 2 will fall into the next search if no match is found, otherwise it returns the first Pice. If the Style number in table 2 isn't on tables 1, 3 or 4 then you'll get a #N/A error.

	A	B	C	D	E	F	G	H	I	J	K	L
1	TABLE 1 WITH PRICES:
2	Style No.	Suppl. Style	brand	Color	Sex	M	L	XL	Quantity/Pcs	Unit price	Amount
3	14406146	MR-1	Mo	light navy	men	29	43	29	101	$2.50	$976.67
4	2222222	MR-1	Mo	black	men	29	43	29	101	$4.22	$976.67
5
6	TABLE 3 WITH PRICES:
7		Style No.	Suppl. Style	brand	Color	Sex	Quantity/Pcs	Unit price	Amount
8		14406147	16203	Mo	light navy	men	101	$1.55	$1,139.28
9		14406147	16203	Mo	black	men	101	$1.55	$1,139.28
10		14406147	16203	Mo	dark beige	men	101	$1.55	$1,139.28
11
12	TABLE 4 WITH PRICES:
13	BD	LD	Style No.	Suppl. Style	brand	Color	Sex	M	L	Quantity/Pcs	Unit price	Amount
14	33	22	14406148	3015	Mo	navy	men	29	43	101	$3.33	$1,374.61
15	22	33	14406148	3015	Mo	black	men	29	43	101	$3.33	$1,374.61
16
17	TABLE 2 WITH SHIPPED QUANTITIES:
18
19	Carton	Total	Value Shipped	Style No.	Extracted Price
20
21	1	1		14406146	2.5
22	5	1		2222222	4.22
23	9	1		14406147	1.55
24	1	1		14406148	3.33
25	5	1		3333333	#N/A

<tbody>
</tbody>

Sheet1 (2)​

Worksheet Formulas Cell Formula E21 =IFERROR(INDEX($J$3:$J$4,MATCH(D21,$A$3:$A$4,0)),IFERROR(INDEX($H$8:$H$10,MATCH(D21,$B$8:$B$10,0)),INDEX($K$14:$K$15,MATCH(D21,$C$14:$C$15,0)))) <tbody> </tbody>

<tbody>
</tbody>

thanks a ton!!!!!!