Merging Cells

[Ginger-daq]

Hi,

How would I go about merging cells with the same values AUTOMATICALLY?

For instance in cells A1:E1, each cell contains a value of 4 except E1 so I require A1:D1 merged.

Driving me insane!! limited excel and VBA knowledge sorry.


Ginger-daq

 

[iliace]

Do you want to literally "merge" them, or just find duplicates?

I would recommend against using merged cells in a structured data table. It will create more headaches than problems it solves.

However, you can use the Data -> Data Tools -> Remove Duplicates feature to ensure you have unique values based on columns SKU, S, and C. That is by far the easiest approach.

 

[SIXTYAVE]

Thanks for your quick reply.

This works as mentioned but I will like to add the value of the duplicate S and C to the corresponding S and C cells of the un-deleted unique record. I hope this make sense.

Like these:

<style>table { }td { padding-top: 1px; padding-right: 1px; padding-left: 1px; color: black; font-size: 12pt; font-weight: 400; font-style: normal; text-decoration: none; font-family: Calibri,sans-serif; vertical-align: bottom; border: medium none; white-space: nowrap; }</style>

sku	color	size
12345	blue	s
12345	red	m
12345	green	l

<tbody>
</tbody>

To these:

<style>table { }td { padding-top: 1px; padding-right: 1px; padding-left: 1px; color: black; font-size: 12pt; font-weight: 400; font-style: normal; text-decoration: none; font-family: Calibri,sans-serif; vertical-align: bottom; border: medium none; white-space: nowrap; }</style>

sku	color	size
12345	blue,red,green	s,m,l

<tbody>
</tbody>

 

[iliace]

But in addition to those, don't you still have the possibility of records such as these:

12345 blue m
12345 blue l
12345 red s

How would you handle those scenarios?

 

[SIXTYAVE]

Those will not affect me because the script I will use to import will ignore duplicate option values in a cell.

 

[iliace]

They aren't duplicates, not based on the original condition. Let's say your resulting list is:

12345 blue s
12345 blue m
12345 blue l
12345 red s
12345 red m
12345 green l


Then do you want this output?

12345 blue,blue,blue,red,red,green s,m,l,s,m,l

 

[SIXTYAVE]

Yes. This is exactly what I want.

 

[iliace]

OK, here is possibility #1. This is a UDF that you can use as a worksheet function. It takes three parameters: the current category, list of categories, and list of attributes. In your case, "category" means SKU, and "attribute" is either size or color.

So for example, if you enter this formula:

=ConcatUnique(12345, A1:A50, B1:B50)

It will return a comma-separated string of values from B1:B50 (attribute list), for each row where A1:A50 (category list) is equal to 12345 (current category).

Here's the code:

Public Function ConcatUnique(CatNum As Excel.Range, CatList As Excel.Range, AttrList1 As Excel.Range) As String
  Dim sRetVal As String
  Dim sCatNum As String
  Dim i As Long
  
  Dim vCatList As Variant, vAttrList1 As Variant
  
  sCatNum = CatNum.Value
  
  vCatList = CatList.Value
  vAttrList1 = AttrList1.Value
  
  For i = LBound(vCatList, 1) To UBound(vCatList, 1)
    If StrComp(sCatNum, vCatList(i, 1)) = 0 Then
      sRetVal = sRetVal & vAttrList1(i, 1) & ","
    End If
  Next i
  
  If StrComp(Right$(sRetVal, 1), ",") = 0 Then
    ConcatUnique = Left$(sRetVal, Len(sRetVal) - 1)
  Else
    ConcatUnique = sRetVal
  End If
End Function

 

[ciprianmp]

@iliace Just to let you know, the final macro VBA works as I dreamed of
It takes around 3 minutes for each of the 3 sheets to unmerge/re-merge all 1.700 cells each (10 days ranges x 3 columns x 56 quarters/lines), with all the optimizations I could get. But it works, no need for copy&paste manual errors anymore
Thank you very much for all your help!

 

[iliace]

Just to make sure we cover all bases, two things that are often omitted are, at the beginning of the code:

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

and, respectively, at the end of the code:

Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic

This will generally speed things up quite a bit.

 

[ciprianmp]

Just to make sure we cover all bases, two things that are often omitted are, at the beginning of the code:

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

and, respectively, at the end of the code:

Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic

This will generally speed things up quite a bit.

Yes, I disabled all 6 recommended optimizations (those 2 plus DisplayAlerts, DisplayStatusbar, DisplayPageBreaks and EnableEvents), by getting the initial value first, setting them to False then restore initial status at the end of each Sub.