Solved

VBA Excel 2000 - Merge cell contents

Posted on 2011-03-18
7
528 Views
Last Modified: 2012-05-11
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
0
Comment
Question by:csehz
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 2
7 Comments
 
LVL 24

Accepted Solution

by:
StephenJR earned 500 total points
ID: 35165932
I don't know if this custom function works in Excel 2000, but enter, e.g. =Lookup2(A2,Sheet1!$A$2:$C$11) in B2 and copy down:
Function LookUp2(vItem, rData As Range) As String
 
Dim rFind As Range, s As String, sOut As String

With rData
    Set rFind = .Find(What:=vItem, After:=.Cells(.Cells.Count), LookAt:=xlWhole, MatchCase:=False, SearchFormat:=False)
    If Not rFind Is Nothing Then
        s = rFind.Address
        Do
            sOut = sOut & "," & rFind.Offset(, 2)
            Set rFind = .Find(What:=vItem, After:=rFind, LookAt:=xlWhole, MatchCase:=False, SearchFormat:=False)
         Loop While rFind.Address <> s
    End If
End With
     
LookUp2 = Mid(sOut, 2)

End Function

Open in new window

0
 
LVL 1

Author Comment

by:csehz
ID: 35171038
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
0
 
LVL 1

Author Comment

by:csehz
ID: 35171098
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
0
SharePoint Admin?

Enable Your Employees To Focus On The Core With Intuitive Onscreen Guidance That is With You At The Moment of Need.

 
LVL 11

Expert Comment

by:JoeNuvo
ID: 35171213
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?
0
 
LVL 1

Author Comment

by:csehz
ID: 35171228
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.
0
 
LVL 24

Expert Comment

by:StephenJR
ID: 35179536
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.
0
 
LVL 1

Author Closing Comment

by:csehz
ID: 35189561
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
0

Featured Post

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

730 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question