Concatenate based on unique column combination.

**samford** · 08-02-2010, 10:02 PM

Hi

I have minimal experience in writing macros in VBA, but was wondering if someone could please help me.

I have data extracted out of a finincial system where the long text firld is broken over many lines. Each unique combination in columns A, B, and C should have column D concatenated and the orginial lines deleted. I have 25,000 lines, and will do so each quarter.

I would really appreciate some help - I have tried to leverage off other solutions in the forum, but my VBA knowledge is so poor, I am unable to get them working.

Attached is a sample of how the data currently looks is in my post below.

Thanks!

**Marcol** · 08-02-2010, 10:34 PM

Hi samford

Welcome to the forum

You would do best to post a sample workbook showing Before and After.

It should clearly illustrate your problem and not contain any sensitive data.

Cheers

**samford** · 08-02-2010, 10:58 PM

Thanks Marcol

I have attached an example of the original post.

Cheers

**DonkeyOte** · 08-03-2010, 02:06 AM

Here is some basic code - not clear from the sample if Line Item & Text Identifier also need to be considered or not ?

(below assumes data sorted by PO number and Text Description per the sample)

Please Login or Register  to view this content.

Working example attached.

**samford** · 08-03-2010, 02:30 AM

Thanks DonkeyOte.

Sorry to be a pain, but now I get an error: "Error 9 (subscript out of range)"

Am I missing something?

**DonkeyOte** · 08-03-2010, 02:39 AM

Are you referring to the sample file or your own file ?

Note in the sample file wsBefore is specified as being named "Before" - modify as appropriate.

**samford** · 08-03-2010, 02:46 AM

sorry - that was an obvious one, should've got that.

So now, it gets half way through and gives me another error "Error 13 (Type mismatch)".

I should point out (incase its important) that some Long text fields have blanks.

Thanks!

**DonkeyOte** · 08-03-2010, 03:06 AM

It would perhaps be an idea to post a sample that generates the specified error.

edit: I suspect you may have underlying error values in your long text data set ...

**samford** · 08-03-2010, 06:51 PM

DonkeyOte

All sorted, an "=" sign had come through in one of the field from the database, so excel was expecting a formula. Once the "=" was deleted, your macro worked like a charm.

Thanks for taking the time to assist.