formula required to return data from one column

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Rich Hayes - 14 Mar 2008 18:27 GMT

Hi,

I have a query on some data i'm currently analysing and it's causing me a
headache. I'm hoping there is a simple solution to this that doesn't involve
macros.

Any help much appreciated

Sample data: this is what i have in one column of data at present. each name
beneath a port represents people working at that particular port. However,
what i want is in the column along side this data for it to show the port
name that each employee works at.

what i have at present is as follows;

cell a1 port:rotterdam
cell a2 richard
cell a3 david
cell a4 paul
cell a5 port:south africa
cell a6 james
cell a7 sam
cell a8 keith
cell a9 duncan
cell a10 port:port talbot
cell a11 simon
cell a12 rachel

what i'd like to see is a formula in column B to return the relevant port
for each individual (shown below) It is a large document with over 5000 rows
so a formula is a must if possible.

column A column B
Port: rotterdam
Richard rotterdam
David rotterdam
Paul rotterdam
Port: south africa
james south africa
sam south africa
keith south africa
duncan south africa
Port: port talbot
simon port talbot
rachel port talbot

Reply to this Message

Mike - 14 Mar 2008 19:00 GMT

Rich -

I'm not smart enough on functions to solve your problem without a little bit
of code. But, the code is very simple. Here it is in case you want to use
it:

In Excel, hit Alt + F11 to get into the Visual Basic Editor.
Go to Insert, Module.
Paste this code into your new module:

Option Explicit
Public Function ReturnName(theCell As Range) As String

Dim irow As Long
Dim icol As Integer
Dim strText As String

If InStr(theCell.Value, "port") > 0 Then 'if its a "port" header, skip it
ReturnName = ""
Else 'just a person's name, find the port above
irow = theCell.Row
icol = theCell.Column

'loop until you find a port name or the top of the sheet
Do Until irow = 1 Or InStr(Cells(irow, icol), ":") > 0
irow = irow - 1
Loop

If irow = 1 Then 'top of sheet
ReturnName = "" 'return a blank
Else 'found a port name
strText = Cells(irow, icol).Value
'return port name
ReturnName = Right(strText, Len(strText) - InStr(strText, ":") -
1)
End If
End If
End Function

Now, in your worksheet, in cell B2, type the following formula:
=returnname(A2)

NOTE: This assumes your list of ports/names starts in cell A2. Copy it all
the way down and your problem should be solved.

One more note: I have experienced issues with Excel '07 recalculating these
custom functions when sheet changes occur, so just be aware of that.

> Hi,
>
[quoted text clipped - 41 lines]
> simon port talbot
> rachel port talbot

Reply to this Message

Ron@Buy - 14 Mar 2008 19:32 GMT

As an alternative to code:-
One way is to insert a blank row above row 1 then using a helper column (say
D) enter the following:
(Using PROPER to capitalise the Port name)
B1 =IF(LEFT(A2,4)="Port","",PROPER(C1))
D1 =IF(B2>"",C1,TRIM(MID(A2,6,15)))
(15 is used for port name, adjust to suit longest name)
Copy both down as far as you need.
You could then "Hide" column D
Hope this helps

> Rich -
>
[quoted text clipped - 89 lines]
> > simon port talbot
> > rachel port talbot

Reply to this Message

Ron@Buy - 14 Mar 2008 19:43 GMT

Try this
Insert blank row above your row 1
Then using a helper column (say D) which you can hide, enter the following:
(using PROPER to capitalise port name)
Cell B2 =IF(LEFT(A2,4)="port","",PROPER(C1))
Cell C2 =IF(B2="",TRIM(MID(A2,6,15)),C1)
(adjust the number 15 to suit longest port name)
Trust this helps

> Rich -
>
[quoted text clipped - 89 lines]
> > simon port talbot
> > rachel port talbot

Reply to this Message

T. Valko - 14 Mar 2008 19:36 GMT

Leave cell B1 empty.

Enter this formula in B2 and copy down as needed:

=IF(LEFT(A2,4)="port","",MID(LOOKUP(10,SEARCH("port",A$1:A1),A$1:A1),6,255))

Signature

Biff
Microsoft Excel MVP

> Hi,
>
[quoted text clipped - 44 lines]
> simon port talbot
> rachel port talbot

Reply to this Message

