sorting unique cells in new column

[Altar46]

Hi, I'm new excel user
Have both version at once (2003 and newest 2010).
My problem is extracting unique names in new column and and summarize his values.

col_A col_B col_C col_D col_E
-------------------------------------------------
DDD.......10
CCC.........5
AAA.......10
BBB.......10
AAA.........5
CCC.........5
CCC.......10
BBB.......10
....

In col_C want sorted unique names from col_A (removing empty cels)
In col_D I want summarized all 5's for given name in col_A (for C = 5+5 = 10)
In col_E I want same, but for values 10.( for B = 10+10 = 20)

so the outcome will look something like

col_A col_B col_C col_D col_E
-------------------------------------------------
DDD.....10.....AAA.......5........10
CCC.......5.....BBB.................20
AAA.....10.....CCC......10........10
BBB.....10.....DDD.................10
AAA.......5
CCC.......5
CCC.....10
BBB.....10
....
For extraction I played with formula
{=INDEX(List,MATCH(0,COUNTIF(($B$1:B1,List),0))} but something is wrong and I do not know what to do next....
Can someone help me with am problem please. I don't know is it possible at all.

Kind regards, Bojan

[zbor]

Here, try this:

(note: it won+t work for XL 2003)

[Altar46]

Thank you for quick reply zbor, ...

I'm sending you whole example with chosen names (from everywhere)....
As I adding names on daily basis (hardly more than 300) can formula recognize new entries and work on them
(I know I asking to much - but you was so kind...)


thank you & kind regards
Bojan

PS I'm: using date field (but not time) before name column with names...

[DonkeyOte]

@Altar46,

I would suggest you upload a sample file to illustrate.

[sunflowers]

I have attached a test excel file to show your example.
Attached Files :

[sunflowers]

Please see attachment file :

[zbor]

Here is green (sunflowers) and blue (Donkeyote and sligthly mine) solution.

If you use SUMIFS function excel will work faster than with SUMPRODUCT approach, but it work only for XL 2007+.

Also, (in blue formula) you can add up to 10.000 names. If you want more you just need to extend ranges in formulas i.e. from $B$2:$B$10000 to $B$2:$B$30000.

[Altar46]

Thanks guys, I did not know that the community around Excel so kind and above all fast. That all I needed. I will use blue formula wich work perfectly for me. When I complete all entrance just what I have to do is copy names in new column and sort them.

Will try green solution for Excel 2003 which is working one. (On my laptop, Excel 2010, perform your blue formula excelent.)
On green formula found no updatas for new entrance.... will come back on that matter after testing - need some time....

DonkyOte, sunflowers,zbor - you are great - thanks again...

[Altar46]

Dear friends / zbor,

Green part of formula in your upload works ok in excel 2003 but cannot insert new data. Kindly allow me to put new names until 300 (don't need more - can be fixed 300) and Unique column is perfect because of sorting names on fly. Sorry of my ignorance.
I'm in the middle of the sea - off Nigeria - so help you provide is priceless.

Kind regards
Bojan

[Altar46]

Dear friends / zbor,

Green part of formula in your upload works ok in excel 2003 but cannot insert new data. Kindly allow me to put new names until 300 (don't need more - can be fixed 300) and Unique column is perfect because of sorting names on fly. Sorry of my ignorance.
I'm in the middle of the sea - off Nigeria - so help you provide is priceless.

Kind regards
Bojan

[sunflowers]

You put name List = Sheet1!$A$2:$A$300 and name List2=Sheet1!$B$2:$B$300
Now You can Insert new Data and Remove #N/A error by ISNA() Function
See attachment file :

[tigertiger]

Green part of formula in your upload works ok in excel 2003 but cannot insert new data. Kindly allow me to put new names until 300 (don't need more - can be fixed 300) and Unique column is perfect because of sorting names on fly. Sorry of my ignorance.

Exactly, Thanks for sunflowes' formula, it is more flexible, it can not only sort names on fly but also remove blank names.

You put name List = Sheet1!$A$2:$A$300 and name List2=Sheet1!$B$2:$B$300
Now You can Insert new Data and Remove #N/A error by ISNA() Function
See attachment file :

To improve the file for remove array-formula and #N/A at extra cells in list unique, I make combination your and DonkyOte's solution (thanks for both of you and zbor) to form a new solution in attached file version 2nd.

@Altar46: Plz, paying attention to the name List = Sheet1!$A$2:$A$300 and List2=Sheet1!$B$2:$B$300 by press Ctrl+F3 - it is easy to adjust their reference corresponding your original data.

[Altar46]

Exactly, Thanks for sunflowes' formula, it is more flexible, it can not only sort names on fly but also remove blank names.



To improve the file for remove array-formula and #N/A at extra cells in list unique, I make combination your and DonkyOte's solution (thanks for both of you and zbor) to form a new solution in attached file version 2nd.

@Altar46: Plz, paying attention to the name List = Sheet1!$A$2:$A$300 and List2=Sheet1!$B$2:$B$300 by press Ctrl+F3 - it is easy to adjust their reference corresponding your original data.

Thanks everybody - green formula work great in Excel 2003.

Could you please help with another solution. (not hard one I hope)
thanks in advance - Bojan

[zbor]

Here...

Doesn't matter is it A in the cell or not...

For more values extend range.

[Altar46]

Yes zbor - that's it.

What I can say?..Thank you professor...

[Altar46]

Something is wrong...
I cannot copy LOOKUP function. When I tried to copy your =LOOKUP(2:1/(C4:$C$10000=5):$A$4:$A$10000)

I get result
=LOOKUP(1:2/($C12:O$10000=5):$A$4:$A$10000) ?
I tried changed some parameters - no result.
Positions of calculated range is column P for starting numbers - which is OK
and last numbers in cell next to starting value - column Q.

Apparent that I wrongly mentioned that my calculation starting from row 4 instead of row 3
LOOKUP function does not working in both cases...

I cannot manually changed part of function "2:1" in "1:2".....

regards, Bojan

[zbor]

Range is A column.
5 and 10 numbers are P column.

Then:

=LOOKUP(2; 1/($P$12:$P$10000=5);$A$12:$A$10000)

[Altar46]

unfortunately - not working

I included example 3

regards, Bojan

[zbor]

Do you see ; in my formula and you convert it into : ?
I paint in red all you need to change... Not 1:2 but 2;1

=LOOKUP(2;1/($C3:C$10000=5);$A$3:$A$10000)

[Altar46]

Do you see ; in my formula and you convert it into : ?
I paint in red all you need to change... Not 1:2 but 2;1

=LOOKUP(2;1/($C3:C$10000=5);$A$3:$A$10000)

I did ... but... I have #VALUE! error

[zbor]

If you using other regional settings, than croatia, try , instead of ;

=LOOKUP(2,1/($C3:C$10000=5),$A$3:$A$10000)

[Altar46]

If you using other regional settings, than croatia, try , instead of ;

=LOOKUP(2,1/($C3:C$10000=5),$A$3:$A$10000)

Not working - even worse...
regional settings - EN English USA - location Nigeria

[Altar46]

Wait a moment - maby is working... wait pls

[Altar46]

Must have been witches around + myself
Suddenly everything works....

Hard to say, but must be my fault.....

million thanks zbor.... have a nice day