Match data

[howard]

I have a heading called asset type in column A and beneath this I have the various asset types for eg Corporate signage, Furniture and Fittings etc, In col H I have a heading called Departments and below this the varios departments for eg Administration, Parts etc and in Column P I have a heading Profit Code. These are all on sheet 1

On sheet 3, I have the Asset Type Dept Code Split

Under asset type I will have for eg Corporate signage
under Dept , I will have for Eg Administration
under code I will have for eg 191475
Under Split i will have for eg 100

I need a code in column P on sheeet 1 that where the asset type and department on sheet 1 and 3 match, that the code and
for Eg Sheet 1 where the asset type is say Corporate signage and the department is Administration and it matches the asset type & department on sheet 3, the under profit code in Col P , the following must appear 191475= 100

See eaxmple of what sheet 3 looks like


Your assistance in this regard will be most appreciated

Excel Workbook
	A	B	C	D
1	Asset Type	Dept	Dep Code	Split
2	Corporate Signage	Administration	197295	100
3	Corporate Signage	New Vehicles	198123	100
4	Furniture and Fittings	Used vehicles	195125	100
Dep Expenses Codes

 

[dodger7]

Will there always only be one or zero matches or can there be multiple matches? Is there any scope to concatenate your raw data in say a hidden column?

Heres one not so elaborate formula that will work if there is only 1 match, however if theres multiple it will return incorrect data:

=SUMPRODUCT(--(Sheet3!A2:A100=Sheet1!A2),--(Sheet3!B2:B100=Sheet1!H2),Sheet3!C2:C100)&"="&SUMPRODUCT(--(Sheet3!A2:A100=Sheet1!A2),--(Sheet3!B2:B100=Sheet1!H2),Sheet3!D2:D100)

 

[howard]

Thanks for the help. The formula for items in A2 and H2 on sheet 1 P2 returns 0= 0 it should return 197295 = 100

See sample data for sheet 1 Col A & H

Your assistance in resolving this will be most appreciated

Excel Workbook
	A	H
1	Asset Type	Department
2	Corporate Signage	ADMINISTRATION
3	Corporate Signage	ADMINISTRATION
4	Corporate Signage	ADMINISTRATION
5	Corporate Signage	ADMINISTRATION
6	Furniture and Fittings	ADMINISTRATION
7	Furniture and Fittings	ADMINISTRATION
8	Furniture and Fittings	ADMINISTRATION
9	Furniture and Fittings	ADMINISTRATION
10	Furniture and Fittings	ADMINISTRATION
11	Furniture and Fittings	ADMINISTRATION
12	Furniture and Fittings	ADMINISTRATION
13	Furniture and Fittings	ADMINISTRATION
14	Furniture and Fittings	ADMINISTRATION
Sheet1

 

[dodger7]

Hi Howard,

It works for me. Maybe make sure you don't have any extra spaces in your data?

p.s. if you have more than 100 records in sheet3 make sure you adjust the formula accordingly.

 

[howard]

Hi Dodger7

I found where my problem was. I had changed the sheet names. It now works perfectly except for asset code Plant & Machinery

See examples below

Sheet1 now names Fassets and sheet3 now names codes

It would be appreciated if you would check & advise. If necessary send me your email address via PM & I will attached my workbook

Excel Workbook
	A	H
378	Plant & Machinery	Service
379	Plant & Machinery	Service
380	Plant & Machinery	Service
381	Plant & Machinery	Service
FASSETS

Excel Workbook
	A	B	C	D
1	Asset Type	Dept	Dep Code	Split
2	Corporate Signage	Administration	197295	100
3	Furniture and Fittings	New Vehicles	137300	100
4	Furniture and Fittings	Used vehicles	157300	100
5	Furniture and Fittings	Service	167300	100
6	Furniture and Fittings	Parts	177300	100
7	Furniture and Fittings	Administration	197300	100
8	Generator	Administration	197305	100
9	Office Equipment	Administration	197310	100
10	Office Equipment	New Vehicles	137310	100
11	Office Equipment	Used vehicles	157310	100
12	Office Equipment	Service	167310	100
13	Office Equipment	Parts	177310	100
14	Plant & Machinery	Service	167315	100
Codes

 

[howard]

Hi Dodger7

I found where my problem was. I had changed the sheet names. It now works perfectly except for asset code Plant & Machinery

See examples below

Sheet1 now names Fassets and sheet3 now names codes

It would be appreciated if you would check & advise. If necessary send me your email address via PM & I will attached my workbook

Excel Workbook
	A	H
378	Plant & Machinery	Service
379	Plant & Machinery	Service
380	Plant & Machinery	Service
381	Plant & Machinery	Service
FASSETS

Excel Workbook
	A	B	C	D
1	Asset Type	Dept	Dep Code	Split
2	Corporate Signage	Administration	197295	100
3	Furniture and Fittings	New Vehicles	137300	100
4	Furniture and Fittings	Used vehicles	157300	100
5	Furniture and Fittings	Service	167300	100
6	Furniture and Fittings	Parts	177300	100
7	Furniture and Fittings	Administration	197300	100
8	Generator	Administration	197305	100
9	Office Equipment	Administration	197310	100
10	Office Equipment	New Vehicles	137310	100
11	Office Equipment	Used vehicles	157310	100
12	Office Equipment	Service	167310	100
13	Office Equipment	Parts	177310	100
14	Plant & Machinery	Service	167315	100
Codes

 

[dodger7]

PM sent.

 

[howard]

Hi Jamie

Problem resolved, Excel playing up. I closed the workbook & re-opened and the formula gave me the correct answer

Have a great day

Regards

Howard