Combine rows with same column value into one row with several columns for that value

Hi,

I was wondering if anyone knows how to combine rows (in Excel 2013) with the same value (in one column) into one row with several columns for that value and its columns?

I've attached a picture of what I am looking for:

excel combine rows.png

I made a VBA Macro several months ago that I believe worked well, but unfortunately I didn't test it more than once and now I cannot find the code.

What's the best solution to this "issue"?

Thanks in advance,

Best regards,

Ps, I've also asked this in Stackoverflow but thought I'll give it a try here as well, since I haven't figured it out yet.

You could use VLOOKUP with a couple of helper columns

Hi, thanks for your replies, sorry that I forgot to include the other link, I couldn't post any links until I posted a few times though?

How would I approach to do it with Vlookup? I would very much appreciate if you would like to explain, as I am trying to learn more and more about Excel every day

Thanks in advance,

Best regards,

Once you provide the link, also provide the example workbook so we can help. See yellow banner above.

Originally Posted by Jonthee

Hi, thanks for your replies, sorry that I forgot to include the other link, I couldn't post any links until I posted a few times though?

How would I approach to do it with Vlookup? I would very much appreciate if you would like to explain, as I am trying to learn more and more about Excel every day

Thanks in advance,

Best regards,

So my example was no help?

maniacb: Thanks, I've attached the sample workbook here

Croweater: I saw your example now, MANY THANKS, will it work out even with, lets say, 7 duplicated "part numbers", 4 duplicated, and so on?

Thanks again for the help, really appreciate it.

Have a great day,

Best regards,

Hi, Thank you very much, unfortunately I cannot get it to work

If I only have the A and B columns like my sample code, where and how do I put the macro VBA code to get the results in the D, E, F etc. columns, like the sample?

I got some error with this line: Range("D2").Resize(.Count, 2) = Application.Transpose(Array(.keys, .items))

Many thanks,

Have a great day,

Error code 9, index is outside interval: ReDim Preserve a(2 To UBound(a, 1), 1 To UBound(a, 2) + 100)

OOps, typo, 2 should be 1

Please Login or Register  to view this content.

Success, it works like a charm Many, many thanks, now I will try to understand everything

Ps, just out of curiousity, is it possible to do it any other way? Not that need to now that this works so great, but I was just thinking Pivot Table, formula etc.?

Best regards and thanks again,

No idea about PT,

formula Assuming all the unique value in col.A listed in D2 onwards.
E2
=IFERROR(INDEX($A$2:$B$11,SMALL(IF($A$2:$A$11=$D2,ROW($A$2:$A$11)),COLUMN(A1)),2),"")
Confirm with Ctrl + Shift + Enter(Array formula entry)
Copy left + down

Originally Posted by Jonthee

I got some error with this line: Range("D2").Resize(.Count, 2) = Application.Transpose(Array(.keys, .items))

I know jindon has already provided a working code for you but I would like to know what is the error message that you got to understand what caused it as it works fine in my laptop ...