Returning multiple text data into 1 column from many column entrie
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 |
Returning multiple text data into 1 column from many columnentrie
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 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 |
All times are GMT +1. The time now is 09:38 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com