ExcelBanter (https://www.excelbanter.com/)
-   Excel Programming (https://www.excelbanter.com/excel-programming/)
-   -   Question; Combine datafields from different rows into one row and onecolumn if the data in column A is identical (https://www.excelbanter.com/excel-programming/454265-question%3B-combine-datafields-different-rows-into-one-row-onecolumn-if-data-column-identical.html)

 [email protected] April 11th 19 03:01 PM

Question; Combine datafields from different rows into one row and onecolumn if the data in column A is identical

Hello,

Sometimes a nice question come up to solve :)

Col.A Col.B Col.C

X123 aaa hhh
X123 bbb iii
X123 ccc jjj
A456 ddd kkk
B678 eee lll
B678 fff mmm
C890 ggg nnn

From row 1 till last; for all the records where in Col.A the data is the same, then combine the data in Col.B and C from those records, into the first row in Col.D.
The combined data into Col.D should be Col.B & ; & Col.C then AltEnter then Col.B & Col.C form the next row etc..
If there are no double rows (datafield in Col.A) then combine for that row only the data in Col.B and C to Col.D

Above result then in

Col.A Col.B Col.C Col.D

X123 aaa hhh aaa; hhh
bbb; iii
ccc; jjj
X123 bbb iii
X123 ccc jjj
A456 ddd kkk ddd; kkk
B678 eee lll eee; lll
fff; mmm
B678 fff mmm
C890 ggg nnn ggg; nnn

It should be (very)great if someone can help me out.

regards, Johan

 [email protected] April 11th 19 07:21 PM

Question; Combine datafields from different rows into one row andone column if the data in column A is identical

It looks good and works almost as asked. Thanks so far !!!

I think it went a bit wrong do to my explanation and the way this group shows it in the text.

Combining the fields are good, but it was not mean to combine in new inserted rows, but in the first one and then in column D. The Alt-Enter I wrote was mean as the actioncode "CHR(10)" (=new row in cell)

Col.A Col.B Col.C Col.D

X123 aaa hhh aaa; hhh CHR(10) bbb; iii CHR(10) ccc; jjj
X123 bbb iii
X123 ccc jjj
A456 ddd kkk ddd; kkk
B678 eee lll eee; lll CHR(10) fff; mmm
B678 fff mmm
C890 ggg nnn ggg; nnn

In your macro also the records that are combined in a first row where combined in colum D. This was not required, but you can leave this if it is a problem to leave them as it is now in the code.

regards, Johan

 [email protected] April 11th 19 07:53 PM

Question; Combine datafields from different rows into one row andone column if the data in column A is identical

A-M-A-Z-I-N-G !!!!

Very Thanks a lot. Works very good :)

regards, Johan

 All times are GMT +1. The time now is 12:13 PM.