Comparing Data

[sunstreaks]

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

 

[Robert Mika]

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]

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]

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

*	A	B	C	D	E
1	12, Broadway, Jaywick, Essex, CO15 2EX	CO15 2EX	CO15 2EX, Golf Green Ward, Tendring District Council	CO15 2EX	12, 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

Cell Formula 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