asked on

VBA Excel 2000 - Merge cell contents

Dear Experts,

Could you please check the attached file, on Sheet1 it contains a simple table where for Items are entered Comments.

My target would be to merge those comments by items like on Merge sheet, so a kind of pivot where the comment cell values are concetanated.

In the Code section I have also attached an Access VBA function which is able to do this, but I would need this in Excel actually

thanks,

Option Compare Database
Option Explicit

Function ConcatenateThem(vID As Long) As String
Dim rs As DAO.Recordset, sList As String
Set rs = CurrentDb.OpenRecordset("select * From Table1 Where [Item]=" & vID)
    rs.MoveFirst
    Do Until rs.EOF
      sList = sList & "," & rs("comments")
      rs.MoveNext
    Loop
    ConcatenateThem = Mid(sList, 2, Len(sList) - 1)
End Function

Open in new window

MergeComments.xls

ASKER CERTIFIED SOLUTION

StephenJR

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

csehz

ASKER

StephenJR thanks, do you mean the formula should be in B and not maybe in D?

It seems in Excel 2000 the "SearchFormat:=False" should be removed because brings error including that, so in the attached file tried but somehow does not bring value. I assume so probably some version issue
MergeComments.xls

csehz

ASKER

Sorry anyway I am stupid because for sure to B2 has to put the formula and copy down on Merge sheet. But unfortunately it is still true that for me just it results a blank cell.
MergeComments.xls

JoeNuvo

So, actually, you just want VBA code to perform merge from "fixed sheet fixed column" into "fixed sheet fixed column"
(from your given file Sheet1 Column A and C -> Merge Column A and B)

or you want it in some kind of function?

csehz

ASKER

The target would be on Merge sheet in colum B a function, which gives such result based on Sheet1

Item      AllComments
100      Comment1,Comment2,Comment3,Comment4
101      Comment1,Comment2,Comment3
102      Comment1
103      Comment1,Comment2

But in Excel 2000, as I assume Stephen's function would work in new version.

StephenJR

Sorry, it must be an xl2000 issue, though I can't see anything obvious. Do you happen to know if you can use UDFs in that version - I've no reason to think you can't, but don't know.

csehz

ASKER

StephenJR, yes sure a version problem, and I think that reliable I could test your code on my machine only, when the company implement the new Excel here. Anyway it is in progress just not sure when, so I accept your solution