Combine data in two column if sum of column is same

[shan]

Hello ,

Good Morning..

Request your assistance on below.

I have data in multiple column. Would like to combine the column if sum of column is same.

If sum of B column is matching with any column then want to club the data and remaining keep as it is....

Note: No restriction on column and rows.

Raw data:

A B C D E F G H I J K L

	BJA	HKK	ISR	JKK	KHA	KUA	MNK	SEA	SHR	SIE	TYW
FB	ANN	ANN	ANN	ANN	ANN	ANN	ANN	ANN	ANN	ANN	ANN
BCR2J	10%	10%	10%	9%	10%	10%	6%	7%	10%	0%	10%
BCR3J	10%	10%	10%	9%	10%	10%	6%	7%	10%	0%	10%
CCR2J	12%	12%	12%	10%	12%	10%	8%	9%	12%	10%	12%
CCR3J	12%	12%	12%	10%	12%	10%	8%	9%	12%	10%	12%
DCR2J	10%	10%	10%	9%	10%	12%	9%	7%	10%	12%	14%
DCR3J	10%	10%	10%	9%	10%	12%	9%	7%	10%	12%	14%
HCR2J	10%	10%	10%	9%	10%	10%	5%	7%	10%	10%	7%
HCR3J	10%	10%	10%	9%	10%	10%	5%	7%	10%	10%	7%
JCR2J	12%	12%	15%	10%	15%	10%	10%	9%	12%	10%	12%
JCR3J	12%	12%	15%	10%	15%	10%	10%	9%	12%	10%	12%
KCR2J	10%	10%	8%	9%	8%	12%	5%	5%	10%	12%	7%
KCR3J	10%	10%	8%	9%	8%	12%	5%	5%	10%	12%	7%
LCR2J							5%
LCR3J							5%
MCR2J	10%	10%	8%	9%	8%	12%	5%	5%	10%	12%	5%
MCR3J	10%	10%	8%	9%	8%	12%	5%	5%	10%	12%	5%
QCR2J	9%	9%	7%	7%	7%	10%	5%	5%	9%	12%	5%
QCR3J	9%	9%	7%	7%	7%	10%	5%	5%	9%	12%	5%
YCR2J	10%	10%	10%	9%	10%	10%	6%	7%	10%	10%	10%
YCR3J	10%	10%	10%	9%	10%	10%	6%	7%	10%	10%	10%

Output:
If you see in above data sum of column B,C and J is same, also sum of column D and F also same hence we need to combine data of B,C and J as in one column and D & F in one column. Rest column will remain as it is.
Combine column highlighted below

BJA HKK SHR ISR KHA JKK KUA MNK SEA SIE TYW FB ANN ANN ANN ANN ANN ANN ANN ANN BCR2J 10% 10% 9% 10% 6% 7% 0% 10% BCR3J 10% 10% 9% 10% 6% 7% 0% 10% CCR2J 12% 12% 10% 10% 8% 9% 10% 12% CCR3J 12% 12% 10% 10% 8% 9% 10% 12% DCR2J 10% 10% 9% 12% 9% 7% 12% 14% DCR3J 10% 10% 9% 12% 9% 7% 12% 14% HCR2J 10% 10% 9% 10% 5% 7% 10% 7% HCR3J 10% 10% 9% 10% 5% 7% 10% 7% JCR2J 12% 15% 10% 10% 10% 9% 10% 12% JCR3J 12% 15% 10% 10% 10% 9% 10% 12% KCR2J 10% 8% 9% 12% 5% 5% 12% 7% KCR3J 10% 8% 9% 12% 5% 5% 12% 7% LCR2J 5% LCR3J 5% MCR2J 10% 8% 9% 12% 5% 5% 12% 5% MCR3J 10% 8% 9% 12% 5% 5% 12% 5% QCR2J 9% 7% 7% 10% 5% 5% 12% 5% QCR3J 9% 7% 7% 10% 5% 5% 12% 5% YCR2J 10% 10% 9% 10% 6% 7% 10% 10% YCR3J 10% 10% 9% 10% 6% 7% 10% 10%

Regards,
Shan

[p45cal]

Adjust the red in the line below in the code (2nd line):

Set EntireTable = Range("A1:L22") 'adjust this line to suit, includes row and column headers.

Sub zxczxc2()
Dim RngToDelete As Range
Set EntireTable = Range("A1:L22")  'adjust this line to suit, includes row and column headers.
Set mydata = EntireTable.Resize(, EntireTable.Columns.Count - 1).Offset(, 1)
Set mydatabody = mydata.Resize(mydata.Rows.Count - 1).Offset(1)
With mydatabody
  For colm1 = .Columns.Count To 2 Step -1
    For colm2 = colm1 - 1 To 1 Step -1
      ColumnsAreTheSame = True
      For rw = 1 To .Rows.Count
        If .Columns(colm1).Cells(rw) <> .Columns(colm2).Cells(rw) Then
          ColumnsAreTheSame = False
          Exit For
        End If
      Next rw
      If ColumnsAreTheSame Then
        With mydata
          If RngToDelete Is Nothing Then Set RngToDelete = .Columns(colm1) Else Set RngToDelete = Union(RngToDelete, .Columns(colm1))
          .Columns(colm2).Cells(1).Value = .Columns(colm2).Cells(1).Value & " " & .Columns(colm1).Cells(1).Value
          .Columns(colm1).Value = "¬"
        End With  'mydata
      End If
    Next colm2
  Next colm1
End With  'mydatabody
RngToDelete.Delete xlShiftToLeft
End Sub

There are 2 sheets in the attached, identical, click the button on one of them to run the macro, keep the other 'as is' so you can copy it to experiment with.

[shan]

Thanks Sir..

Is there any possibility to put the auto range in below code .. otherwise each time manually we need to update the range

EntireTable = Range("A1:L22")

[p45cal]

It depends on what's around the table, if nothing then something along the lines of
Set EntireTable = Range("A1").currentregion
but if there are entirely blank rows or blank columns within the table or there are cells with data/formulae right next to the table then this will fail.
Is there something else that can be relied upon to determine the range of the table?
Perhaps attach a file with several typical sheets so I can explore.

Otherwise we could change the code so that the user selects the table on the sheet and then runs the macro.

[shan]

thank you so much