Ron@Buy - 14 Mar 2008 20:00 GMT

Brilliant !
Tried breaking down your formula to see how it works but got lost on the 10,
would appreciate a brief on how it produces the correct result.

> Leave cell B1 empty.
>
[quoted text clipped - 50 lines]
> > simon port talbot
> > rachel port talbot

Reply to this Message

Mike - 14 Mar 2008 20:28 GMT

I second Ron's request. My co-workers and I are completely baffled!!!

> Brilliant !
> Tried breaking down your formula to see how it works but got lost on the 10,
[quoted text clipped - 55 lines]
> > > simon port talbot
> > > rachel port talbot

Reply to this Message

T. Valko - 14 Mar 2008 22:19 GMT

Let's break it down using this data:

a1 port:rotterdam
a2 richard
a3 david
a4 paul
a5 port:south africa
a6 james

=IF(LEFT(A2,4)="port","",MID(LOOKUP(10,SEARCH("port",A$1:A1),A$1:A1),6,255))

Everyone probably understands the IF(LEFT....) stuff so I'll skip that.

MID(LOOKUP(10,SEARCH("port",A$1:A1),A$1:A1),6,255)

SEARCH returns the starting position of a substring within a string. The
starting position is the character number. If the substring is not found
SEARCH returns a #VALUE! error. We're searching for the substring "port"
within the string indicated by the cell reference that grows into a range of
cells as we copy the formula down.

In the sample data that contains the substring "port" it's found at position
1. If a string contains multiple instances of the substring SEARCH will find
the *first* instance from left to right and return the starting position of
that *first* instance. So, with the sample data the result of SEARCH will
always be either 1 or #VALUE!.

With the formula entered in B2 and copied down this is what the SEARCH
function returns (V = #VALUE! error):

B2 = SEARCH("port",A$1:A1) = 1
B3 = SEARCH("port",A$1:A2) = {1;V}
B4 = SEARCH("port",A$1:A3) = {1;V;V}
B5 = "" blank due to IF(LEFT(....)
B6 = SEARCH("port",A$1:A5) = {1;V;V;V;1}

The results of the SEARCH function are then passed to the LOOKUP function.

B2 = LOOKUP(10,1,A$1:A1)
B3 = LOOKUP(10,{1;V},A$1:A2)
B4 = LOOKUP(10,{1;V;V},A$1:A3)
B5 = "" blank due to IF(LEFT(....)
B6 = LOOKUP(10,{1;V;V;V;1},A$1:A5)

Now comes the confusing part!!!!

The way that LOOKUP works is if the lookup_value is greater than any numeric
value in the lookup_vector, it will "match" the *last numeric* value in the
lookup_vector that is *less* than the lookup_value. The lookup_vector is the
result of the SEARCH function. Since the SEARCH function returned only
either 1 or V, the lookup_value (10) *is* greater than any numeric value in
the lookup_vector so it will "match" the *last numeric* value in the
lookup_vector.

LOOKUP returns the result from the result_vector that corresponds to *last
numeric* value in the lookup_vector that is *less* than the lookup_value.
So, this is what the lookup_vector and the result_vector look like as the
formula is copied down. The result of LOOKUP is the value in the
result_vector that corresponds to the *last* 1 in the lookup_vector:

LV = lookup_vector
RV = result_vector

B2::
LV............RV
1...............port:rotterdam

B3:
LV............RV
1..............port:rotterdam
V.............richard

B4:
LV...........RV
1..............port:rotterdam
V.............richard
V.............david

B5: "" blank due to IF(LEFT(....)

B6:
LV...........RV
1..............port:rotterdam
V.............richard
V.............david
V.............paul
1..............port:south africa

So:

B2 = port:rotterdam
B3 = port:rotterdam
B4 = port:rotterdam
B5 = ""
B6 = port:south africa

Then the result of the LOOKUP function is passed to the MID function where
we get rid of the "port:" at the beginning of the string.

So, the final result is:

B2: rotterdam
B3: rotterdam
B4: rotterdam
B5:
B6:south africa

That's a "deluxe" explanation! Hopefully it didn't make things more
confusing.

Signature

Biff
Microsoft Excel MVP

>I second Ron's request. My co-workers and I are completely baffled!!!
>
[quoted text clipped - 63 lines]
>> > > simon port talbot
>> > > rachel port talbot

Reply to this Message