Create a : deliminated string from titles of array of data, depending on if a value exists

Hello,

I have a problem, in that I have rows of data some with values in, some without and some rows with data in multiple colums. I need to create a string of data in a colon ( deliminated format with no colon at the end by checking if a row has any data in a specific colum, if it does take the column name/title and add it to the string of data which would sit in the variant name column in the example data I have attached. Where there is no data for a row in a specific column these need to be ignored and go onto the next colum.

I also need it to be possible to expand the number of colums, either by editing any code or simply by incerting a column. The number of rows I would need to do this to would be aproximatly 10000.

I would be very greatful for any help you can provide.

Best regards,

Paul

I think I follow,

Try this code

Function aconcat(a As Variant, Optional sep As String = "") As String
  ' Harlan Grove, Mar 2002
  Dim y As Variant

  If TypeOf a Is Range Then
  For Each y In a.Cells
  aconcat = aconcat & y.Value & sep
  Next y
  ElseIf IsArray(a) Then
  For Each y In a
  aconcat = aconcat & y & sep
  Next y
  Else
  aconcat = aconcat & a & sep
  End If

  aconcat = Left(aconcat, Len(aconcat) - Len(sep))
End Function

With this formula, Array confirmed with Shift Ctrl Enter

Formula:

=SUBSTITUTE(aconcat(IF(A2:E2<>"",A2:E2,),":")&":","0:","")

Hello Jason,

Thanks once again for helping out with this.

I'm not sure if I'm implimenting this code correctly as I am getting 0's for every row and a circular reference message.

I have entered the first long code as a module for the spreadsheet or as a macro for the sheet1, and have enetered the formula in the to cells in column A, but post circular reference message (taht suggest I have probably set this up wrong) I just get 0 in all the A column cells for all rows.

I dont quite know what you mean by the Shift Ctrl Enter confirms the array or when that should be pressed?

Thanks for your help.

Paul

Hi Paul

I thought that the formula was going the other side and that column A should be part of the result.

=SUBSTITUTE(aconcat(IF(B2:E2<>"",B2:E2,),":")&":","0:","")

Adding extra columns is where formula can start to get messy, If you're going to add new data to column F, or insert it between A and B then the formula will need to be made dynamic to allow for that, if you insert new columns between B and E then the ranges will expand automatically.

Hi Jason,

Sorry about that yeah needed the results of the formula to go into the Column A.

I understand about the expansion issues and will make sure if we do need to expand the range that we do so from within the range as aposed to adding on an end so to speak.

I'm still experiancing a bit of a problem not sure if this is just something to do with how I've put it in or not but I just get a result in colum A of #VALUE!

Is there a change required in the function code to acomodate the fact that the result needs to be in column A or is the colum reference irelevant to the function?

Thanks once again for your help.

Paul

Hi Jason,

Top bannana.

Sorry yeah I think I must be haing a bad case of Friday brain, for some reason I struggled to explain what I was trying to achieve with this problem.

The latest function works perfectly just the job thanks once again, thats bloody brilliant.

Also thanks for explaining what is and how to get a confirmed array I'll remember this next time I need it.

Thanks once again very much apreciated.

---------- Post added at 02:43 PM ---------- Previous post was at 02:39 PM ----------

Sorry my appologies Stunn, Jason was coming back with lots very helpful responses and I assumed it was him that had posted the new function.

Thank you very much, as that function works great Stunn.