asked on

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

ChloesDad

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?

tesla764

ASKER

Yes the data should append.
Thanks.

ChloesDad

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"

tesla764

ASKER

There should be no repeats.

ASKER CERTIFIED SOLUTION

ChloesDad

membership

This solution is only available to members.

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

Start Free Trial

tesla764

ASKER

Thanks, I will give that a try 1st thing tomorrow.

tesla764

ASKER

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

ChloesDad

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

tesla764

ASKER

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.

tesla764

ASKER

Oops, here is the file attachment.
EE-MoreInfo.docx

tesla764

ASKER

Did you get the attachment alright?

tesla764

ASKER

Thanks. Your help was fantastic.