Formulating word formulas for excel

[jen0919]

http://www.ozgrid.com/forum/showthread.php?t=201635
http://www.mrexcel.com/forum/excel-q...help-asap.html

Hi everyone, I have a problem that I hope someone can help me with. I have a list of postcodes in one sheet and these are assigned an area in the next column.

e.g. 2000 Sydney
2060 North Sydney


I then have another sheet of data with just postcodes (lots of them). I want to use the data from the first sheet to populate the adjacent column in the new sheet.

i.e. 2000 = some formula to look up the value in the column next to the postcode in the first spreadsheet
2060 = some formula to look up the value in the column next to the postcode in the first spreadsheet

The result being:

2000 Sydney
2060 North Sydney

How could i do this?

[AliGW]

Maybe like this in B1 of the second sheet?

=VLOOKUP(A1,Sheet1!$A$1:$B$500,2,0)

where A1 contains the postcode to be looked up and the array is where your list of codes and regions is to be found.

[Shareez Saleem]

Can you post a sample worksheet?

I think you can use =VLOOKUP(A2,Sheet1!$A$2:$B$3,2,0)

[sandy666]

Read about VLOOKUP or INDEX / MATCH

[jen0919]

I'll try this and let you know. Thank you!

[AliGW]

Your post does not comply with Rule 8 of our Forum RULES. Do not crosspost your question on multiple forums without including links here to the other threads on other forums.

Cross-posting is when you post the same question in other forums on the web. The last thing you want to do is waste people's time working on an issue you have already resolved elsewhere. We prefer that you not cross-post at all, but if you do (and it's unlikely to go unnoticed), you MUST provide a link (copy the url from the address bar in your browser) to the cross-post.

Please add a link in your opening post in this thread: this is not optional. Thanks!

Read this to understand why we ask you to do this, and then please edit your first post to include links to any and all cross-posts in any other forums (not just this site).

[cytop]

And please update your thread on Ozgrid for this issue.

[jen0919]

Thank you for the information

[jen0919]

Thanks Ali. Will try it out

[AliGW]

Please add links to your posts on the other forums where you have asked the same question. This is not optional. Thanks!

[AliGW]

Thank you for adding the link, but you also need to add a link to the third forum where you have asked the same question.

[macropod]

I have a list of postcodes in one sheet and these are assigned an area in the next column.

e.g. 2000 Sydney
2060 North Sydney


I then have another sheet of data with just postcodes (lots of them). I want to use the data from the first sheet to populate the adjacent column in the new sheet.

i.e. 2000 = some formula to look up the value in the column next to the postcode in the first spreadsheet
2060 = some formula to look up the value in the column next to the postcode in the first spreadsheet

The result being:

2000 Sydney
2060 North Sydney

Given that there are multiple localities per postcode - and that those localities can even span state boundaries, I suspect you'll need something rather more elaborate than a single lookup result. Depending on what you're trying to achieve, you may need to use Dependent Dropdowns and/or Matching with Repeated Data. For examples of these, see-

Dependent Dropdowns:
http://windowssecrets.com/forums/sho...474#post861474
http://www.eileenslounge.com/viewtopic.php?f=27&t=8830

Matching with Repeated Data:
http://windowssecrets.com/forums/sho...l=1#post734296
http://www.techsupportforum.com/foru...ml#post2567119

[AliGW]

Thank you for adding the other link.

[jen0919]

Ali, this formula isn't working. It gives me n/a as a result.

[jen0919]

thank you for the info

[AliGW]

Can you attach a sample workbook?

1. Make sure that your sample data are REPRESENTATIVE of your real data. The use of unrepresentative data is very frustrating and can lead to long delays in reaching a solution.

2. Make sure that your desired solution is also shown (mock up the results manually).

3. Make sure that all confidential data is removed or replaced with dummy data first (e.g. names, addresses, E-mails, etc.).

4. Try to avoid using merged cells as they cause lots of problems.

Unfortunately the attachment icon doesn't work at the moment, so to attach an Excel file you have to do the following: just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.

Please pay particular attention to point 2 (above): without an idea of your intended outcomes, it is often very difficult to offer appropriate advice.

[jen0919]

thank you Ali. Will get back to you on the worksheet.

[jen0919]

Hi again Ali,

Attached is our sample spread sheet. I hope it helps you help us. Thank you!

[Shareez Saleem]

