formula required to return data from one column

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

Mike
Fri Mar 14 13:00:02 CDT 2008

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.


"Rich Hayes" wrote:

> 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

RonBuy
Fri Mar 14 13:32:00 CDT 2008

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



"Mike" wrote:

> 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.
>
>
> "Rich Hayes" wrote:
>
> > 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

T
Fri Mar 14 13:36:35 CDT 2008

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))


--
Biff
Microsoft Excel MVP


"Rich Hayes" <RichHayes@discussions.microsoft.com> wrote in message
news:708D76E9-B294-4EF0-A017-A81603A9F71A@microsoft.com...
> 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

RonBuy
Fri Mar 14 13:43:02 CDT 2008

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



"Mike" wrote:

> 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.
>
>
> "Rich Hayes" wrote:
>
> > 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

RonBuy
Fri Mar 14 14:00:03 CDT 2008

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.


"T. Valko" wrote:

> 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))
>
>
> --
> Biff
> Microsoft Excel MVP
>
>
> "Rich Hayes" <RichHayes@discussions.microsoft.com> wrote in message
> news:708D76E9-B294-4EF0-A017-A81603A9F71A@microsoft.com...
> > 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
>
>
>

Mike
Fri Mar 14 14:28:06 CDT 2008

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

"Ron@Buy" wrote:

> 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.
>
>
> "T. Valko" wrote:
>
> > 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))
> >
> >
> > --
> > Biff
> > Microsoft Excel MVP
> >
> >
> > "Rich Hayes" <RichHayes@discussions.microsoft.com> wrote in message
> > news:708D76E9-B294-4EF0-A017-A81603A9F71A@microsoft.com...
> > > 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
> >
> >
> >

T
Fri Mar 14 16:19:41 CDT 2008

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.



--
Biff
Microsoft Excel MVP


"Mike" <Mike@discussions.microsoft.com> wrote in message
news:611B1A77-1FC4-4BEE-BB85-3CBFFADEB63B@microsoft.com...
>I second Ron's request. My co-workers and I are completely baffled!!!
>
> "Ron@Buy" wrote:
>
>> 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.
>>
>>
>> "T. Valko" wrote:
>>
>> > 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))
>> >
>> >
>> > --
>> > Biff
>> > Microsoft Excel MVP
>> >
>> >
>> > "Rich Hayes" <RichHayes@discussions.microsoft.com> wrote in message
>> > news:708D76E9-B294-4EF0-A017-A81603A9F71A@microsoft.com...
>> > > 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
>> >
>> >
>> >