Issue with a formula

richard1234567891011

Board Regular
Joined
Feb 21, 2018
Messages
91
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:

Some videos you may like

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
Joined
Jul 4, 2014
Messages
1,275
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
Joined
Feb 21, 2018
Messages
91
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
Joined
May 28, 2005
Messages
45,255
Office Version
365
Platform
Windows
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
Joined
Nov 6, 2013
Messages
628
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)),"")
 

Sam_D_Ben

Active Member
Joined
Oct 17, 2012
Messages
379
Peter thats cool.
 

richard1234567891011

Board Regular
Joined
Feb 21, 2018
Messages
91
Is this what you mean?
Note that I have added two more rows to the sample data.

Order

ABCDEF
1
2Europe 300 450
3Europe 50 350
4Europe 450 300
5Europe 350 200
6Europe 200 200
7Cina 200 100
8Europe 100 50
9Cina 50 50
10Cina 200
11Europe 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>

Spreadsheet Formulas
CellFormula
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
Joined
May 28, 2005
Messages
45,255
Office Version
365
Platform
Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
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...
Top