Data to match

dinkss

Board Regular
Joined
Aug 25, 2020
Messages
94
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
Hi there,

I have created 2 worksheets with data. First is all materials used and second has the bottle type used for each of material. How can I match it? I mean - how to assign data from bottle types to material? Is there any formula like vlookup or anything else? I have no idea how to start :( Can you help me please?

I just want to get YES / NO to show in the rows with material number which would indicate what bottle type is used for which product.
 

Attachments

  • 01.png
    01.png
    52 KB · Views: 15
  • 02.png
    02.png
    95.5 KB · Views: 13

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
52,274
Office Version
  1. 365
Platform
  1. Windows
I cannot see how you got that particular "Yes" and "No" in your images, but my best guess is for cell B2 in 'MASTER-MATERIAL', and copied across and down after adjusting ranges if required:

Excel Formula:
=IF(ISNUMBER(MATCH($A2,FILTER('PRODUCT & BOTTLE TYPE'!$C$3:$Z$100,'PRODUCT & BOTTLE TYPE'!$C$2:$Z$2=B$1,""),0)),"Yes","No")
 

Snakehips

Well-known Member
Joined
May 17, 2009
Messages
5,395
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
If your bottle types are ordered the same in both sheets but just offset by one column, as they appear to be, then maybe just like below.
Formula in B2 and drag down and accross.

Excel Formula:
=IF(COUNTIF('PRODUCT & BOTTLE TYPE'!$A$3:$A$200,$A2)>0,"Yes","No")

Hope that helps.
 

dinkss

Board Regular
Joined
Aug 25, 2020
Messages
94
Office Version
  1. 365
  2. 2016
Platform
  1. Windows

ADVERTISEMENT

I cannot see how you got that particular "Yes" and "No" in your images, but my best guess is for cell B2 in 'MASTER-MATERIAL', and copied across and down after adjusting ranges if required:

Excel Formula:
=IF(ISNUMBER(MATCH($A2,FILTER('PRODUCT & BOTTLE TYPE'!$C$3:$Z$100,'PRODUCT & BOTTLE TYPE'!$C$2:$Z$2=B$1,""),0)),"Yes","No")
I will try now and let you know, thank you. I'm only new to this so don't really know how to start and adjust anything.
 

dinkss

Board Regular
Joined
Aug 25, 2020
Messages
94
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
It
If your bottle types are ordered the same in both sheets but just offset by one column, as they appear to be, then maybe just like below.
Formula in B2 and drag down and accross.

Excel Formula:
=IF(COUNTIF('PRODUCT & BOTTLE TYPE'!$A$3:$A$200,$A2)>0,"Yes","No")

Hope that helps.
Doesn't work for me as formula changes for each cell and not looking for full information in table of Product & bottle type?
 

dinkss

Board Regular
Joined
Aug 25, 2020
Messages
94
Office Version
  1. 365
  2. 2016
Platform
  1. Windows

ADVERTISEMENT

I cannot see how you got that particular "Yes" and "No" in your images, but my best guess is for cell B2 in 'MASTER-MATERIAL', and copied across and down after adjusting ranges if required:

Excel Formula:
=IF(ISNUMBER(MATCH($A2,FILTER('PRODUCT & BOTTLE TYPE'!$C$3:$Z$100,'PRODUCT & BOTTLE TYPE'!$C$2:$Z$2=B$1,""),0)),"Yes","No")
I did try your formula but it gives me only NO answer for all products :( Is there something that I am missing? If it is too complicated I don't need YES / NO, it will be enough if material type will be displayed beside/ under each of the columns. See screenshot attached.
 

Attachments

  • 03.png
    03.png
    73.4 KB · Views: 6
  • 04.png
    04.png
    12.8 KB · Views: 7

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
52,274
Office Version
  1. 365
Platform
  1. Windows
Is there something that I am missing?
Mainly that you have not given us some simple sample data, the expected results and how you get those results manually.

Can you make up a small set of dummy data for each sheet, enter the results (or at least a few of them) manually into MASTER-MATERIAL, post both sheets with XL2BB (that way we can copy paste to test which we cannot do from a picture) and explain how you got those entered results manually?
 

dinkss

Board Regular
Joined
Aug 25, 2020
Messages
94
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
Mainly that you have not given us some simple sample data, the expected results and how you get those results manually.

Can you make up a small set of dummy data for each sheet, enter the results (or at least a few of them) manually into MASTER-MATERIAL, post both sheets with XL2BB (that way we can copy paste to test which we cannot do from a picture) and explain how you got those entered results manually?
OK, will try that, I will install all and will post worksheet :) thank you for your help :)
 

dinkss

