How to combine rows based on value in column A?

[ClassicVAG]

Hi all

I have spent some time searching and trying, but I haven't been able to solve my problem yet. Hope some can help me.

I have an Excel workbook containg several sheets that imports data from XML. One of the sheets contain data like this:
Column A - Column B - Column C
1000 - 1 - Data (text)
1000 - 2 - Data (text)
1000 - 3 - Data (text)
1001 - 1 - Data (text)
1002 - 1 - (empty)
1003 - 1 - Data (text)
1003 - 2 - Data (text)

The first row (A) is a uniqe identifier from a database, the second column (B) contains an index number to show how many records there is in the database for this uniqe identifier and finally column three (C) contains the actual data.

I need to combine the data so that I get all comments on the same row, prefferably in separate columns.

Once this is done I will use vlookup to take the cell content and put it on another worksheet before importing to another database.

All help and tips appriciated.

Best regards
Lars-Inge

[arlu1201]

So how do you want the output to show?

[ClassicVAG]

Hi Arlette

I would like the output to be like this:
Column A - Column B - Column C - Column D
1000 - Data (text) - Data (text) - Data (text)
1001 - Data (text)
1002 - (empty)
1003 - Data (text) - Data (text)

The number of comments (data) varies from each uniqe ID, but at the moment I think there is 6 at the most.

Hope this helps.

[arlu1201]

Ensure your data starts from row 2, try this code

Please Login or Register  to view this content.

Copy the Excel VBA code
Select the workbook in which you want to store the Excel VBA code
Hold the Alt key, and press the F11 key, to open the Visual Basic Editor
Choose Insert | Module
Where the cursor is flashing, choose Edit | Paste

To run the Excel VBA code:
Choose View | Macros
Select a macro in the list, and click the Run button

[ClassicVAG]

Thanks, this works brilliant I think.

The only thing is that my question wasn't specific enough.

What happens, I think, is that it adds a column for all entries and since there is just about 18.000 rows it will be a lot of columns. Would it be possible to modify the macro (add a function) and get it to delete the rows where cell in column C is emtpy?

Not all issues has a comment, and these i don't need.

[arlu1201]

Do you mean there are chances that the data can be like this?
1000 - 1 - Data (text)
1000 - 2 - Data (text)
1000 - 3 - Data (text)
1000 - 4 - (empty)

Or maybe you can attach a sample file as it will be easy that way to update the code.

[ClassicVAG]

Hi

Yes, it could. Below is a quick and dirty "copy & paste" of the first few rows. No offence, but I honestly can't upload any file due to company policy. It does contain information that we don't want to lay out in the open... *S*


BAR-3467 1
BAR-3466 1
BAR-3466 2 Thanks for the info. Set to shared information.
CAS-3465 1
CAS-3465 2
CEL-3464 1
CEL-3463 1
CEL-3463 2 Hello.....
CAR-3462 1
CAR-3462 2
PAC-3461 1
ATL-3460 1
CAS-3459 1

As you can see, all issues will have at least one line, but they could be empty and therefore can be deleted. I don't know why they have done it like that, but it's down to the database and I can't do anything with it.

I really appriciate your time on this, at the moment it's a really big concern for me.

[arlu1201]

Regarding sensitive data, you only need to upload a file with dummy data, so i understand the layout.

Will work with the above for now.

Can the deletion be done first and then the combining of rows?

[ClassicVAG]

Sorry, didn't even think about that option...

Attached is a dummy workbook with the same setup as the original. I have only swappet out some comments with something else.

[jindon]

Is this what you wanted?

[ClassicVAG]

Hi jindon

It worked on the DummyWorkbook, but when I copy & paste the code to the "real" workbook it breaks (look at the code).

Please Login or Register  to view this content.

It breaks with:
Runtime error '13':
Type mismatch

I'll try to get this correct, but as you already have figured out, I'm not very good at this coding...

Best regards
Lars-Inge

[jindon]

If the output cell has more than 255 characters, Transpose function will fail.

Try this one

Please Login or Register  to view this content.

[ClassicVAG]

Hi jindon

This works like a charm.

It does add a lot of columns that are empty, but it doesn't matter at all, I can use this one. Thank you very much for your assistance, you have saved me a lot of manual editing...

I'll mark the thread as solved.

Br
Lars-Inge

[jindon]

Hi jindon

This works like a charm.

It does add a lot of columns that are empty, but it doesn't matter at all, I can use this one. Thank you very much for your assistance, you have saved me a lot of manual editing...

It shouldn't.

Col.C might be dirty as containing space/hidden characters etc.
try change

Please Login or Register  to view this content.

to

Please Login or Register  to view this content.