help

[Aidan]

help - might be lookup but not sure

I have two sheets

In sheet 1 in column B I have a list of local councils in Scotland - column c onwards has the postcodes associated with each of these local authorities

In sheet 2 I have postcodes in column c with number and amount in columns d and e.

I want to (in sheet 3) look up the values in column c in sheet 2 against sheet 1 and then return the values associated to each local council....

Any help would be appreciated

Cheers

 

[Gerald Higgins]

Re: help - might be lookup but not sure

Does your data on sheet 1 look a bit like this ?

Col B............Col C........Col D.......Col E
Edinburgh......EH1..........EH2.........EH3....and so on....

So there will be a different number of postcodes, and therefore columns, for each council ?

 

[Aidan]

Hi Gerald

Yip it does, CJ is the last column for any authority.

 

[Gerald Higgins]

OK. In that case, are you able to transpose your postcode data ?
So, for example,

EH1.....EH2.....EH3 across 3 columns
becomes
EH1
EH2
EH3
in the same column, across 3 rows ?
Use Copy, Paste Special, Transpose.
I'm not sure how many councils there are in Scotland, but even if there are, say, 20, then it should only take you a couple of minutes to do this.

 

[Aidan]

Hi Gerald

Transposed the rows and colums but still unsure of the right formula to use

 

[Gerald Higgins]

OK good.

First, not sure why you need a third sheet, I would just do it on the 2 sheets you already have.

In sheet 1.....

=vlookup(B1,sheet2!c1:e100,2,false)

should bring up the "number" for the first postcode.

This assumes your first postcode on sheet1 is in B1, your first postcode on sheet2 is in C1, and you have 100 of them. Adapt as required.
Also, you can adapt this formula for the "amount".

 

[Gerald Higgins]

If you then want to summarise the data for each council, you could use SUMIF, or a Pivot Table.