Part of the data in a cell, plus work out an average.

[K-Linerz]

Hi Guys
I have attached a file.

I need a few things on this and I have tried but not had luck.

So the Post Code.
I need in column C a formula so that it pulls the first part of the post code up to the space. I did do a simple formula of =LEFT(A2,4) but on an odd post code there is 2 characters a space and then it pulls the first part of the next part.

For this next bit I tried a concatenate but it didnt work right for me. On the column D I need it to look at the result in column C and the add ", UK". So for example, if the original postcode is AB1 3EG in Column A, I want it to show AB1 in column C, and in column D, AB1, UK.

In addition to this, I need something that looks at a single result, probably best to use column C as there will be duplicates, and work out the average lead time for that particular postal area.
So for example, if AB1 is a result 4 times, and on each one of them the Lead Time is 10, 15, 20, 25, I need to see the average of that.
Basically I want a list of all postcodes not showing duplicates but giving what the average lead time is.

Sorry ha. I tried to explain this best as possible

As always, many thanks for your help

Thanks

Nath'

[jeffreybrown]

If I understand your requirement, for starters, try in C2 copied down...

=LEFT(SUBSTITUTE(A2," ",""),4)

Then in D2 copied down...

=C2&" UK"

You could then build a pivot table off of the four columns

Row Labels >> 3rd column (whatever you title it)
Values >> Average of Lead Time

[jeffreybrown]

If you want a formula solution

In F2 copied down

=LOOKUP(REPT("Z",255),CHOOSE({1;2},"",INDEX($C$2:$C$501,MATCH(TRUE,INDEX(ISNA(MATCH($C$2:$C$501,$F$1:$F1,0)),0),0))))

In G2 copied down

=AVERAGEIF(C:C,F2,B:B)

[K-Linerz]

The =LEFT(SUBSTITUTE(A2," ",""),4) brings in the 4th character of the post code.
So if the Post Code is AB1 4EG, it leaves me with AB14, all I want is the first set of characters before the space.
UK post codes are like : B1 3GE, B12 3EG, BE12, 3GE, BE1 3GE. SO I need the first set before the first space but that can be 2, 3 or 4 characters.

Sorry, I hope im explaining it clear.

[jeffreybrown]

Okay, sorry, I see now...

=LEFT(A2,FIND(" ",A2)-1)

Haven't live in the UK since 1977...

[K-Linerz]

Fantastic! Thank you so much jeffreybrown. That works perfect.

Thanks

Nath'

[jeffreybrown]

So happy to hear you have what you need now. Please do drop back by if you need anything else.

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

[K-Linerz]

All done

Thanks again

[jeffreybrown]

You're welcome and thx for the rep