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,
 

Some videos you may like

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.

Toadstool

Well-known Member
Joined
Mar 5, 2018
Messages
1,300
Office Version
  1. 2016
Platform
  1. Windows
Hi shah0101,

I'd normally build a third table, starting with the unique distinct list of Style Nos. then do the calculations. If you want it in Table 2 then I've inserted a column but the calculation only show for the last entry of a duplicated Style No.

I don't see a Shipping Qty so I'm assuming the Carton number is the quantity shipped.

This calculation assume that TABLE 1 prices are the same for duplicate Style Nos.

The formula in C23 needs to be copied down all rows in TABLE 2.

ABCDEFGHIJK
1TABLE 1 WITH PRICES:
2Style No.Suppl. StylebrandColorSexMLXLQuantity/PcsUnit priceAmount
314406146MR-1Molight navymen294329101$2.50$976.67
414406146MR-1Moblackmen294329101$2.50$976.67
514406146MR-1Modark beigemen294329101$2.50$976.67
614406146MR-1Molight olivemen294329101$2.50$976.67
71440614716203Molight navymen294329101$3.50$1,139.28
81440614716203Moblackmen294329101$3.50$1,139.28
91440614716203Modark beigemen294329101$3.50$1,139.28
101440614716203Molight olivemen294329101$3.50$1,139.28
11144061483015Monavymen294329101$1.50$1,374.61
12144061483015Moblackmen294329101$1.50$1,374.61
131440614917323Monavymen294329101$2.30$1,049.39
141440614917323Moblackmen294329101$2.30$1,049.39
151440615019618Moblackmen294329101$2.21$1,031.21
161440615019618Monavymen294329101$2.21$1,031.21
17
18
19TABLE 2 WITH SHIPPED QUANTITIES:
20
21CartonTotalValue ShippedStyle No.Customs-No.ColourLotColumn1Column2Column3Pcs/Ctn
22MLXL
231114406146light navyM/L/XL10101030
245114406146blackM/L/XL10101030
259114406146dark beigeM/L/XL10101030
26131$70.0014406146light oliveM/L/XL10101030
271114406147light navyM/L/XL10101030
285114406147blackM/L/XL10101030
299114406147dark beigeM/L/XL10101030
30131$98.0014406147light oliveM/L/XL10101030
311114406148navyM/L/XL10101030
3251$9.0014406148blackM/L/XL10101030
331114406149navyM/L/XL10101030
3451$13.8014406149blackM/L/XL10101030
351114406150blackM/L/XL10101030
3651$13.2614406150navyM/L/XL10101030

<tbody>
</tbody>
Sheet2 (2)

Worksheet Formulas
CellFormula
C23=IF(COUNTIF($D23:$D$36,D23)=1,INDEX($J$3:$J$16,MATCH(D23,$A$3:$A$16,0))*SUMIFS($A$23:$A$36,$D$23:$D$36,D23),"")

<tbody>
</tbody>

<tbody>
</tbody>
 

shah0101

New Member
Joined
Jul 4, 2019
Messages
45
view
Dear ToadStool,

Thank you very much for your help.

As you said: "I'd normally build a third table, starting with the unique distinct list of Style Nos. then do the calculations". I think this would be the right approach so I made a new sheet with Pivot and now all the "Style No." from table 2 are unique and have the relevant quantities (screenshot is attached, which is such a relief, thanks to you).

Now is there any possibility that we can bring / fetch / link the relevant price, brand, etc of similar "Style No." from table 1 to the the pivot table or otherwise?

Thanks in advance.

view
-------
P.S. for some reason I am unable to link the scrrenshot so here is the link: https://drive.google.com/file/d/1el52YYwZZHr3274RxpBjnxs6w59Ht61K/view?usp=sharing
 
Last edited:

Toadstool

Well-known Member
Joined
Mar 5, 2018
Messages
1,300
Office Version
  1. 2016
Platform
  1. Windows
It's too late here for me to build pivot tables so I'll just say that if you've the total then the INDEX MATCH will retrieve prices:

Code:
[COLOR=Blue]=INDEX([COLOR=Red]$J$3:$J$16,MATCH([COLOR=Green]D23,$A$3:$A$16,0[/COLOR])[/COLOR])[/COLOR]
 

shah0101

New Member
Joined
Jul 4, 2019
Messages
45

ADVERTISEMENT

It's too late here for me to build pivot tables so I'll just say that if you've the total then the INDEX MATCH will retrieve prices:

Code:
[COLOR=Blue]=INDEX([COLOR=Red]$J$3:$J$16,MATCH([COLOR=Green]D23,$A$3:$A$16,0[/COLOR])[/COLOR])[/COLOR]


===================

I created the pivot table already with unique values based on "Style No.".

The prices are in Sheet "Contract" Range L14:L200 - The range for "Style No." in this sheet is: A14:A200.

The "Pivot" Sheet brought the records which are now shrinked to 56 records which is okay. The "Style No." column range is: A3:A58 -

Can you please help me write the index code to bring the relevant prices in "Pivot" sheet in column range J3:J58 from the "Contract" sheet for matching "Style No.".

Much appreciated!

Thanks in advance.
 

Toadstool

Well-known Member
Joined
Mar 5, 2018
Messages
1,300
Office Version
  1. 2016
Platform
  1. Windows
Add a new column to TABLE 2 to get the unit price.


ABCD
21CartonTotalStyle No.Price from TABLE 1
22
231114406146$2.50
245114406146$2.50
259114406146$2.50
2613114406146$2.50
271114406147$3.50
285114406147$3.50
299114406147$3.50
3013114406147$3.50
311114406148$1.50
325114406148$1.50
331114406149$2.30
345114406149$2.30
351114406150$2.21
365114406150$2.21

<colgroup><col style="width: 25pxpx"><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>
Sheet2 (3)

Worksheet Formulas
CellFormula
D23
=INDEX($J$3:$J$16,MATCH(C23,$A$3:$A$16,0))

<thead>
</thead><tbody>
</tbody>

<tbody>
</tbody>
 

shah0101

New Member
Joined
Jul 4, 2019
Messages
45

ADVERTISEMENT

Add a new column to TABLE 2 to get the unit price.


ABCD
21CartonTotalStyle No.Price from TABLE 1
22
231114406146$2.50
245114406146$2.50
259114406146$2.50
2613114406146$2.50
271114406147$3.50
285114406147$3.50
299114406147$3.50
3013114406147$3.50
311114406148$1.50
325114406148$1.50
331114406149$2.30
345114406149$2.30
351114406150$2.21
365114406150$2.21

<tbody>
</tbody>
Sheet2 (3)

Worksheet Formulas
CellFormula
D23=INDEX($J$3:$J$16,MATCH(C23,$A$3:$A$16,0))

<tbody>
</tbody>

<tbody>
</tbody>





Dear Toadstool,

Thank you soooooooooooooooooooo very much.
 

shah0101

New Member
Joined
Jul 4, 2019
Messages
45
You're welcome!


Hi Toadstool,

Need some more help.

The scenario is still the same but now the prices are in three different worksheets. Can we now have a same index code which we can write for different ranges?

TIA.
 

Toadstool

Well-known Member
Joined
Mar 5, 2018
Messages
1,300
Office Version
  1. 2016
Platform
  1. Windows
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

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><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))))

<thead>
</thead><tbody>
</tbody>

<tbody>
</tbody>
 

Watch MrExcel Video

Forum statistics

Threads
1,122,979
Messages
5,599,156
Members
414,294
Latest member
shariflotfi

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