excel vba aggregate data in column

I have written the code to a point, but need help in completion. I would like to be able to modify this code so the data found in certain rows is written to a cell that expands 2 columns and is in row 2.
Please see the attached word document for the code as exists now and a screen shot of the work sheet.
The words in red is the psuedo-code.

Thanks in advance for your help.
Aggregate2.docx
tesla764Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

ChloesDadCommented:
Is the text in row 2 append to when reading each new row, or overwritten.

In other words, should Cell F2 say Yes,Yes,Tell&Do,Tell&Do,Yes,,Yes,Yes or just Yes (from the last row (Brazil)

Given the screen shot shown, what should the other cells in row 2 show?
0
tesla764Author Commented:
Yes the data should append.
Thanks.
0
ChloesDadCommented:
Thanks,

And is it OK for the same text to appear multiple times, i.e. as above or should it be consolidated to say "Yes, Tell & Do"
0
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

tesla764Author Commented:
There should be no repeats.
0
ChloesDadCommented:
Here is some simple code, it assumes that the more info row is the last row with data,

Sub Aggregate_IP_row()
    Dim LastRow As Long, LastCol As Integer, c As Integer, r As Long
    Dim Phrases As String
    Dim FoundPhrase As String
    LastRow = ActiveSheet.UsedRange.Rows(ActiveSheet.UsedRange.Rows.Count).Row
    LastCol = ActiveSheet.UsedRange.Columns(ActiveSheet.UsedRange.Columns.Count).Column
     
    For c = 1 To LastCol
            If Cells(4, c) = "In-Practice" Then
               Phrases = ""
               
               For r = 5 To LastRow - 1
               
                 FoundPhrase = Cells(r, c)
                 
                 If FoundPhrase <> "" And InStr(Phrases, "#" & FoundPhrase & " ,") = 0 Then
                 
                   Phrases = Phrases & "#" & FoundPhrase & " ,"
                   
                   Else
'                                    MsgBox "Duplicate Phrase " & FoundPhrase

                  
                End If
               
               Next
               
' Tidy up the message 

               Phrases = Replace(Phrases, "#", " ")
               Phrases = Left(Phrases, Len(Phrases) - 1)
               Phrases = Trim(Phrases)

               Cells(2, c) = Phrases
               

            End If
    Next c
End Sub

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
tesla764Author Commented:
Thanks, I will give that a try 1st thing tomorrow.
0
tesla764Author Commented:
Hi ChloesDad,
  I gave the code a test and it really looks good. One last thing before closing. I did not want to include the last row "More Info". What would I change to make that happen?
Thanks
0
ChloesDadCommented:
In my testing, the More Info row was the last row of used rows, so my loop counter to lastRow - 1 will exclude the More Info Row.

If you have data below the more info row then you could change the condition statement to

                 If FoundPhrase <> "" And FoundPhrase <> "More Info..." AND InStr(Phrases, "#" & FoundPhrase & " ,") = 0 Then

Edit "More Info..." as required for the sheet as I'm not certain how many ...s are in the cell
0
tesla764Author Commented:
There will never be any data below "More Info". I do NOT want More Info to appear in Row 2 with all the other data. Data concatenation should stop before the row that contains "More Info".
See Attached word file for screen shot. Notice in the row 2 cell the words "More Info" appear. Everything else looks great.
0
tesla764Author Commented:
Oops, here is the file attachment.
EE-MoreInfo.docx
0
tesla764Author Commented:
Did you get the attachment alright?
0
tesla764Author Commented:
Thanks. Your help was fantastic.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Applications

From novice to tech pro — start learning today.