=IFERROR(VLOOKUP(A2,Sheet1!$A$2:$B$5,2,0),"")
Us this formula in B2

[jen0919]

thank you.

this formula worked! But can you tell me how to formulate it? because aside from the postcode, we also want to put in other data/information.

[AliGW]

The formula is essentially the same on I gave you, just adapted to fit your data!

It looks at cell A2, and then searches the value in the first column of the array on Sheet 1 (A2 to A5). When it finds it, it returns what it finds on the same row in the second column (B2 to B5) - that's the 2 in the formula. The zero at the end makes it look for an exact match for the lookup value. The IFERROR statement makes the formula return a blank if there is an error, and you shouldn't really and it until you have finished adapting and testing the formula to suit your needs.

Here's an example adapted for an extra column of data:

Excel 2016 (Windows) 32 bit

	A	B	C
1	Postcode	Territory
2	3056	Brunswick	House
3	3057	Brunswick East	Farm
4	3055	Brunswick West	Cottage
5	3058	Coburg	Bungalow

Sheet: Sheet1

Excel 2016 (Windows) 32 bit

	A	B	C
1	Postcode	Territory
2	3056	Brunswick	House

Sheet: Sheet2

Excel 2016 (Windows) 32 bit

	A	B	C
1	Postcode	Territory
2	3056	=IFERROR(VLOOKUP(A2,Sheet1!$A$2:$B$5,2,0),"")	=IFERROR(VLOOKUP(A2,Sheet1!$A$2:$C$5,3,0),"")

Sheet: Sheet2

You have to extend the lookup array and then change the column reference.

[jen0919]

thank you for this Ali!


But what I don't get is that where did all the other characters came from? Like the and '? I mean, when will I know when and where to put them in the formula? Thanks.

[AliGW]

I'm sorry - I don't know what you mean.

=IFERROR(your_formula,"")

returns a blank ("") if the formula returns an error. Have you actually tried to adapt the formula? If not, do so, and then come back with specific issues. All you should need to adjust is the lookup array and the column reference. If you are struggling, attach a workbook with more realistic sample data and explain what outcomes you expect - you are being a little bit vague at the moment!

[jen0919]

Hi Ali,

Attached is a new spreadsheet with our concern stated on it. The formula given earlier is not working well as expected. We hope that you can help us. Thank you!



Best,
Jen

[JohnTopley]

With postcode in A2

in B2

=IFERROR(INDEX(Sheet1!$B$2:$B$1000,SMALL(IF(Sheet1!$A$2:$A$1000=Sheet2!$A$2,ROW(Sheet1!$A$2:$A$1000)-ROW($A$2)+1,""),ROWS($A$2:A2))),"")

Enter with Ctrl+Shift+Enter

Drag down column B: this will give all territories for the given postcode

Putting this in column C:

=IFERROR(INDEX(Sheet1!$C$2:$C$1000,SMALL(IF(Sheet1!$A$2:$A$1000=Sheet2!$A$2,ROW(Sheet1!$A$2:$A$1000)-ROW($A$2)+1,""),ROWS($A$2:B2))),"")

Enter with Ctrl+Shift+Enter

will only get state on first postcode which has state in column C

e.g with postcode 6211 you will get state: with 6210 no state.

[AliGW]

Please post the workbook again, but include some expected outcomes, especially where you say there are multiple results for a post code - without expected outcomes we are stabbing in the dark.

Please also explain why only some of the entries on the source sheet have a territory next to them.

=IFERROR(VLOOKUP(A2,Sheet1!$A$2:$B$5,2,0),"")

What this formula is doing is looking at whatever is in cell A2 on Sheet 2, then comparing it to the range A2:A5 on Sheet 1 - if it finds a match, it returns what is next to that match in column B, and if not it returns a blank. You will have to extend the lookup range to match your much larger dataset (e.g. $A$2:$B$500).

EDIT: It looks like John has understood what you want.

[JohnTopley]

@Ali: my interpretation of the requirement!

Looking at the data, a better (?) option might be to search on territory and return all postcode/territory matches.

[JohnTopley]

another option ..

With TERRITORY in A2

in B2

=IFERROR(INDEX(Sheet1!$A$2:$A$1000,SMALL(IF(ISNUMBER(SEARCH($A$2,Sheet1!$B$2:$B$1000)),ROW(Sheet1!$A$2:$A$1000)-ROW($B$2)+1,""),ROWS(A$2:$B2))),"")

