Matching data on different spreadsheets

[StewartM666]

I have 2 different spreadsheets running to 100,000 rows each, with around 15 columns on both of them. They both show different measurements of performance and i want to bring some of the data across from sheet 2 to sheet 1 where the same event is on both spreadsheets. This is beyond my skills and so i would really appreciate some help.

On both sheets the common data is in columns A/B/C being Date, Person, Place so i need something that checks for identical entries in all 3 fields in same row on both spreadsheets and then if there is a match it grabs data from a column on spreadsheet 2 and puts it in a column on spreadsheet 1.

Please help as doing it manually is proving very slow!

 

[Toadstool]

Hi StewartM666,

I'm going to suggest that maybe you don't want to pull the data from the 2nd sheet if the data you pull matches data already on the first sheet, so I'm giving two formulae: column F on Sheet1 is as per your request but column G on Sheet1 only populate if the "Other Data" is also different.

Here's my second sheet data:

StewartM666.xlsx
	A	B	C	D
1	Date	Person	Place	Other Data
2	1/1/1956	Paddington Bear	Peru	Furry
3	3/3/1912	Hansel Smith	Forest	Breadcrumbs
4	3/3/1912	Gretal Smith	Forest	At Risk
5	4/4/1967	Asterix the Gaul	Brittany	Courageous
6	2/2/1979	Toadstool	San Francisco	Alcatraz
7	9/9/1997	Toadstool	Sao Paulo	Very Hot
8	3/3/2003	Toadstool	San Francisco	Working
Sheet2

Here's my first sheet data and columns F & G formulae:

StewartM666.xlsx
	A	B	C	D	E	F	G
1	Date	Person	Place	Other Data		Pulled Data	Pulled Data if not the same
2	1/1/1956	Paddington Bear	Peru	Furry		Furry
3	3/3/1912	Hansel Smith	Cottage	At Risk
4	3/3/1912	Gretal Smith	Forest	At Risk		At Risk
5	4/4/1967	Asterix the Gaul	Marseilles	Courageous
6	2/2/1979	Toadstool	San Francisco	Vacation		Alcatraz	Alcatraz
7	9/9/1997	Toadstool	Sao Paulo	Hot		Very Hot	Very Hot
8	3/3/2002	Toadstool	San Francisco	Working
Sheet1

Cell Formulas
Range	Formula
F2:F8	F2	=IFERROR(INDEX(Sheet2!$D$2:$D$100000,MATCH(1,INDEX((A2=Sheet2!$A$2:$A$100000)*(B2=Sheet2!$B$2:$B$100000)*(C2=Sheet2!$C$2:$C$100000),0,1),0)),"")
G2:G8	G2	=IFERROR(INDEX(Sheet2!$D$2:$D$100000,MATCH(1,INDEX((A2=Sheet2!$A$2:$A$100000)*(B2=Sheet2!$B$2:$B$100000)*(C2=Sheet2!$C$2:$C$100000)*(D2<>Sheet2!$D$2:$D$100000),0,1),0)),"")

 

[StewartM666]

Hi Toadstool,

Thanks so much. That works perfectly.

Stewart

 

[Toadstool]

You're welcome! Thanks for the feedback.