loop through merged cells in a Column to get their addresses

[Sagar0650]

I have one column. say column A, which has multiple merged cells of different ranges.
for example first cell is merged from A2 to A15 whereas second merged cell ranges from A16 to A115
now i want to run through entire column to get the address of each merged cell.
i have code which helps me to get the address of the only first merge cell.
can anyone help me to run a loop to get these cell address?

 

[Sagar0650]

If it is in a cell, in which cell? on which sheet?

into a cell which is next to highest value

if there are two or more maximum equal values?

This is an exceptional & rare case. even i havent scene such scenario.
99.99% of times the values would be unique. even of they are same the decimal values would be different.
see below screen shot

but do you have more ID

yes i have multiple ID's, but the formatting is same as of the screenshots.
only the difference would be the number of rows of data.

 

[Peter_SSs]

See if one of these is any use. Both do allow for multiple maximum values in each section but will work just as well if there is only one.

In this first example, the results are tabulated in columns M, N, O, ...
Columns K:L are helper columns & can be hidden if you want.
Formulas in K2:N2 are copied down.
Formula in O2 is copied across and down.
(Green colouring was just to help me identify the results)

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R
1	COL1	COL2	COL3	COL4	COL5	COL6	COL7	COL8	COL9
2	Data 1				output1				1		1	6	Data 1	6	output6
3					output2				2		8	18	Data 2	50	output13	output16	output17
4					output3				3		20	23	Data 3	100	output21	output23
5					output4				4
6					output5				5
7					output6				6
8		Summary			output7				21
9	Data 2				output8				21
10					output9				17
11					output10				19
12					output11				21
13					output12				23
14					output13				50
15					output14				27
16					output15				29
17					output16				50
18					output17				50
19					output18				35
20		Summary			output19				342
21	Data 3				output20				39
22					output21				100
23					output22				43
24					output23				100
25		Summary			output24				282
26
Sheet4

Cell Formulas
Range	Formula
K2:K6	K2	=IFERROR(AGGREGATE(15,6,ROW(A:A)/(A$2:A$100<>""),ROWS(K$2:K2)),"")
L2:L6	L2	=IF(K2="","",K2+MATCH("Summary",INDEX(B$2:B$100,K2):B$100,0)-2)
M2:M6	M2	=IF(K2="","",INDEX(A$2:A$100,K2))
N2:N6	N2	=IF(K2="","",MAX(INDEX(I$2:I$100,K2):INDEX(I$2:I$100,L2)))
O2:R6	O2	=IF($K2="","",IFERROR(INDEX($E:$E,AGGREGATE(15,6,ROW(INDEX($E$2:$E$100,$K2):INDEX($E$2:$E$100,$L2))/(INDEX($I$2:$I$100,$K2):INDEX($I$2:$I$100,$L2)=$N2),COLUMNS($O2:O2))),""))

In this second example, the results are provided beside the maximum values as suggested.
K1 houses a 0
J2 & K2 copied down.
Column K can be hidden.

Book1
	A	B	C	D	E	F	G	H	I	J	K
1	COL1	COL2	COL3	COL4	COL5	COL6	COL7	COL8	COL9		0
2	Data 1				output1				1		1
3					output2				2
4					output3				3
5					output4				4
6					output5				5
7					output6				6	output6	x
8		Summary			output7				21
9	Data 2				output8				21		2
10					output9				17
11					output10				19
12					output11				21
13					output12				23
14					output13				50	output13
15					output14				27
16					output15				29
17					output16				50	output16
18					output17				50	output17
19					output18				35		x
20		Summary			output19				342
21	Data 3				output20				39		3
22					output21				100	output21
23					output22				43
24					output23				100	output23	x
25		Summary			output24				282
26
Sheet5

Cell Formulas
Range	Formula
J2:J26	J2	=IF(OR(I3="",B2<>""),"",IF(I2=MAX(INDEX(I$2:I2,MATCH(MAX(K$2:K2),K$2:K2,0)):INDEX(I2:I$100,MATCH("x",K2:K$100,0))),E2,""))
K2:K26	K2	=IF(A2<>"",MAX(K$1:K1)+1,IF(B3<>"","x",""))

 