in C2

=IFERROR(INDEX(Sheet1!$B$2:$B$1000,SMALL(IF(ISNUMBER(SEARCH($A$2,Sheet1!$B$2:$B$1000)),ROW(Sheet1!$A$2:$A$1000)-ROW($B$2)+1,""),ROWS($B$2:B2))),"")

in D2

=VLOOKUP($B$2,Sheet1!$A$2:$C$1000,3,0)

[jen0919]

Thank you for all the information. Will get back to you to let you know how things went!


By the way AliG, the 3rd column is almost irrelevant. We just placed it there to indicate the state, because we also have QLD, NSW, etc. and when we're done transferring all the data, we will be merging them into one final sheet and use the forumla there.





Best,
Jen

[jen0919]

Oh, one more question. What about if we have different territories for one post code? like for Mandurah: Dawesville and Mandurah: Bouvard? Is there a way to specify which one we would like to appear in case the address is in Dawesville and not in Bouvard?

Thank you very much

[JohnTopley]

Not without specifying "Mandurah: Dawesville" or "Mandurah: Bouvard" as the search in A2

[jen0919]

I see. Thanks for the information

[jen0919]

Hi John, what about if we make all possible matches appear when the postcode is typed in, is that possible? Say for example, if I type in 5501 Mandurah Dawesville and Mandurah Bouvard would appear instead of just one. In that way, we can just choose the right territory and delete the others. Thanks in advance

[JohnTopley]

For the first solution I gave if you type in 6210 ALL territories with that postcode are listed: so I don't understand your last post re 5501..

[jen0919]

Oh, i see. We are actually using this formula:=IFERROR(VLOOKUP(A2,Sheet1!$A$2:$B$550,2,0),""), because for some reason the formula that you've provided earlier isn't working.

[jen0919]

=IFERROR(INDEX(Sheet1!$B$2:$B$1000,SMALL(IF(Sheet1!$A$2:$A$1000=Sheet2!$A$2,ROW(Sheet1!$A$2:$A$1000)-ROW($A$2)+1,""),ROWS($A$2:A2))),"") ---- this one isn't working... sorry.

[macropod]

If you used the 'Dependent Dropdowns' approach demonstrated in the links I posted way back in post #12 in this thread, you could get a choice of all localities for a given postcode quite easily - once you've compiled the data. Since you appear to be working with Australian postcode data but your profile indicates you're in the UK, I'd suggest asking the Australian High Commission in London whether they'd be willing to give you a copy of the 'List of Streets and Localities' (aka EF054) produced by the Australian Electoral Commission for the last federal election - assuming they have a spare copy. Every postcode's scope is exhaustively detailed in that book. FWIW, it used to be produced as a Word document as a precursor to printing; I don't know if it still is.

[JohnTopley]

Did you enter with Ctrl+Shift+Enter?

ALL formulas work: they are tested before I post.

See sheet2 and Sheet3

[jen0919]

Yes, but just to confirm, do you mean and i press them altogether? or press CTRL then shift then Enter simultaneously?

[JohnTopley]

Hold down Ctrl and Shift together then hit Enter.

If this is done correctly you will see brackets like these {......} appear round the formula. You can then drag the formula down the column.

[jen0919]

Hi again,

We've tried out your instructions but it really doesn't work on our end. To make ourselves clearer with what we are trying to achieve, I've attached a sample worksheet that has more data in it with our goal stated on Sheet 2. Hopefully, this can help you help us achieve our goal. Thank you very much!


Best,
Jen

[jen0919]

As an additional info, we also thought about doing it the other way around. Instead of looking for the territory, we'll look for the postcodes. But the problem is, most of our clients only provide their postcodes and state. So we still have to look up their territories.

[JohnTopley]

What about changing thr ranges??!!!


=IFERROR(INDEX(Sheet1!$B$2:$B$4000,SMALL(IF(Sheet1!$A$2:$A$4000=$A$2,ROW(Sheet1!$A$2:$A$4000)-ROW($A$2)+1,""),ROWS($A$2:A2))),"")

and 5501 is not Mandurah!

[JohnTopley]

Try changing the range ..

=IFERROR(INDEX(Sheet1!$B$2:$B$4000,SMALL(IF(Sheet1!$A$2:$A$4000=$A$2,ROW(Sheet1!$A$2:$A$4000)-ROW($A$2)+1,""),ROWS($A$2:A2))),"")