sumif, vlookup, index??

[stunt]

Hi,

I have tried combining a number of formulas to give me a combination of text results but am very unsuccessful right now.

Data is as below:
A1 Joe ; B1 A
A2 Peter ; B2 B
A3 Raka ; B3 C
A4 Lew ; B4 A
A5 Ken ; B5 B
A6 Ray ; B6 C
A7 Joe ; B7 A
A8 Peter ; B8 B
A9 Raka ; B9 B
A10 Lew ; B10 C
A11 Ken ; B11 C
A12 Joe ; B12 A
---------------------------------------
D1 Joe ; E1 A,A,A
D2 Peter ; E2 B,B,
D3 Raka ; E3 C,B,
D4 Lew ; E4 A,C
D5 Ken ; E5 B,C
D6 Ray ; E6 C

I want a formula that can give me exact results in E1 to E6 when D1 to D6 are looked up in A1 to A12 & B1 to B12. Note semi colons are just used to separate columns (not part of data).

Thanks
Stuntz

[teylyn]

Hello,

this would be a lot easier if you could just upload your data as an Excel file. Please type in the expected results manually, so we know what the goal is.

[stunt]

Thanks teylyn,

You will note the cell references have changed on the attachement.

Not sure if Column D can be indexed as well as what I intended to do was to run a simple pivot and copy paste values there.

Thanks in advance

stuntz

[teylyn]

Hello,

A formula to do this will be hard to find. It would be easier to write a custom procedure with VBA to do this.

How many rows of data would your column A include in the real world?

If you could live with a different format of the presentation, then a Pivot table would be able to produce a report with just a few clicks. No formula, no VBA.

See attached.

cheers,

[teylyn]

You could use GetPivotData to get the results concatenated in the format you describe. See extra data in attachment.

[stunt]

Thanks teylyn

Am looking at 2,000 columns in Column A.

Rgs
Stuntz

[stunt]

If i VLOOKUP "Peter", it has 2 results but will give only one. =(VLOOKUP(D4,A:B,2,FALSE)&",") = B,.
Is there any way that I can combine IF and INDEX to tell VLOOKUP to give next output of the same result, which will be "B," again. and this should be repeated up to at least 4 options so if there is no result, it should just give or blank for all 4 or for some.

Thanks
stuntz

[teylyn]

Have you had a look at my file in post #5 at all? It's all in there. I don't know how else to tell you.

[stunt]

I had a look but i still want something that is automated rather than doing Pivots. Have a look at the attachment. I did some search and got pieces here and there but dont seem to give me the desired result accurately. please help.

stuntz

[teylyn]

I've given you a working solution.

You can put the pivot table on another sheet and hide it, if you want. Pivot tables can be configured to grow and shrink with the source data automatically and can be maintenance free, once they are set up and configured just so.

You can use the GetPivotData formula I provided to get exactly the result you outlined.

I don't know how else to help you.

You may WANT to achieve a solution with a formula, but that does not necessarily mean that it CAN actually be done.

Talk to Microsoft and see how far you'll get.

[DonkeyOte]

I'm with Teylyn in so far as formula approaches will prove cumbersome - to illustrate - using your sample file:

C4:
=IF(ISNUMBER(MATCH($A4,$A$3:$A3,0)),LOOKUP(2,1/($A$3:$A3=$A4),$C$3:$C3),"")&","&$B4
copied to C15
(creates a running string of results for given name)

D2:
=SUMPRODUCT(($A$4:$A$15<>"")/COUNTIF($A$4:$A$15,$A$4:$A$15&""))
gives unique name count

D4:
=IF(ROWS(D$4:D4)>$D$2,"",INDEX($A$4:$A$15,MATCH(1,INDEX(($A$4:$A$15<>"")*ISNA(MATCH($A$4:$A$15,$D$3:$D3,0)),0),0)))
copied to D15

E4:
=IF($D4="","",REPLACE(LOOKUP(2,1/($A$4:$A$15=$D4),$C$4:$C$15),1,1,""))
copied to E15

If you tried the same without use of Column C things would become really convoluted.

Note: in addition to being unusual in construct the above aren't very efficient either.

[grmhsr]

Hi,

one more easiest way to get the result is

1. "Sort" the data based on name.
2. In c3 use 'if(a2<>a3,b2,b2&c3)' - column 'a' is names, column 'b' is the grade.
3. Then I hope u can just do a 'vlookup' and get the required result in the non duplicate list.

Hope this helps you. Have attached a file for reference but not done the 'vlookup'.