Board Regular
Joined
Aug 25, 2020
Messages
94
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
I have attached excel spreadsheet now, hopefully you will be able to help me, and explain they way your code is working?

comparison.xlsm
BCDEFGHIJKLMNOPQRST
125mL Architect Natural Alternate25mL Architect Black25mL Architect Black Alternate32mL Architect Natural32mL Architect Natural Alternate32mL Architect Black32mL Architect Black Alternate60mL Architect Natural60mL Architect Black4mL Round5mL Natural4mL Amber Glass5mL Dropper Natural5mL Dropper White10ml Dropper Natural10ml Dropper White30mL round42mL Alinity59mL Alinity
2990933LF990935990935LF990937990937LF990939990939938961G41290LFG41132938363LFG41085938731938723938671938688938677LFG41268LFG41535
31P37G00011P37H00011P37H00011P37G00051P37G00051P37H00051P37H00051P74X00015P02J000509P29201000100012K98B00041P37A00011P7407P011P37L00011P74L00011P74S00014Z21G00016R90J01
41P74G00011P74J00011P74J00011P74G00051P74G00051P74H00011P74H00011P74X000209P29301000200012K98C00041P37B00011P7407W011P37M00011P74M00013P21V00014Z21G00056R90J02
51P74G00021P74J00021P74J00021P74G00061P74G00061P74H00021P74H00021P74X00034Z21A00011000300012K98D00041P37C00011P74A00011P37N00011P74N00018P14S00014Z21H00016R90J05
61P74U00012K42H00012K42H00011P74U00051P74U00051P74H00031P74H00031P74X00044Z21B00011000400012K98E00041P37D00011P74B00012P33L00012R98L00018P15V00014Z21H00056R90J06
71P74U00022P33H00012P33H00011P74U00061P74U00061P74H00041P74H00046R86J054Z21C00011000500012K98F00041P37E00011P74C00012P33M00012R98M00016R90G017P48J0001
82K42G00012P40H00012P40H00011P74Y00011P74Y00011P74J00051P74J00057K61J00054Z21D00011000600012K98L00041P37F00011P74D00012P33N00013P2501P016R90G027P48J0006
92P33G00012R98H00012R98H00011P74Y00021P74Y00021P74J00061P74J00067K61J00074Z21E00011001300012K98N00042K42A00011P74E00017K59L00013P2501R016R90G057P50U0001
102P40G00013P25H00013P25H00011P74Y00051P74Y00052K42H00052K42H00057K61U00054Z21F00011001400018P04B00042P33A00011P74F00017K59M00013P2501S016R90G067P50U0002
112R98G00013P25H00023P25H00021P74Y00061P74Y00062P33H00052P33H00057K61U00075P02A00011001500018P04C00042P33B00012559900017K59N00013P2502P016R90H017P50U0005
123P25G00013P25H00033P25H00032K42G00052K42G00052P40H00052P40H00057K62J00055P02B00011130100018P04D00042P33C00012559900027K62L00013P2502R016R90H027P50U0006
PRODUCT & BOTTLE TYPE


comparison.xlsm
ABCDEFGHIJKLMNOPQRSTU
1Product25mL Architect Natural25mL Architect Natural Alternate25mL Architect Black25mL Architect Black Alternate32mL Architect Natural32mL Architect Natural Alternate32mL Architect Black32mL Architect Black Alternate60mL Architect Natural60mL Architect Black4mL Round5mL Natural4mL Amber Glass5mL Dropper Natural5mL Dropper White10ml Dropper Natural10ml Dropper White30mL round42mL Alinity59mL Alinity
2Material NumberLF990933990933LF990935990935LF990937990937LF990939990939938961G41290LFG41132938363LFG41085938731938723938671938688938677LFG41268LFG41535
32R98A0001
42R98B0001
52R98C0001
62R98D0001
72R98E0001
82R98F0001
92R98G0001
102R98G0005
112R98H0001
122R98H0005
132R98L0001
142R98M0001
153P25A0002
163P25B0002
173P25C0002
183P25D0002
193P25E0002
203P25F0002
213P25G0002
223P25G0003
233P25G0005
243P25G0006
253P25H0002
263P25H0003
273P25H0005
283P25H0006
293P25L0001
303P25L0002
313P25M0001
323P25M0002
333P25N0001
MASTER-MATERIAL




comparison.xlsm
ABCDEFGHIJKLMNOPQRSTU
5859P29M0001
5869P29N0001
5871P37G0001YESYES
5881P37G0005YESYES
5891P37H0001YESYES
5901P37H0005YESYES
5912P33G0001
5922P33G0005
5932P33H0001
MASTER-MATERIAL
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,151,957
Messages
5,767,308
Members
425,403
Latest member
MellieD

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