Prev: vlookup with two data in a single cell
Next: Create drop down list in Excel 2007
From: Roger on 4 Mar 2010 00:59
Hi.
I have a large worksheet that contains records of avtivities from record 3
and onwards with logic link predecessors to other activities in columns E
through IT.

I want Excel to identify where there is a logical link represented by an
Integer number in columns E though IT and return the cell data into column D
record by record as indicated below.
Where there is more than 1 entries in columns E through IT the returned data
in column D needs to be separated by a comma.
Preferrably I would like to avoid having a comma at the end if possible.

I would appreciate if someone could give me some help on this issue.

D E F G H

2,3,4 2 3 4
2 2
3,4 3 4
1,3 1 3
4 4


--
The Oilman
From: Jarek Kujawa on 4 Mar 2010 03:07
you might use this UDF. Press ALT+F11 to go to VBA, then click Insert-
>Module and copy/paste this code.
then go to D1 and insert: =link(D1)


Function link(cel As Range) As String
Dim counter As Integer

For Each cell In Range("E" & cel.Row & ":IT" & cel.Row).Cells
If Len(cell.Value) Then
counter = counter + 1
If counter > 1 Then
tekst = tekst & "," & cell.Value
ElseIf counter = 1 Then
tekst = cell.Value
End If
End If
Next cell

link = tekst

End Function




On 4 Mar, 06:59, Roger <Ro...(a)discussions.microsoft.com> wrote:
> Hi.
> I have a large worksheet that contains records of avtivities from record 3
> and onwards with logic link predecessors to other activities in columns E
> through IT.
>
> I want Excel to identify where there is a logical link represented by an
> Integer number in columns E though IT and return the cell data into column D
> record by record as indicated below.
> Where there is more than 1 entries in columns E through IT the returned data
> in column D needs to be separated by a comma.
> Preferrably I would like to avoid having a comma at the end if possible.
>
> I would appreciate if someone could give me  some help on this issue.
>
> D       E       F       G       H
>
> 2,3,4           2       3               4
> 2               2      
> 3,4                     3               4
> 1,3     1               3
> 4                                        4
>
> --
> The Oilman

 | 
Pages: 1
Prev: vlookup with two data in a single cell
Next: Create drop down list in Excel 2007