# Thread: Issue with a formula Thanks: 0 Likes: 0

1. ## Issue with a formula

Hi guys,

I have an Issue with a formula.

I have to put the data in order. The formula in cell F2 is LARGE(C2:C8;1), from cells F3 to F7 is the array in the picture. The array is ok, the issue is that is not working exactly as I want. The issue is underline in the picture, I want to repeat 200 because we are in another continent. Guys, Keep in mind that these are a very simplify data.
I need a general formula, not stuffs like if<>Europe or stuffs like that. I need and Array like this, that repeat data only based on the difference in value from the column on the right. I hope this is clear

Thank you very much guys.

2. ## Re: Issue with a formula

Hi,

Maybe i'm over simplifying this but this looks like an ordinary rundown of values in column C to me.
If that's case why not use tyhe following: =LARGE(\$C\$2:\$C\$9;ROW()-1)

3. ## Re: Issue with a formula

Originally Posted by jorismoerings
Hi,

Maybe i'm over simplifying this but this looks like an ordinary rundown of values in column C to me.
If that's case why not use tyhe following: =LARGE(\$C\$2:\$C\$9;ROW()-1)

Dear jorismoerings,

Thank you for your answer. But no it is not the same. The issue with =LARGE(\$C\$2:\$C\$9;ROW()-1) is that you will repeat ALL the value that are equal. I don't want to repeat all equal value, I want to repeat specific equal value, in this simplified example, the only values with the same continent.

Thank you very much.

4. ## Re: Issue with a formula

Is this what you mean?
Note that I have added two more rows to the sample data.

Order

 A B C D E F 1 2 Europe 300 450 3 Europe 50 350 4 Europe 450 300 5 Europe 350 200 6 Europe 200 200 7 Cina 200 100 8 Europe 100 50 9 Cina 50 50 10 Cina 200 11 Europe 350 12

 Cell Formula F2 =IFERROR(AGGREGATE(14,6,C\$2:C\$11/(MATCH(A\$2:A\$11&C\$2:C\$11,A\$2:A\$11&C\$2:C\$11,0)=(ROW(C\$2:C\$11)-ROW(C\$2)+1)),ROWS(F\$2:F2)),"")

Excel tables to the web >> Excel Jeanie HTML 4

5. ## Re: Issue with a formula

May be this.
In F2 the cop down.

=IFERROR(AGGREGATE(14,6,\$C\$2:\$C\$9/(\$A\$2:\$A\$9="Europe"),ROWS(\$F\$2:\$F2)),"")

6. ## Re: Issue with a formula

Originally Posted by kvsrinivasamurthy
May be this.
In F2 the cop down.

=IFERROR(AGGREGATE(14,6,\$C\$2:\$C\$9/(\$A\$2:\$A\$9="Europe"),ROWS(\$F\$2:\$F2)),"")
That doesn't seem to achieve this

Originally Posted by richard1234567891011
... I want to repeat 200 because we are in another continent.

7. ## Re: Issue with a formula

Peter thats cool.

8. ## Re: Issue with a formula

Originally Posted by Peter_SSs
Is this what you mean?
Note that I have added two more rows to the sample data.

Order

 A B C D E F 1 2 Europe 300 450 3 Europe 50 350 4 Europe 450 300 5 Europe 350 200 6 Europe 200 200 7 Cina 200 100 8 Europe 100 50 9 Cina 50 50 10 Cina 200 11 Europe 350 12

 Cell Formula F2 =IFERROR(AGGREGATE(14,6,C\$2:C\$11/(MATCH(A\$2:A\$11&C\$2:C\$11,A\$2:A\$11&C\$2:C\$11,0)=(ROW(C\$2:C\$11)-ROW(C\$2)+1)),ROWS(F\$2:F2)),"")

Excel tables to the web >> Excel Jeanie HTML 4

Your formula is working!! Thank you very much!!!!

9. ## Re: Issue with a formula

Originally Posted by richard1234567891011
Your formula is working!! Thank you very much!!!!
You're welcome.

BTW, best not to fully quote long posts as it makes the thread harder to read/navigate. If you want to quote, quote small, relevant parts only.