If values are de same, merge cells in another column.

EWSDustin

New Member
Joined
Dec 9, 2021
Messages
6
Office Version
  1. 365
Platform
  1. Windows
Hi to all,

First time posting, I was no able to attach the MiniSheet, sorry for the looks!
I'm still at basic level with Macros, but I am looking to simplify something that is taking me hours each week, to a simple clicks.
I'm stuck with something super simple to do manually (merge cells), but that cost to much time going one x one.

What I would like to achieve,
Column "Measure" will always indicate the total number of articles that are repeated in column "Articles". What I need is that the values of "Measure" are combined matching the repeated values from "Article". In other words, a macro where I can combine the cells from column "Measure" and "Reduction" that contain a value with those blank cells that are above it.
The spreadsheet is updated weekly, with different amounts each time, approximately 300 to 500 rows so it is not practical to do it manually one by one.
Thank you very much for trying to help me.

A picture says more than a thousand words, so attached how I receive the information (before), and how it should look (after).

Also, I add below the data in case its helps to copy paste.
SupplierContainerProduct
Article​
MeasureReduction
LIDL HK (FOB)​
MSUU2116438​
FRäS- UND BOHRER SET PFBS 4 A1 10TLG./ 12TLG. 4FACH SORT.​
359695​
LIDL HK (FOB)​
MSUU2116063​
FRäS- UND BOHRER SET PFBS 4 A1 10TLG./ 12TLG. 4FACH SORT.​
359695​
LIDL HK (FOB)​
SGRU3660920​
FRäS- UND BOHRER SET PFBS 4 A1 10TLG./ 12TLG. 4FACH SORT.​
359695​
33
DELTA-SPORT HANDELSKONTOR GMBH (DDP)​
MSUU2116192​
WINDLICHT GLAS MIT MICRO-LED COSY LIVING 3FACH SORT.​
359798​
11
DELTA-SPORT HANDELSKONTOR GMBH (DDP)​
CMAU4790058​
SPIELFIGUREN TIERSETS 4FACH SORT.​
359809​
DELTA-SPORT HANDELSKONTOR GMBH (DDP)​
CRXU9884641​
SPIELFIGUREN TIERSETS 4FACH SORT.​
359809​
DELTA-SPORT HANDELSKONTOR GMBH (DDP)​
TCNU6256870​
SPIELFIGUREN TIERSETS 4FACH SORT.​
359809​
31
DISTRA (DDP)​
CCLU4925063​
M JACKE FLEECE THERMO SKI 2FACH SORT.​
363054​
DISTRA (DDP)​
OOLU7710090​
M JACKE FLEECE THERMO SKI 2FACH SORT.​
363054​
21
DAYTEX (DDP)​
OOLU8959262​
D PYJAMA KURZ/LANG WIRK/WEB 3TLG. 2FACH SORT.​
363114​
11
DISTRA (DDP)​
BMOU4408564​
D HAUSANZUG FLEECE 2TLG. 2FACH SORT.​
363258​
DISTRA (DDP)​
CBHU9017428​
D HAUSANZUG FLEECE 2TLG. 2FACH SORT.​
363258​
DISTRA (DDP)​
OOLU8765539​
D HAUSANZUG FLEECE 2TLG. 2FACH SORT.​
363258​
DISTRA (DDP)​
TCLU8719719​
D HAUSANZUG FLEECE 2TLG. 2FACH SORT.​
363258​
DISTRA (DDP)​
UETU5317535​
D HAUSANZUG FLEECE 2TLG. 2FACH SORT.​
363258​
53
 

Attachments

  • Before.jpg
    Before.jpg
    137.2 KB · Views: 45
  • After.jpg
    After.jpg
    137.1 KB · Views: 46

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.
This seems to be A way to do it. I'm sure there are more elegant ways.

Code:
Sub MergeRows()
Dim lr As Long, i As Long, o As Long
lr = Cells(Rows.Count, "A").End(xlUp).Row
While lr > 1
  If IsNumeric(Cells(lr, 5)) Then
    o = Cells(lr, 5)
    i = lr - o + 1
    Range("E" & i & ":E" & lr).Merge Across:=False
    Range("F" & i & ":F" & lr).Merge Across:=False
    lr = lr - o
  Else
  lr = lr - o
  End If
Wend
End Sub
 
Upvote 0
Solution
This seems to be A way to do it. I'm sure there are more elegant ways.

Code:
Sub MergeRows()
Dim lr As Long, i As Long, o As Long
lr = Cells(Rows.Count, "A").End(xlUp).Row
While lr > 1
  If IsNumeric(Cells(lr, 5)) Then
    o = Cells(lr, 5)
    i = lr - o + 1
    Range("E" & i & ":E" & lr).Merge Across:=False
    Range("F" & i & ":F" & lr).Merge Across:=False
    lr = lr - o
  Else
  lr = lr - o
  End If
Wend
End Sub

Thanks Kweaver, it worked!
I am not used to ask for help, not because of pride, but because I don't want to waste other people's time. I have been a chaotic self-learner with Excel for about two years, and I can remember a thousand situations where I always thought how comfortable it would be to be able to ask the doubts to an expert, but I never dared to participate in a forum.
I hope now not to send 200 questions in a week haha

Quick question, it works perfectly, but when I activate it a second time, it blocks me in all Excel cases as if it was asking for something that it cannot process. It is not very important since I will put it in a template, in which I will put that the one who selects the button twice will go through a trap door straight to a shark aquarium, Austin Powers style.
Thanks again!
 
Upvote 0

Forum statistics

Threads
1,223,893
Messages
6,175,240
Members
452,621
Latest member
Laura_PinksBTHFT

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top