Merging With Same Cell Value and Matching Other Column Value

shah0101

New Member
Joined
Jul 4, 2019
Messages
45
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. StylebrandColorSexMLXLQuantity/PcsUnit priceAmount
14406146MR-1Molight navymen294329101 $ 2.50 $ 976.67
14406146MR-1Moblackmen294329101 $ 2.50 $ 976.67
14406146MR-1Modark beigemen294329101 $ 2.50 $ 976.67
14406146MR-1Molight olivemen294329101 $ 2.50 $ 976.67
1440614716203Molight navymen294329101 $ 3.50 $ 1,139.28
1440614716203Moblackmen294329101 $ 3.50 $ 1,139.28
1440614716203Modark beigemen294329101 $ 3.50 $ 1,139.28
1440614716203Molight olivemen294329101 $ 3.50 $ 1,139.28
144061483015Monavymen294329101 $ 1.50 $ 1,374.61
144061483015Moblackmen294329101 $ 1.50 $ 1,374.61
1440614917323Monavymen294329101 $ 2.30 $ 1,049.39
1440614917323Moblackmen294329101 $ 2.30 $ 1,049.39
1440615019618Moblackmen294329101 $ 2.21 $ 1,031.21
1440615019618Monavymen294329101 $ 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 Column1Column2Column3Pcs/Ctn Total Pcs G.W. N.W.Carton SizeColumn4Column5CBM
MLXL LengthWidthHeight
1114406146 light navyM/L/XL10101030301312.26040320.0768
5114406146 blackM/L/XL10101030301312.26040320.0768
9114406146 dark beigeM/L/XL10101030301312.26040320.0768
13114406146 light oliveM/L/XL10101030301312.26040320.0768
1114406147 light navyM/L/XL101010303014.413.26040320.0768
5114406147 blackM/L/XL101010303014.413.26040320.0768
9114406147 dark beigeM/L/XL101010303014.413.26040320.0768
13114406147 light oliveM/L/XL101010303014.413.26040320.0768
1114406148 navyM/L/XL10101030301918.16040500.12
5114406148 blackM/L/XL10101030301918.16040500.12
1114406149 navyM/L/XL101010303010.910.16040300.072
5114406149 blackM/L/XL101010303010.910.16040300.072
1114406150 blackM/L/XL10101030301110.26040300.072
5114406150 navyM/L/XL10101030301110.26040300.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

New Member
Joined
Jul 4, 2019
Messages
45
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.

ABCDEFGHIJKL
1TABLE 1 WITH PRICES:
2Style No.Suppl. StylebrandColorSexMLXLQuantity/PcsUnit priceAmount
314406146MR-1Molight navymen294329101$2.50$976.67
42222222MR-1Moblackmen294329101$4.22$976.67
5
6TABLE 3 WITH PRICES:
7Style No.Suppl. StylebrandColorSexQuantity/PcsUnit priceAmount
81440614716203Molight navymen101$1.55$1,139.28
91440614716203Moblackmen101$1.55$1,139.28
101440614716203Modark beigemen101$1.55$1,139.28
11
12TABLE 4 WITH PRICES:
13BDLDStyle No.Suppl. StylebrandColorSexMLQuantity/PcsUnit priceAmount
143322144061483015Monavymen2943101$3.33$1,374.61
152233144061483015Moblackmen2943101$3.33$1,374.61
16
17TABLE 2 WITH SHIPPED QUANTITIES:
18
19CartonTotalValue ShippedStyle No.Extracted Price
20
2111144061462.5
225122222224.22
2391144061471.55
2411144061483.33
25513333333#N/A

<tbody>
</tbody>
Sheet1 (2)

Worksheet Formulas
CellFormula
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!!!!!!
 

Some videos you may like

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.

Watch MrExcel Video

Forum statistics

Threads
1,122,976
Messages
5,599,139
Members
414,292
Latest member
kingshuk963

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top