Comparing Data

sunstreaks

New Member
Joined
Jun 28, 2012
Messages
2
Hi everyone,

Im have serious issues!!

I have a data sheet and am trying to compare/combine columns.

Basically i have a list of about 10,000 address'.

One cell lists house number, street, town and postcode.

I have another data set which within one cell lists postcode, ward and District Council.

What im try to do is run a formula that would match the postcodes. So for every record i would be able to see which ward and district council relates to that record based on postcode?

Does that make sense?

Hope so!

Thanks in advance
 

Some videos you may like

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes

Robert Mika

MrExcel MVP
Joined
Jun 29, 2009
Messages
7,256
Coudl you post your sample?
You can download and install two of the following programs:

HTLMaker

or
Excel Jeanie


or when using Internet Explorer just put borders around your data in Excel and copy those cells into your post.
 

sunstreaks

New Member
Joined
Jun 28, 2012
Messages
2
Yep, For Example

A1 - 12, Broadway, Jaywick, Essex, CO15 2EX

B1 - CO15 2EX, Golf Green Ward, Tendring District Council

What I need is:

C1 - 12, Broadway, Jaywick, Essex, CO15 2EX, Golf Green Ward, Tendring District Council

I cant really give you a live example because its confidential data

Thanks
 

Robert Mika

MrExcel MVP
Joined
Jun 29, 2009
Messages
7,256
Yep, For Example

A1 - 12, Broadway, Jaywick, Essex, CO15 2EX

B1 - CO15 2EX, Golf Green Ward, Tendring District Council

What I need is:

C1 - 12, Broadway, Jaywick, Essex, CO15 2EX, Golf Green Ward, Tendring District Council

I cant really give you a live example because its confidential data

Thanks
I understand that.
Is the pattern going to be always the same and can you have more than match?
So the let say the postcode in one data set is shown more than once?
With your one example:
Sheet1

*ABCDE
112, Broadway, Jaywick, Essex, CO15 2EXCO15 2EXCO15 2EX, Golf Green Ward, Tendring District CouncilCO15 2EX12, Broadway, Jaywick, Essex, CO15 2EX, Golf Green Ward, Tendring District Council

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:259px;"><col style="width:81px;"><col style="width:367px;"><col style="width:98px;"><col style="width:550px;"></colgroup><tbody>
</tbody>

Spreadsheet Formulas
CellFormula
B1=TRIM(RIGHT(SUBSTITUTE(TRIM(A1),",",REPT(" ",99)),99))
D1=TRIM(LEFT(SUBSTITUTE(TRIM(C1),",",REPT(" ",99)),99))
E1=INDEX($A$1:$A$16,MATCH(D1,$B$1:$B$16,0))&SUBSTITUTE(C1,D1,"")

<tbody>
</tbody>

<tbody>
</tbody>


Excel tables to the web >> Excel Jeanie HTML 4
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,123,473
Messages
5,601,863
Members
414,479
Latest member
Beau the dog

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