Data Comparison

[ExcelBeginner34]

Hi, hoping for some help. I have a spreadsheet which holds data for a number of buildings that my company owns. Each building has a unique reference number. I have a list of the areas (sq m) of each building and i want to compare this with another list which has updated areas. I then want to copy the updated area from the second list and add this to a new column to the right of the old area so i can compare the differences. The problem is that the lists are not in the same order. I have sorted them but one list has some items that the other doesn't so it isn't quite in sync. The format of the spreadsheet is as follows:

A = the unique building number from the first list
J= the area from 2014
K = the blank column which i want to paste the 2020 area into
AG = the unique building number from the second list
AS = the area from 2020

So what I want to be able to do is scan down column A and AG to find the numbers that match and if they do, copy and paste the area in AS on the same row and paste that into column K.

Hope that makes sense.

 

[Peter_SSs]

First, I suggest that you update your Account details (click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version.

See if this does what you want

20 04 08.xlsm
	A									J	K																						AG												AS
1	Building No.									2014 Area	2020 Area																						Building No.												2020 Area
2	BN1									1740	1750																						BN5												1180
3	BN2									1003	Not found																						BN3												1376
4	BN3									1376	1376																						BN25												2000
5	BN4									1080	1050																						BN4												1050
6	BN5									1176	1180																						BN1												1750
7										`																							BN6												1500
8
Lookup Building No

Cell Formulas
Range	Formula
K2:K8	K2	=IFERROR(IF(A2="","",INDEX(AS$2:AS$1000,MATCH(A2,AG$2:AG$1000,0))),"Not found")

 

[ExcelBeginner34]

Thanks, Peter. I have tried that but had to adjust slightly because my data starts on row 6. So i have just switched the A2 for A6 and AS2 for AS6 etc. All the rows are just coming up as not found. Should there be a space between each of the " after A2 equals?

Best regards

 

[Peter_SSs]

Should there be a space between each of the " after A2 equals?

No

20 04 08.xlsm
	A									J	K																						AG												AS
5	Building No.									2014 Area	2020 Area																						Building No.												2020 Area
6	BN1									1740	1750																						BN5												1180
7	BN2									1003	Not found																						BN3												1376
8	BN3									1376	1376																						BN25												2000
9	BN4									1080	1050																						BN4												1050
10	BN5									1176	1180																						BN1												1750
11										`																							BN6												1500
12
Lookup Building No

Cell Formulas
Range	Formula
K6:K12	K6	=IFERROR(IF(A6="","",INDEX(AS$6:AS$1004,MATCH(A6,AG$6:AG$1004,0))),"Not found")

If it is still not working then perhaps you should post some sample data with XL2BB as I have, then we can see what your data is really like.

This is still true:

I suggest that you update your Account details (click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version.

 

[ExcelBeginner34]

Sorry i can't seem to find the add in for XL2BB - below is a paste from Excel. of my current set up. Will update account now thanks.

A	E	J	K						AG	AS
Building Asset Code	Year Built		2014 Gross Internal Area (m2)	2020 Gross Internal Area (m2)					Building Asset Code	2020 Gross Internal Area (m2)
100	2005		1,168​	Not Found					100	1,237.80
101	1887		279​	Not Found					101	291.98
102	1902		2,608​	Not Found					102	14,810.35
103	1902		2,896​	Not Found					103	3,168.48
104	1870		25,698​	Not Found					104	29,128.15
105	1886		1,567​	Not Found					105	1,660.90
106	1973		83​	Not Found					106	88.31
107	1870		536​	Not Found					107	563.99
108	1870		551​	Not Found					108	583.57
109	1870		574​	Not Found					109	608.46
110	1870		540​	Not Found					110	572.23
111	1870		605​	Not Found					111	641.14
112	1870		577​	Not Found					112	611.64
113	1870		542​	Not Found					113	574.07
114	1870		547​	Not Found					114	579.37

 

[Peter_SSs]

Sorry i can't seem to find the add in for XL2BB

Click the link in my signature

then you should see all the installation and use instructions, including a link to the actual download.

In your sheet if you use some blank cells, what do these formulas return?

=ISNUMBER(A9)
=ISNUMBER(AG9)