How to identify list of materials in one column supplied by multiple or single suppliers which data in other column

Venuvip

New Member
Joined
Sep 25, 2020
Messages
3
Office Version
  1. 365
Platform
  1. Windows
One column has thousands of materials list [codes & descriptions] and in other column has corresponding supplier name and lastly third column has purchase value. Here one material could be supplied by single or multiple suppliers.
How to derive one material is supplied by how many suppliers and their corresponding value. pls help.
 

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.

kweaver

Well-known Member
Joined
May 8, 2018
Messages
1,792
Office Version
  1. 365
  2. 2010
Welcome to Mr Excel...it would help everyone who would like to address your issue if you'd post some examples using XL2BB (we don't want to have to retype your info).
 

Venuvip

New Member
Joined
Sep 25, 2020
Messages
3
Office Version
  1. 365
Platform
  1. Windows
Welcome to Mr Excel...it would help everyone who would like to address your issue if you'd post some examples using XL2BB (we don't want to have to retype your info).
Could you pls help what stands for XL2BB here.. however attached snap shot

Material CodeMaterial DescriptionVendorVendor NameValue
111x11111aaaa1,500.00
111x12222bbbb950.00
111x13333cccc1,400.00
222x21111aaaa1,500.00
222x23333cccc1,400.00
333x34444dddd750.00
444x45555eeee550.00
555x54444dddd650.00
666x61111aaaa150.00
777x73333cccc920.00
888x82222bbbb850.00
999x93333cccc1,200.00
111ax1a1111aaaa390.00
111ax1a2222bbbb930.00
111ax1a5555eeee625.00
222ax2a3333cccc1,800.00
333ax3a4444dddd2,500.00
444ax4a1111aaaa3,000.00
555ax5a2222bbbb550.00
666ax6a3333cccc1,100.00
777ax7a5555eeee1,500.00
777ax7a1111aaaa1,300.00
777ax7a4444dddd3,000.00
777ax7a2222bbbb450.00
888ax8a3333cccc1,600.00
999ax9a1111aaaa2,900.00
 

kweaver

Well-known Member
Joined
May 8, 2018
Messages
1,792
Office Version
  1. 365
  2. 2010
Is it anything similar to this?

Book1
ABCDEF
1MaterialsSupplierValueQuery
2ABC4722SUP8$12Material:ABC4240
3ABC1163SUP2$44SUP612
4ABC4240SUP6$12SUP1018
5ABC3551SUP6$36SUP620
6ABC4113SUP4$30SUP344
7ABC4240SUP10$18SUP513
8ABC3878SUP4$38
9ABC4982SUP5$45
10ABC4274SUP5$20
11ABC4274SUP8$29
12ABC4240SUP6$20
13ABC4636SUP10$49
14ABC4240SUP3$44
15ABC4240SUP5$13
16ABC2294SUP2$42
17ABC1212SUP1$44
18ABC1278SUP2$29
19ABC4274SUP3$43
20ABC4881SUP7$46
21ABC4881SUP7$27
Sheet3
Cell Formulas
RangeFormula
E3:F7E3=FILTER(B2:C21,A2:A21=F2)
Dynamic array formulas.
 

kweaver

Well-known Member
Joined
May 8, 2018
Messages
1,792
Office Version
  1. 365
  2. 2010

ADVERTISEMENT

Book1
ABCDEFGHI
1Material CodeMaterial DescriptionVendorVendor NameValue
2111x11111aaaa1,500.00Material:777a
3111x12222bbbb9505555eeee1500
4111x13333cccc1,400.001111aaaa1300
5222x21111aaaa1,500.004444dddd3000
6222x23333cccc1,400.002222bbbb450
7333x34444dddd750
8444x45555eeee550
9555x54444dddd650
10666x61111aaaa150
11777x73333cccc920
12888x82222bbbb850
13999x93333cccc1,200.00
14111ax1a1111aaaa390
15111ax1a2222bbbb930
16111ax1a5555eeee625
17222ax2a3333cccc1,800.00
18333ax3a4444dddd2,500.00
19444ax4a1111aaaa3,000.00
20555ax5a2222bbbb550
21666ax6a3333cccc1,100.00
22777ax7a5555eeee1,500.00
23777ax7a1111aaaa1,300.00
24777ax7a4444dddd3,000.00
25777ax7a2222bbbb450
26888ax8a3333cccc1,600.00
27999ax9a1111aaaa2,900.00
Sheet4
Cell Formulas
RangeFormula
G3:I6G3=FILTER(C1:E27,A1:A27=H2)
Dynamic array formulas.
 

kweaver

Well-known Member
Joined
May 8, 2018
Messages
1,792
Office Version
  1. 365
  2. 2010
 

mehidy1437

Active Member
Joined
Nov 15, 2019
Messages
270
Office Version
  1. 2013
Platform
  1. Windows
  2. Mobile
  3. Web
Another approach,

