Get results from Merged Cells

[onp]

Hello,
I have attached a Sample WB to better describe what I am trying to accomplish.
I have tried various formulas to get results from merged cells but none seem to work.
I am most likely doing it all wrong, perhaps someone could help me resolve the issue.

In the attached Work Book I wrote a brief description of my issue along with sample formulas.

Thank you
Nino

[TMS]

Try:

Please Login or Register  to view this content.

[FDibbins]

You have just discovered 1 of the problems associated with merged cells, and why we all advocate avoiding them if at all possible

[onp]

Hi Trevor, I tried your formula but not working

[onp]

I sure have, However I am not able to make it work even if I were to unmerge the cells.

[humdingaling]

revised TMS solution
make index array start from A1 instead of A17

Please Login or Register  to view this content.

[humdingaling]

if you want OFFSET Formula

Please Login or Register  to view this content.

[onp]

Hi humdingaling, thank you for your help
both formulas work well. However, the "offset" Formula is Much slower than the "Index" formula.
Index formula took 10.18 sec to calculate
Offset formula took 52.35 sec to calculate
that's because of to the amount of data that needs calculated.

I have discovered a problem with the Index Formula and I don't understand why. I Get a #REF! after I reach cell A433
I have reattached the workbook saved where the problem occurs.

What is causing the #REF! result?

Up until then it works perfectly

Thank you
Nino

[humdingaling]

yes offset is much slower because it is a volatile function
A Volatile Function is one that causes recalculation of the formula in the cell where it resides every time Excel recalculates.
so really should be avoided where possible
i just left it in there in case you really wanted to use it for some reason

your ref problem

the index only goes up to row 5000
$A$1:$A$5000
increase 5000 to whatever your max is going to be A13133 in this case

[azumi]

Another option

[samba_ravi]

if your merged cells are unique in size
=INDEX(A$17:A$5000,ROW()*12,0) this formula will works perfect
the error occurs may be due to the array is out of range means your range is a17:a5000, your row number is =(433-16)*12=5004, it is the out of range so you got error. to over come this you may use below
=iferror(INDEX(A$17:A$5000,ROW()*12,0),"")

if your merged cells are not unique in size the below formula will works

c17=IFERROR(INDEX(A:A,SMALL(INDEX((A$17:INDEX(A:A,MATCH(99^99,A:A))="")*99^99+ROW(A$17:INDEX(A:A,MATCH(99^99,A:A))),0),ROWS(C$17:C17))),"")
Try this and copy towards down

[TMS]

@humdingaling: dohhhh, thanks. That's what you get when you don't test a solution Sadly, working on an iPad has its disadvantages.

[humdingaling]

@TMS: that's some dedication to reply on ipad!

[TMS]

Well, I could see the formulae in the workbook and I could see what was wrong, in principle, I just couldn't test it ... so, I didn't make the Range absolute and didn't adjust the start cell. You see what you expect to see and, without the test, it looked right. Ho hum

[onp]

Thank you,
It works now
I shall try all the versions provided
cheers

[onp]

@TMS:
@humdingaling:
@nflsales
@azumi
Thank you to all of you, you have been great help, and I have learned allot from the different versions
Cheers
Nino

[TMS]

You're welcome. Thanks for the rep.