• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Duplicate Lookup

V

vinu

Member
Hello,


I have a data in two colums like

A 10

B 20

A 30

C 15

D 20

B 25

A 40


If I use Vlookup will get for lookup up value"A" - it will pick up corresponding first occurance only ie,10 instead of 10,30,40


If I need like this how do I get it?


Regards,

Vinu.
 
Hi Vinu,


Are you trying to have the lookup values listed as you have shown or summed?


If you are looking to sum all occurances you can use SUMIF(RANGE,CRITERIA).


I would imagine that if you want the lookup values listed you will need some VBA.
 
You should be able to work something out using the code on the link below:


http://www.ozgrid.com/VBA/occurrence-lookup.htm
 
I need corresponding value(not the sum) without using VBA.

Is there any possibility by using Array formual or any other formula.


Regards,

vinu
 
also have a look at the last post by Jive, towards the bottom of this page

http://chandoo.org/wp/2008/05/28/how-to-add-a-range-of-cells-in-excel-concat/

That should do what you want
 
Vinu,


The two links that Hui and I have referenced are for User Defined Functions (UDFs). Whilst these are written VBA code you can use them as straight formulae within your speadsheet.


Copy the code into the VBE (Alt+F11 - add module into your workbook and copy the code in),save and then simply type the formula into your spreadsheet.


The one Hui has directed you to looks like a brilliant function that should certainly do what you need.
 
Thanx Clarity


Vinu

If you are looking for a cell to contain an answer like 10,30,40

what I have suggested will help you

or do you want to be able to pick from the values 10,30,40 based on a value "A" elsewhere

Otherwise can you please explain what exactly you are after as the display in a cell
 
Hi all,


I wud like to see like this.

If i want to lookup and pick the numbers based on letters - I use vlookup, but it picks only 10 for All "A". I want to get

A 10

A 30

A 40


Vlookup wont give you this, so is there any formula to get this.??

I used below array formula but not able to find correct ans for few items.

=INDEX($A$1:$B$12,SMALL(IF($A$1:$A$12=$I19,ROW($A$1:$A$12)),IF(I18<>I19,ROW($1:$1),ROW(1:1)-1)),2)
 
Vinu

Try the following,

Select the data Area A1:B7 in your example

Insert a Pivot Table with th e following fields

Report Filter Column A

Sum Values Sum of 10 (Col B) Dont worry if it shows Count of 10 instead of Sum of 10,

Insert the Pivot Table some where

On the Report Filter, Select Multiple Items and then just select Value A

Double Click on the value in the main part of the pivot table

A New Page will open with the values listed as you requested
 
You must log in or register to reply here.
Back
Top