Venuvip.xlsx
ABCDEFGHI
1Material CodeMaterial DescriptionVendorVendor NameValueDistinct Material CodeMaterial CodeVendorValue
2111x11111aaaa1,500.00111111aaaa1500
3111x12222bbbb950222bbbb950
4111x13333cccc1,400.00333cccc1400
5222x21111aaaa1,500.00444  
6222x23333cccc1,400.00555  
7333x34444dddd750666  
8444x45555eeee550777  
9555x54444dddd650888  
10666x61111aaaa150999  
11777x73333cccc920111a  
12888x82222bbbb850222a  
13999x93333cccc1,200.00333a  
14111ax1a1111aaaa390444a  
15111ax1a2222bbbb930555a  
16111ax1a5555eeee625666a  
17222ax2a3333cccc1,800.00777a  
18333ax3a4444dddd2,500.00888a  
19444ax4a1111aaaa3,000.00999a  
20555ax5a2222bbbb550   
21666ax6a3333cccc1,100.00   
22777ax7a5555eeee1,500.00   
23777ax7a1111aaaa1,300.00   
24777ax7a4444dddd3,000.00   
25777ax7a2222bbbb450   
26888ax8a3333cccc1,600.00   
27999ax9a1111aaaa2,900.00   
Sheet1
Cell Formulas
RangeFormula
H2:H27H2=IFERROR(INDEX($D$2:$D$1100, SMALL(IF($G$2=$A$2:$A$1100, ROW($A$2:$A$1100)-ROW($A$2)+1), ROW(1:1))),"" )
I2:I27I2=IF(SUMIFS(E:E,D:D,H2,A:A,$G$2)=0,"",SUMIFS(E:E,D:D,H2,A:A,$G$2))
F2:F27F2=IFERROR(INDEX($A$2:$A$10000, MATCH(0, COUNTIF($F$1:F1, $A$2:$A$10000&"") + IF($A$2:$A$10000="",1,0), 0)), "")
Press CTRL+SHIFT+ENTER to enter array formulas.
Named Ranges
NameRefers ToCells
_FilterDatabase=Sheet1!$A$1:$E$27I2:I27, H2
Cells with Data Validation
CellAllowCriteria
G2List=$F$2:$F$309
 

padapinto

New Member
Joined
Sep 21, 2020
Messages
42
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
One column has thousands of materials list [codes & descriptions] and in other column has corresponding supplier name and lastly third column has purchase value. Here one material could be supplied by single or multiple suppliers.
How to derive one material is supplied by how many suppliers and their corresponding value. pls help.
How lucky are you, i have done it with the data you provided, it not only gives you the number of suppliers but we can also put a conditional format that highlights the rows involved. see the formulas first and how the data works down there. you select the material code you want to check from my added column then all the rows with material code will show up. the cell you are selecting from has a drop down list i made by highlighting all the data in material code column A, also vendor name cell is same. before making the column for supplier no the last one, i was able to select individual vendor name but that will not bring the result you want because it will only show data for particular vendor. so only change the vendor code and the data will show up plus the total no of suppliers of that item.
THIS IS THE CORRECT FORMULA FOR TOTAL NO OF SUPPLIER. REMEMBER TO CHANGE FROM SUM AS I WROTE BEFORE.
VENDOR.png

MATERIAL CODEVENDOR NAMEAMOUNTNO OF SUPPLIERS
Material CodeMaterial DescriptionVendorVendor NameValue444a
111x11111aaaa1500=IF($F$73=A74,A74,"")=IF(F74=A74,D74,IF(AND(D74=$G$73,F74=""),"",""))=IF(F74=A74,E74,"")=IF(D74=G74,COUNTIF($G$74:$G$99,G74),"")
111x12222bbbb950=IF($F$73=A75,A75,"")=IF(F75=A75,D75,IF(AND(D75=$G$73,F75=""),"",""))=IF(F75=A75,E75,"")=IF(D75=G75,COUNTIF($G$74:$G$99,G75),"")
111x13333cccc1400=IF($F$73=A76,A76,"")=IF(F76=A76,D76,IF(AND(D76=$G$73,F76=""),"",""))=IF(F76=A76,E76,"")=IF(D76=G76,COUNTIF($G$74:$G$99,G76),"")
MATERIAL CODEVENDOR NAMEAMOUNTNO OF SUPPLIERS
Material CodeMaterial DescriptionVendorVendor NameValue444a
111x11111aaaa
1,500.00​
111x12222bbbb
950​
111x13333cccc
1,400.00​
222x21111aaaa
1,500.00​
222x23333cccc
1,400.00​
333x34444dddd
750​
444x45555eeee
550​
555x54444dddd
650​
666x61111aaaa
150​
777x73333cccc
920​
888x82222bbbb
850​
999x93333cccc
1,200.00​
111ax1a1111aaaa
390​
111ax1a2222bbbb
930​
111ax1a5555eeee
625​
222ax2a3333cccc
1,800.00​
333ax3a4444dddd
2,500.00​
444ax4a1111aaaa
3,000.00​
444aaaaa3,000.001
555ax5a2222bbbb
550​
666ax6a3333cccc
1,100.00​
777ax7a5555eeee
1,500.00​
777ax7a1111aaaa
1,300.00​
777ax7a4444dddd
3,000.00​
777ax7a2222bbbb
450​
888ax8a3333cccc
1,600.00​
999ax9a1111aaaa
2,900.00​
TOTALS SUPPLIERS1
 

Watch MrExcel Video

Forum statistics

Threads
1,130,191
Messages
5,640,755
Members
417,165
Latest member
Hilders1

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