[DanteAmor]

Hi @Sagar0650,
I put the option with macro.
But for it to work well, your data must be just as you showed it in your image:
1. Header in row 1.
2. A row of Summary within the merged cell of column "A".
3. The data to be extracted is in column "G".
4. The last column has values, in your example it is the "O" column, but it can be any.
5. After column "O" there should be no data, nor cells with spaces, that is, in your example of column P to the right the cells must be empty.
6. The cells in column A must be merged.
7. The results will be 2 columns to the right of the last column, in this example the results will be written in columns Q and R.

------------------------------

Sub get_merged_cells_5()
  Dim c As Range, dic As Object, r As Range
  Dim wMax As Variant, output As Variant
  Dim ini As Long, fin As Long, lc As Long, i As Long, k As Long
 
  lc = Cells(1, Columns.Count).End(xlToLeft).Column
  Range(Cells(2, lc + 2), Cells(Rows.Count, lc + 3)).ClearContents
  k = 2
  Set dic = CreateObject("scripting.dictionary")
 
  For Each c In Range("A2", Range("A" & Rows.Count).End(xlUp))
    If c.MergeCells And Not dic.exists(c.MergeArea.Address(0, 0)) Then
      dic(c.MergeArea.Address(0, 0)) = Empty
      Set r = c.MergeArea
      ini = r.Cells(1).Row
      fin = r.Cells(1).Row + r.Rows.Count - 2
      wMax = 0
      For i = ini To fin
        If IsNumeric(Cells(i, lc).Value) Then
          If Cells(i, lc).Value > wMax Then
            wMax = Cells(i, lc).Value
            If Cells(i, "G").MergeCells Then
              output = Cells(i, "G").MergeArea.Cells(1).Value
            Else
              output = Cells(i, "G").Value
            End If
          End If
        End If
      Next
      Cells(k, lc + 2).Value = output
      Cells(k, lc + 3).Value = wMax
      k = k + 1
    End If
  Next
  MsgBox "End"
End Sub

----------------------------------------
8. Attach my test file.

Dropbox - File Deleted - Simplify your life

www.dropbox.com

----------------------------------------
9. See example below:

Book1
	A	B				F	G	H						N	O	P	Q	R
1	COL1	COL2				COL6	COL7	COL8						COL14	COL15
2	Data 1						output1								1		output1	17
3															17		output8	101
4															3		output21	100
5							output4								4		output25	39
6							output5								5
7							output6								6
8		Summary					output7								36
9	Data 2						output8								101
10															17
11															19
12															21
13							output12								23
14															50
15															27
16															29
17															31
18							output17								33
19															35
20		Summary													386
21	Data 3						output20								39
22							output21								100
23							output22								43
24															45
25		Summary					output24								227
26	Data 4						output25								39
27		Summary					output26								39
Data

Cell Formulas
Range	Formula
O8	O8	=SUM(O2:O7)
O20	O20	=SUM(O9:O19)
O25	O25	=SUM(O21:O24)
O27	O27	=SUM(O26:O26)

 

[Sagar0650]

it worked
you guys are awesome.
apologies if any of my comments bothered you & Thank you so much for help.

 

[DanteAmor]

No problem ? . Glad to know that it works for you.
Let me know if you have questions.
Thanks for notifying us.

 

[Sagar0650]

the alternate option suggested, pivot table. i would like to know more about it.
if you can help me get same result using pivot then it would be great.
please share the file with me if possible so that i can analyse & understand the logic behind it.
Thank you for support.

 

[DanteAmor]

the alternate option suggested, pivot table. i would like to know more about it.
if you can help me get same result using pivot then it would be great.
please share the file with me if possible so that i can analyse & understand the logic behind it.
Thank you for support.

Try

Dropbox - File Deleted - Simplify your life

www.dropbox.com