Data to match

[dinkss]

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.

 

[bebo021999]

There must be way to solve, but, could you upload it in XL2BB?

 

[Peter_SSs]

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:

=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]

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.

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

Hope that helps.

 

[dinkss]

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:

=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]

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.

=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]

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:

=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.

 

[Peter_SSs]

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]

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]

I have attached excel spreadsheet now, hopefully you will be able to help me, and explain they way your code is working?

comparison.xlsm
	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T
1	25mL Architect Natural Alternate	25mL Architect Black	25mL Architect Black Alternate	32mL Architect Natural	32mL Architect Natural Alternate	32mL Architect Black	32mL Architect Black Alternate	60mL Architect Natural	60mL Architect Black	4mL Round	5mL Natural	4mL Amber Glass	5mL Dropper Natural	5mL Dropper White	10ml Dropper Natural	10ml Dropper White	30mL round	42mL Alinity	59mL Alinity
2	990933	LF990935	990935	LF990937	990937	LF990939	990939	938961	G41290	LFG41132	938363	LFG41085	938731	938723	938671	938688	938677	LFG41268	LFG41535
3	1P37G0001	1P37H0001	1P37H0001	1P37G0005	1P37G0005	1P37H0005	1P37H0005	1P74X0001	5P02J0005	09P2920	100010001	2K98B0004	1P37A0001	1P7407P01	1P37L0001	1P74L0001	1P74S0001	4Z21G0001	6R90J01
4	1P74G0001	1P74J0001	1P74J0001	1P74G0005	1P74G0005	1P74H0001	1P74H0001	1P74X0002		09P2930	100020001	2K98C0004	1P37B0001	1P7407W01	1P37M0001	1P74M0001	3P21V0001	4Z21G0005	6R90J02
5	1P74G0002	1P74J0002	1P74J0002	1P74G0006	1P74G0006	1P74H0002	1P74H0002	1P74X0003		4Z21A0001	100030001	2K98D0004	1P37C0001	1P74A0001	1P37N0001	1P74N0001	8P14S0001	4Z21H0001	6R90J05
6	1P74U0001	2K42H0001	2K42H0001	1P74U0005	1P74U0005	1P74H0003	1P74H0003	1P74X0004		4Z21B0001	100040001	2K98E0004	1P37D0001	1P74B0001	2P33L0001	2R98L0001	8P15V0001	4Z21H0005	6R90J06
7	1P74U0002	2P33H0001	2P33H0001	1P74U0006	1P74U0006	1P74H0004	1P74H0004	6R86J05		4Z21C0001	100050001	2K98F0004	1P37E0001	1P74C0001	2P33M0001	2R98M0001		6R90G01	7P48J0001
8	2K42G0001	2P40H0001	2P40H0001	1P74Y0001	1P74Y0001	1P74J0005	1P74J0005	7K61J0005		4Z21D0001	100060001	2K98L0004	1P37F0001	1P74D0001	2P33N0001	3P2501P01		6R90G02	7P48J0006
9	2P33G0001	2R98H0001	2R98H0001	1P74Y0002	1P74Y0002	1P74J0006	1P74J0006	7K61J0007		4Z21E0001	100130001	2K98N0004	2K42A0001	1P74E0001	7K59L0001	3P2501R01		6R90G05	7P50U0001
10	2P40G0001	3P25H0001	3P25H0001	1P74Y0005	1P74Y0005	2K42H0005	2K42H0005	7K61U0005		4Z21F0001	100140001	8P04B0004	2P33A0001	1P74F0001	7K59M0001	3P2501S01		6R90G06	7P50U0002
11	2R98G0001	3P25H0002	3P25H0002	1P74Y0006	1P74Y0006	2P33H0005	2P33H0005	7K61U0007		5P02A0001	100150001	8P04C0004	2P33B0001	255990001	7K59N0001	3P2502P01		6R90H01	7P50U0005
12	3P25G0001	3P25H0003	3P25H0003	2K42G0005	2K42G0005	2P40H0005	2P40H0005	7K62J0005		5P02B0001	113010001	8P04D0004	2P33C0001	255990002	7K62L0001	3P2502R01		6R90H02	7P50U0006
PRODUCT & BOTTLE TYPE

comparison.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U
1	Product	25mL Architect Natural	25mL Architect Natural Alternate	25mL Architect Black	25mL Architect Black Alternate	32mL Architect Natural	32mL Architect Natural Alternate	32mL Architect Black	32mL Architect Black Alternate	60mL Architect Natural	60mL Architect Black	4mL Round	5mL Natural	4mL Amber Glass	5mL Dropper Natural	5mL Dropper White	10ml Dropper Natural	10ml Dropper White	30mL round	42mL Alinity	59mL Alinity
2	Material Number	LF990933	990933	LF990935	990935	LF990937	990937	LF990939	990939	938961	G41290	LFG41132	938363	LFG41085	938731	938723	938671	938688	938677	LFG41268	LFG41535
3	2R98A0001
4	2R98B0001
5	2R98C0001
6	2R98D0001
7	2R98E0001
8	2R98F0001
9	2R98G0001
10	2R98G0005
11	2R98H0001
12	2R98H0005
13	2R98L0001
14	2R98M0001
15	3P25A0002
16	3P25B0002
17	3P25C0002
18	3P25D0002
19	3P25E0002
20	3P25F0002
21	3P25G0002
22	3P25G0003
23	3P25G0005
24	3P25G0006
25	3P25H0002
26	3P25H0003
27	3P25H0005
28	3P25H0006
29	3P25L0001
30	3P25L0002
31	3P25M0001
32	3P25M0002
33	3P25N0001
MASTER-MATERIAL

comparison.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U
585	9P29M0001
586	9P29N0001
587	1P37G0001	YES	YES
588	1P37G0005					YES	YES
589	1P37H0001			YES	YES
590	1P37H0005							YES	YES
591	2P33G0001
592	2P33G0005
593	2P33H0001
MASTER-MATERIAL