Share via

Merge Rows Using Column One Values

Anonymous
2022-08-25T08:09:55+00:00

I would like to merge rows based on Column 1 values of my data set.

ℹ Sample data is pasted below.

E.g. I select the range from A1:D20 and run the macro.

See After Merge screenshot:

Need to autofit row height,

no border required for the selected range. I added for the merged rows to stand out.

Before Merge:

After Merge:

Sample data to copy into your workbook.

453564 4926 453564 AMZ CM PCD Name One TNB 5455-66
453564 4926 453564 AMZ CM PCD Name One HGT 3343-5
453564 4926 453564 AMZ CM PCD Name One VVF 434-67
453564 210254 453564 AMZ CM PCD Name Two TNB 5455-66
453564 210254 453564 AMZ CM PCD Name Two HGT 3343-5
453564 210254 453564 AMZ CM PCD Name Two VVF 434-67
45356450 20243 45356450 Reusable Temp Name Three GST 223-5
45356450 20243 45356450 Reusable Temp Name Three VDDDR 888-55
564703941 1024926 564703941 CHL-VS for VSa Name One TNB 5455-66
564703941 1024926 564703941 CHL-VS for VSa Name One HGT 3343-5
564703941 1024926 564703941 CHL-VS for VSa Name One VVF 434-67
564703941 1025470 564703941 CHL-VS for VSa Name Two TNB 5455-66
564703941 1025470 564703941 CHL-VS for VSa Name Two HGT 3343-5
564703941 1025470 564703941 CHL-VS for VSa Name Two VVF 434-67
3564801131 1024926 3564801131 CHL-VI 20 IO Name One TNB 5455-66
3564801131 1024926 3564801131 CHL-VI 20 IO Name One HGT 3343-5
3564801131 1024926 3564801131 CHL-VI 20 IO Name One VVF 434-67
3564801131 1025470 3564801131 CHL-VI 20 IO Name Two TNB 5455-66
3564801131 1025470 3564801131 CHL-VI 20 IO Name Two HGT 3343-5
3564801131 1025470 3564801131 CHL-VI 20 IO Name Two VVF 434-67
Microsoft 365 and Office | Excel | For business | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

14 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2022-08-26T21:45:08+00:00

    I will keep that in mind.

    But in the current situation VBA is the only way to go as I am formatting the template.

    0 comments
  2. Anonymous
    2022-08-26T21:47:28+00:00

    Which tool? I am seeing a SQL statement but its not clear.

    0 comments
  3. Anonymous
    2022-08-26T22:29:43+00:00
    0 comments
  4. Anonymous
    2022-08-27T10:56:03+00:00

    Order gets changed and can't export to excel?

    0 comments
  5. Anonymous
    2022-08-27T23:35:22+00:00

    http://e.anyoupin.cn/bsbm/pdo/eh2/a2502/mybookmark/sql/sql/display_table.php?find=select+count%28%2A%29+%E8%A1%8C%2C%E5%AD%97%E6%AE%B5A%2C%E5%AD%97%E6%AE%B5B%2C%E5%AD%97%E6%AE%B5C%2C%E5%AD%97%E6%AE%B5D%2Cgroup_concat%28%E5%AD%97%E6%AE%B5E%29%2Cgroup_concat%28%E5%AD%97%E6%AE%B5F%29+from+MergeRowsUsingColumnOne+group+by+%E5%AD%97%E6%AE%B5A+order+by+rowid%3B

    http://e.anyoupin.cn/bsbm/pdo/eh2/a2502/mybookmark/sql/sql/display_table_t.php?find=select count(*) 行,字段A,字段B,字段C,字段D,group_concat(字段E),group_concat(字段F) from MergeRowsUsingColumnOne group by 字段A order by rowid;

    add order by rowid clause to the parameter find of the above url.

    the former sql default order by first column.

    exort to excel please refer to web query.

    https://answers.microsoft.com/en-us/msoffice/forum/all/excel-web-query-options/6c156a72-7466-4cb6-a62e-f57f3bb71a52

    0 comments