Issue with a formula

[richard1234567891011]

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.

 

[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)

 

[richard1234567891011]

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]

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

Excel Workbook
	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
Order

 

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

 

[Peter_SSs]

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]

Peter thats cool.

 

[richard1234567891011]

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

Order

	A	B	C	D	E	F
Europe
Europe
Europe
Europe
Europe
Cina
Europe
Cina
Cina
Europe

<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>
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]1[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]2[/TD]

[TD="align: right"]300[/TD]

[TD="align: right"]450[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]3[/TD]

[TD="align: right"]50[/TD]

[TD="align: right"]350[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]4[/TD]

[TD="align: right"]450[/TD]

[TD="align: right"]300[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]5[/TD]

[TD="align: right"]350[/TD]

[TD="align: right"]200[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]6[/TD]

[TD="align: right"]200[/TD]

[TD="align: right"]200[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]7[/TD]

[TD="align: right"]200[/TD]

[TD="align: right"]100[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]8[/TD]

[TD="align: right"]100[/TD]

[TD="align: right"]50[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]9[/TD]

[TD="align: right"]50[/TD]

[TD="align: right"]50[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]10[/TD]

[TD="align: right"]200[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]11[/TD]

[TD="align: right"]350[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]12[/TD]

</tbody>

Spreadsheet Formulas

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]

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.