# Issue with a formula

#### richard1234567891011

##### Board Regular
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.

Last edited:

### Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes

#### jorismoerings

##### Well-known Member
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)

#### richard1234567891011

##### Board Regular
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.

#### Peter_SSs

##### MrExcel MVP, Moderator
Is this what you mean?
Note that I have added two more rows to the sample data.

Excel Workbook
ABCDEF
1
2Europe300450
3Europe50350
4Europe450300
5Europe350200
6Europe200200
7Cina200100
8Europe10050
9Cina5050
10Cina200
11Europe350
12
Order

#### kvsrinivasamurthy

##### Well-known Member
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)),"")

#### Peter_SSs

##### MrExcel MVP, Moderator
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

... I want to repeat 200 because we are in another continent.

#### Sam_D_Ben

##### Active Member
Peter thats cool.

#### richard1234567891011

##### Board Regular
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

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:60px;"><col style="width:23px;"><col style="width:56px;"><col style="width:22px;"><col style="width:26px;"><col style="width:46px;"></colgroup><tbody>
</tbody>

 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)),"")

<tbody>
</tbody>

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

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

#### Peter_SSs

##### MrExcel MVP, Moderator
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.

1,102,731
Messages
5,488,543
Members
407,645
Latest member
suyoggore

### This Week's Hot Topics

• Timer in VBA - Stop, Start, Pause and Reset
[CODE=vba][/CODE] Option Explicit Dim CmdStop As Boolean Dim Paused As Boolean Dim Start Dim TimerValue As Date Dim pausedTime As Date Sub...
• how to updates multiple rows in muliselect listbox
Hello everyone. I need help with below code. code is only chaning 1st row in mulitiselect list box. i know issue with code...
• Delete Row from Table
I am trying to delete a row from a table using VBA using a named range to find what I need to delete. My Range is finding the right cell. In the...
• Assigning to a variable
I have a for each block where I want to assign the value in column 5 of the found row to the variable Serv. [CODE=vba] For Each ws In...
• Way to verify information
Hi All, I don't know what to call this formula, and therefore can't search. I have a spreadsheet with information I want to reference...
• Active Cell Address – Inactive Sheet
How to use VBA to get the cell address of the active cell in an inactive worksheet and then place that cell address in a location on the current...