Extracting the right data from a CSV report

[ShogunPatch]

I have created an Excel template to tidy up a CSV export report produced by vtiger, a popular open source crm. The report I have been using essentially lists all our business opportunities with each opportunity as a single separate row in the CSV and until now it has been very easy to simply copy the CSV into one sheet of my Excel template and have another sheet then lift the relevant data, put it in the order I want, format it, do various calculations, etc.

However, the vtiger report I have been using until now missed one key element of the opportunities which is that vtiger allows unlimited comments to be added to each opportunity as the negotiations, etc, develop. I've now managed to produce a report which includes comments, but instead of adding each comment into a new column (which I think would have been easier for what I want to do), it duplicates each record as many times as there are comments associated with it (note that vtiger only provides so much flexibility as to the design of its report, hence the reason I am adjusting them in Excel in the first place). So for example, if I create an opportunity record with no comments or add just one comment, that opportunity will take up just one row in the CSV export. However, if I add further comments (say 10 in all), then the CSV report will list the opportunity 10 times taking up 10 rows with the data in all 10 being identical in all columns save the comment column).

What I want is to adjust my existing template so that it lists each opportunity only once (as it did when the CSV report didn't include comments, because the CSV only listed each opportunity once) but to add a single column to it which list only the latest comment relating to that opportunity (i.e. in the example above the comment listed in the 10th iteration of that record.

I suspect the above setup sounds complex even though it isn't, it's just that I'm finding it quite hard to explain clearly.

Can anybody suggest a solution - either a simple formula (a lookup maybe) or else a macro, which will allow me to do what I want?

Many thanks.

 

[Randy R]

If I'm understanding this correctly, all of your columns will have the same value for records with extra comments, except for the comment column. If that is the case, if you remove duplicates based on all of the columns that match you will be left with just the one row. However, if the data is in order of the comments you will probably be left with first comment instead of the last.

You could create helper column that counts the number of comments for a group of like rows and then restarts when it gets to new grouping. Then you could sort descending by that column, use the remove duplicates process and finally sort the data back in the original sequence.

Just a possible workaround if you need to do something quick

 

[ShogunPatch]

Hhmmm... all good thoughts. Removing duplicates might be the answer; in which case presumably I could just reverse the order of all my cells and de-duplicate, no? (sounds easy, but I'll have to think about how to implement all this).
Do you happen to have a quick and easy bit of code to remove duplicates based on info in a given column?
Many thanks!