[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

VBA Excel 2000 - Merge cell contents

Posted on 2011-03-18
7
Medium Priority
?
541 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 1500 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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
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.

649 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