1) How to create a list based on user input 2) Previous values based on the last occurance

[CV_Sam]

Hi,

I request help on 2 challenges I am facing

1) How to generate a unique list from a col based on user input. Pl refer to the Sheet Question 1 of the attached excel file in which I have explained the question

Just to clarify, I want a formula to be put in one cell only and then the list should appear automatically without copying the formula to below cells.

2) How to find out the previous values of unique names in a col? Pl refer to the Sheet Question 2 of the attached excel file in which I have explained the question with example

Thanks and regards,

Sam

[azumi]

Put in C2 then press all together CTRL+SHIFT+ENTER because this an array formula then copied down and copied cross:

=IF(ROWS($A$1:A1)<=$G$1,IFERROR(INDEX(CHOOSE({2\1},SUMIF($A$2:$A$17,$A$2:$A$17,$B$2:$B$17),$A$2:$A$17),MATCH(LARGE(IF(FREQUENCY(MATCH(SUMIF($A$2:$A$17,$A$2:$A$17,$B$2:$B$17)&$A$2:$A$17,SUMIF($A$2:$A$17,$A$2:$A$17,$B$2:$B$17)&$A$2:$A$17,),ROW($A$2:$A$17)-MIN(ROW($A$2:$A$17))+1),SUMIF($A$2:$A$17,$A$2:$A$17,$B$2:$B$17)+ROW($A$2:$A$17)/10000),ROWS(A$1:A1)),SUMIF($A$2:$A$17,$A$2:$A$17,$B$2:$B$17)+ROW($A$2:$A$17)/10000,),COLUMNS($A1:A1)),""),"")

[kvsrinivasamurthy]

In C2 and D2 copied down. Pl see file.

Please Login or Register  to view this content.

Please Login or Register  to view this content.

[CV_Sam]

Thanks Azumi.

I am getting the error msg saying there's problem with the formula. After i press ok, it highlights 2/1 in the formula. Pl refer to the attached screenshots

Regards,

Sam

[CV_Sam]

Thanks KV.

There seems to be some error in the formula as the values in Col C & D are incorrect & Col C has names repeating. I have added Col F, G & H to tally. Please refer to the attached file.

There's also one more important thing, which I think, I should mention in the orginal post too....I want to create the list, just by putting formula at one cell and the list should appear from that cell downwards automatically without dragging the formula down. For eg>

In C2, I put a formula to create list based on user input in J1 and that list appears from C2 downwards. Similarly, in D2, I put the formula and the values of total profit correspndng to the list in Col C appear.

If there can be one formula in C2 that can create both the list in C2 downwards and correspnding values D2 downwards, that would be even great.

Regards,

Sam

[azumi]

Thanks Azumi.

I am getting the error msg saying there's problem with the formula. After i press ok, it highlights 2/1 in the formula. Pl refer to the attached screenshots

Regards,

Sam

Its an array formula you need to be sure press F2 button to open formula then to hit CTRL+SHIFT+ENTER buttons all together then you can copied down and cross

[CV_Sam]

I pressed ctrl+shift+enter. the error (as shown in the screenshots) still comes. I am using excel 2016. Maybe u used a formula that 2016 doesnt support?

[menem]

For 1st question, if you don't mind to use helpers cells.

You may need to insert some columns too.

check is that the first occurance of each name.
C2
=IF(COUNTIF(A$1:A2,A2)=1,MAX(C$1:C1)+1,0)

calculate total sum of each name (sum only to first occurance)
D2
=IF(C2>0,SUMIFS($B$1:$B$17,$A$1:$A$17,A2),"x")

ranking sumed value
E2
=RANK(D2,$D$2:$D$17)

G2 is just a running number

The answer
H2
=IF($G2<=$L$1,INDEX($A$2:$A$17,MATCH($G2,$E$2:$E$17,0)),"")

I2
=IF($G2<=$L$1,INDEX($D$2:$D$17,MATCH($G2,$E$2:$E$17,0)),"")



and for question #2
E2
=IFERROR(LOOKUP(2,1/($A$1:$A1=$A2),B$1:B1),B2)

F2
=IFERROR(LOOKUP(2,1/($A$1:$A1=$A2),C$1:C1),C2)

Regards.

[kvsrinivasamurthy]

Pl see file .
In Sheet Q1.
Helper column C is used. Helper column may be hidden and you can select any other blank columns helper.
In C2

=IF(COUNTIF($A$2:$A2,$A2)=1,SUMIF($A$2:$A$17,$A2,$B$2:$B$17),"")

In D2

=IF(OR($M$1="",$E2=""),"",IFERROR(INDEX($A$2:$A$17,AGGREGATE(15,6,ROW($A$2:$A$17)/($C$2:$C$17=$E2),COUNTIF($E$2:$E2,E2))),""))

In E2

=IF(ROWS($E$2:$E2)<=$M$1,LARGE($C$2:$C$17,ROW(A1)),"")

Copy down all.

In Sheet Q2.
In E2 then copied to E and F column.

=IF(COUNTIF($A$2:$A2,$A2)=1,B2,IFERROR(LOOKUP(2,1/($A$1:$A1=$A2),B$1:B1),""))