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?

 

[DanteAmor]

But, could you do the test with the code of post #26?

 

[Peter_SSs]

i'll share the exact information with you in coming couple of days.

When you do that you should also clarify this part

• Next step is to find the largest number present from that range.
• Last step is to find the corresponding value from “col5” of that large number. Use of msgbox to display the result.

What should the last step be if 2 or 3 or 10 cells in the last column are all the equal largest in that range?

 

[DanteAmor]

Hi @Sagar0650, I apologize if any of my comments bothered you, my intention is only to help you.

The macro of post #26 works with the sample data. But I don't think you want the final result only in a msgbox.
I go further, if you need any statistics or submit a report with those results, I suppose it would be best to store them in a cell.

Hence my next proposal: if you remove the combined cells (because for a presentation they look beautiful, but for the handling of data they become complicated, even as you saw the code in VBA it becomes complex), however, if you fill in the cells You can use a pivot table.

With the pivot table, you can find the maximum values, sums, counts, etc. Check out the example below:

Either way, let me know if you need any changes in the macro and I will gladly support you.

 

[Sagar0650]

I apologize if any of my comments bothered you, my intention is only to help you.

please you don't apologize to me
i know you have tried to help me.
to be very honest with you, i am currently outside of my business working hours.
i will surely goinf to contact you guys with proper input when i am back to work.

But, could you do the test with the code of post #26?

yes i did check, but as you said correctly it is the formatting of existing cells which is why the code is not working with me.

Hence my next proposal: if you remove the combined cells (because for a presentation they look beautiful, but for the handling of data they become complicated, even as you saw the code in VBA it becomes complex), however, if you fill in the cells You can use a pivot table.

i believe this option is without using macro but doing manual task with pivot table right?
output here looks much more easy to handle in further processing.
can you please elaborate more on this?
sorry for bothering you again and again.
Thank you for continuous support.

 

[DanteAmor]

It is no trouble for me, I like to help you.
Enjoy your weekend, when you return to work, we talk about pivot tables, it is very simple to use, you only select your information, in the menu you select Insert / pivot table. And ready you have your pivot table. Now you just have to accommodate the rows and columns, but with watching a couple of videos on YouTube you will be an expert.

 

[Peter_SSs]

without using macro

If you are interested in doing this without a macro, then you can do it with formulas without un-merging and including if there are multiple maximum values in a 'merged range'.

Either this where ..
L2 & M2 are copied down and
N2 is copied across and down

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			Data 1	7	output7
3					output2				2			Data 2	50	output13	output16	output17
4					output3				3			Data 3	100	output21	output23
5					output4				4
6					output5				5
7					output6				6
8					output7				7
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					output19				37
21	Data 3				output20				39
22					output21				100
23					output22				43
24					output23				100
25					output24				47
26
Sheet3

Cell Formulas
Range	Formula
L2:L6	L2	=IFERROR(INDEX($A$2:$A$100,MATCH(0,INDEX(COUNTIF($L$1:L1,$A$2:$A$100)+(A$2:A$100=""),0),0)),"")
M2:M6	M2	=IF(L2="","",MAX(INDEX(I$2:I$100,MATCH(L2,A$2:A$100,0)):INDEX(I$2:I$100,IF(L3="",ROWS(I$2:I$100),MATCH(L3,A$2:A$100,0)-1))))
N2:R6	N2	=IF(L2="","",IFERROR(INDEX($E:$E,AGGREGATE(15,6,ROW(INDEX($E$2:$E$100,MATCH($L2,$A$2:$A$100,0)):INDEX($E$2:$E$100,IF($L3="",ROWS($E$2:$E$100),MATCH($L3,$A$2:$A$100,0))))/(INDEX($I$2:$I$100,MATCH($L2,$A$2:$A$100,0)):INDEX($I$2:$I$100,IF($L3="",ROWS($I$2:$I$100),MATCH($L3,$A$2:$A$100,0)))=$M2),COLUMNS($N2:N2))),""))

.. or if you want to keep the formulas shorter, we could use a couple of helper columns that use your original idea of capturing the first and last rows of each merged area. The helper columns could be hidden after you populate the formulas in them.
Here .. K2, L2, M2 and N2 are copied down, and
O2 is copied across and down

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	7	Data 1	7	output7
3					output2				2		8	19	Data 2	50	output13	output16	output17
4					output3				3		20	99	Data 3	100	output21	output23
5					output4				4
6					output5				5
7					output6				6
8					output7				7
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					output19				37
21	Data 3				output20				39
22					output21				100
23					output22				43
24					output23				100
25					output24				47
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="","",IF(COUNT(K$2:K2)=COUNTA(A$2:A$100),ROWS(A$2:A$100),K3-1))
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))),""))

 

[Sagar0650]

Hello,
This is the screen-shot of actual data

Col1 to col9 have few merged cells.

Columns are hidden from I to O because they are of no use here.

Col1 has some ID’s.

Now for a given ID in col1, I have to find the largest number from col15.

Once I have the cell with largest value selected, I want to have the corresponding value from col7.

For example in this screenshot, for given particular ID from col1, the largest value is $44,532 in last column(col15) so the output would be

“I47 PAROXYSMAL TACHYCARDIA” from col7.

Please let me know in case of any information required.

 

[DanteAmor]

In fact, in your image, the highest value for the ID from col1 is $ 47,379.
Which means another consideration.

And the result, do you still want it in a msgbox?

 

[Sagar0650]

thank you for putting that point.
47379 is total of all values so we have to exclude it
you can consider the second largest value from that range
msgbox or into cell
anything would work for me

 

[DanteAmor]

If it is in a cell, in which cell? on which sheet?
And returning to Peter's question, what happens, if there are two or more maximum equal values?
That is, in total 80,000 and two values with 40,000, what would you do?

In your image you only put one ID, but do you have more ID? And I guess you also want the values of each ID?