array formula

[gakiss2]

I have used array formulas successfully but only when the result is a single value. It was my impression that excel could list out the results when there are multiple resuls. I don't see where I can attach an excel file. =IFERROR(INDEX('All GK Data'!I:I, MATCH(0,COUNTIF($M$19:M19, 'All GK Data'!I:I), 0)),"")

 

[Marcelo Branco]

I forgot to ask
In which cell are you entering the first formula? It has to be in K2, because the formula uses
COUNTIF(K$1:K1, ...)

Note that K$1:K1 refers to the cell immediately above that of the formula. If you are, for example, putting the first formula in M2, change to
COUNTIF(M$1:M1, ...)

M.

 

[gakiss2]

I forgot to ask
In which cell are you entering the first formula? It has to be in K2, because the formula uses
COUNTIF(K$1:K1, ...)

Note that K$1:K1 refers to the cell immediately above that of the formula. If you are, for example, putting the first formula in M2, change to
COUNTIF(M$1:M1, ...)

M.

So I copy the formula so it is in the memory. I press ESC. I select the range of cells I want the data to flow into. I put the cursor in the formula bar then hit paste. the formula is pasted into the formula bar while the column of cells remain selected. I select the line in the formula bar (highlghed) and then I press Ctl + Shift + Enter. and thn space that was selected gets filled with the first entry for category, 'Function'.

And ye s I got the bit about.. ca'tstand rying t type in thbrowser.

 

[gakiss2]

So I copy the formula so it is in the memory. I press ESC. I select the range of cells I want the data to flow into. I put the cursor in the formula bar then hit paste. the formula is pasted into the formula bar while the column of cells remain selected. I select the line in the formula bar (highlghed) and then I press Ctl + Shift + Enter. and thn space that was selected gets filled with the first entry for category, 'Function'.

And ye s I got the bit about.. ca'tstand rying t type in thbrowser.

I got the bit about putting it one cell below.

 

[Marcelo Branco]

You must initially enter the formula into a single cell.
Please tell us in which cell you want to insert the first formula and then I can help you do it, step by step.
After this, the first formula should be copied (dragged) down.

M.

 

[gakiss2]

You must initially enter the formula into a single cell.
Please tell us in which cell you want to insert the first formula and then I can help you do it, step by step.
After this, the first formula should be copied (dragged) down.

M.

OK, so I want the output on a differet tab from the data. The Data is on 'All GK Data' (small ' added). The actual range will grow over time but for now lets say I2:I44444. The tab the data goes on is 'HYCO AL'. The cell the data should go to is L20. Maybe now is a good time to add that I want to limit the data to cases where the value in B is equal to cell B1 on the HYCO AL tab and the value in D should be greater than B2 and less than B2 on the 'HYCO AL' tab, Actually I have a similar function working but it is a single value - but very similar in concept. =SUMIFS('All GK Data'!F:F,'All GK Data'!D:D,"=" & L16,'All GK Data'!B:B,"="&'HYCO AL'!$B$1) Since this is sumifs, it is a single value.

 

[gakiss2]

OK, so I want the output on a differet tab from the data. The Data is on 'All GK Data' (small ' added). The actual range will grow over time but for now lets say I2:I44444. The tab the data goes on is 'HYCO AL'. The cell the data should go to is L20. Maybe now is a good time to add that I want to limit the data to cases where the value in B is equal to cell B1 on the HYCO AL tab and the value in D should be greater than B2 and less than B2 on the 'HYCO AL' tab, Actually I have a similar function working but it is a single value - but very similar in concept. =SUMIFS('All GK Data'!F:F,'All GK Data'!D:D,"=" & L16,'All GK Data'!B:B,"="&'HYCO AL'!$B$1) Since this is sumifs, it is a single value.

… less than B3

 

[gakiss2]

So progress...
=IFERROR(INDEX('All GK Data'!I2:I44444,MATCH(0,COUNTIF(K$19:K19,'All GK Data'!I2:I44444),0)),"")

I pasted down. It is now just a copy of the original data, It does not have duplicates removed.

 

[gakiss2]

 

[gakiss2]

Sorry was supposed to be an image of the data output. Looks like I can't copy then paste images onto this board.

 

[gakiss2]

Discovered when I put the '$' in front of address components for the source range then I get the first entry repeated. When I remove the '$' then I just get the column of data repeated in exact order as the range it is evaluating.